Solved

SQL Procedures

Posted on 1998-11-12
27
388 Views
Last Modified: 2013-12-25

I'm using VB Professional Edition vers. 5 and am accessing a ORACLE database via ODBC using DAO.

I'm having problems in calling an SQL procedure which is stored on the database and has output parameters.

This is an example of the sort of thing I'm trying to do
Whenever the direction of the parameter is defined I get an error saying that it is not part of the collection ?

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

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


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

qdf.Parameters(0).Direction = dbParamOutput



Can anyone help ?
0
Comment
Question by:nicknakpaddywak
  • 14
  • 3
  • 3
  • +5
27 Comments
 

Author Comment

by:nicknakpaddywak
ID: 1497600
Adjusted points to 120
0
 
LVL 1

Expert Comment

by:tjp
ID: 1497601
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.


0
 

Author Comment

by:nicknakpaddywak
ID: 1497602
Edited text of question
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1497603
Try this instead

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

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

Cheers,

0
 
LVL 2

Expert Comment

by:vspeter
ID: 1497604
OOOOps, There is a typo

qdf.rdoParameters(0).Direction = dbParamReturnValue

Should be

qdf.Parameters(0).Direction = dbParamReturnValue
0
 

Author Comment

by:nicknakpaddywak
ID: 1497605

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


0
 
LVL 3

Expert Comment

by:fguerreiro_inix
ID: 1497606
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
0
 

Author Comment

by:nicknakpaddywak
ID: 1497607

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.

0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1497608
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.  
0
 

Author Comment

by:nicknakpaddywak
ID: 1497609

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

qdf.parameters(0).direction = dbParamReturnValue

?

0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1497610
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.


0
 

Author Comment

by:nicknakpaddywak
ID: 1497611

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

0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1497612
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.


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:nicknakpaddywak
ID: 1497613

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.

0
 
LVL 3

Expert Comment

by:fguerreiro_inix
ID: 1497614

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

0
 

Author Comment

by:nicknakpaddywak
ID: 1497615

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.

0
 
LVL 3

Expert Comment

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

Regards
0
 
LVL 6

Expert Comment

by:zebada
ID: 1497617
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

0
 

Author Comment

by:nicknakpaddywak
ID: 1497618

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.


0
 

Author Comment

by:nicknakpaddywak
ID: 1497619

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.
0
 
LVL 2

Expert Comment

by:majed100598
ID: 1497620
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
0
 

Author Comment

by:nicknakpaddywak
ID: 1497621

I'm using Oracle 7.3 not Oracle 8

0
 

Accepted Solution

by:
fergusons earned 120 total points
ID: 1497622
I've had problems with the "Microsoft ODBC driver for Oracle 2.00.006325 driver when calling stored procedures

From http://www.microsoft.com/data/download2.htm

you can download the lastest ODBC Driver this should fix your problem

Good Luck
0
 

Author Comment

by:nicknakpaddywak
ID: 1497623

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.
0
 

Author Comment

by:nicknakpaddywak
ID: 1497624

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.


0
 
LVL 6

Expert Comment

by:zebada
ID: 1497625
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

0
 

Author Comment

by:nicknakpaddywak
ID: 1497626

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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

13 Experts available now in Live!

Get 1:1 Help Now