MySQL 5

[dhongu]

I have a procedure ex: proced()

from mysql command prompter

mysql > call proced()

work !

mysql > select 1;

work !

and work fine.

But from delphi with zeos not work correctly. I find “solution” with command "sleep" between "call procd()" and "select1" .



query.sql.text:='call proced()';
query.exec;
sleep(500); /// without this linie not work !!
query.sql.text:='select 1';
query.exec;

i am sorry for my English.

[zippo]

Try to use ".Open" instead of ".Exec".

[Visor123]

Try to use ".Open" instead of ".Exec".

Call proced() ZQuery.Open ??? why?

Use libmysql.dll from that version mysql 5, in which work.

[pol]

Mmh... Open is always better when retrieving a result set, which your second query does. What does your procedure?
Another thing: when you are typing these commands at the mysql command prompt, the pause between your first and second pressing "enter" will be longer than sleep(500) which is 0,5 sec.
Btw what is the error message you get without sleep?

[dhongu]

I use libmysql.dll from that version mysql 5 and renamed to libmysql41.dll. If procedure is short, time to pause maybe small. Doesn't matter contents from procedure.

[pol]

But what happens when you don't use sleep? What kind of error message?
Do I understand you right that it depends on the length of your procedure (in that way content matters!) how long the sleep must be?

[dhongu]

if don't use "sleep" error message is "Lost connection".
Long the sleep depends by lenght of procedure.

I tray to execute from EMS SQL Maneger and work but from Navicat 2005 not.
Is strange

[pol]

Strange, really. Have you tried to run it in debug mode? Put a stop point on the first query.sql.text:=... and then proceed with F8. Watch if the curser stops a short time on the exec or if it goes right on to the next statement. That would mean that the exec "comes back" before the stored procedure is finished. Not good. What does it look like at the mysql command prompt? Is there a little pause before you can type in anything new?

[dhongu]

It is example:

CREATE PROCEDURE `dorepeat`(p1 INTEGER(11))
BEGIN
select @x:=0; -- without this line work in any situation
set @x=0;
REPEAT SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
END


call dorepeat(100000); -- work
select (@x)

call dorepeat(1000000); --don't work
select (@x)

[dhongu]

If use debug mode and press F8 before take o lilte pause work.

[zippo]

Try to use LargeInt insteda of Integer

CREATE PROCEDURE `dorepeat`(p1 LargeInt)

[dhongu]

I resolved.

I use path [ 1220418 ] !Quick and durty! patch for 5.x stored procedures http://sourceforge.net/tracker/index.ph ... tid=415826

I implement link to function mysql_next_result. http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

In unit ZDbcMySqlStatement I introdus repeat.
[syntax="delphi"]
function TZMySQLStatement.ExecuteUpdate(SQL: string): Integer;
var
QueryHandle: PZMySQLResult;
begin
Result := -1;
if FPlainDriver.ExecQuery(FHandle, PChar(SQL)) = 0 then
begin
DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
{ Process queries with result sets }
if FPlainDriver.GetStatus(FHandle) <> MYSQL_STATUS_READY then
begin
repeat /// new line
QueryHandle := FPlainDriver.StoreResult(FHandle);
if QueryHandle <> nil then
begin
Result := FPlainDriver.GetRowCount(QueryHandle);
FPlainDriver.FreeResult(QueryHandle);
end else
Result := FPlainDriver.GetAffectedRows(FHandle);
until FPlainDriver.NextResult(FHandle)<>0; /// new line

end
{ Process regular query }
else Result := FPlainDriver.GetAffectedRows(FHandle);
end else
CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
LastUpdateCount := Result;
end;
[/syntax]

[Visor123]

It is example:
CREATE PROCEDURE `dorepeat`(p1 INTEGER(11))
BEGIN
select @x:=0; -- without this line work in any situation
set @x=0;
REPEAT SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
END

call dorepeat(100000); -- work
select (@x)
call dorepeat(1000000); --don't work
select (@x)

The correct sintaxis
CREATE PROCEDURE `dorepeat`(p1 INTEGER(11))
BEGIN
select 0*1
INTO @x;
/*or SET @x=0;*/
REPEAT SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
END

[Michael]

Hi there!

Thanks a lot for all the work, here ! I'll try to implement the "Quick&Dirty" patch into MySQL-5-protocol-source and let our test suite run over it. In deed we also had some problems with storedprocs in our tests when using MySQL 5. With MySQL 4 there were no probs at all...

Again: , guys!

Regards.

P.S.: You are the best!

[Visor123]

I resolved.

I use path [ 1220418 ] !Quick and durty! patch for 5.x stored procedures http://sourceforge.net/tracker/index.ph ... tid=415826

I implement link to function mysql_next_result. http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

In unit ZDbcMySqlStatement I introdus repeat.
[syntax="delphi"]
function TZMySQLStatement.ExecuteUpdate(SQL: string): Integer;
var
QueryHandle: PZMySQLResult;
begin
Result := -1;
if FPlainDriver.ExecQuery(FHandle, PChar(SQL)) = 0 then
begin
DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
{ Process queries with result sets }
if FPlainDriver.GetStatus(FHandle) <> MYSQL_STATUS_READY then
begin
repeat /// new line
QueryHandle := FPlainDriver.StoreResult(FHandle);
if QueryHandle <> nil then
begin
Result := FPlainDriver.GetRowCount(QueryHandle);
FPlainDriver.FreeResult(QueryHandle);
end else
Result := FPlainDriver.GetAffectedRows(FHandle);
until FPlainDriver.NextResult(FHandle)<>0; /// new line

end
{ Process regular query }
else Result := FPlainDriver.GetAffectedRows(FHandle);
end else
CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
LastUpdateCount := Result;
end;
[/syntax]

Where FPlainDriver.NextResult(FHandle)<>0; /// new line
NextResult introducing ???