I have a number of Delphi applications, which use an MSSQLServer database as backend. These applications use ADO to connect to the database.

Now I am checking if these applications are possible to move over to Lazarus/FPC. I realize that I cannot just recompile them since ADO is a COM system on Windows, which is not going to be cross-platform for example.

Is there an alterate connection mechanism that can be used towards MSSQLServer which works also on Linux so the applications can be made cross-platform? The database itself will of course remain on a Windows server.

Sorry, I found this by searching:
http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL

I will test that as soon as I get my lazarus to work properly with breakpoints (see next post).

Problem....

In the aforementioned WIKI page it is mentioned that one could use ODBC towards MSSQL by:
"just use the SQLdb component that is included"

In order to check how to do this I went to the SQLDb WIKI page at http://wiki.lazarus.freepascal.org/SQLdb_Package

Here is stated the following:
"The SQLdb package contains FPC units to access a number of SQL databases. It is "packaged" as sqldblaz.lpk in Lazarus, and the components can be found on the "SQLdb" tab. "

But in my new Lazarus built from the SVN 0_9_30_fixes branch there simply is no such tab....

Is the WIKI page in error or am I doing something stupid? Or rather (since right now I have not done anything at all): am I NOT doing something that is obvious to all Lazarians?

If you build using make bigide, it will get included. You can install it by going to install/uninstall packages under the package menu.

Henry

I have built Lazarus using these commands in a batch file (on Windows):

set PATH=C:\Programs\fpc\2.4.2\bin\i386-win32
make.exe clean all

Do you mean that I should do this instead:

set PATH=C:\Programs\fpc\2.4.2\bin\i386-win32
make.exe bigide

I am not very used to this way of working so I made the batch file some time ago to make sure the correct FPC and command was used.

I personally don't know whether bigide is the correct approach (I don't know whether it installs ALL available packages or only those selected).

