Thread: Re: no data returning multiple recordsets from single batch sql s

Try using a union of the three selects rather than three seperate selects
hth,
- Stuart

> -----Original Message-----
> From: Patrick Hatcher [mailto:PHatcher@macys.com]
> Sent: 14 May 2002 20:15
> To: pgsql-odbc@postgresql.org
> Subject: no data returning multiple recordsets from single batch sql
> send
>
>
> Driver version:  7.1.10
> ADO version 2.6
>
> Hello,
> I have an Excel app that sends a batch query with 3 select
> statements. This
> is to reduce network traffic. I must be setting something incorrectly
> because I get no data and it appears only 1 recordset is
> being returned.
> This code works fine with MS SQL. Can the driver handle multiple
> recordsets?
>
> I've tried using a connect statement with a cursorlocation
> set to client,
> but this results in a 0 RecordCount.  With the cursor set to
> server, I get
> a -1 which is to be expected.
> Any guidence would be appreciated.
> TIA
> -patrick
>
> Public Const strsqlA As String = "SELECT feddiv, COUNT(UPC)
> AS CountOfOZUPC
> FROM Chesh_No_OZ_v GROUP BY feddiv;" & _
>                                   "SELECT DivID, COUNT(UPC)
> AS CountOfOZUPC
> fROM FL_No_OZ_v GROUP BY DivID;" & _
>                                   "SELECT DivID , COUNT(UPC) AS
> CountOfOZUPC FROM Loc_No_OZ_v GROUP BY DivID"
>
> ' Open compound recordset.
> strCnn = "Driver
> ={Postgresql};Server=myserver;Database=mdc_oz;UID=mcom_user;PWD="
>
> Set Rs = New ADODB.Recordset
> mySQLArray = Array(strsqlA, strsqlB, strsqlC, strsqlD, strsqlE,
> strsqlCount)
>
>
>    Set Rs = New ADODB.Recordset
>    WS.Activate
>    WS.Cells.Select
>    Selection.ClearContents
>    WS.Range("A1").Select
>     X = 2
>
>
>   Rs.Open mySQLArray(ArrayLoop), strCnn
>   DoEvents
>   If Rs.RecordCount = 0 Then
>     MsgBox "No Records are available for your selection"
>     Rs.Close
>   Else
>
>     'Send Header
>     fldCount = Rs.Fields.Count
>
>     For R = 1 To fldCount
>          WS.Cells(1, R).Value = Rs.Fields(R - 1).Name
>     Next R
>     WS.UsedRange.Font.Bold = True
>
>
>     ' Try printing results from each of the 3 SELECT
>     ' statements.
>     booNext = True
>     intCount = 1
>
>     Do Until Rs Is Nothing
>        Do While Not Rs.EOF
>          WS.Range("A" & X).CopyFromRecordset Rs
>         Loop
>       Set Rs = Rs.NextRecordset
>       X = getlastrow(WS, X)
>     Loop
>
>
>     Application.Selection.CurrentRegion.Columns.AutoFit
>     Application.Selection.CurrentRegion.Rows.AutoFit
>
>
>     ' Close ADO objects
>     If Not Rs Is Nothing Then
>     Rs.Close
>     End If
>
>
>   End If
> Next
>
> Set Rs = Nothing
>
>
> -TIA
>
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
>
>
>
>