Zeoslib Portal

Posted: **16.11.2006, 07:43**

I have a very simple stored procedure named p1, containing this code:
select * from t1;

then I set up a ZConnection component and was able to successfully connect to my MySQL server...then I used a ZQuery and specified in the SQL Property a stament to call the stored procedure which is: call p1();[/B] and specified in the database property the ZConnection...but to my surprise, when I tried activating the query (by setting its property to True) it generated an error: 'Can't Return a result set in the given context'...which is a surprise, coz, calling it from console, returns a clean result set...well i tried replacing the libmysql41.dll with the libmysql.dll (then renaming it to libmysql41.dll) from my MySQL server installation folder, but still it won't work...I've seen such technique as posted by zippo in this thread http://zeos.firmos.at/viewtopic.php?t=4 ... procedures

i hope someone could help me on this..thanks to all!

Posted: **17.11.2006, 06:07**

Try to update to latest zeoslib 6.6. The post you're refering is a little bit old. zeos 6.6 has mysql5 protocol

Posted: **17.11.2006, 10:37**

ZEOSDBO-6.6.0-beta is the current version I am using...I got it from the official link posted for the beta download...
here is the link: http://zeos.firmos.at/viewtopic.php?t=837

Posted: **13.12.2006, 13:19**

I had the same problem until I'd explored the sources.
To get multiple resultset via stored procedure call, you should pass parameter CLIENT_MULTI_STATEMENTS to connection.

To do this, add string 'CLIENT_MULTI_STATEMENTS=1' to property ZConnection1.Properties.

Note that the string doesn't contain spaces!

Posted: **02.03.2007, 15:31**

hello
i also have a similar problem.
I pass this parameter show that i do not receive an error, but how do i browse through the different results?

Posted: **02.03.2007, 16:53**

iLLiCiT wrote:hello
i also have a similar problem.
I pass this parameter show that i do not receive an error, but how do i browse through the different results?

As I see, currently Zeos Lib doesn't support multiple statements 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 resultsets you should get them separately, i.e. one query - one resultset.

Posted: **04.03.2007, 08:02**

Well,

How should zeoslib components treat this different resultsets? Typically a ZQuery corresponds to one resultset/dataset. What should we do when we have multiple resultsets?

The components don't know what they will receive in this situation, so it's just guessing what the result is based on the datatypes and column names the server returns. I think that usually works out quite well.
When you have multiple resultsets everything becomes even more difficult. What will be the format of the second, third, ... result, compared to the first. Equal number of fields? (This check should be easy) Equal or comparable column datatypes?
If the component could be sure it must be all resultsets of same query we could indeed loop all resultsets, but this is an assumption we can not make as a procedure can do many different selects.
If we test on equallity and we have different result set types, how should we behave? Create more reseultsets? Where do we put them, how will you access them?

Think about it. Proposals are happyly accepted.

Mark

Posted: **05.03.2007, 15:16**

Hi Mark,
I agree with you. It's not convenient to get access to multiple result sets through ZQuery component.

But what about dbc level.
Comments in unit ZDbcMySQLStatement.pas say:

The methods <code>execute</code>,
<code>getMoreResults</code>, <code>getResultSet</code>,
and <code>getUpdateCount</code> let you navigate through multiple results.

but method getMoreResults is not implemented (inherits Result := False).

Are there any plans to support this feature for MySQL 5?

With respect to all ZeosLib team,
Andrew

Posted: **06.03.2007, 00:54**

No there are no specific plans yet. Could you do it, with a little help, eventually?

I don't think it would be difficult to implement the function using mysql api which provides a similar function. The problem will be fetching the new resultset. I have no experience with using the dbc immediately from user code, so I can't imagin which code you would like to write using dbc level.

Mark

Posted: **06.03.2007, 12:35**

mdaems wrote:I can't imagin which code you would like to write using dbc level.

Here is the sample of client's code that should handle multiple result sets:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var conn: IZConnection;
    stmt: IZStatement;
    rs: IZResultSet;
    updateCount: integer;
begin
  ZConnection1.Connect;

  conn := ZConnection1.DbcConnection;
  stmt := conn.CreateStatement;

  if stmt.Execute( 'CALL AnySP();' ) then begin
    repeat
      rs := stmt.GetResultSet;
      if rs <> nil then begin
        // do something with current resultset rs
        // ....
      end else begin
        updateCount := stmt.GetUpdateCount;
        // do something with current update count
        // ....
      end;

    until not stmt.GetMoreResults and (stmt.GetUpdateCount = -1);
  end;

  stmt := nil;
end;

mdaems wrote:Could you do it, with a little help, eventually?

I think I could.

Posted: **06.03.2007, 19:07**

That would be great...
I guarantee you that your changes get merged with the next official zeoslib version (6.7 or higher) on only 3 conditions :
- It works.
- It doesn't break up existing coding and other database driver implementations
- It is not a hack that's impossible to understand without a degree in magic

Some hint to get started : please use SVN testing branch or the most recent SVN testing snapshot to avoid merging problems afterwards. It already contains the prepared statement coding so this experimental part can already be adapted/tested as well.