I'd suggest you the following (Note: the mentioned captions might only be similar to yours, because I'm using a German Lazarus): Go to "Packages=>Configure installed packages" and search whether SQLDBLaz is in "installed" or in "not installed".
If it's in the second one, then select and and click "install selected". Then you just do a "Save and recompile IDE".
If the package is listed in the list of installed packages, but with a plus in the icon you need to rebuild your IDE from within the IDE (you can either trigger a change in that dialog to enable "save and recompile IDE" or you can just use "Tools=>Recompile IDE".

Regards,
Sven

Did this and indeed now I have the SQLdb tab on my lazarus. Thanks a bunch!

Bo Berglund wrote

Did this and indeed now I have the SQLdb tab on my lazarus.

More problems:
I have created a test application and did a cut-and-paste operation on the example on this page:
http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_and_MSSQL

After changing the specifics of the database (server, name, user, login) in the code I tried to run it in Lazarus debugger.

After fixing the uses clause so it will compile and after creating a datasource pointing to my database I tried running against a real MSSQLServer database but it failed miserably with an exception on line:

  query.Open;

The exception text is:
'Cannot open a non-select statement'

The database I run against uses stored procedures to select data and I have specified a call to such a procedure in the test program as follows:

query.SQL.Text := 'EXEC SelectDongleData @SerialNo=2485';

This works fine in my Delphi program where I use an ADO connection. There the query object is defined as
qryADO: TADOQuery;

and the code to get the records is:

 sSql := 'EXEC SelectDongleData @SerialNo=' + IntToStr(KeyNo);
 qryADO.SQL.Text := sSql;
 qryADO.Open;

What did I do wrong regarding the use of TSQLQuery to get this error?

Use query.ExecSQL instead of query.Open even in Delphi!

Marcos Douglas

Not if the stored procedure returns a recordset....

Bo Berglund wrote

> >Use query.ExecSQL instead of query.Open even in Delphi! Not if the stored procedure returns a recordset....

And with this whole mess I would end by saying: And that is why you will not see me ever use a Stored Procedure! 99.99% of the time they contain business logic, which doesn't belong in the data layer of your application in any case - you can't unit test that business logic.

Just my 2? worth...

2011/1/20 Graeme Geldenhuys

Why can't that business logic be unit tested ? I did it myself many times. It results into a more maintenable code(Object Pascal and SQL) and a high perfoming applications.
Stored Procedures have their uses, just do not abuse them, as with other technologies/features.

2011/1/21 Inoussa OUEDRAOGO :

This is off topic, please use lazarus-other list.

Vincent

Apologies to Vincent, I just couldn't resist, I need to say that Stored Procedures in some cases are absolutely necessary, even for business logic.

Sometimes you need to do a whole lot of processing, and if you have to drag all the data down a slow WAN link for processing, the app will fail miserably.

When you need to poll the database every second for some events, it is much easier to do the processing server side and return the results.

There is also the need to keep critical logic server side for security reasons. Have you ever viewed your apps strings at runtime to see how much of the programs SQL logic is exposed?

You might even see the odd password there. Scary.

2011/1/21 Frank Church :

Apologies to Vincent, I just couldn't resist, I need to say that Stored

Well, I thought the people subscribed to the list where grown ups, that don't need enforced moderation ...
I enabled moderation on this thread.

Vincent

Vincent Snijders wrote

This is off topic, please use lazarus-other list.

My question was why a database component that ships with *lazarus* would not work with a perfectly legitimate SQL command (which we have used for years in Delphi with ADO).

I assumed at first that I was doing something illegal or so for lazarus/fpc so I asked here. Did not think it would be off-topic.

Is there a list of acceptable topics somewhere so one could check before posting?

2011/1/21 Bo Berglund :

On Fri, 21 Jan 2011 13:14:15 +0100, Vincent Snijders wrote:

You forgot to quote this part:

The above is off topic for the Lazarus: Why Graeme doesn't use stored procdures and what valid and invalid reasons he may have for that.

That is on topic. How to use stored procedure with Lazarus.

I assumed at first that I was doing something illegal or so for lazarus/fpc so I asked here. Did not think it would be off-topic.

AFAIK, in this thread you did not post off topic. And I did not say so.

Is there a list of acceptable topics somewhere so one could check before posting?

No, there is no such list. Most people can decide for them self what is on topic for the general Lazarus list. If they make a mistake and I fear more may follow, I help to remind them that this the Lazarus list and a discuss about anything list.

Vincent

To get back on topic:

Quite a number of Lazarus components are not designed as a toolkit to serve a wide range of programmer needs. It is clear that a number of them are developed for the programmers own needs. Even if they are capable enough to serve your needs, those features may not be well documented.

They turn out to be 'useful enough' for many others and are included in the Lazarus package.

If sqldb cannot serve your needs, or that aspect is not well documented or lacks examples, try zeoslib. I have used zeoslib, which has been around for a long time, and I suspect it will be capable of what you want.

PS. You can also check the forum for more sqldb examples.

2011/1/21 Frank Church :

I think Zeoslib use ADO on Windows, but on Linux it uses ODBC, AFAIK.

Marcos Douglas

While I will not comment on the general validity of your statement, for SQLDB it is certainly not correct. SQLDB was developed exactly to serve a wide range of programmer needs. Of course, the engines we use ourselves will be tested best, this is only natural.

That said, SQLDB is under continuous development; The current case was not one which we were familiar with, so it is entirely possible that it doesn't work currently. That doesn't mean we're not open to fixing it, and enhance SQLDB in the process.

So, in that spirit:
Unless I have missed it in the SQL syntax sidetrack debate, I still haven't seen the original poster's error message, which I asked for in the very first reply to his original post.

Without the error message, we cannot begin to start looking for a solution.

Michael.

work currently. That doesn't mean we're not open to fixing it, and enhance SQLDB in the process.

Is SqlDB the correct code to add Firebird service features? By service features, I mean specific features of Firebird like user management API, Event listening API, etc. FBLib contains many of those implementations (latest FBLib is in tiOPF repository as the original author of FBLib fell off the planet).
I don't think SqlDB is the correctly place - hence I ask first, but I also don't know where else to add such code (our just leave it in our company repository - which is a bit of a waist).

I can now answer that after I have restarted the virtual machine where I was investigating this:

The error message captured as the Exception.Message is:

'Cannot open a non-select statement'

The code I executed at this time is (mostly copied from the wiki page):

[code]
procedure TForm1.DatabaseTest;
var
  S: String;
  conn: TODBCConnection;
  query: TSQLQuery;
  transaction: TSQLTransaction;
begin
  conn := TODBCCOnnection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);
  try
    try
      conn.HostName := '<name of our db server>';
      conn.DatabaseName := '<name of odbc data source>';
      conn.Transaction := transaction;
      conn.UserName:= '<user login name>';
      conn.Password:= '<password>';
      query.DataBase := conn;
      query.UsePrimaryKeyAsKey:=false;
      query.SQL.Text := 'EXEC SelectDongleData @SerialNo=2485';
      query.Open; <== Exception here!
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('AppNo').AsString + #13#10;
        query.Next;
      end;
    finally
      query.Free;
      conn.Free;
    end;
  except
    on E: Exception do
    begin
      S := E.Message; <== Exception message text picked up here
      ShowMessage(S);
    end;
  end;
  Memo1.Text:= S;
