Stored proc and Lost connection to mysql server - workaround

[law]

when running a stored proc with call stmt on mysql, the next statement will raise a Lost connection to mysql server error.
this is because multiple-result processing is required when calling a sp with call stmt (see http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html).
zeosdbo processes only the first result so when you execute the next statement the Lost connection error occures.
test project is attached and the workaround is here (note this code doesn't really process multiple statements just the first, then loop through and ignores the others).
i tested this with ZeosDBO6.6.0 and ZeosDBO6.6.2 on MySQL5.0.45.

Code: Select all

procedure TZMySQLResultSet.Open;
var
  I: Integer;
  ColumnInfo: TZColumnInfo;
  FieldHandle: PZMySQLField;
  FieldFlags: Integer;
begin
  if ResultSetConcurrency = rcUpdatable then
    raise EZSQLException.Create(SLiveResultSetsAreNotSupported);

  if FUseResult then
  begin
    FQueryHandle := FPlainDriver.UseResult(FHandle);
    LastRowNo := 0;
  end
  else
  begin
    FQueryHandle := FPlainDriver.StoreResult(FHandle);
    if Assigned(FQueryHandle) then
      LastRowNo := FPlainDriver.GetRowCount(FQueryHandle)
    else LastRowNo := 0;
  end;

  // +law 20080120
  repeat
  until FPlainDriver.RetrieveNextRowset(FHandle)<>0;
  // -law 20080120

  if not Assigned(FQueryHandle) then
    raise EZSQLException.Create(SCanNotRetrieveResultSetData);

  { Fills the column info. }
  ColumnsInfo.Clear;
  for I := 0 to FPlainDriver.GetFieldCount(FQueryHandle) - 1 do
  begin
    FPlainDriver.SeekField(FQueryHandle, I);
    FieldHandle := FPlainDriver.FetchField(FQueryHandle);
    if FieldHandle = nil then
      Break;

    ColumnInfo := TZColumnInfo.Create;
    with ColumnInfo do
    begin
      FieldFlags := FPlainDriver.GetFieldFlags(FieldHandle);

      ColumnLabel := FPlainDriver.GetFieldName(FieldHandle);
      TableName := FPlainDriver.GetFieldTable(FieldHandle);
      ReadOnly := (FPlainDriver.GetFieldTable(FieldHandle) = '');
      ColumnType := ConvertMySQLHandleToSQLType(FPlainDriver,
        FieldHandle, FieldFlags);
      ColumnDisplaySize := FPlainDriver.GetFieldLength(FieldHandle);
      Precision := Max(FPlainDriver.GetFieldMaxLength(FieldHandle),
        FPlainDriver.GetFieldLength(FieldHandle));
      Scale := FPlainDriver.GetFieldDecimals(FieldHandle);
      if (AUTO_INCREMENT_FLAG and FieldFlags <> 0)
        or (TIMESTAMP_FLAG and FieldFlags <> 0) then
        AutoIncrement := True;
      if UNSIGNED_FLAG and FieldFlags <> 0 then
        Signed := False
      else Signed := True;
      if NOT_NULL_FLAG and FieldFlags <> 0 then
        Nullable := ntNoNulls
      else Nullable := ntNullable;
      // Properties not set via query results here will be fetched from table metadata.
    end;

    ColumnsInfo.Add(ColumnInfo);
  end;

  inherited Open;
end;

[mdaems]

Hi Law,

Thanks for this workaround. There's only one problem with this... I think it breaks the possibility to handle multiple resultsets using the zdbc interfaces directly.
For people using only the components this workaround seems allright.

Also thanks for the test project. It looks complete but I didn't test it yet. I'll do it when I have some time to spend. So the idea below is not proven to be better than yours. Just have a look at it, maybe together we can work out a good solution that can be added to the Zeos source code.

My idea : A solution may be to add your workaround when closing the statement. By overriding TZAbstractStatement.Close in the ZMysqlXXXStatement classes. Have a look at ZDbcMysqlStatement.pas. This way any pending resultsets are cleaned when ending the statement.
This solution only works when 2 different statements can be processed in parallel. If mysql fails on fetching from a second statement handle while a first statement handle with multiple resultsets is still open, we still have a problem.

It would be nice to get a reaction on this. The problem has been annoying since mysql invented the stored procedure. So if we can solve it without breaking dbc functionality, a lot of people will be happy.

Mark

[law]

hi Mark

yes, with this workaround can't process multiple results with zdbc interface. but i guess if someone can use this 'low level' interface, he doesn't need this workaround. anyway, zdbc is too high for me, hahh.

as i can see, the CALL stmt always returns more resultset (or work like this way), and the client MUST process all results BEFORE sends any other statements to the server. the multiple-result processing have to be done right after running the statement if the client wants to run any other statements.
this code raise CR_SERVER_LOST (error 2013) as you can see in the test project:

Code: Select all

zquery1.sql.text:='CALL storedp()';
zquery1.open;
zquery2.sql.text:='SELECT * FROM table';
zquery2.open;

so my workaround in TZAbstractStatement.Close doesn't solve the problem.

others wrote in some threads to run a sp set CLIENT_MULTI_STATEMENTS to 1 for the connection.
if CLIENT_MULTI_STATEMENTS=1 you can run multiple statements separated with ';' in one query (in c api). when you do this the error will occur too.
mysql doc says if client want to process resultsets from sp, CLIENT_MULTI_RESULTS must be enabled (tried, it works). CLIENT_MULTI_STATEMENTS implicitly enables CLIENT_MULTI_RESULTS, so that works too. i just write it to see clear.