For more arrangements, please contact me using pm to avoid this thread gets flooded with replies only for our purpose.

Mark

Posted: **14.03.2007, 12:30**

Hi all,

Asis did send me his patch to support multiple result sets on the dbc level.
I applied and did some basic tests. (Test suite and example provided by Asis)
As it did not show any problems I committed it to SVN testing branch. (Rev 228)

I can't promise it will get pushed to release 6.6 already but the patch is very easy if you want to do it yourself. I'll attach the SVN patch file.

For a short example : see in our Tutorials Section

Mark

Posted: **09.04.2007, 05:59**

Got that working, however when clicking the button twice, I'm getting an error message "SQL Error: Lost connection to MySQL server during query". Hitting it one more time gives me the obvious "SQL Error: MySQL server has gone away".

It seems that calling the stored procedure this way closes the connection to the server.

I'm using revision 235.

Tracing it back in Delphi, it gives me this :

Code: Select all

 + $111[19ACDD85]{ZDbc100.bpl } Zdbcmysqlutils.CheckMySQLError + $111
 + $AB[19AD49F3]{ZDbc100.bpl } Zdbcmysqlstatement.TZMySQLStatement.ExecuteQuery + $AB
 + $36[19AAA2FE]{ZDbc100.bpl } Zdbcstatement.TZEmulatedPreparedStatement.ExecuteQuery + $36
 + $2F[19AAA3FF]{ZDbc100.bpl } Zdbcstatement.TZEmulatedPreparedStatement.ExecuteQueryPrepared + $2F
 + $191[1817BEFD]{ZComponent100.bpl} Zabstractrodataset.TZAbstractRODataset.CreateResultSet + $191
 + $2E[181761BE]{ZComponent100.bpl} Zabstractdataset.TZAbstractDataset.CreateResultSet + $2E
 + $8C[1817BFE8]{ZComponent100.bpl} Zabstractrodataset.TZAbstractRODataset.InternalOpen + $8C
 + $4[030BF069]{dbrtl100.bpl} DB.DB.TDataSet.DoInternalOpen (Line 9259, "DB.pas" + 2) + $4
 + $0[51F4AC2E]{rtl100.bpl  } TypInfo.TypInfo.SetOrdProp (Line 1318, "TypInfo.pas" + 27) + $0
 + $6[20EFD30A]{designide100.bpl} DesignEditors.DesignEditors.TEnumProperty.SetValue (Line 1402, "DesignEditors.pas" + 5) + $6
 + $16[20EFB99C]{designide100.bpl} DesignEditors.DesignEditors.TPropertyEditor.Edit (Line 671, "DesignEditors.pas" + 11) + $16
 + $5[20C9697E]{coreide100.bpl} PropInsp.PropInsp.TPropertyInspector.PropListEditDblClick (Line 837, "PropInsp.pas" + 20) + $5
 + $A[20FDD681]{vclide100.bpl} IDEInspListBox.IDEInspListBox.TInspListBox.DoEditDblClick (Line 981, "ideinsplistbox.pas" + 2) + $A
 + $6[5205CA19]{vcl100.bpl  } Controls.Controls.TWinControl.WndProc (Line 7246, "Controls.pas" + 105) + $6
 + $4[20FDC857]{vclide100.bpl} IDEInspListBox.IDEInspListBox.TPropInspEdit.WndProc (Line 431, "ideinsplistbox.pas" + 7) + $4
 + $0[51F60BC0]{rtl100.bpl  } Classes.Classes.StdWndProc (Line 11572, "classes.pas" + 8) + $0
 + $6A[7E418731]{USER32.dll  } GetDC + $6A
 + $14A[7E418811]{USER32.dll  } GetDC + $14A
 + $122[7E4189C8]{USER32.dll  } GetWindowLongW + $122
 + $A[7E418A0B]{USER32.dll  } DispatchMessageW + $A

Posted: **09.04.2007, 10:16**

As far as I know, the reason of this trouble is not a closed connection.
It's, very likely, logical sequence of MySQL API calls breaks somewhere.

Could you give more detailed info, namely:
- stored procedure code;
- program code fragment that retrieves resultsets.

Posted: **09.04.2007, 13:22**

Sure, the stored procedure as currently defined as follws. It's to be extended later on. I first want to be sure that I can call the stored procedure.

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `hero`.`sp_ProjectCost` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_ProjectCost`(in_ProjectID int)
    COMMENT 'sp_ProjectCost'
BEGIN
  declare SubTotal float;

  Select sum(prkCost) as prkCost from prkProjectCosts where prkProjectID = in_ProjectID;


END $$

DELIMITER ;

The code that calls the stored procedure is the same as described above, with the obvious difference of

Code: Select all

if stmt.Execute( 'CALL sp_ProjectCost(1);' ) then begin

I have it happening with any stored procedure, not just this one.
The MyDAC components seem to do it correctly, so I don't think is a mysql problem, but a vcl one.

Zeoslib Portal

MySQL 5 stored procedures and ZEOSDBO beta

MySQL 5 stored procedures and ZEOSDBO beta

MySQL 5 stored procedures and ZEOSDBO beta