Solved

How would you return the values when calling a Sybase stored procedure call ?

Posted on 2008-10-15
7
316 Views
Last Modified: 2013-11-27
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

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

Open in new window

0
Comment
Question by:zimmer9
  • 4
  • 3
7 Comments
 
LVL 9

Expert Comment

by:borki
ID: 22728261
Hi Zimmer9

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


Private Sub test()

   Dim oConn As New ADODB.Connection

   Dim oCmd As New ADODB.Command

   Dim recSP As New ADODB.Recordset

   Dim recMod As New ADODB.Recordset

    

   DoCmd.Hourglass True

   On Error Resume Next

   

   'Prepare command object with SP

   oConn.ConnectionString = "DSN=N_DIG_MAIL;Uid=PC;Pwd=po"

   With oCmd

       .ActiveConnection = oConn

       .CommandType = adCmdStoredProc

       .CommandText = "sp3D_GetListOfDocuments"

   End With

       

   'Open command object - executes SP

   recSP.Open oCmd

   If recSP.EOF Then

      'Handle error...

      Exit Sub

   End If

   

   'Open local dataset

   recMod.Open "tblNameAddressMod", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic, adCmdTable

   

   'Loop through records retrieved from SP

   Do While Not recSP.EOF

      'Copy data from SP output into new local record

      recMod.AddNew

      recMod!MailID = recSP!MailID

      recMod!NoOfPages = Nz(recSP!NoOfPages, " ")

      recMod!NoOfAttributes = Nz(recSP!NoOfAttributes, " ")

      recMod.Update

      'Advance cursor to next row

      recSP.MoveNext

   Loop

   recSP.Close

   recMod.Close

   Set recSP = Nothing

   Set recMod = Nothing

   DoCmd.Hourglass False

End Sub

Open in new window

0
 

Author Comment

by:zimmer9
ID: 22729939
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.

0
 

Author Comment

by:zimmer9
ID: 22730040
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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 9

Expert Comment

by:borki
ID: 22735156
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

0
 

Author Comment

by:zimmer9
ID: 22742242
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.
0
 

Author Comment

by:zimmer9
ID: 22742274
Don't I need a .Execute line in the following With/End With ?

With oCmd
       .ActiveConnection = oConn
       .CommandType = adCmdStoredProc
       .CommandText = "sp3D_GetListOfDocuments"
End With
0
 
LVL 9

Accepted Solution

by:
borki earned 500 total points
ID: 22751574
Hi Zimmer9

There are two types of commands you can have with ADO,
1) use Execute to run an Action query (Insert/Update/Delete)
2) use Recordset.Open to run a Select query that returns rows of data

Did you try the code with the modification I sent?

I am not sure what DataDirect 4 for Sybase is and does... I gound some references on the Sybase site that uses "ASEOLEDB" - what appears to be a specific OLEDB provider for Sybase. Not sure where ODBC fits in, but if you are using it, I would think that your remote data is already accessible via a linked table.

Good luck



0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now