end;

[code]

I might add that when I replace the SQL.Text entry with the actual procedure code for the case I am testing here (the procedure makes different processing depending on parameter values) then I get proper database execution.
In this case the procedure executes a SELECT statement with an inner join so I entered this into the SQL.Text property. So the problem is entirely due to the inability of the component to handle an EXEC statement that actually returns records.

I would certainly be very intereated in a fix to the SQLdb system for this problem, rather than starting to investigate the innards of ZEOSlib....

Bo Berglund wrote

query.SQL.Text := 'EXEC SelectDongleData @SerialNo=2485';

Having just spent a bit of time with Google and MS docs: is EXEC standard? What happens if you use CALL or EXECUTE? What happens if you use positional rather than named parameters?

Mark Morgan Lloyd wrote

Having just spent a bit of time with Google and MS docs: is EXEC standard?

Yes, I would say EXEC is standard in MS SQL-Server. Or at least we used it like that in Delphi and SQL-Server's management tools.

'EXECUTE SelectDongleData @SerialNo=2485'
'EXECUTE SelectDongleData 2485'

Both work, but return only one record (there should be 4). Or at least the code loop below only traverses once:

  while not query.EOF do
  begin
    S := S + query.FieldByName('AppNo').AsString + #13#10;
    query.Next;
  end;

'CALL SelectDongleData 2485'

==> Exception: 'Cannot open a non-select statement'

Seems like SQLdb accepts EXECUTE in place of EXEC but when running the call it fails to observe that more than one record is returned.

CALL is probably not recognized at all...

Attached is a possible patch. I don't have MS-SQL (doesn't run on Linux) so I can't test. Please apply it to packages/fcl-db/src/sqldb/sqldb.pp and report whether this helped you solve your problem. If so, I will commit it to SVN.

Michael.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: sqldb.diff
Type: text/x-diff
Size: 520 bytes
Desc:
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20110123/a6920fb6/attachment.bin>

2011/1/23 Michael Van Canneyt :

The comment is not correct, both EXEC and EXECUTE are allowed: http://msdn.microsoft.com/en-us/library/aa258848%28v=sql.80%29.aspx

Vincent

I suspect that it depends on how the client is connecting, i.e. ADO vs ODBC etc.

I don't use MS SQL Server and I have very little incentive to research it, but when I Googled this last night I found specific MS ODBC information relating to EXECUTE and if I recall correctly to CALL, and did not see anything about EXEC. I also found specific discussion of what API within ODBC could be used for invoking stored procedures in the most efficient way, and I presume that the client-side driver is parsing the start of the query to decide how to handle it.

Zitat von Mark Morgan Lloyd :

for me, in last 15 years exec works better then execute conneted to MSsql , I found no problem in

Query.Open; .....; Query.Close; and Query.execute; as needed.

and next you might think of using query.SQL.Text := 'EXEC SelectDongleData @@SerialNo=2485';

"Dipl. Phys. J?rg Schl?ger" wrote

and next you might think of using query.SQL.Text := 'EXEC SelectDongleData @@SerialNo=2485';

Tried adding an extra @ to parameter.
Results are as follows:

EXEC SelectDongleData @@SerialNo=2485

'Cannot open a non-select statement'

EXECUTE SelectDongleData @@SerialNo=2485

'Could not execute statement. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: 42000; NativeError: 8145; Message: [Microsoft][ODBC SQL Server Driver][SQL Server]@@SerialNo is not a parameter for procedure SelectDongleData.;'

How do I apply this file to my lazarus? It appears to deal with the file sqldb.pp so I tried to look in my lazarus installation dir but I could not find this file below my main dir:
C:\Programs\lazarus_fix_30 (where I checked out the lazarus sources)

All a search found was the file registersqldb.ppu, which of course is not the same as what the patch targeted.

Marcos Douglas wrote

> PS. You can also check the forum for more sqldb examples. I think Zeoslib use ADO on Windows, but on Linux it uses ODBC, AFAIK.

Only with Delphi. FPC misses ADO headers that Zeos bases on.

I started translating those, but do it only on and off.

