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!
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
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.
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.
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?
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?
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.
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?
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.
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.
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.
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
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.
