SQL Procedures

Adjusted points to 120

what kind of problems?  please be more specific (ADO, RDO, or DAO? what version, if ADO?  etc...)

pasting in code snippets with a description of what isn't working and what you need it to do is the best way to get the answer you want.

Edited text of question

Try this instead

strSQL = "{? = call today (?) }"
Set qdf = cnn.CreateQueryDef("", strSQL)

qdf.rdoParameters(0).Direction = dbParamReturnValue
qdf.Parameters(1).Direction = dbParamOutput

Cheers,

OOOOps, There is a typo

qdf.rdoParameters(0).Direction = dbParamReturnValue

Should be

qdf.Parameters(0).Direction = dbParamReturnValue

Didn't work.

As I said above, as soon as the processing get to the line:

qdf.parameter(0).direction = dbParameterReturnValue

An error occurs saying:

Runtime error '3265'

Item not found in collection

Set wks =  DBEngine.CreateWorkspace("ODBCworkspace", _
            "", "", dbUseODBC)
             
wks.DefaultCursorDriver = dbUseDefaultCursor

Set cnn = wks.OpenConnection("aaaaa", _
    dbDriverPrompt, _
    False, "ODBC;DATABASE=XXXXX")


strSQL = "{ call today (?) }"
Set rsReturnValue = cnn.openrecordset(strSQL,dbsqlpassthough)

if not rsReturnValue.eof then
   rsReturnValue(0) = dbParamOutput
endif



Try to use a recordset with dbsqlpassthough option
You must declare rsReturnValue as recordset.

Hope this helps
Regards

Did work I'm afraid.

I was a bit confused by your OpenRecordSet statement as
you had the dbSQLPassthough in the 'type' position. As I understand it dbSQLPassthrough is an option you can have when defining a record set as type dpOpenSnapshot. So I assume you meant;

Set rsReturnValue = cnn.OpenRecordset(strSQL,  _
                                                                 dbOpenSnapshot,  _
                                                                 dbSQLPassThrough)

This returned an error of 'invalid argument'

If I have misunderstood your instructions then I apologise and can you tell me where I've gone wrong. Otherwise, it was a good try but no cigar.

Hi,

From what I can see, I think the string containing the SQL statement isn't correct.  From what I can guess, you are trying to execute a stored procedure called TODAY and my guess is that the stored procedure doesn't need any input paramaters, it just returns something as a result.  The correct syntax for this is :

strSQL = "{? = CALL Today()}"

If the TODAY stored procedure needs an input parameter then the syntax would be :

strSQL = "{? = CALL Today(?)}"

In the last case, you will need to bind 2 parameters one for the input value and one for the result.


So try this out and see if it works.  

I'm still getting the 'item not found in collection' error whenever
the processing gets to the

qdf.parameters(0).direction = dbParamReturnValue

?

Did you already give it a try with qdf.parameters(1).direction ?
Normally I'm used to programming in delphi.  In delphi the first item in a collection is item 0, but it is possible that in VB the first item is 1.  Give it a try and see if it works.

Yeah I have tried that. The first Item is meant to be 0 as in Delphi.

Did you already try to execute the Query first, before setting the direction of the parameters ?

Try This :

strSQL = "{? = call today() }"
Set qdf = cnn.CreateQueryDef("", strSQL)

qdf.Execute dbSQLPassThrough      
qdf.Parameters(0).Direction = dbParamOutput

See if this gives you any problems.  From what I can see, when you try to set the direction of the Parameter, the Parameter doesn't exist yet in the Parameters collection.  By executing the statement, I think the Parameters will be created automatically.

What you suggest makes sense. However I have an example in a Microsoft VB manual with the Parameter direction being set before the execute statement.

I did try it though and I got an error on the Execute statement.
It won't allow me to use the dbSQLPassThrough option ( get an 'invalid argument' error). If I remove this I get an 'ODBC failure' error.

This is not correct...
Set rsReturnValue = cnn.OpenRecordset(strSQL, dbOpenSnapshot,_
       dbSQLPassThrough)

This is the correct way:
Set recordset = object.OpenRecordset (source, type, options, lockedits)

