linked table or Passthrough query (1 Viewer)

'This API reads a value from the products table for a given id
Public Function LocateProductByID() As Boolean
On Error GoTo Err_LocateProductByID

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_LocateProductByID"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@id").Value = Me.id

    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      'Clear the class attributes
      Me.Clear
      LocateProductByID = False
    Else
      'Fetch the values found
      Me.id = Nz(adoRS!id, 0)
      Me.authid = Nz(adoRS!authid, 0)
      'Field resulting from the join to auth to look up the username for field authid
      Me.authusername = Nz(adoRS!authusername, vbNullString)
      Me.logtimestamp = Nz(adoRS!logtimestamp, vbNullString)
      Me.projectid = Nz(adoRS!projectid, 0)
      Me.title = Nz(adoRS!title, vbNullString)
      Me.productnumber = Nz(adoRS!productnumber, vbNullString)
      Me.bomcad = Nz(adoRS!bomcad, vbNullString)
      'Set a good return code
      LocateProductByID = True
    End If

    'Close the database table
    .Close
  End With

Exit_LocateProductByID:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_LocateProductByID:
  Call errorhandler_MsgBox("Class: clsObjProductsTbl, Function: LocateProductByID()")
  LocateProductByID = False
  Resume Exit_LocateProductByID

End Function

Set adoRS = .Execute()

Public Sub obj_classif()

    Dim cn As New ADODB.Connection
    Dim db_name, Username, Password As String

    Dim adoCMD As ADODB.Command
    Dim adoRS As ADODB.Recordset
    Dim param1, param2 As ADODB.Parameter

    Set wb = Excel.ActiveWorkbook
    Set ih = wb.Sheets("InfoSheet")
    db_name = ih.Cells(1, 2)   'Instanzname
    Username = ih.Cells(2, 2)  'Login
    Password = ih.Cells(3, 2)  'Passwort

    Set cn = New ADODB.Connection
    cn.ConnectionTimeout = 600
    cn.Open db_name, Username, Password

    'Define attachment to database table specifics and execute commands via With block
    Set adoCMD = New ADODB.Command
    With adoCMD
        .ActiveConnection = cn
        .CommandText = "s#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        Set ParamReturn = .CreateParameter("result_line", adVarChar, adParamReturnValue, 1)
            .Parameters.Append ParamReturn
        Set param1 = .CreateParameter("STG_DATA_REQUEST", adVarChar, adParamInput, 1)
            .Parameters.Append param1
        Set param2 = .CreateParameter(120, adInteger, adParamInput, 1)
            .Parameters.Append param2

        Set adoRS = .Execute() '******here comes the error
    End With

    MsgBox adoCMD.Parameters("result_line")

End Sub

Private Sub obj_classif()

    Set wb = Excel.ActiveWorkbook
    Set ih = wb.Sheets("InfoSheet")

    With New ADODB.Connection
      .ConnectionTimeout = 600
      .Open Sheets("InfoSheet").Cells(1, 2).Value, Sheets("InfoSheet").Cells(2, 2).Value, Sheets("InfoSheet").Cells(3, 2).Value
      sn = .Execute("declare result_line varchar2(2000) := ''; begin result_line := s#mdb$stg_da_extr_util.get_all_classes_of_classif('STG_DATA_REQUEST', 120); dbms_output.put_line(result_line);commit;commit;end;")
      .close
    End With

    MsgBox TypeName(sn)

End Sub

Public Sub obj_classif()

    Dim cn As New ADODB.Connection
    Dim strSQL As String
    Dim db_name, Username, Password As String

    Dim adoCMD As ADODB.Command
    Dim adoRS As ADODB.Recordset
    Dim param1, param2 As ADODB.Parameter

    Set wb = Excel.ActiveWorkbook
    Set ih = wb.Sheets("InfoSheet")
    db_name = ih.Cells(1, 2)   'Instanzname
    Username = ih.Cells(2, 2)  'Login
    Password = ih.Cells(3, 2)  'Passwort

    Set cn = New ADODB.Connection
    cn.ConnectionTimeout = 600
    cn.Open db_name, Username, Password

    'Define attachment to database table specifics and execute commands via With block
    Set adoCMD = New ADODB.Command
    With adoCMD
        .ActiveConnection = cn
        .CommandText = "s#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        Set param1 = .CreateParameter("STG_DATA_REQUEST", adVarChar, adParamInput, 1)
            .Parameters.Append param1
        Set param2 = .CreateParameter(120, adInteger, adParamInput, 1)
            .Parameters.Append param2

        Set adoRS = .Execute()
    End With