Zitat von Bo Berglund :

of course you have to change your sql statement to define @@SerialNo

procedure SelectDongleData @@SerialNo int AS

Why?
It is already defined as follows:

CREATE PROC dbo.SelectDongleData
@SerialNo int = 0
AS

I don't see the reason to add another @ in the declaration. Can you explain this a bit?

Zitat von Bo Berglund :

I agree there is no reason, but it is from MS with historie local variable: declare @SerialNr int
in versions before MSsql 2008 @@ referr a GLOBAL variable that is the different depending on server and client I had some problems with local variable declaration on some client config/version.

After making a search on my complete disk I found the file sqldb.pp in the FPC2.4.2 folder tree.
That is, it was not found in the lazarus tree at all, is this OK?

After adding the new lines from the patch I want to test it, but I don't know how I can make lazarus use the new file.... Just compiling my example again seems not to work.

What can I do to make it "take"? Do I need to compile FPC (since the file is part of the FPC sources) or do I rebuild the lazarus itself? Or maybe both?

I have actually no clue in this respect. I always worked in Delphi before and there the compiler checked the files and recompiled if needed, but with lazarus it seems like you have to do this manually...

Now I have rebuilt Lazarus with the changed sqldb.pp file in place in the FPC sources. I used the Lazarus function to build itself. But the code still rejects the EXEC verb with the same error message as before.
I have probably not succeeded to make lazarus use the modified sqldb.pp source file....

2011/1/23 Bo Berglund :
Now I have rebuilt Lazarus with the changed sqldb.pp file in place in

That is correct, you have to build the fpc units separately first. When compiling in Lazarus, Lazarus doesn't use the fpc source directly, but only the compiled sqldb.ppu and sqldb.o

Vincent

Any ideas how this "build the fpc units separately first" is accomplished?
I have installed FPC 2.4.2 from the binary distribution (on Windows) and then to make Lazarus happy I also separately downloaded the FPC 2.4.2 sources. It complained that it could not find them when I first started lazarus after building from sources.

I know how to build Lazarus, but not how to build FPC...

See this tutorial to build FPC and Lazarus on Windows: http://wiki.lazarus.freepascal.org/Installing_Lazarus#Compiling.2Finstalling_FPC_and_Lazarus_from_Sources_of_SVN_.28Win32.29

Marcos Douglas

The instructions tell me to download FPC 2.5.1 but I don't want that, I want to just *recompile* my 2.4.2 with the single source change being the patch of sqldb.pp I received in a post here.

It also tells me to get binutil stuff from a fpc fixes branch, http://svn.freepascal.org/svn/fpcbuild/branches/fixes_2_4/install/binw32 using TortoiseSVN, but when I try that exactly nothing happens. :-(

Then I tried this command line command (all on one line):

svn co
http://svn.freepascal.org/svn/fpcbuild/branches/fixes_2_4/install/binw32 binw32

But it stalled completely, nothing was shown unlike when I retrieved the lazarus sources.

Next:
The URL when clicked normally in a browser opens a page with a set of exe files, though.

Am I supposed to click and download these one by one? It takes about a minute or more for each of the 4 files I have tried and there are many more (actually 29 files more). Why isn't there a zipfile with all of these binaries so that one can download a single file to get the binutils?

Then I am asked to get 2.5.1 trunk sources from SVN and make a clean install from these....

Isn't there a simpler way????

When I already have a working fpc 2.4.2 can't I build a new fpc where the single change is the sqldb.pp modification???? Isn't there a command similar to rebuilding lazarus that works for FPC itself?
These instructions seem to be geared towards building FPC from scratch without having anything at all....

Maybe it would work if you simply put all units from the directory of sqldb into the lazarus/lcl directory and rebuild the LCL then.

FWIW: i'm pretty sure you have already gotten an answer to this but i just wanted to take a moment to point out that this type of patch would be like a direct patch to delphi, which you are used to working with, and using the existing delphi to compile a new delphi... now, of course, you have not been able to do this with delphi but you can with FPC and Lazarus... in the delphi world, you have had to wait on the delphi folks to release a new delphi version... this is one of the advantages of FPC and/or FPC/Lazarus over the delphi and other similar commercial packages' worlds ;)

actually, since it concerns components, you WERE able to do this with Delphi. The entire VCL's sourcecode is included. I once worked for a company that had the luminous idea to add a few properties to TControl (to add extra security/visibility/enable). Henceforth, for that project, they required to rebuild the entire VCL and every used 3rd and other party components.

