Re: Urgent Need help on VB to ORacle.

Satish S.Narasimha <somasatish_at_hotmail.com> wrote in article <343A4593.1ED0_at_hotmail.com>...
> Hello,
>
> Can anybody help me how to call the stored procedure which is in
> Oracle from Visual Basic....
>
> Any help or comments are welcome and awaiting reply ASAP....
>
> from
> satish : somasatish_at_hotmail.com
> somasatish_at_rocketmail.com
>

Pseudo code version:

	Instantiate connection object using OO4O
	Set parameters in connection object's parameters collection
	If query has not been created before
		Create the query
		Submit query
		Set "query created" flag
	Else
		Refresh query
	Endif

VB version:
Note: connection was instantiated earlier in the program, setting a boolean flag upon successful

        connection. This particular program manages an array of connections, hence the index used

        for the oConnect variables and flags.

Private Sub PostToProcedure(nConnectID As Integer, sTable As String, sValue As String)
  Dim sSQL As String
  Static bSQLCreated(1 To MAX_CONNECTIONS) As Boolean   

  On Error GoTo TrapError   

  mbQueryExecuted = False   

  'Set values

  oConnect(nConnectID).Parameters("RETNCODE").Value = "1"
  oConnect(nConnectID).Parameters("TABLENAME").Value = sTable
  oConnect(nConnectID).Parameters("TABLEDATA").Value = sValue
  

  'Execute stored procedure
  If Not bSQLCreated(nConnectID) Then   

    'Now that table space is created, set parameters     oConnect(nConnectID).Parameters.Add "RETNCODE", "0", ORAPARM_OUTPUT     oConnect(nConnectID).Parameters("RETNCODE").ServerType = ORATYPE_NUMBER   

    oConnect(nConnectID).Parameters.Add "TABLENAME", sTable, ORAPARM_INPUT     oConnect(nConnectID).Parameters("TABLENAME").ServerType = ORATYPE_VARCHAR2        oConnect(nConnectID).Parameters.Add "TABLEDATA", sValue, ORAPARM_INPUT     oConnect(nConnectID).Parameters("TABLEDATA").ServerType = ORATYPE_VARCHAR2     sSQL = "Declare RETNCODE number(7,2); " & _

          "BEGIN :RETNCODE := Process01(:TABLENAME, :TABLEDATA);end;"     Set oQuery = oConnect(nConnectID).CreateSQL(sSQL, 0&)     bSQLCreated(nConnectID) = True   

  Else
    oQuery.Refresh
  End If

  mbQueryExecuted = True   

ExitSub:
  Exit Sub

TrapError:
  Call Display_Message("Executing procedure", ERROR_MSG)   GoTo ExitSub

End Sub

Dick Willis, Senior Engineer
Synchrony Industrial Controls, Inc.
rmw_at_synchrony.com
http://www.synchrony.com Received on Tue Oct 07 1997 - 00:00:00 CEST