Recent

Author Topic: Why TSQLQuery can not handle multiple result sets?  (Read 3592 times)

nocoder

  • Newbie
  • Posts: 3
Why TSQLQuery can not handle multiple result sets?
« on: August 28, 2016, 04:32:24 am »
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

  • New Member
  • *
  • Posts: 43
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #1 on: August 28, 2016, 10:48:18 am »
You just need to iterate the records.

Code: Pascal  [Select][+][-]
  1. SQLQuery1.First;
  2. while not SQLQuery1.EOF do
  3. begin
  4.  
  5.   SQLQuery1.Next;
  6. end;    

nocoder

  • Newbie
  • Posts: 3
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #2 on: August 28, 2016, 01:07:52 pm »
The meaning of multiple result sets does not refer to multiple records. Suppose you have a stored procedure:
Code: SQL  [Select][+][-]
  1. CREATE proc sp_getdata
  2. AS
  3.  -- Perform some complex calculations ...
  4.  -- ...
  5.  SELECT * FROM table1 -- First resultset
  6.  SELECT * FROM table2 -- Second resultset
  7.  
  8.  

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

  • Jr. Member
  • **
  • Posts: 91
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #3 on: August 28, 2016, 02:54:17 pm »
As I know such thing is MS SQL Server specific, most of other databases does not supports several results from the single call.
OS: Linux Mint + MATE, Compiler: FPC trunk (yes, I am risky!), IDE: Lazarus trunk

miab3

  • Full Member
  • ***
  • Posts: 145
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #4 on: August 28, 2016, 03:12:39 pm »
ZEOS (7.2) suports MySQL(MariaDB) stored procedures returning multiple ResultSets:
 
Code: Pascal  [Select][+][-]
  1.    { Multiple ResultSet support API }
  2.     function HasMoreResultSets: Boolean;
  3.     function GetFirstResultSet: IZResultSet;
  4.     function GetPreviousResultSet: IZResultSet;
  5.     function GetNextResultSet: IZResultSet;
  6.     function GetLastResultSet: IZResultSet;
  7.     function BOR: Boolean;
  8.     function EOR: Boolean;
  9.     function GetResultSetByIndex(const Index: Integer): IZResultSet;
  10.     function GetResultSetCount: Integer;

Code: Pascal  [Select][+][-]
  1. procedure TZStoredProc.ExecProc;
  2. begin
  3.   Connection.ShowSQLHourGlass;
  4.   try
  5.     if Active then
  6.       Close;
  7.     ExecSQL;
  8.     RetrieveParamValues;
  9.   finally
  10.     Connection.HideSQLHourGlass;
  11.   end;
  12. end;
  13.  
  14. {**
  15.   Procedure the First retrieved resultset if the givens
  16. }
  17. procedure TZStoredProc.FirstResultSet;
  18. begin
  19.   if Assigned(Statement) then
  20.     if (Statement as IZCallableStatement).HasMoreResultSets then
  21.       SetAnotherResultset((Statement as IZCallableStatement).GetFirstResultSet);
  22. end;
  23.  
  24. {**
  25.   Procedure the Previous retrieved resultset if the givens
  26. }
  27. procedure TZStoredProc.PreviousResultSet;
  28. begin
  29.   if Assigned(Statement) then
  30.     if (Statement as IZCallableStatement).HasMoreResultSets then
  31.       SetAnotherResultset((Statement as IZCallableStatement).GetPreviousResultSet);
  32. end;
  33.  
  34. {**
  35.   Procedure the Next retrieved resultset if the givens
  36. }
  37. procedure TZStoredProc.NextResultSet;
  38. begin
  39.   if Assigned(Statement) then
  40.     if (Statement as IZCallableStatement).HasMoreResultSets then
  41.       SetAnotherResultset((Statement as IZCallableStatement).GetNextResultSet);
  42. end;
  43.  
  44. {**
  45.   Procedure the Last retrieved resultset if the givens
  46. }
  47. procedure TZStoredProc.LastResultSet;
  48. begin
  49.   if Assigned(Statement) then
  50.     if (Statement as IZCallableStatement).HasMoreResultSets then
  51.       SetAnotherResultset((Statement as IZCallableStatement).GetLastResultSet);
  52. end;
  53.  
  54. {**
  55.   Retrieves a ResultSet by his index.
  56.   @param Integer the index of the Resultset
  57.   @result <code>IZResultSet</code> of the Index or nil.
  58. }
  59. procedure TZStoredProc.SetResultSet(const Index: Integer);
  60. begin
  61.   if Assigned(Statement) then
  62.     if ( Index < 0 ) or ( Index > (Statement as IZCallableStatement).GetResultSetCount -1 ) then
  63.       raise Exception.Create(Format(SListIndexError, [Index]))
  64.     else
  65.       SetAnotherResultset((Statement as IZCallableStatement).GetResultSetByIndex(Index));
  66. end;
  67.  
  68. {**
  69.   Returns the Count of retrived ResultSets.
  70.   @result <code>Integer</code> Count
  71. }
  72. function TZStoredProc.ResultSetCount: Integer;
  73. begin
  74.   Result := 0;
  75.   if Assigned(Statement) then
  76.     if (Statement as IZCallableStatement).HasMoreResultSets then
  77.       Result := (Statement as IZCallableStatement).GetResultSetCount;
  78. end;
  79.  
  80. {**
  81.   First ResultSet?
  82.   @result <code>True</code> if first ResultSet
  83. }
  84. function TZStoredProc.BOR: Boolean;
  85. begin
  86.   Result := True;
  87.   if Assigned(Statement) then
  88.     if (Statement as IZCallableStatement).HasMoreResultSets then
  89.       Result := (Statement as IZCallableStatement).BOR;
  90. end;
  91.  
  92. {**
  93.   Last ResultSet?
  94.   @result <code>True</code> if Last ResultSet
  95. }
  96. function TZStoredProc.EOR: Boolean;
  97. begin
  98.   Result := True;
  99.   if Assigned(Statement) then
  100.     if (Statement as IZCallableStatement).HasMoreResultSets then
  101.       Result := (Statement as IZCallableStatement).EOR;
  102. end;

Michal
« Last Edit: August 28, 2016, 03:25:27 pm by miab3 »

bylaardt

  • Sr. Member
  • ****
  • Posts: 309
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #5 on: August 28, 2016, 03:25:12 pm »
if you have the same set of fields, try this:

Code: SQL  [Select][+][-]
  1. SELECT * FROM table1 -- First resultset
  2. UNION
  3. SELECT * FROM table2 -- Second resultset
  4.  

nocoder

  • Newbie
  • Posts: 3
Re: Why TSQLQuery can not handle multiple result sets?
« Reply #6 on: August 28, 2016, 03:35:12 pm »
I wrote some code on github, it seems works (only TODBCConnection).
https://github.com/UnlicensedCoder/sqldb_odbc_multiresultsets

 

TinyPortal © 2005-2018