End Sub

Public Sub obj_classif()

    Dim cn As New ADODB.Connection
    Dim strSQL As String
    Dim db_name, Username, Password As String

    Dim adoCMD As ADODB.Command
    Dim adoRS As ADODB.Recordset

    Set wb = Excel.ActiveWorkbook
    Set ih = wb.Sheets("InfoSheet")
    db_name = ih.Cells(1, 2)   'Instanzname
    Username = ih.Cells(2, 2)  'Login
    Password = ih.Cells(3, 2)  'Passwort

    Set cn = New ADODB.Connection
    cn.ConnectionTimeout = 600
    cn.Open db_name, Username, Password

    Set adoCMD = New ADODB.Command
    With adoCMD
        .ActiveConnection = cn
        .CommandText = "s#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh

        .Parameters("@i_caller").Value = "STG_DATA_REQUEST" '*****error
        .Parameters("@i_obj_classif_id").Value = 120

        Set adoRS = .Execute()
    End With

Public Sub obj_class()

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strConn As String

    Set cmd = New ADODB.Command
    Set cnn = New ADODB.Connection

    strConn = "DSN=***;UID=***;PWD=***"

    'Open the connection to the database
    cnn.Open strConn

    'use a stored procedure to get the data
    With cmd

        .CommandText = "S#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandTimeout = 60
        .CommandType = adCmdStoredProc
        .ActiveConnection = cnn

        Call .Parameters.Append(.CreateParameter("retVal", adInteger, adParamReturnValue))

        Call .Parameters.Append(.CreateParameter("i_caller", adVarChar, adParamInput, 100, "STG_DATA_REQUEST"))
        Call .Parameters.Append(.CreateParameter("i_obj_classif_id", adNumeric, adParamInput, 30, 120))
 
        ' The current commandtext look like:
        '  "{ ? = call S#mdb$stg_da_extr_util.get_all_classes_of_classif(?, ?) }"
        ' The cmd object parameters look like this:
        ' cmd.Parameters.Item1.Value = Empty
        ' cmd.Parameters.Item2.Value = "STG_DATA_REQUEST"
        ' cmd.Parameters.Item3.Value = 120

       ' it crashes here
        .Execute , , adExecuteNoRecords

       ' with the following error:
       ' Run-time error '-2147217887 (80040e21):
       'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. 

End Sub

'ConnectionString : "Provider=MSDASQL.1;Extended Properties="DSN=***;UID=***;PWD=***;DBQ=EE_AVA35;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;"

Private Sub obj_class()

    Dim cmd As New ADODB.Command
    Dim cn As ADODB.Connection
    Dim rslt as String
    Dim db_name, UserName, PassWord As String
    Dim i_caller As ADODB.Parameter
    Dim i_obj_class_id As ADODB.Parameter
    Dim ParamReturn As ADODB.Parameter

    Set cn = New ADODB.Connection

    With cn
        .ConnectionString = "Provider=MSDAORA.1;Data Source=EE_AVA35;User Id=***;Password=***"
        .CursorLocation = adUseClient
        .Open
    End With
    
    Set cmd = New ADODB.Command
    
    With cmd
        Set .ActiveConnection = cn
        .CommandText = "S#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandType = adCmdStoredProc
        
        Set ParamReturn = .CreateParameter("RESULT", adVarChar, adParamReturnValue, 10000) '10000 stands here for the character number in a string
            .Parameters.Append ParamReturn
        Set i_caller = .CreateParameter("Caller Identification", adVarChar, adParamInput, 50, "STG_DATA_REQUEST")
            .Parameters.Append i_caller
        Set i_obj_class_id = .CreateParameter("Class ID", adInteger, adParamInput, 1000, 42)
            .Parameters.Append i_obj_class_id
        
        .Execute , , adExecuteNoRecords 
        cn.Close
        rslt = .Parameters("RESULT")
    End With

End Sub

'...
    With cmd
        Set .ActiveConnection = cn
        .CommandText = "S#mdb$stg_da_extr_util.get_all_classes_of_classif"
        .CommandType = adCmdStoredProc
        
        .Parameters.Refresh 'ORA-06550 Wrong number or types of arguments in call to GET_ALL_CLASSES_OF_CLASSIF
        .Parameters("@i_caller").Value = "STG_DATA_REQUEST" 
        .Parameters("@i_obj_classif_id").Value = 120
End with
'...

  .CommandText = "S#mdb$stg_da_extr_util.get_all_classes_of_classif(?,?)"