Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSAccess2003 ODBCDirect call should return recordset but always get -1 recordcount

Posted on 2008-10-23
6
Medium Priority
?
415 Views
Last Modified: 2011-09-20
I am attempting to use MS Access 2003 to execute a ODBCDirect call to a stored proc on a DB2 back end running on AIX.  

My environment is:
Windows XP Pro Sp2
MS Office Access 2003 SP3
MS DAO 3.6
IBM DB2 ODBC DRIVER Version 8.01.14.292 configured as a System DSN

I have included two function examples of my code (test code so pls no comments on coding ettiquite!)

The DB2 Stored Proc in the following examples simply takes as input a single parameter and echoes back that parameter value as a multie record recordset containing a single field.
 
The first example is the function ParamSPT, which has the input to the stored proc coded directly into the SQL call (e.g., the generated SQL is "{ call prc_test( 'abc123' ) }".  The result of my OpenRecordset() method is as expected, I am able to see that my Recordset has multple records in it.

However, The DB2 engineer wants to, in addition to the recordset return, pass back output parameters and MS Access does not seem to want to allow me to configure anything like { call prc_test2('abc123', ?, ?, ?) } as it generates an exception stating I have given an incorrect number of arguments.  More on this in a moment.

The second function I have created, ParamSPTODBC, uses ODBCDirect to create the Query Def.  I (more appropriately) define the signature of the stored proc call, then specify what each of the parameters are.  In the code given in the example, I have only one input parameter.  The OpenRecordset() command completes successfully, however, I never receive back the Recordset (or don't appear to).  While MyRs.RecordCount = -1, MyRS.EOF and .BOF are both FALSE.  Calling MyRs.MoveNext results in a "Runtime Error 3219 - Invalid Operation".

Interestingly enough, I am able to configure both input and output paramters, and I do see the return values for those when I call the prc_test2 stored proc.

So!  I am at an impasse.  I would prefer to call the stored procs in the standard way using the methodology that the DB2 Engineer would like me to use (having both input and output params) but am unable to do that and also retrieve recordsets.

Any help you can provide here is greatly appreciated!
Function ParamSPT(strVal As String)
    
    Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset
    Set MyDb = CurrentDb()
    Set MyQry = MyDb.CreateQueryDef("")
    
    ' Type a connect string using the appropriate values for your
    ' server.
    MyQry.Connect = "ODBC;DSN=<snip>;UID=<snip>;PWD=<snip>;DBALIAS=<snip>;PATCH1=131072;LOBMAXCOLUMNSIZE=1048575;LONGDATACOMPAT=1;"
    
    ' Set the SQL property and concatenate the variables.
    MyQry.sql = "{ call ttndbo.prc_test ( '" & strVal & "' ) }"
    
    MyQry.ReturnsRecords = True
    
    Set MyRS = MyQry.OpenRecordset()
    MyRS.MoveLast
    Debug.Print MyRS.RecordCount
        
    MyQry.close
    MyRS.close
    MyDb.close
    
End Function
 
 
 
Function ParamSPTODBC(strVal As String)
    
    Dim wrkMain As Workspace, MyQry As QueryDef, MyRS As Recordset
    Dim conMain As Connection
    Dim strSql As String
         
    ' Create ODBC workspace and open a connection
    Set wrkMain = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
    
    Set conMain = wrkMain.OpenConnection("", dbDriverComplete, True, "ODBC;DSN=<snip>;UID=<snip>;PWD=<snip>;DBALIAS=<snip>;PATCH1=131072;LOBMAXCOLUMNSIZE=1048575;LONGDATACOMPAT=1;")
    
    ' Set SQL string to call the stored procedure
    strSql = "{ call ttndbo.prc_test ( ? ) }"
 
    Set MyQry = conMain.CreateQueryDef("", strSql)
    
    With MyQry
        ' Indicate that the two query parameters will only
        ' pass information to the stored procedure.
        .Parameters(0).Direction = dbParamInput
        
        ' Assign initial parameter values.
        .Parameters(0) = strVal
        
        Set MyRS = .OpenRecordset()
        
        Debug.Print MyRS.RecordCount
        MyRS.MoveLast   ' <-- Generic Error as MyRs.RecordCount = -1
        Debug.Print MyRS.RecordCount
        MyRS.MoveFirst
        Debug.Print .Fields(0).Name & " = " & .Fields(0)
        
    End With
    
    MyRS.close
    MyQry.close
    conMain.close
    wrkMain.close
    
End Function

Open in new window

0
Comment
Question by:MAGRATTON
  • 3
  • 3
6 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 1000 total points
ID: 22788763
maybe this sample will help you
http://www.experts-exchange.com/Database/DB2/Q_20951083.html
i didn't understand what happened when you tried using multiple parameters, which error did you receive?
0
 

Author Comment

by:MAGRATTON
ID: 22788819
Reviewing your link in a moment.  

To answer your question, if I specify the call in my function ParamSPT as

{call prc_test2('abc213", ?, ?, ?) }  

instead of {call prc_test('abc123') }

I get a generic error ("ODBC -- call failed.").  Using the query editor in MS Access I get a slightly more meaningful error ("OCBD -- Call Failed.  [IBM][CLI Driver] CLI0100E Wrong number of paramters.  SQLSTATE=07001 (#-99999)")

In the second method, I am able to specify any number of input and output paramters, receive results for them, but do not receive back the expected recordset.
0
 

Accepted Solution

by:
MAGRATTON earned 0 total points
ID: 22789534
OKAY...
I noticed in your linked example that they were using ADO, whereas I am using DAO.

So I coded a new version of my function using ADO.  Executed the function with anticipation of success and ....  !!  same result.  ResultCount = -1.  No Records returned.

For some reason ( I cannot recall why, but think it was due to an "invalid operation" error I had received on the previous pass through the function) in the next pass through the function I changed "myRs.MoveLast" to "myRs.MoveNext" as I was stepping through the code.  Much to my surprise... IT DID!?!

Huh? are there records there?  In my immediate window -> ?MyRs.Fields(0) <enter> and out pops the expected return value.   I check the .RecordCount again and still -1.  Tried .MoveNext again and it worked, etc. all the way to the end of the expected return result.  In other words, I got back all of the expected records!

Went back to my original DAO version of the code and Surprise! Surprise!, it works exactly the same as the ADODB version.  RecordCount = -1, but the records are actually there if I .MoveNext through them.  BTW: the .EOF method returns TRUE and FALSE expectedly, so even through you do not initially know how many records are returned, you can still iterate through the recordset result until .EOF = TRUE.

So, where are we:
- DAO and ADO both work, though RecordCount always seems to = -1.  
- MoveFirst and MoveLast.. frankly anything other than MoveNext will not work.  You must MoveNext through the returned RecordSet.

Outstanding issue (if anyone feels like commenting)
- ResultSets are ReadOnly.  If I attach them to a MS Access Form, I will not (likely) be able to edit.
- Is there a way to have the ResultSet based off of a Dynamic Cursor, that I can move around in (first, last, next, prev, etc.)

Thanks for you help!
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22793804
i believe the same reason causes the result set to be read only and to the resultcount property to be -1
this is caused since you don't work with a disconnectede result set (which might be the default with other database vendors) which means that the driver does not pull all the results from the database to a local cache,
every time you perform moveNext you are accessing the database, so infact, the driver can't know how many records were returned and that is why resultCount=-1
0
 

Author Comment

by:MAGRATTON
ID: 22795519
So, how do I process this as a disconnected result set?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22803825
i'm not sure that you can
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 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