So just to state: you already HAD the possibility. Whether it was great design or wise to do so is another story. :-)

what you could NOT do is rebuild the IDE or compiler/debugger/tools. (you could override a lot in the IDE by custom packages though)

kind regards,
Dimitri Smits

Well, the comparison is a bit off in that here all that was done is to change 3 lines of code in the sqldb.pp file.
It is more like a modification of a VCL source file in Delphi, which we have done several times.

All we need to do in Delphi is to modify the source file and command a build of *our application* for this change to be effected. The IDE or the compiler is never involved in this at any stage.

Another possibility in Delphi is to subclass the VCL component into a new one, but that has not been suggested here. We have done that with the TListView for instance, to get better usability. It involves making our own component class where we inherit from TListView and add our own minor stuff, then install this new component into the IDE (without recompiling the IDE itself).

The problem I see here is that whatever small change you want to make in something not related to the IDE at all you are forced to rebuild lazarus itself. And in this particular case rebuilding Lazarus does not even help, it seems like one has to rebuild FPC itself. Very strange to me.

And btw I have NOT gotten a working solution to the problem of testing the change in sqldb.pp....

I just realized that it could be a firewall issue and sure enough, I had a VPN channel open to the company and the svn call was apparently blocked by the VPN. When I shut down VPN the svn command worked fine.

Now I got the binutils in one svn command. :-)

Sorry to have blamed the fpc svn! It was an error on my side.

Op 2011-01-24 08:43, Bo Berglund het geskryf:

And btw I have NOT gotten a working solution to the problem of testing the change in sqldb.pp....

All you need to do is recompile SqlDB, no need to recompile Lazarus or the FPC compiler! Lazarus simply uses the compiled units *.ppu files created by FPC when it compiled the FCL (and thus SqlDB), So when you recompile SqlDB, make sure you install the new *.ppu files over the existing ones - wherever they may be on your system (each developer can have there own setup, so I can't be more specific).

Alternatively, add the SqlDB source path to your project, then when you compile your project, it will compile the SqlDB code too - as if it was part of your project source code.

These are all rather simple constructs any software developer should know. Learn the tools used for your trade.

Regards,
- Graeme -

Op 2011-01-24 01:16, Felipe Monteiro de Carvalho het geskryf:

Maybe it would work if you simply put all units from the directory of sqldb into the lazarus/lcl directory and rebuild the LCL then.

What are you on about??? Simply recompile the SqlDB code, and install the new *.ppu files!

Change the paths to suite your setup...

$ cd /opt/fpc-2.4.4/src/packages/fcl-db
$ make
$ make install INSTALL_PREFIX=/opt/fpc-2.4.3/x86_64-linux/

The the above example, my FPC source and binaries are installed in /opt/fpc-x.y.z/ and my binaries are installed <fpc>/x86_64-linux/ because I have binaries for other targets and platforms too.

OR

As I mentioned in a previous post, add the SqlDB source code path to your project unit path settings, then simply recompile your project.

Regards,
- Graeme -

That probably won't work and will only cause trouble. He is probably using the LCL counterpart to sqldb (lazsqldb or however it's called) which in turn uses the FCL sqldb units. Since the signature of the ones in the project library path will not match the ones the LCL was compiled against, the compiler will complain and stop.

But the first solution you gave (recompiling the LCL [or just sqldb]) and afterwards the respective lazarus package should work just fine.

Op 2011-01-24 09:57, Andreas Schneider het geskryf:

That probably won't work and will only cause trouble. He is probably using the LCL counterpart to sqldb (lazsqldb or however it's called)

Ah, good point. I always use the database components directly from FCL, so this does work in my cause though.

Regards,
- Graeme -

Pray:
exactly how do you do this? What ia FCL?
What would be the procedure be for someone who has installed FPC from the binary distribution and downloaded the corresponding FPC sources to a directory, which has been configured in lazarus?

I have not yet learned these tools (lazarus, fpc) fully because I am still avaluating them, so I have installed the FPC 2.4.2 release binary package and used svn to get the fixed lazarus. Therefore I have learned how to rebuild lazarus, but not FPC.
Must one really rebuild the *compiler* to make a simple source change in a peripheral component work in a single lazarus project?

My evaluation involves the following:
- Possibility to transfer code from Delphi 7
- GUI application compatibility (simple graphics and listview handling)
- Handling of serial ports
- Database functionality
- Cross-compilation for the embedded ARM platform
- etc

