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
Solved

SQL Procedures

Posted on 1998-11-12
27
396 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA saving file message display 5 72
how can i unlock a picture file that was loaded in a picturebox in visual c#? 4 105
Protecting vb6 & .Net code Obfuscation 18 151
MsgBox 4 68
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

840 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