MySQL5 - Stored procedures

[awerner]

Is it possible to use the new features in MySQL5 eg Stored Procedures, Triggers and so on?

If I use a common "SELECT-statement" I get a proper answer from MySQL, but if I call a Stored Procedure containing the same query I get the following:
"SQL Error: Procedure db5.p1 can't return a result set in the given context."

//Alf

[zippo]

I'm using latest Zeos and have no problems at all.

[awerner]

Thanks Zippo,
I'm also using the latest Zeos (downloaded yesterday) and MySQL 5.0.13-rc-nt.

This is a new topic to me - can you please give some hint about it?

//Alf

[zippo]

Okay.

First replace the libmysql41.dll with the one provided with MySQL 5.x. Then as the protol select mysql-4.1 or mysql (I'm not sure right now). Et voila'! You are ready to connect to a 5.x server!

I'm porting a quite large application and so fra had no problems at all (about 80% done).

[uwwin]

what kind of zeos.....? 6.5.1 alpha or rework?
i have run store procedure in console and it's working
but if i run in zeos error....
the statement like this....
call name_store('param');

[mdaems]

Uwin,

For the moment we are speaking about the alpha version you can get from svn (testing branch). See other posts for details about this. CVS isn't used anymore.
You can get a quite recent copy at http://users.telenet.be/mdaems

Mark

[uwwin]

For the moment we are speaking about the alpha version you can get from svn (testing branch). See other posts for details about this. CVS isn't used anymore.
You can get a quite recent copy at http://users.telenet.be/mdaems

Mark

Thank 4 ur info... i'll try to download this version...

[mdaems]

Hi Uwwin,

Meanwhile we have released our beta version. See portal page of this forum or sourceforge.
Mark

[Asis]

to awerner:

Code: Select all

ZConnection.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
ZConnection.Connect;

From now you can execute SP, that return resultset.
For instance:

Code: Select all

  ZQuery1.SQL.Add('call myproc();');
  ZQuery1.Open;

[andreass]

Hi,

I'm using mysql 5.0 and the newest beta components.
I tried to use a stored procedure using the tzquery.sql property.
But If I have an OUT Variable in the stored procedure I get an error.

Out or INOUT argument 2 for NEUER_KUNDE is not a variable.

The Code of the storec Procedure is the following. And in MYSQL it is working fine.

Code: Select all

CREATE PROCEDURE `NEUER_KUNDE`(in match_var varchar(15), out kdnr_var integer)
    BEGIN
      INSERT INTO KUNDEN
       (MATCHCODE)
      VALUES
        (MATCH_var);
      SET KDNR_var = LAST_insert_id(); 
    END$$

The SQL.Property of the TZquery component is

call NEUER_KUNDE(:MATCH, :KDNUMMER)

What is wrong here.

Andreas

[iLLiCiT]

call NEUER_KUNDE(:MATCH, :KDNUMMER)

I think you should try something like

Code: Select all

call NEUER_KUNDE(:MATCH, @VARIABLE);
SELECT @VARIABLE;

I've read this somewhere... but i still cannot find a solution regarding my problem. using CLIENT_MULTI_STATEMENTS=1 i'm able to accept multiple results. if i try

Code: Select all

while not zquery1.eof do
begin
---CODE---
zquery1.next
end;

i only browse results included in the first resultset ... how can i browse to the second result set?

[Asis]

Hi,

I'm using mysql 5.0 and the newest beta components.
I tried to use a stored procedure using the tzquery.sql property.
But If I have an OUT Variable in the stored procedure I get an error.

Out or INOUT argument 2 for NEUER_KUNDE is not a variable.

The Code of the storec Procedure is the following. And in MYSQL it is working fine.

Code: Select all

CREATE PROCEDURE `NEUER_KUNDE`(in match_var varchar(15), out kdnr_var integer)
    BEGIN
      INSERT INTO KUNDEN
       (MATCHCODE)
      VALUES
        (MATCH_var);
      SET KDNR_var = LAST_insert_id(); 
    END$$

The SQL.Property of the TZquery component is

call NEUER_KUNDE(:MATCH, :KDNUMMER)

What is wrong here.

Andreas

To make the query work from your program code, you may do the following:

Correct the code of stored procedure this way:

Code: Select all

CREATE PROCEDURE `NEUER_KUNDE`(in match_var varchar(15))
BEGIN
  INSERT INTO KUNDEN
    (MATCHCODE)
  VALUES
    (MATCH_var);

  SELECT LAST_insert_id();
END

Correct the SQL property of the TZQuery component like this:

Code: Select all

call NEUER_KUNDE(:MATCH);

And use this code in your program unit:

Code: Select all

var kdnr_var: integer;
begin
...
  ZQuery1.Close;
  ZQuery1.ParamByName('MATCH').AsString := 'Any value';
  ZQuery1.Open;
  kdnr_var := ZQuery1.Fields[0].AsInteger;

...
end;

Do not forget to insert string 'CLIENT_MULTI_STATEMENTS=1' into ZConnection.Properties.

[Asis]

...but i still cannot find a solution regarding my problem. using CLIENT_MULTI_STATEMENTS=1 i'm able to accept multiple results. if i try

Code: Select all

while not zquery1.eof do
begin
---CODE---
zquery1.next
end;

i only browse results included in the first resultset ... how can i browse to the second result set?

Currently Zeos Lib doesn't support multiple result sets for MySQL.

Property CLIENT_MULTI_STATEMENTS=1 only lets you avoid errors like 'Can't Return a result set in the given context'
on calling stored procedures.

Thus, to browse through different result sets you should get them separately, i.e. one query - one resultset.

[bravecobra]

Is there a way of getting only the last resultset instead of the first one? Which would make more sense when that last is the result of a stored procedure. I'm not in need (currently) of the select statements I make within the stored procedure.

[mdaems]

Hoi bravecobra,

Getting more resultsets (and so the last as well) from a query is only possible using the zdbc interfaces. There is a small example here : http://zeos.firmos.at/viewtopic.php?t=1141

More information can be found here :
http://zeos.firmos.at/viewtopic.php?t=295
http://zeos.firmos.at/viewtopic.php?t=912

I hope this helps you?

Mark