Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Call a PLSQL Procedure with VB

Posted on 1998-10-13
16
Medium Priority
?
518 Views
Last Modified: 2013-12-25
How to call a PLSQL-Procedure with IN-OUT-Parameters.
0
Comment
Question by:mario_ba
[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
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 3

Expert Comment

by:altena
ID: 1496868
Use OO4O (Ole Objects for Oracle)
There is a sample in the manual.

(There was when I needed it, long time ago I did it.
Sorry, can't halp you more than this)
0
 

Author Comment

by:mario_ba
ID: 1496869
I want to connect with rdo
0
 
LVL 3

Expert Comment

by:vmano
ID: 1496870
check out the MSDN article Q167225 at
http://support.microsoft.com/support/kb/articles/q167/2/25.asp

let me know if this helps
vmano
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mario_ba
ID: 1496871
It's not the problem with one parameter, just with more than 10 parameters
0
 
LVL 3

Expert Comment

by:vmano
ID: 1496872
i think your question never said mentioned about number of parameters!!!!! please try to be more clear, when you ask the question itself.
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496873
This method requires a pre-defined number of parameters (NOT dynamic)

The stored procedure list here does not use IN-OUT parameter, but there 3 parameters,

1st         - OUT
2nd and 3rd - IN

If your stored procedure has a need to use IN-OUT parameter than just replace the appropriate on (direcion) with 'rdParamInputOutput '

Assuming that you have all the connection in place.

With lrdoQry
  .SQL = "{? = Call cms_InsertAddressType(?, ?)}"
  .CursorType = rdUseServer
  .LockType = rdConcurLock
  Set .ActiveConnection = gRDOCon

  .rdoParameters(0).Direction = rdParamReturnValue
  .rdoParameters(1).Direction = rdParamInput
  .rdoParameters(2).Direction = rdParamInput

  .rdoParameters(1).Type = rdTypeVARCHAR
  .rdoParameters(2).Type = rdTypeVARCHAR

  .rdoParameters(1).Value = msAddressDesc                
  .rdoParameters(2).Value = gsCMSAppSrvUserId            
End With

lrdoQry.Execute

' Check the return value
If lrdoQry.rdoParameters(0).Value = 0 Then

Cheers
0
 

Author Comment

by:mario_ba
ID: 1496874
vspeter: I know this, it works up to 10 Parameters but above VB crashs totaly
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496875
Mario

What VB version are you using ?. Is there any document saying that VB crashs if there are more than 10 parameters (what type, IN, OUT, IN-OUT ?) ?.

Below is one of our stored procedure currently use. Environment, VB, SYBASE 11.XXX

With lrdoQry
       .SQL = "{ ? = Call cms_GetFranAssoSales (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"

        .LockType = rdConcurReadOnly
        .CursorType = rdUseServer

        Set .ActiveConnection = gRDOCon

        .rdoParameters(0).Direction = rdParamReturnValue
        .rdoParameters(1).Direction = rdParamInput
        .rdoParameters(2).Direction = rdParamInput
        .rdoParameters(3).Direction = rdParamInput
       
        .rdoParameters(4).Direction = rdParamOutput
        .rdoParameters(5).Direction = rdParamOutput
        .rdoParameters(6).Direction = rdParamOutput
        .rdoParameters(7).Direction = rdParamOutput
        .rdoParameters(8).Direction = rdParamOutput
        .rdoParameters(9).Direction = rdParamOutput
        .rdoParameters(10).Direction = rdParamOutput
        .rdoParameters(11).Direction = rdParamOutput
        .rdoParameters(12).Direction = rdParamOutput
        .rdoParameters(13).Direction = rdParamOutput
        .rdoParameters(14).Direction = rdParamOutput
        .rdoParameters(15).Direction = rdParamOutput
        .rdoParameters(16).Direction = rdParamOutput
        .rdoParameters(17).Direction = rdParamOutput
        .rdoParameters(18).Direction = rdParamOutput
        .rdoParameters(19).Direction = rdParamOutput
        .rdoParameters(20).Direction = rdParamOutput
        .rdoParameters(21).Direction = rdParamOutput
        .rdoParameters(22).Direction = rdParamOutput
        .rdoParameters(23).Direction = rdParamOutput
        .rdoParameters(24).Direction = rdParamOutput
        .rdoParameters(25).Direction = rdParamOutput
        .rdoParameters(26).Direction = rdParamOutput
        .rdoParameters(27).Direction = rdParamOutput
        .rdoParameters(28).Direction = rdParamOutput
        .rdoParameters(29).Direction = rdParamOutput
        .rdoParameters(30).Direction = rdParamOutput
        .rdoParameters(31).Direction = rdParamOutput
        .rdoParameters(32).Direction = rdParamOutput
        .rdoParameters(33).Direction = rdParamOutput
       
        .rdoParameters(1).Type = rdTypeVARCHAR
        .rdoParameters(2).Type = rdTypeINTEGER
        .rdoParameters(3).Type = rdTypeINTEGER
        .rdoParameters(4).Type = rdTypeFLOAT
        .rdoParameters(5).Type = rdTypeFLOAT
        .rdoParameters(6).Type = rdTypeFLOAT
        .rdoParameters(7).Type = rdTypeFLOAT
        .rdoParameters(8).Type = rdTypeFLOAT
        .rdoParameters(9).Type = rdTypeFLOAT
        .rdoParameters(10).Type = rdTypeFLOAT
        .rdoParameters(11).Type = rdTypeFLOAT
        .rdoParameters(12).Type = rdTypeFLOAT
        .rdoParameters(13).Type = rdTypeFLOAT
        .rdoParameters(14).Type = rdTypeFLOAT
        .rdoParameters(15).Type = rdTypeFLOAT
        .rdoParameters(16).Type = rdTypeFLOAT
        .rdoParameters(17).Type = rdTypeFLOAT
        .rdoParameters(18).Type = rdTypeFLOAT
        .rdoParameters(19).Type = rdTypeFLOAT
        .rdoParameters(20).Type = rdTypeFLOAT
        .rdoParameters(21).Type = rdTypeFLOAT
        .rdoParameters(22).Type = rdTypeFLOAT
        .rdoParameters(23).Type = rdTypeFLOAT
        .rdoParameters(24).Type = rdTypeFLOAT
        .rdoParameters(25).Type = rdTypeFLOAT
        .rdoParameters(26).Type = rdTypeFLOAT
        .rdoParameters(27).Type = rdTypeFLOAT
        .rdoParameters(28).Type = rdTypeFLOAT
        .rdoParameters(29).Type = rdTypeFLOAT
        .rdoParameters(30).Type = rdTypeFLOAT
        .rdoParameters(31).Type = rdTypeFLOAT
        .rdoParameters(32).Type = rdTypeFLOAT
        .rdoParameters(33).Type = rdTypeINTEGER

        .rdoParameters(1).Value =  rCFinSumOptions.OptionTypeKey         'AssociationKey
        .rdoParameters(2).Value = rCFinSumOptions.FinCalendarMonth      'AnsettMonth
        .rdoParameters(3).Value = CInt(rCFinSumOptions.SumYear)         'AnsettYear
   
    End With
   
               
    lrdoQry.Execute

As you can see, there are 33 parameters, and it works beautifull. Could it be something else like the way you set up your rdoConnection, query ...etc.

Cheer,
0
 

Author Comment

by:mario_ba
ID: 1496876

I use VB 6 and Oracle 8, ADO 2.0 or RDO 2.0.
I search the hole MSDN-Library but I found nothing.
I will use 1 IN, 1 IN/OUT and n OUT Parameters.

I don't think that the connection or the query doesn't works. Why should it works up to 10 params?
0
 

Author Comment

by:mario_ba
ID: 1496877

I use VB 6 and Oracle 8, ADO 2.0 or RDO 2.0.
I search the hole MSDN-Library but I found nothing.
I will use 1 IN, 1 IN/OUT and n OUT Parameters.

I don't think that the connection or the query doesn't works. Why should it works up to 10 params?
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496878
So what is the problem ?.
0
 

Author Comment

by:mario_ba
ID: 1496879
vspeter:

How to say the procedure to send a returnvalue(SQL-Side)?
0
 

Author Comment

by:mario_ba
ID: 1496880
First it doesn't generates any parmeters, when I used {} then it works, up to 10 parameters, now I use the sample from u.

Now I can set the parameters, but now I get an error message ("invalide sql-statment") when I execute the procedure. (I get no return value)

Are the "{}" and the return value necessary?
How can I tell the proc to return a value?

0
 
LVL 2

Accepted Solution

by:
vspeter earned 600 total points
ID: 1496881
Okie,

1. I guess, You've set all parameters (from VB) correctly in term of Direction, Type and Value (if as IN, IN-OUT).

2. You've set all parameter (from Oracle PL/SQL) correctly in term of Type and Direction. (Keep in mind, If you would to refer to my first suggestion, there are 3 parameters from VB, but you only have to define 2 parameters in Oracle PL/SQL. Parameter(0) will hold the value of 1, IF AND WHEN PL/SQL (from Oracle) executes the statement RETRUN 1 or whatever number you want it to be.

3. For any other parameters you defined as IN, IN-OUT from VB, you would retrieve just like parameter(0) (Refer to my first suguestion). From Oracle PL/SQL side, you would have to do a SELECT statement, for example, the parameter name is '@RewardPoint' and field name is 'Point' from table name 'Reward' then

   SELECT @RewardPoint = Point From Reward

The suggestion above (1,2,3) is only apply if you want to return 1-n value (OUTPUT). But if you want to return 1-n RECORDS, then you are talking about different thing.

Below is a piece of code cut out from our system to retrieve 1-n RECORDS from database

With lrdoQry
 .SQL = "{? = Call cms_GetCodeTypeList (?)}"
 .CursorType = rdUseServer
 .LockType = rdConcurReadOnly
 Set .ActiveConnection = gRDOCon
 .rdoParameters(0).Direction = rdParamReturnValue

 .rdoParameters(1).Direction = rdParamInput
 .rdoParameters(1).Type = rdTypeVARCHAR
 .rdoParameters(1).Value = rsParameterValue
End With

Set lrdoRSet = lrdoQry.OpenResultset(rdOpenForwardOnly)
   
miNumbRow = 0

While Not lrdoRSet.EOF
 miNumbRow = miNumbRow + 1
       
 maSumOptItemList_00(miNumbRow) = lrdoRSet(0)
 maSumOptItemList_01(miNumbRow) = lrdoRSet(1)
 maSumOptItemList_02(miNumbRow) = lrdoRSet(2)
 lrdoRSet.MoveNext
Wend

Now again the number of parameters not the issues. We know that, we want to retrieve 3 columns from within stored procedure 'cms_GetCodeTypeList'.
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496882
One more thing, within the stored procedure, you would just have to do a SELECT statement including those columns you want to return, as the last statement

Let me know how you go !

Cheers,

0
 

Author Comment

by:mario_ba
ID: 1496883
Thanx vspeter great it works
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

604 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