zimmer9
asked on
How would you return the values when calling a Sybase stored procedure call ?
I am developing an Access mdb application using Access as the front end and I call a sybased stored procedure named sp3D_GetListOfDocuments. My code is the Attach Code Snippet.
How would you modify this code to execute the stored procedure that returns
a result set which is comprised of the following 3 columns:
1) MailID
2) NoOfPages
3) NoOfAttributes
This stored procedure does not require any parameters.
My goal is to load the result set records by APPENDING THEM into the table tblNameAddressMod.
In the following Attach Code Snippet, the table tblNameAddressMod has the same 3 fields:
1) MailID
2) NoOfPages
3) NoOfAttributes
For Example, the result set could have the following values:
-------------------------- ---------- ---------- ---------- ---------- ------
MailID NoOfPages NoOfAttributes
----------------- ---------------- -------------------
200707091208 1 3
200707091235 1 3
200707091356 1 3
How would you modify this code to execute the stored procedure that returns
a result set which is comprised of the following 3 columns:
1) MailID
2) NoOfPages
3) NoOfAttributes
This stored procedure does not require any parameters.
My goal is to load the result set records by APPENDING THEM into the table tblNameAddressMod.
In the following Attach Code Snippet, the table tblNameAddressMod has the same 3 fields:
1) MailID
2) NoOfPages
3) NoOfAttributes
For Example, the result set could have the following values:
--------------------------
MailID NoOfPages NoOfAttributes
----------------- ---------------- -------------------
200707091208 1 3
200707091235 1 3
200707091356 1 3
Private Sub Command9_Click()
Dim com As ADODB.Command
Dim recNameAdress As ADODB.Recordset
Dim cmd As ADODB.Command
Dim oConn
Dim strMailID As String
Dim strNoOfPages As String
Dim strNoOfAttributes As String
DoCmd.Hourglass True
On Error Resume Next
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DSN=N_DIG_MAIL;" & _
"Uid=PC;" & _
"Pwd=po"
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT MailID FROM tblNameAddressMod", CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = oConn
.CommandType = adCmdText
.CommandText = "exec sp3D_GetListOfDocuments"
Set recNameAdress = .Execute
End With
strMailID = recNameAdress("MailID")
strNoOfPages = Nz(recNameAdress("NoOfPages"), " ")
strNoOfAttributes = Nz(recNameAdress("NoOfAttributes"), " ")
CurrentProject.Connection.Execute "INSERT INTO tblNameAddress Set MailID ='" & Replace(strMailID, "'", "''") & "', " & _
"NoOfPages ='" & strNoOfPages & "', NoOfAttributes ='" & strNoOfAttributes & " "
recNameAdress.Close
ASKER
I am a user who needs to access a Sybase database and call a stored procedures exposed to me to get information as a Sybase result set. I don't have any choice in the matter.
ASKER
Can anyone confirm whether or not the solution above proposed by Felix should work as is
using ADO with Sybase ? I have tried implementing it and as I step throught the execution I get to the following line in which the recordset is opened and the EOF is reached immediatately and the subroutine exits.
'Open command object - executes SP
recSP.Open oCmd
If recSP.EOF Then
'Handle error...
Exit Sub
End If
thanks,
zimmer9
using ADO with Sybase ? I have tried implementing it and as I step throught the execution I get to the following line in which the recordset is opened and the EOF is reached immediatately and the subroutine exits.
'Open command object - executes SP
recSP.Open oCmd
If recSP.EOF Then
'Handle error...
Exit Sub
End If
thanks,
zimmer9
Hi Zimmer9
I just re-checked my code, and I think I forgot to open the connection. Try to add these 2 lines
10a oConn.Provider = "ASEOLEDB"
11a oConn.Open
Let me know how you go
I just re-checked my code, and I think I forgot to open the connection. Try to add these 2 lines
10a oConn.Provider = "ASEOLEDB"
11a oConn.Open
Let me know how you go
ASKER
Where is the linkage between the result set from the execution of stored procedure sp3D_GetListOfDocuments and the record set recSP ?
By the way, I use ODBC with DataDirect 4.0 Sybase Wire Protocol driver.
By the way, I use ODBC with DataDirect 4.0 Sybase Wire Protocol driver.
ASKER
Don't I need a .Execute line in the following With/End With ?
With oCmd
.ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "sp3D_GetListOfDocuments"
End With
With oCmd
.ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "sp3D_GetListOfDocuments"
End With
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you were almost there!
You can directly execute a stored procedure (SP) using ADO with MS SQL (I don't have Sybase).
You did not specify the data types of the columns returned. I would have thought that the 2nd and 3rd one were numeric. You need to watch out for conversion problems.
While your approach to build a SQL Insert statement is valid, I prefer to use a recordset.
Hope that helps
Felix Burkhard
Open in new window