I am currently stuck in the database evaluation because the SQLdb component does not allow returning records from an EXEC call into an MSSQL Server database engine. This is what this is all about fixing.

FCL = Free Component Library, a set of units, distributed with FPC.

The instructions given to you by Graeme should be sufficient:

1. Apply the patch I sent. From the mails, I see you found out how to do this. (tip: when working with open source, knowledge of the 'patch' tool is almost essential)

2. Recompile the sqldb package in FPC. In a DOS window: (I assume the FPC sources are in c:\fpc\src, modify as needed)

cd c:\fpc\src\packages\fcl-db\src\sqldb
make clean
make all
make install

If there are no errors, this should have updated your FPC installation.

3. Optionally, recompile the lazsqldb (or is it sqldblaz ?) package of lazarus. It is not necessary to recompile the LCL or Lazarus itself.

4. Recompile your application.

That should do it.

Michael.

2011/1/24 Graeme Geldenhuys :

I would not bet on the make files knowing in what directory the installer put the compiled unit and explicitly mention the install prefix, especially on windows.

Vincent

Op 2011-01-24 10:35, Bo Berglund het geskryf:

Pray:

God can't help you here, I think he subscribed to another mailing list. :)

exactly how do you do this? What ia FCL?

As Michael said. FCL = Free Component Library. It is included with FPC and contains lost of non-GUI components. That would be things like a image library (reading/writing of image files), database components, translated C headers etc...

The rest of the instruction I have listed before, or what Michael listed should solve your problem. I always install FPC in a non-standard location, so for me it is a requirement to specify the INSTALL_PREFIX in the 'make install' command. I would imagine, seeing that you installed from the binary setup, that everything is in known standard locations so simply 'make install' would work.

Must one really rebuild the *compiler* to make a simple source change in a peripheral component work in a single lazarus project?

No, in this case there is no need for that. That is what surprised me of the others that gave you advice. They have been around in the FPC scene for a long time, so why they give such stupid advice is beyond me.

Our company also moved from Delphi 7 to FPC (5+ years ago)... It takes a little while to get used to FPC and Lazarus, and the whole allowed to recompile the compiler or IDE etc. So give yourself some time to adjust
- after all, it is a totally different toolchain compared to Delphi.

- Possibility to transfer code from Delphi 7

relatively easy.

- GUI application compatibility (simple graphics and listview handling)

relatively easy.

- Handling of serial ports

I've heard of a few which do this - no problems.

- Database functionality

near perfect.

- Cross-compilation for the embedded ARM platform

I would consider cross-compiling an advanced feature, but once you get your head around it, and got your setup working, it is pretty good too.

I am currently stuck in the database evaluation because the SQLdb component does not allow returning records from an EXEC call into an MSSQL Server database engine. This is what this is all about fixing.

I also used to use MS SQL Server, but with ADO components (and then later some 3rd party components). I have only heard of a handful of FPC developers using MS SQL Server, so I would guess SqlDB code hasn't had as much exposure and testing against that RDBMS. So some tweaking might be required (as you just noticed), but overall, SqlDB is very good. We use SqlDB against Firebird and PostgreSQL database servers.

Regards,
- Graeme -

Op 2011-01-24 11:33, Vincent Snijders het geskryf:

I would not bet on the make files knowing in what directory the installer put the compiled unit and explicitly mention the install prefix, especially on windows.

Oh, I thought that was only a Linux issue. I guess I should never assume things. :)

Regards,
- Graeme -

I changed to /fpc/branches/fixes_2_4/

In the Windows Explorer, click on right button on a folder (follow the tutorial) and after click "SVN Checkout".

NO! Do the same above.

Then I am asked to get 2.5.1 trunk sources from SVN and make a clean install from these....

I changed to 2.4.3 version now...

Isn't there a simpler way????

The simpler way is use the installer.

When I already have a working fpc 2.4.2 can't I build a new fpc where the single change is the sqldb.pp modification????

YES, you can! Just use the BAT in the tutorial. They serve to any version of FPC.

Isn't there a command similar to rebuilding lazarus that works for FPC itself? These instructions seem to be geared towards building FPC from scratch without having anything at all....

No, I use the same BAT files always I want to rebuild the FPC. For Lazarus, just use the BAT only once. After you have the IDE, use the Build option on Lazarus' menu.

Marcos Douglas

Returning to the patch.

I have applied it to the sqldb.pp file I found in the FPC sources, then followed Graeme G's advice to add this folder to the project options so it would be recompiled.
So now I believe that I have actually succeeded in getting the patch to work.

