calling selectable stored proc for MySQL

[Lacak2]

Here is zipped my test project directory.

Code: [Select]

http://www.ulozto.sk/4529082/lazmysql.zip
HTH

[motaz]

where is the button of download.
it is strange language. I tried google, but google translator can't understand this language.
where is sk anyway, it is solvakia?

[Lacak2]

Sorry, try this : http://www.pces.sk/download/lazmysql.zip
Yes, SK is Slovakia (Near of Polland, Hungary, Czech rep.) ;-)

[motaz]

It is similar to my project, but I'm still getting the same result.

I think I should modify my record set to output parameter and wait until next stable version of Lazarus, because snap shot version has a problem with LNet package.

Slovíčka na kartičkách

[motaz]

Veľmi vám ďakujem za vaše úsilie a času

[Lacak2]

Veľmi vám ďakujem za vaše úsilie a času

It is ok, not at all.

It is strange for me, where may be problem.
Looking at error message "Cannot open a non-select statement", the only place,where it is raised is in TCustomSQLQuery.InternalOpen method in sqldb.pp.
The error is raised, when StatementType is not stSelect, stExecProcedure.
So it means, that your SQL.Text is not recognized like stExecProcedure.
This is done into SQLParser method.
So you can trace this ... ? or what exactly is in your SQL.Text property ?

[motaz]

call getBalanceMdn( '120258463', 'motaz@bananaaa.com')

I feel that my application is not using the correct version of sqldb.ppu
I put it locally with the application in directory, but the same error

Dúfam, že vyriešenie tohto v budúcej verzii Lazara

[Lacak2]

call getBalanceMdn( '120258463', 'motaz@bananaaa.com')

yes this is ok, space between <call> and <name of proc.>

I feel that my application is not using the correct version of sqldb

I feel so too. Put sqldb.pp and mysqlconn.inc into your Lazarus directory under FPC/2.4.1/source/fcl-db/src/...
Delete all *.ppu files from your project directory.
Then set/check in Lazarus menu ... Environment options/Path to source units of FPC = C:/.../Lazarus/fpc/2.4.1/source (I do not know how is it exactly in english, because i use localized version of Lazarus) http://wiki.lazarus.freepascal.org/IDE_Window:_Environment_Options#FPC_Source_directory
Then set in your project Paths as I wrote:
$(FPCSrcDir)\packages\fcl-db\src\sqldb\mysql\;
$(FPCSrcDir)\packages\fcl-db\src\sqldb\;
$(LazarusDir)\components\sqldb\

 and try recompile ... new ppu files appears (mysql50conn, sqldb)

Dúfam, že vyriešenie tohto v budúcej verzii Lazara

Yes, but this may take months.

[motaz]

I tried your last configuration without luck
I'll ask my colleague (Database Admin) to make them output parameters instead of record set. Fortunately it is always returning 1 record.

Še enkrat hvala

[Lacak2]

Shade, I will submit my patch into bug report and we will wait until it will be fixed into FPC, then you can test it, if it helps ...

[motaz]

Ok, I'll check it, just tell me when you have do the batch and they add it to current Lazarus snapshots.

[cellx]

Lacak2, your fix works well for me. Thank you!
but I have a question regarding this part which I guess it's for store procedure that will return multiple result sets:

@@ -470,7 +472,14 @@ begin
       C.RowsAffected := mysql_affected_rows(FMYSQL);
       C.LastInsertID := mysql_insert_id(FMYSQL);
       if C.FNeedData then
-        C.FRes:=mysql_store_result(FMySQL);
+        repeat
+        Res:=mysql_store_result(FMySQL); //returns a null pointer if the statement didn't return a result set
+        if Res<>nil then
+          begin
+          mysql_free_result(C.FRes);
+          C.FRes:=Res;  <--- since C.FRes will be assigned a new result set each time in the loop, does it mean it will only contain the last result set?
+          end;
+        until mysql_next_result(FMySQL)<>0;

I'm a new user of lazarus, please forgive me if it's a silly question

[Lacak2]

Lacak2, your fix works well for me. Thank you!
but I have a question regarding this part which I guess it's for store procedure that will return multiple result sets:

@@ -470,7 +472,14 @@ begin
       C.RowsAffected := mysql_affected_rows(FMYSQL);
       C.LastInsertID := mysql_insert_id(FMYSQL);
       if C.FNeedData then
-        C.FRes:=mysql_store_result(FMySQL);
+        repeat
+        Res:=mysql_store_result(FMySQL); //returns a null pointer if the statement didn't return a result set
+        if Res<>nil then
+          begin
+          mysql_free_result(C.FRes);
+          C.FRes:=Res;  <--- since C.FRes will be assigned a new result set each time in the loop, does it mean it will only contain the last result set?
+          end;
+        until mysql_next_result(FMySQL)<>0;

Exactly as you wrote ;-)
AFAIK there is no way how to process multiple result-sets in one call to Open, so only last result-set is used. And all previous result-sets must be red, but are forgotten.

This patch is already registered in bug-tracker: http://bugs.freepascal.org/view.php?id=16236

[kenpem]

Despite the fact that this is closed in the bug-tracker, it's still there in the latest release of Lazarus. Driving me nuts, quite frankly. Tried and tried to apply the patches, but (a) there's little in the way of help as to how this is done; (b) it seems to be problematic under Windows7x64 anyhow.

Don't suppose somebody's got a "Dummies Guide to making MySQL connections work properly in Lazarus applications" handy?

[BigChimp]

Well, it was correctly closed in the bug tracker: it was implemented in FPC version 2.7.1 (current trunk/development version).

You're probably using 2.6.0 (released Lazarus version) or fixes 2.6 (=2.6.1) for a snapshot.... where this fix is indeed not applied

I commented on bug
http://bugs.freepascal.org/view.php?id=22250
that a backport of the fix from 2.7.1 to 2.6.1 might be an idea. Once that is done, the snapshots will pick it up.

Regarding patches, try the applying a patch page on the wiki. Yes, it's not always easy, but you can even edit the source code yourself, save a backup copy of the edited file and recompile FPC then Lazarus.



(Note: Firebird and PostgreSQL are better supported by FPC/Lazarus - see other threads on this; it might be worthwile to switch)