calling selectable stored proc for MySQL
lazarus / 16-Databases

----------------------------------------------------------------------
motaz · 2010-04-05 11:59

I failed to call a selectable stored procedure that returns a record
set from Lazarus, using standard SQL components:

Code: [Select]  SQLQuery1.Close;
  SQLQuery1.SQL.Text:= 'call MyProc( ''123456'', ''motaz'')';
  SQLQuery1.Open;

I get this error: SQLQuery1 : Cannot open a non-select statement
any body knows how to call a selectable stored proc please

----------------------------------------------------------------------
Marc · 2010-04-05 22:11

The error says it all, you cannot open a non-select query. Replace
SQLQuery1.Open with SQLQuery1.Execute

----------------------------------------------------------------------
motaz · 2010-04-07 10:14

but it is selectable stored procedure and it should return record set.
.Exec returns no thing

----------------------------------------------------------------------
Lacak2 · 2010-04-07 10:28

I am not expert for MySQL, but reading documentation:

"A procedure is invoked using a CALL statement (see Section 12.2.1,
“CALL Syntax”), and can only pass back values using output variables.
A function can be called from inside a statement just like any other
function (that is, by invoking the function's name), and can return a
scalar value. "

IMHO this does not speak about returning recordsets, but:

"MySQL supports the very useful extension that allows the use of
regular SELECT statements (that is, without using cursors or local
variables) inside a stored procedure. The result set of such a query
is simply sent directly to the client."

So what is the conclusion ? ;-)))
Looking into mysqlconn.inc:
Code: [Select]function TConnectionName.StrToStatementType(s : string)
: TStatementType;

begin
  S:=Lowercase(s);
  if s = 'show' then exit(stSelect);
  result := inherited StrToStatementType(s);
end;

There is not handled "call" like "selectable" ... in some cases it may
be bug!!! So you can post bug report.
Correction may be done:

Code: [Select]function TConnectionName.StrToStatementType(s : string)
: TStatementType;

begin
  S:=Lowercase(s);
  if s = 'show' then exit(stSelect);
  if s = 'call' then exit(stExecProcedure);
  result := inherited StrToStatementType(s);
end;

----------------------------------------------------------------------
motaz · 2010-04-07 11:45

I tried to call .ExecSql and I get the same error: MySQL50Connection1
: Error executing query: PROCEDURE radius2.getBalanceMdn can't return
a result set in the given context

suppose that I want to return a record set that contains for example
10,000 records, I cann't do it in an output parameters.

btw. In firebird calling selectable stored procedure that returns
records set is working fine

----------------------------------------------------------------------
motaz · 2010-04-07 11:53

Ok, the question now is how to call a selectable procedure that return
record set, should it be like select procname, or execute procname, or
just procname, etc!

----------------------------------------------------------------------
Lacak2 · 2010-04-07 11:55

Quote from: motaz on April 07, 2010, 01:45:44 pmI tried to call
.ExecSql and I get the same error: MySQL50Connection1 : Error
executing query: PROCEDURE radius2.getBalanceMdn can't return a result
set in the given context
ExecSQL is not good, you must use Open if you want receive recordset,
but as I wrote, there is bug so you can not do it :-(
without first fixing bug ;-)

Quote from: motaz on April 07, 2010, 01:45:44 pmsuppose that I want to
return a record set that contains for example 10,000 records, I cann't
do it in an output parameters.
sure ;-)

Quote from: motaz on April 07, 2010, 01:45:44 pmbtw. In firebird
calling selectable stored procedure that returns records set is
working fine
yes as expected ;-)

Quote from: motaz on April 07, 2010, 01:45:44 pmselect procname
no it is not supported by MySQL

Quote from: motaz on April 07, 2010, 01:45:44 pmexecute procname
no it is not supported by MySQL

Quote from: motaz on April 07, 2010, 01:45:44 pmor just procname
no it is not supported by MySQL

----------------------------------------------------------------------
motaz · 2010-04-07 12:30

Ok, thanks
is it known bug, or should someone report it?

how can I call selectable stored procedure for MySQL anyway? what is
the right syntax?

----------------------------------------------------------------------
Lacak2 · 2010-04-08 04:11

Quote from: motaz on April 07, 2010, 02:30:31 pmOk, thanks
is it known bug, or should someone report it?
AFAIK this bug is ATM not in bug tracker, but I am not 100% sure.
IMHO you should report it there ...

