From: | "Patrick Hatcher" <PHatcher(at)macys(dot)com> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | no data returning multiple recordsets from single batch sql send |
Date: | 2002-05-14 19:15:00 |
Message-ID: | OF1198A9D1.1D58D3D7-ON88256BB9.0067AAF1@fds.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2002-05-14 23:45:59 | Re: getting rid of CTIDs |
Previous Message | Francisco Jr. | 2002-05-14 19:01:10 | [ANN] Npgsql .Net Data Provider Launch at Gborg |