You're putting dbOpenSnapshot as type and dbSQLPassThrough as
an option, and this constants are 'type' kind and you only can use one of then.

Hope this helps
Regards

Bit confused by your reply.

I assume your suggesting that dbOpenSnapShot and dbSQLPassThrough are both 'type' arguments. If this is what your suggesting them I disagree. Looking at the on-line help with  VB dbSQLPassthrough is an 'option' which you can only use with a recordset set type of dbOpenSnapShot.

Again if I've misunderstood what your getting at then I apologise, but I think there was a typing error in the final line of you explanation which made it difficult to understand what you where getting at.

dbOpenSnapshot and dbSQLPassThrough are constansts in the type argument and you can only use one of then, not both.

Regards

Dear nicknakpaddywak,

I was suffering the exact same problem that you described, and frustrated by the same
answers that didn't solve anything.

I "fixed" the problem by using a Microsoft supplied ODBC driver instead of the Oracle
supplied ODBC driver that shipped with Oracle 7.

Go into the control panel and select ODBC.
Add a new datasource, I used "Microsoft ODBC driver for Oracle 2.00.006325"
Configure the ODBC connection parameters like this:

  Set m_rcnRdoConnection = New rdoConnection
  With m_rcnRdoConnection
    .Connect = "DSN=" & DSN & ";UID=" & UserID & ";PWD=" & Password & "; DSN=" & DSN
    .CursorDriver = rdUseOdbc
    .EstablishConnection rdDriverNoPrompt
    rdoErrors.Clear
  End With

where DSN is the datasource name you just created in the ODBC setup.

Define your stored proc like this:

Private m_rqyProc As rdoQuery
Private strSQL as String

strSQL = "{call procname (?, ?)}" <== Seems like you NEED the space before the (
Set m_rqyProc = m_rcnRdoConnection.CreateQuery("Proc",strSQ)
m_rqyProc(0).Type = rdTypeVARCHAR    <===No more errors here
m_rqyProc(0).Type = rdTypeVARCHAR

Regards
Zebada

I notice your solution was using RDO. I have been trying to get it to work using DAO. I'm using the Professional edition of VB and as I understand it I would need the Enterprise edition of VB for RDO. Please correct me if I'm wrong about this.

As a result of this I kind of adapted your solution to mine. My code looks like this,

Set wk = CreateWorkspace("ODBC WorkSpace", "", "", _
                         dbUseODBC)
                         
wk.DefaultCursorDriver = dbUseODBC

Set cn = wk.OpenConnection("test1", dbDriverPrompt, _
                            False)
                                                                                   
Set qd = cn.CreateQueryDef("", "{call today (?)}")

qd.Parameters(0) = dbParamOutput
                                 
Set rs = qd.OpenRecordset()

I used the Driver you suggested and I now get the error "Invalid Operation" on the "qd.parameters(0) = ......." line.

I've now altered the code to say

qd(0).type = rdTYPEVarChar

This seems to work  ok but I get an ODBC failure error on the
OpenRecordSet statement at the end.

I advice you to use oracle data control come with oracle 8
and a good example with examples with oracle cd is good to see
and try to do the same

I'm using Oracle 7.3 not Oracle 8

Thank's for the tip. To be honest I've already got a solution to
my answer so in true christmas spirit you can have the points. At least I'll not get anymore emails with 'solutions' that
aren't.

Thanks for the tip. I've already solved my problem so I may aswell let you have the points so that I don't get anymore annoying emails.

Hi nicknakpaddywak,

I see that you already have a solution, (I'm not looking
for any points) but I managed to get the Oracle supplied
ODBC 7.3 driver working correctly with VB5.

There is a neat white paper at:

http://www.oracle.com/support/bulletins/odbc/html/thirdpty.pdf

that explains why the error occurs and how to fix it.

Basically what is says is the stored procedure names are
now case sensitive, I changed my VB code from

"{call myproc (?,?,?)}"

to

"{CALL MYPROC (?,?,?)}"

it seems to work OK now.

Regards
Zebada

That's spot on Zebada it works ! Thanks very much for that and
I wish I could give you the points but unfortunately I've already gave the points away already.