Quote from: motaz on April 07, 2010, 02:30:31 pmhow can I call
selectable stored procedure for MySQL anyway? what is the right
syntax?
IMHO: call <procname> [(<params>)]
See: http://dev.mysql.com/doc/refman/5.5/en/call.html

----------------------------------------------------------------------
motaz · 2010-04-08 06:26

Thanks
but I can not login into bug track, and there is no report a bug for
Anonymous login.
anyway, I can not wait until they fix this problem, I should find a
solution for this problem in a very few days.
Now I'm using Lazarus in a production in a telecom system.

----------------------------------------------------------------------
bobo · 2010-04-08 07:25

You could try the ZEOS library to access your MySQL database and check
if it is working there.
http://sourceforge.net/projects/zeoslib/

I am using ZEOS for all my MySQL access (both under Windows and Linux)
and I yet to find any problem with it. However, I am not using stored
procedures.

----------------------------------------------------------------------
motaz · 2010-04-08 08:02

unfortunately Zeos has the same problem: can't return a result set in
the given context

----------------------------------------------------------------------
bobo · 2010-04-09 02:23

I would ask on the ZeosLib forums (http://zeos.firmos.at) about it.
Maybe you are using a new MySQL version and that might only be
supported in the 7.0 Zeos version for example.

----------------------------------------------------------------------
Lacak2 · 2010-04-09 04:46

Quote from: motaz on April 08, 2010, 08:26:00 ambut I can not login
into bug track, and there is no report a bug for Anonymous login.
You must register, before posting bug reports

Quote from: motaz on April 08, 2010, 08:26:00 amanyway, I can not wait
until they fix this problem, I should find a solution for this problem
in a very few days.
Hm, then only solution is try fix the problem by your own hands. I
will do some tests, but I am not sure if it will be in few days

----------------------------------------------------------------------
motaz · 2010-04-09 05:50

that's interesting..
can I modify source code of SQLQuery.Open then recompile Lazarus?

----------------------------------------------------------------------
Lacak2 · 2010-04-09 06:37

Quote from: motaz on April 09, 2010, 07:50:15 amcan I modify source
code of SQLQuery.Open then recompile Lazarus?
Yes locate into your lazarus instalation directory a there in:
FPC/x.x.x/source/packages/fcl-db/src/sqldb/...
There are 2 important files:
sqldb.pp and mysql/mysqlconn.inc

You must make changes in them ... I made some changes and it seems,
that it is working as expected (I use only MySQL 5.1!)
See attached files ...

You must not recompile whole Lazarus, only modify Paths in your
project.

I also have reported the bug
http://bugs.freepascal.org/view.php?id=16236

----------------------------------------------------------------------
motaz · 2010-04-09 07:07

Thank you very much for reporting the problem and for telling me how
to fix it.
I'll do this modification and I'll let you know.
I have this problem in my work computer (Windows client and Linux
server). At home I have only Linux, and I'll go to work after election
here in Sudan, we may have a complete off week.

----------------------------------------------------------------------
Lacak2 · 2010-04-09 11:58

ok, let me know ...
I found bug in my previous mysqlconn.inc
Here are updated files.

----------------------------------------------------------------------
clauslack · 2010-04-09 12:41

Hello
I don't use MySQL, but you patch sqldb.pp

With Firebird, this code (without select) return a dataset.

insert into imagenes (Socio) values (21095) returning clave_imagen
SQLQuery1.open;

Look at this example
http://www.lazarus.freepascal.org/index.php/topic,8217.msg39713.html#m
sg39713

Strange...

Regards

----------------------------------------------------------------------
motaz · 2010-04-11 09:06

Lacak2, I do the modifications in sqlconn.inc and sqldb.pas, then I
include it's source path in compiler options, I get the same error.
Then I copied sqldb.pas/.ppu and sqlconn.inc to local application
folder, and I get the same error.
any ideas

----------------------------------------------------------------------
Lacak2 · 2010-04-12 04:55

Quote from: clauslack on April 09, 2010, 02:41:12 pmI don't use MySQL,
but you patch sqldb.pp

Strange...
I patched FPC 2.2.5 version, where patch is required
Current version (trunk) is ok, so no patch is required ;-)

----------------------------------------------------------------------
motaz · 2010-04-12 05:13

are u suggesting to download and install the latest version from
snapshot 0.9.29?

----------------------------------------------------------------------
Lacak2 · 2010-04-12 05:13

