Solved

Call a PLSQL Procedure with VB

Posted on 1998-10-13
16
492 Views
Last Modified: 2013-12-25
How to call a PLSQL-Procedure with IN-OUT-Parameters.
0
Comment
Question by:mario_ba
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 3

Expert Comment

by:altena
Comment Utility
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
Comment Utility
I want to connect with rdo
0
 
LVL 3

Expert Comment

by:vmano
Comment Utility
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
 

Author Comment

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

Expert Comment

by:vmano
Comment Utility
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
Comment Utility
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
Comment Utility
vspeter: I know this, it works up to 10 Parameters but above VB crashs totaly
0
 
LVL 2

Expert Comment

by:vspeter
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mario_ba
Comment Utility

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
Comment Utility

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
Comment Utility
So what is the problem ?.
0
 

Author Comment

by:mario_ba
Comment Utility
vspeter:

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

Author Comment

by:mario_ba
Comment Utility
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 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanx vspeter great it works
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

763 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

6 Experts available now in Live!

Get 1:1 Help Now