What happens now is that no exception is raised at all. :-)

But, the recordset I get contains only one record even though I *know* there should be 4. If I use the MSSQLServer Query Analyzer and send the same command I get 4 records.

So now the EXEC verb is accepted by the query.Open method, but it seems to not set its returned records count correctly, thus losing all but the first record.

Half done, I would say...

I tried to use the debugger and "step into" the query.Open method to see what was going on but it seems like this is not allowed by lazarus, it does just like I had clicked "step over".

Graeme Geldenhuys wrote

Alternatively, add the SqlDB source path to your project, then when you compile your project, it will compile the SqlDB code too - as if it was part of your project source code.

Thanks, this was the simple way I have been looking for! Now that I did that the patch activated and I could see that it no longer barfs at the EXEC verb.
Still only returns one record out of 4, though.

The actual code I use is this:

      query.Open;
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('AppNo').AsString + ' - ' +
             query.FieldByName('AppInfo').AsString + #13#10;
        query.Next;
      end;

After the Open call the while loop is entered and the query.Next statement causes the query.EOF flag to be set so the loop exits. This is what I interpreted as caused by the Open method interpreting the return as having only one record.

As an experiment I added a line as follows after the query.Open statement:

      i := query.RecordCount;

But this only affirms the fact that the recordset handled by the query only contains a single record, i = 1.

In general RecordCount cannot be trusted;
It can be updated as the TDataset fetches records.

In each case: SQLDB relies on ODBC to connect to MS-SQL.

From testing with MS-Access and MySQL I know that ODBC works fine with other

databases, at least for SELECT Statements.

1. Can you test a regular SELECT statement on the same SQL database ? If that doesn't work either, we know the problem is in the ODBC connection component.

2. Can you test the same EXEC statement in a Delphi app through ODBC ? If this gives problems, it means the ODBC driver is well - weird. (although this would surprise me)

3. If both previous attempts work, there is nothing left but to debug the ODBC connection component. That means stepping through your code with the debugger.

Michael.

I did this before too, but I have repeated it now by copying the procedure code into the application. The procedure differentiates the processing depending on the value of the argument, but I have copied the relevant select section into the lazarus project code.

'SELECT dr.SerialNo, da.AppNo, da.AppOption, da.AppInfo, da.ExpDate, da.ProgDate' +
' FROM DongleReg dr' +
' JOIN DongleAppData da' +
' ON da.DongleRegID = dr.DongleRegID' +
' WHERE dr.SerialNo = 2584' +
' ORDER BY da.AppNo ASC';

As expected it runs just fine returning 4 records, in fact the query.RecordCount method actually shows this too. :-)

Only problem is that our database contains a whole lot of procedures that return various kinds of data so I cannot really move that logic over to the GUI application code. I really need to be able to retrieve those records after sending an EXEC command (which now is legal in sqldb.pp after the patch is applied).

See previous reply, I sent it off too soon....

2. Can you test the same EXEC statement in a Delphi app through ODBC ? If this gives problems, it means the ODBC driver is well - weird. (although this would surprise me)

I will have to check how to use ODBC in Delphi7 first. I only ever used ADO in Delphi for database work.

3. If both previous attempts work, there is nothing left but to debug the ODBC connection component. That means stepping through your code with the debugger. Michael.

This last point I tried to do but when I did "step into" after stopping at a breakpoint at the query.Open line the complete Open method executed and I got to the next line in the main procedure....

Can you please specify which ODBC database components you used in Delphi 7??
For example what is the type of Query1?
And if it needs a database connection component to work, what is that?

Please post your complete test code.

As I said I never used ODBC with Delphi, so I would need to know which component to use to do that test. I have installed Delphi7 on this PC too, but straight out of the box (CD)...

You can use TDatabase and TQuery.
In that case, you must configure the ODBC connection in the BDE.

Michael.

Bo Berglund wrote

I tried to use the debugger and "step into" the query.Open method to see what was going on but it seems like this is not allowed by lazarus, it does just like I had clicked "step over".