Motaz, which version of FPC do you use ?
My paths (in Compiler Options/Paths/Source units):
$(FPCSrcDir)\packages\fcl-db\src\sqldb\mysql\;
$(FPCSrcDir)\packages\fcl-db\src\sqldb\;
$(LazarusDir)\components\sqldb\

When you compile your project, do you see in Message window, that
units sqldb.pp and mysqlconn.inc are compiled ?

----------------------------------------------------------------------
Lacak2 · 2010-04-12 05:15

Quote from: motaz on April 12, 2010, 07:13:11 amare u suggesting to
download and install the latest version from snapshot 0.9.29?
You can try it.
Then replace mysqlconn.inc (sqldb.pp is ok in trunk, so no
modification is required)

----------------------------------------------------------------------
motaz · 2010-04-12 05:51

since I'm using Lazarus in a real production, which is better to
download:

- Lazarus fixes + fpc 2.4.0   : 
Lazarus-0.9.28.3-24591-fpc-2.4.0-20100412-win32.exe
- Lazarus + fpc 2.4.0 :
Lazarus-0.9.29-24591-fpc-2.4.0-20100412-win32.exe

----------------------------------------------------------------------
Lacak2 · 2010-04-12 06:50

IMHO you can use both, FPC (FreePascal) version is the same 2.4.0 (so
fcl-db (sqldb) code will be identical)

----------------------------------------------------------------------
motaz · 2010-04-12 07:39

I've downloaded 0.9.28.3 with FPC 2.4.0, and I replaced mysqlconn.inc
and I open this file from Lazarus, and I see your modifications, but I
get the same error:

Code: [Select]call getBalanceMdn( '120258463', 'motaz@bananaaa.com')
when I called .Open I get:

SQLQuery1 : Cannot open a non-select statement

----------------------------------------------------------------------
motaz · 2010-04-12 08:30

I tried also 0.9.29 with FPC 2.4.1
I replaced mysqlconn.inc and I get the same error

----------------------------------------------------------------------
Lacak2 · 2010-04-12 10:15

Quote from: motaz on April 12, 2010, 10:30:36 amI tried also 0.9.29
with FPC 2.4.1
I replaced mysqlconn.inc and I get the same error
I have also downloaded and installed todays Lazarus snapshot 0.9.29 +
FPC 2.4.1 and after replacing mysqlconn.inc (+small modification in
sqldb.pp) works for me.

My environment is:
- Windows Vista + MySQL 5.1
My test procedure:
- create procedure proc1 begin select * from tab1; end
(tab1 contains 2 rows)

I am sure, that patch is ok, but I do not know where problem may be ?
Can you test with very simple SQL procedure, which returns only small
amount of rows ?

----------------------------------------------------------------------
motaz · 2010-04-12 10:37

I tried your file, but the same result.
Should I do any modification to the path of include or .pp directory?

my environment:

Client: Vista
Server: CentOS
Clustered MySQL 5.1

----------------------------------------------------------------------
Lacak2 · 2010-04-12 11:52

Here is zipped my test project directory.
Code: [Select]http://www.ulozto.sk/4529082/lazmysql.zip
HTH

----------------------------------------------------------------------
motaz · 2010-04-13 06:10

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 · 2010-04-13 06:34

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

----------------------------------------------------------------------
motaz · 2010-04-13 08:00

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 · 2010-04-13 08:03

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

----------------------------------------------------------------------
Lacak2 · 2010-04-13 09:46

Quote from: motaz on April 13, 2010, 10:03:20 amVeľ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 · 2010-04-13 10:12

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 · 2010-04-13 10:28

Quote from: motaz on April 13, 2010, 12:12:14 pmcall getBalanceMdn(
'120258463', 'motaz@bananaaa.com')
yes this is ok, space between <call> and <name of proc.>

Quote from: motaz on April 13, 2010, 12:12:14 pmI 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) htt
p://wiki.lazarus.freepascal.org/IDE_Window:_Environment_Options#FPC_So
urce_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)

Quote from: motaz on April 13, 2010, 12:12:14 pmDúfam, že vyriešenie
tohto v budúcej verzii Lazara
Yes, but this may take months.

----------------------------------------------------------------------
motaz · 2010-04-13 17:17

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 · 2010-04-14 04:14

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 · 2010-04-14 06:01

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

----------------------------------------------------------------------
cellx · 2010-09-29 08:25

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 · 2010-09-29 11:35

Quote from: cellx on September 29, 2010, 10:25:34 amLacak2, 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 · 2012-06-11 09:24

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 · 2012-06-11 09:48

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)

