Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-23
6
Medium Priority
?
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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