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(at)macys(dot)com]
> Sent: 14 May 2002 20:15
> To: pgsql-odbc(at)postgresql(dot)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
>
>
>
>