Why TSQLQuery can not handle multiple result sets?
lazarus / 16-Databases

----------------------------------------------------------------------
nocoder · 2016-08-28 02:32

Sometimes I had to return one or more result sets from the stored
procedure, but TSQLQuery only get the first one. Many databases
support returning multiple result sets, but this feature is not
reflected in TSQLQuery. I hope that future versions can add this
feature.

----------------------------------------------------------------------
markus_ja · 2016-08-28 08:48

You just need to iterate the records.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.First;while not SQLQuery1.EOF dobegin   SQLQuery1.Next;end;

----------------------------------------------------------------------
nocoder · 2016-08-28 11:07

The meaning of multiple result sets does not refer to multiple
records. Suppose you have a stored procedure:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE proc
sp_getdataAS -- Perform some complex calculations ... -- ... SELECT *
FROM table1 -- First resultset SELECT * FROM table2 -- Second
resultset  
When you execute the statement: EXEC sp_getdata, then returns two
resultsets.
TSQLQuery only take the first one currently. How can I take the
second?

----------------------------------------------------------------------
Abelisto · 2016-08-28 12:54

As I know such thing is MS SQL Server specific, most of other
databases does not supports several results from the single call.

----------------------------------------------------------------------
miab3 · 2016-08-28 13:12

ZEOS (7.2) suports MySQL(MariaDB) stored procedures returning multiple
ResultSets:
 Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    {
Multiple ResultSet support API }    function HasMoreResultSets:
Boolean;    function GetFirstResultSet: IZResultSet;    function
GetPreviousResultSet: IZResultSet;    function GetNextResultSet:
IZResultSet;    function GetLastResultSet: IZResultSet;    function
BOR: Boolean;    function EOR: Boolean;    function
GetResultSetByIndex(const Index: Integer): IZResultSet;    function
GetResultSetCount: Integer;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TZStoredProc.ExecProc;begin  Connection.ShowSQLHourGlass;  try    if
Active then      Close;    ExecSQL;    RetrieveParamValues;  finally 
 Connection.HideSQLHourGlass;  end;end; {**  Procedure the First
retrieved resultset if the givens}procedure
TZStoredProc.FirstResultSet;begin  if Assigned(Statement) then    if
(Statement as IZCallableStatement).HasMoreResultSets then     
SetAnotherResultset((Statement as
IZCallableStatement).GetFirstResultSet);end; {**  Procedure the
Previous retrieved resultset if the givens}procedure
TZStoredProc.PreviousResultSet;begin  if Assigned(Statement) then   
if (Statement as IZCallableStatement).HasMoreResultSets then     
SetAnotherResultset((Statement as
IZCallableStatement).GetPreviousResultSet);end; {**  Procedure the
Next retrieved resultset if the givens}procedure
TZStoredProc.NextResultSet;begin  if Assigned(Statement) then    if
(Statement as IZCallableStatement).HasMoreResultSets then     
SetAnotherResultset((Statement as
IZCallableStatement).GetNextResultSet);end; {**  Procedure the Last
retrieved resultset if the givens}procedure
TZStoredProc.LastResultSet;begin  if Assigned(Statement) then    if
(Statement as IZCallableStatement).HasMoreResultSets then     
SetAnotherResultset((Statement as
IZCallableStatement).GetLastResultSet);end; {**  Retrieves a ResultSet
by his index.  @param Integer the index of the Resultset  @result
<code>IZResultSet</code> of the Index or nil.}procedure
TZStoredProc.SetResultSet(const Index: Integer);begin  if
Assigned(Statement) then    if ( Index < 0 ) or ( Index > (Statement
as IZCallableStatement).GetResultSetCount -1 ) then      raise
Exception.Create(Format(SListIndexError, [Index]))    else     
SetAnotherResultset((Statement as
IZCallableStatement).GetResultSetByIndex(Index));end; {**  Returns the
Count of retrived ResultSets.  @result <code>Integer</code>
Count}function TZStoredProc.ResultSetCount: Integer;begin  Result :=
0;  if Assigned(Statement) then    if (Statement as
IZCallableStatement).HasMoreResultSets then      Result := (Statement
as IZCallableStatement).GetResultSetCount;end; {**  First ResultSet? 
@result <code>True</code> if first ResultSet}function
TZStoredProc.BOR: Boolean;begin  Result := True;  if
Assigned(Statement) then    if (Statement as
IZCallableStatement).HasMoreResultSets then      Result := (Statement
as IZCallableStatement).BOR;end; {**  Last ResultSet?  @result
<code>True</code> if Last ResultSet}function TZStoredProc.EOR:
Boolean;begin  Result := True;  if Assigned(Statement) then    if
(Statement as IZCallableStatement).HasMoreResultSets then      Result
:= (Statement as IZCallableStatement).EOR;end;
Michal

----------------------------------------------------------------------
bylaardt · 2016-08-28 13:25

if you have the same set of fields, try this:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM table1 -- First resultsetUNION SELECT * FROM table2 -- Second
resultset

----------------------------------------------------------------------
nocoder · 2016-08-28 13:35

I wrote some code on github, it seems works (only TODBCConnection).
https://github.com/UnlicensedCoder/sqldb_odbc_multiresultsets