That is because the "Open" method is located inside unit "db" which isn't in the same folder as "sqldb" and thus has (by default) no debug information available. You could either add the
"%fpcdir%/packages/fcl-db/src/base" directory to your project as well (like you did with the "sqldb" path) or you need to compile the FCL with debug information (I'd need to look it up, so it might be simpler to use the first version).
Then you should be able to step into the "query.Open" method.

Another possibility would be to place a breakpoint in one of the "TSQLQuery" methods (inside "sqldb"), but here you'd need to know the rough layout of the code flow FPC's database system uses.

Regards,
Sven

What happens if you put an explicit .First before the loop?

   query.Active:= true;
   query.First;
   while not query.EOF do
   begin
     S := S + query.FieldByName('AppNo').AsString + #13#10;
     query.Next;
   end;

I actually never worked with BDE either, but now at work I have found out that ADO is able to work through ODBC too! So I can use the components I have worked with before to make a Delphi test application through ODBC using TADODB. Will do tonight and report back.

works fine with SELECT

I now repeated the same simple application code in Delphi7 where I used a TADOConnection and a TADOQuery component to execute the queries.
I used the same ODBC datasource as had been used by the lazarus program and this time all 4 records were correctly retrieved.

No need since the ODBC connection is OK proven by the Delphi program. Now only remains to check why the sqldb component does not see all returned records when the verb is EXEC. It does see them if the asq is a select statemant exactly the same as the stored procedure executes...

... And that means you must debug the SQLDB component.

Please recompile and reinstall SQLDB with the instructions given earlier, give an additional option to make:

OPT=-gl

This will enable debug information for the SQLDB units.

After that, you can debug the ODBCConnection components.

The interesting routines where you should put a breakpoint are in odbcconn.pas :

line 690 : TODBCConnection.Fetch
line 648 : TODBCConnection.Execute
line 563 : TODBCConnection.PrepareStatement

Try to determine what they do differently when doing an EXEC and a SELECT.

Unfortunately I can't do it for you, since I have no MS-SQL database.

Michael.

This does not help at all...
The query.Recordcount property shows the number of records as 1 and this is also what is listed in the loop.

A similar Delphi 7 test program using the same ODBC data source but with Delphi's TADOConnection and TADOQuery correctly delivers the expected 4 records.
So the ODBC datasource is not at fault by itself.

I believe that there is some assumption made inside the SQLdb system, which tries to estimate what the query text is going to accomplish and in this case when it sees the EXEC verb it assumes a simple return, not a collection of records...

But I am in no position to prove or disprove this because I don't know the inner workings of database components.
I just use them RAD style.

Hoping that someone here that is more capable will find a solution.

Hello Lazarus-List,

BB> This does not help at all...
BB> The query.Recordcount property shows the number of records as 1 and BB> this is also what is listed in the loop.

Do not care the RecordCount property, it could be less than real fetchable records.

Anyway it seems to not affect this case, just a clarification.

Hello Lazarus-List,

Sunday, January 23, 2011, 12:06:12 PM, you wrote:

MVC> Attached is a possible patch. I don't have MS-SQL (doesn't MVC> run on Linux) so I can't test.
MVC> Please apply it to packages/fcl-db/src/sqldb/sqldb.pp and MVC> report whether this helped you
MVC> solve your problem. If so, I will commit it to SVN.

I think this piece of code is a problem for your patch (last line):

function TCustomSQLQuery.Fetch : boolean;
begin
  if not (Fcursor.FStatementType in [stSelect,stExecProcedure]) then
    Exit;
  if not FIsEof then FIsEOF := not TSQLConnection(Database).Fetch(Fcursor);
  Result := not FIsEOF;
  // A stored procedure is always at EOF after its first fetch
  if FCursor.FStatementType = stExecProcedure then FIsEOF := True;
end;

Aha....

This is indeed the problem. However, under Firebird, it is correct. An Execute will return only the first values.

To Bo: Please comment the above line, and see if that fixes your problem. Please report back here on your findings.
I will talk to Joost and what to do with this.

Michael.

Thanks a bunch!
I commented out the last line in the function:

  //if FCursor.FStatementType = stExecProcedure then FIsEOF := True;

And this solved the problem, now the EXEC call returns the 4 expected records! :)

Great! Now I can proceed to test my other database functionality with lazarus. :-)

Great :)

We'll see about a permanent fix. I already mailed Joost.

Michael.

Op 2011-01-25 22:45, Michael Van Canneyt het geskryf:

This is indeed the problem. However, under Firebird, it is correct. An Execute will return only the first values.

Is this true even for stored procs that return a result set (aka temp table of data)?

Regards,
- Graeme -

If you do a simple execute: yes.
If you do a 'SELECT * From ' then all records are returned.

Michael.

Congratulations Jos?, good job!

Marcos Douglas