Search code examples
mysqldelphidelphi-2007dbexpress

How to process multi result set queries with dbExpress

I have such an sql statement:

UPDATE tbworker SET iState=2 WHERE iState=1;
UPDATE tbworker SEt iState=3 WHERE iState=0;

And I want to execute this statement in one request to MySQL.

Simple sql requests are excecuted this way:

SQLQuery.SQL.Text := SomeSQLText;
AffectedRows := SQLQuery.ExecSQL(true);

But now I need a method such as NextResultSet, for example:

SQLQuery.SQL.Text := MultyResultSetSQLText;
ResultSetCounter := 1;
AffectedRows.Add(ResultSetCounter, SQLQuery.ExecSQL(true));
while SQLQuery.HasNextResultSets do
begin
  Inc(ResultSetCounter);
  AffectedRows.Add(ResultSetCounter, SQLQuery.GetNextResultSet);
end;

How can dbExpress process multy result sets?

Solution

  • AFAIK the TSQLDataSet and TSQLQuery components can't handle the return of multiple datasets, the only dbexpress component which support this feature is the TSQLStoredProc object.