Recent

Author Topic: calling selectable stored proc for MySQL  (Read 42099 times)

motaz

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
calling selectable stored proc for MySQL
« on: April 05, 2010, 01:59:10 pm »
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

  • Administrator
  • Hero Member
  • *
  • Posts: 2582
Re: calling selectable stored proc for MySQL
« Reply #1 on: April 06, 2010, 12:11:23 am »
The error says it all, you cannot open a non-select query. Replace SQLQuery1.Open with SQLQuery1.Execute
//--
{$I stdsig.inc}
//-I still can't read someones mind
//-Bugs reported here will be forgotten. Use the bug tracker

motaz

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #2 on: April 07, 2010, 12:14:59 pm »
but it is selectable stored procedure and it should return record set.
.Exec returns no thing

Lacak2

  • Guest
Re: calling selectable stored proc for MySQL
« Reply #3 on: April 07, 2010, 12:28:44 pm »
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;
« Last Edit: April 07, 2010, 01:11:31 pm by Lacak2 »

motaz

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #4 on: April 07, 2010, 01:45:44 pm »
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

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #5 on: April 07, 2010, 01:53:38 pm »
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

  • Guest
Re: calling selectable stored proc for MySQL
« Reply #6 on: April 07, 2010, 01:55:24 pm »
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
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 ;-)

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.
sure ;-)

btw. In firebird calling selectable stored procedure that returns records set is working fine
yes as expected ;-)

select procname
no it is not supported by MySQL

execute procname
no it is not supported by MySQL

or just procname
no it is not supported by MySQL
« Last Edit: April 07, 2010, 01:58:51 pm by Lacak2 »

motaz

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #7 on: April 07, 2010, 02:30:31 pm »
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

  • Guest
Re: calling selectable stored proc for MySQL
« Reply #8 on: April 08, 2010, 06:11:07 am »
Ok, 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 ...

how 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

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #9 on: April 08, 2010, 08:26:00 am »
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

  • Full Member
  • ***
  • Posts: 171
Re: calling selectable stored proc for MySQL
« Reply #10 on: April 08, 2010, 09:25:10 am »
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.
« Last Edit: April 08, 2010, 09:29:55 am by bobo »

motaz

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #11 on: April 08, 2010, 10:02:32 am »
unfortunately Zeos has the same problem: can't return a result set in the given context

bobo

  • Full Member
  • ***
  • Posts: 171
Re: calling selectable stored proc for MySQL
« Reply #12 on: April 09, 2010, 04:23:20 am »
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

  • Guest
Re: calling selectable stored proc for MySQL
« Reply #13 on: April 09, 2010, 06:46:51 am »
but I can not login into bug track, and there is no report a bug for Anonymous login.
You must register, before posting bug reports

anyway, 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

  • Sr. Member
  • ****
  • Posts: 495
    • http://code.sd
Re: calling selectable stored proc for MySQL
« Reply #14 on: April 09, 2010, 07:50:15 am »
that's interesting..
can I modify source code of SQLQuery.Open then recompile Lazarus?

 

TinyPortal © 2005-2018