----------------------------------------------------------------------
kenpem · 2012-06-11 11:23

I wonder... is there any merit in me trying to shim FPC 2.7.1 under
Lazarus instead of the default included 2.6?

----------------------------------------------------------------------
BigChimp · 2012-06-11 11:29

Yes, that certainly can be done.

Have a look at my fpcup program (see third party announcements), which
will allow you to set up a separate copy of FPC 2.7.1 + Lazarus (use
fpcup --help; you'll need something like fpcup
--fpcurl=http://svn.freepascal.org/svn/fpc/trunk; if you already use
c:\development (on Windows) or ~/fpc and ~/lazarus (Linux/OSX),
suggest you use --binutilsdir, --fpcbootstrapdir, --lazdir and
--fpcdir as well).

Alternatively, follow the instructions on the wiki for installing from
svn...

----------------------------------------------------------------------
kenpem · 2012-06-11 14:47

I might give that a try... have now spent too many hours trying to get
Postgre and Firebird behave themselves   

I'm expert-level at MS-SQL Server, and not bad with mySQL, so a little
frustrated that I can't get these other beasts running nicely. Anyway,
I'll see if  I can get FPC 2.7.1 playing nicely with Laz. Last resort,
really, otherwise I'll just junk the project and switch to Python,
can't afford much more time on getting the tools going, need to put
some time into the work!

----------------------------------------------------------------------
BigChimp · 2012-06-11 14:59

There's also an MS SQL connector in FPC 2.6.1+=> included in Lazarus
snapshots.

Getting Firebird to behave itself... should be doable if you don't
expect MS SQL like behaviour, but seeing you're also working with
MySQL I would imagine you don't.
Glad to help you with Firebird stuff if I can.

If you have to switch to Python, hope you come back to us

----------------------------------------------------------------------
kenpem · 2012-06-11 15:18

I'll always come back

I cut my teeth on Pascal, and it's always my language of choice.
Coupled to a good (but not over-complicated) IDE, it is without
question the best, fastest, most comfortable and pleasant way to craft
code.

But when I'm in a hurry on a very small but incredibly important
project, I have to use whatever works. At this time, FPC/Laz is just
getting in my way so I might have to go around it for now, then spend
the weekend ironing out the mySQL wrinkles and port the project back.

Fear not!

----------------------------------------------------------------------
kenpem · 2012-06-11 16:50

OK, with thanks to BigChimp and friends in the Third Party tools
thread, I've now got a copy of Lazarus reporting:
QuoteLazarus 1.1 r37618 FPC 2.7.1
x86_64-win64-win32/win64Unfortunately, I get the same error:
QuoteProject xxxx raised exception class 'EDatabaseError' with
message:
qryGP : Cannot open a non-select statement

----------------------------------------------------------------------
kenpem · 2012-06-11 16:56

ahhh hang on... that was with ODBC connectivity (obviously I was
getting desperate!)

Switching over to native mySQL 5.5  ....... works!

MANY thanks for the assist, and sorry to have been such a pain in the
ar$e.

Now I can get on with actually coding the project.

== Happy Customer.

----------------------------------------------------------------------
BigChimp · 2012-06-11 18:16

Quote from: BigChimp on June 11, 2012, 11:48:09 amWell, 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.
Oops. Marco corrected me in the bug tracker: apparently the patch was
backported to fixes 2.6 so it would appear in a Lazarus snapshot with
fixes 2.6.
Sorry to have lead you to installing FPC 2.7+Lazarus; you could
probably just as well have used Laz snapshot.

Note: now that you have a separate FPC 2.7+Lazarus installation with
FPCUP, you can install a Lazarus snapshot over your original
Lzarus+FPC 2.6 release version - default in c:\lazarus, without
problems. However, given Murphy's law and the fact I already steered
you wrong once, you might wait until your project is finished.

----------------------------------------------------------------------
kenpem · 2012-06-12 16:07

No problem, I'm happily now chugging along with 2.7.1, and Lazarus
works just fine. I have in fact now removed the old installation, and
I'll carry on with this one.

Unless there's any particular reason why I should install a Laz
snapshot?

----------------------------------------------------------------------
BigChimp · 2012-06-12 16:15

Quote from: kenpem on June 12, 2012, 06:07:04 pmUnless there's any
particular reason why I should install a Laz snapshot?
Only if you run into problems due to new bugs in 2.7.1...