Could someone please help me out parameters returned to vb6 program is returning NULL values when it is not NULL values

Hi all,

Could someone help me with this one. The OUT parameters comming from Oracle 10g database package is returning null back to the calling vb6 program using ado. It shoud be returning values because the stored procedure in Oracle has been excuted and has worked. So there is something wrong with the vb6 program can anyone please help. I think it maybe ADO but i am not sure. It could be how the vb6 program is written. Below is the code that calls the oracle stored procedure and gets the data.

We are only concerned about the OUT Parameters returning a null value:
' Role Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm
         
     ' Generic User Id Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm
             
     ' Generic Password Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm


Function GetGenericUser(sUserId As String, _
                        sRole As String, _
                        sGenericUserId As String, _
                        sGenericPassword As String, _
                        iRetCode As Integer)
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  Name:       GetGenericUser
'*
'*  Purpose:    Call the oduser.get_generic_user stored procedure to find out which
'*  generic user account to use for the user logging on to ODSS.  The generic user
'*  chosen will depend on the database role(s) granted to the user.
'*
'*  The package specification for the stored procedure is:
'*
'*  Procedure get_generic_user
'*                     (pin_user_id            IN VARCHAR2
'*                     ,pin_auth_key           IN VARCHAR2
'*                     ,pout_role             OUT VARCHAR2
'*                     ,pout_generic_user_id  OUT VARCHAR2
'*                     ,pout_generic_password OUT VARCHAR2
'*                     ,pout_ret_code         OUT INTEGER
'*                     ,pout_sqlcode          OUT INTEGER
'*                     ,pout_sqlerrm          OUT VARCHAR2
'*                     ,pin_debug_mode         IN BOOLEAN := FALSE)
'*
'*  Params:     sUserId
'*
'*  Returns:    sGenericUserid
'*              sGenericPassword
'*              iRetCode
'*
'*  History
'*  Date:       Author:     Comments:
'*  22/09/1998  G.Date      Created
'*  29/10/1998  G.Date      Added CAUTH_KEY
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset

     ' Initialise the command and record set objects
     Set cmd = Nothing
     Set rst = Nothing
     
     ' Open command object
     Set cmd = New ADODB.Command
     
     ' Identify stored procedure
     cmd.CommandText = "oduser.get_generic_user"
     cmd.CommandType = adCmdStoredProc
     
     ' User Id Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamInput, Len(sUserId), sUserId)
     cmd.Parameters.Append prm

     ' Auth Key Parameter - proves that this call is from an authorised application
     Set prm = cmd.CreateParameter(, adChar, adParamInput, Len(CAUTH_KEY), CAUTH_KEY)
     cmd.Parameters.Append prm

     ' Role Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm
         
     ' Generic User Id Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm
     
         
     ' Generic Password Parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 30)
     cmd.Parameters.Append prm
     
       
     ' Return Code parameter
     Set prm = cmd.CreateParameter(, adInteger, adParamOutput)
     cmd.Parameters.Append prm
     
     
     ' Sqlcode parameter
     Set prm = cmd.CreateParameter(, adDouble, adParamOutput)
     cmd.Parameters.Append prm
     
   
     ' Sqlerrm parameter
     Set prm = cmd.CreateParameter(, adChar, adParamOutput, 120)
     cmd.Parameters.Append prm
     
     ' Sqlerrm parameter
     Set prm = cmd.CreateParameter("pout_sqlerrm", adChar, adParamOutput, 120)
     cmd.Parameters.Append prm

     ' Create command object
     Set cmd.ActiveConnection = objConn
     
     ' Execute the stored procedure and store its results in a record set
     Set rst = cmd.Execute
     
     If cmd.Parameters.Item(5) = CSUCCESS Then
        ' oduser.get_generic_user was successfull
        ' Store the generic User Id and Password for use later on
        iRetCode = CSUCCESS
        sRole = cmd.Parameters.Item(2)
        sGenericUserId = cmd.Parameters.Item(3)
        sGenericPassword = cmd.Parameters.Item(4)
     Else
        ' oduser.get_generic_user failed
        iRetCode = CFAILURE
        sRole = ""
        sGenericUserId = ""
        sGenericPassword = ""
        MsgBox "A system error has been detected.  " & _
                "The generic User Id for this user could not be obtained.  " & _
                "Please report the error to the Help Desk.  " & _
                "Diagnostic information: " & _
                "oduser.get_generic_user sqlcode: " & cmd.Parameters(6) & " " & _
                "sqlerrm: " & cmd.Parameters(7)
     End If
     
     ' Clean up
     Set cmd = Nothing
     Set rst = Nothing
     
End Function
darrylf290567Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please use adVarchar instead of adChar
does using the parameter name in your CreateParameter make it any better?

what is the code of your oracle procedure?
0
darrylf290567Author Commented:
Hi angel1111,

I tried to used the adVarchar and the parameter name and I still get the same problem.

Kind Regards
darrylf290567
0
darrylf290567Author Commented:
Hi angel1111,

I connected using oracle 8i and there were values returned correctly?

So maybe it might have something to do with Oracle 10g?

Kind Regards
darrylf290567
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the code of your oracle procedure?
0
darrylf290567Author Commented:
Hi angel111

Here is the PLSQL code:
This PLSQL stored procedure worked for both 8i and 10g using a test sql script in Toad.

PROCEDURE get_generic_user
                     (pin_user_id            IN VARCHAR2
                     ,pin_auth_key           IN VARCHAR2
                     ,pout_role             OUT VARCHAR2
                     ,pout_generic_user_id  OUT VARCHAR2
                     ,pout_generic_password OUT VARCHAR2
                     ,pout_ret_code         OUT INTEGER
                     ,pout_sqlcode          OUT INTEGER
                     ,pout_sqlerrm          OUT VARCHAR2
                     ,pin_debug_mode         IN BOOLEAN := FALSE)
   IS
      v_ret_value    VARCHAR2(100);
      v_role         VARCHAR2(30);
      v_ret_code     INTEGER;
      v_sqlcode      INTEGER;
      v_sqlerrm      VARCHAR2(120);
   BEGIN
      v_debug_mode := pin_debug_mode;
      IF v_debug_mode THEN
         maximise_dbms_output_buffer;
         prt ('oduser.get_generic_user');
         prt ('pin_user_id: ' || UPPER (pin_user_id));
      END IF;
      /* ---------------------------------------
      |  Initialise error flags and output parms
      |  --------------------------------------- */
      pout_generic_user_id := NULL;
      pout_generic_password := NULL;
      pout_role := NULL;
      pout_ret_code := ode.c_failure;
      pout_sqlcode  := NULL;
      pout_sqlerrm  := NULL;
      /* -------------------------
      |  Validate input parameters
      |  ------------------------- */
      IF pin_user_id IS NULL THEN
         RAISE ode.e_account_name_is_null;
      END IF;
      IF UPPER (pin_auth_key) <> UPPER (c_auth_key) THEN
         RAISE ode.e_unauthorised_application;
      END IF;
      IF v_debug_mode THEN
         prt ('about to call oduser.get_role');
         prt ('pin_user_id: ' || UPPER (pin_user_id));
      END IF;
      get_role (dbac.c_odssv4_application, pin_user_id, v_role, v_ret_code, v_sqlcode, v_sqlerrm);
      IF v_ret_code = ode.c_success THEN
         /* ---------------------------------------------------------------------------
         |  Determine which generic account the person is authorised for, ie which role
         |  --------------------------------------------------------------------------- */
         IF    v_role = dbac.c_administrator THEN
            pout_role := v_role;
         ELSIF v_role = dbac.c_custodian THEN
            pout_role := v_role;
         ELSIF v_role = dbac.c_update_user THEN
            pout_role := v_role;
         ELSIF v_role = dbac.c_read_only_user THEN
            pout_role := v_role;
         ELSE
            pout_role := dbac.c_read_only_user;
         END IF;
         /* ------------------------------------------------------------
         |  All ODSSv4 sessions will use the generic administrator user.
         |  ------------------------------------------------------------ */
         pout_generic_user_id  := dbac.c_generic_admin_user;
         pout_generic_password := dbac.c_generic_admin_password;
         pout_ret_code := ode.c_success;
      ELSE
         pout_ret_code := v_ret_code;
         pout_sqlcode := v_sqlcode;
         pout_sqlerrm := v_sqlerrm;
      END IF;
      IF v_debug_mode THEN
         prt ('oduser.get_generic_user ends');
      END IF;
   EXCEPTION
    WHEN OTHERS THEN
         pout_ret_code := ode.c_failure;
         pout_sqlcode  := sqlcode;
         pout_sqlerrm  := SUBSTR (SQLERRM, 1, 120);
         IF v_debug_mode THEN
            prt ('oduser.get_generic_user fails');
         END IF;
   END get_generic_user;
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looks fine.
so, what is the connection string, ie what version of the oracle client do you use?
0
darrylf290567Author Commented:
I use Oracle 10g driver
SQORA32.dll 18/11/2007

the Oracle 8i client that works is:
SQORA32.dll 01/11/2001

I use a DSN to connect as well:

Kind Regards
darrylf290567
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looks still fine.
can you trace using the odbc trace, to see what is going over the line and back?
0
cquinnCommented:
Try this version of the code - you do not use the recordset object for anything significant, so drop it from the code, and doing a parameters.refresh allows you to refer to the existing stored procedure's parameters by name and you do not have to set the data type etc

It also has error handling code which may help
Function GetGenericUser(sUserId As String, _
                        sRole As String, _
                        sGenericUserId As String, _
                        sGenericPassword As String, _
                        iRetCode As Integer)
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  Name:       GetGenericUser
'*
'*  Purpose:    Call the oduser.get_generic_user stored procedure to find out which
'*  generic user account to use for the user logging on to ODSS.  The generic user
'*  chosen will depend on the database role(s) granted to the user.
'*
'*  The package specification for the stored procedure is:
'*
'*  Procedure get_generic_user
'*                     (pin_user_id            IN VARCHAR2
'*                     ,pin_auth_key           IN VARCHAR2
'*                     ,pout_role             OUT VARCHAR2
'*                     ,pout_generic_user_id  OUT VARCHAR2
'*                     ,pout_generic_password OUT VARCHAR2
'*                     ,pout_ret_code         OUT INTEGER
'*                     ,pout_sqlcode          OUT INTEGER
'*                     ,pout_sqlerrm          OUT VARCHAR2
'*                     ,pin_debug_mode         IN BOOLEAN := FALSE)
'*
'*  Params:     sUserId
'*
'*  Returns:    sGenericUserid
'*              sGenericPassword
'*              iRetCode
'*
'*  History
'*  Date:       Author:     Comments:
'*  22/09/1998  G.Date      Created
'*  29/10/1998  G.Date      Added CAUTH_KEY
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 
Dim cmd As ADODB.Command
GetGenericUser = True
 
On Error GoTo HandleErr
 
    ' Open command object
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = objConn
    With cmd
        ' Identify stored procedure
        .CommandText = "oduser.get_generic_user"
        .CommandType = adCmdStoredProc
 
        'The following line will allow you to refer to the sp's defined parameters
        .Parameters.Refresh
 
        ' Set the User Id Parameter
        .Parameters("pin_user_id") = sUserId
        ' Set the Auth Key Parameter - proves that this call is from an authorised application
        .Parameters("pin_auth_key") = CAUTH_KEY
       
        'Fire the stored procedure - no need for a recordset
        .Execute
 
        ' Check the return values
 
        If .Parameters("pout_ret_code") = CSUCCESS Then
            ' oduser.get_generic_user was successfull
            ' Store the generic User Id and Password for use later on
            iRetCode = CSUCCESS
 
            'refer to the output parameters by name - much easier to understand
            sRole = .Parameters("pout_role")
            sGenericUserId = .Parameters("pout_generic_user_id")
            sGenericPassword = .Parameters("pout_generic_password")
        Else
            ' oduser.get_generic_user failed
            iRetCode = CFAILURE
            sRole = ""
            sGenericUserId = ""
            sGenericPassword = ""
            MsgBox "A system error has been detected.  " & _
                   "The generic User Id for this user could not be obtained.  " & _
                   "Please report the error to the Help Desk.  " & _
                   "Diagnostic information: " & _
                   "oduser.get_generic_user sqlcode: " & .Parameters("pout_sqlcode") & " " & _
                   "sqlerrm: " & .Parameters("pout_sqlerrm")
        End If
    End With
 
    ' Clean up
    Set cmd = Nothing
 
ExitHere:
    Exit Function
 
HandleErr:
 
    GetGenericUser = False
    Select Case Err.Number
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "GetGenericUser"
    End Select
    Resume ExitHere
    ' End Error handling block.
End Function

Open in new window

0
darrylf290567Author Commented:
Hi cquinn,

There was a problem with your code because I am geeting the error msg:
Error 3265 Item cannot be found in the collection corresponding to the requested name or ordinal.

This happens after the line where you do the .Parameters.Refresh and then when you set the pin_user_id.

Kind regards
darrylf290567
0
darrylf290567Author Commented:
Hi All,

I have resolved the issue myself. I thank you all so sincerely for your input anyway.

My code is:
Function GetGenericUser(sUserId As String, _
                        sRole As String, _
                        sGenericUserId As String, _
                        sGenericPassword As String, _
                        iRetCode As Integer)
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  Name:       GetGenericUser
'*
'*  Purpose:    Call the oduser.get_generic_user stored procedure to find out which
'*  generic user account to use for the user logging on to ODSS.  The generic user
'*  chosen will depend on the database role(s) granted to the user.
'*
'*  The package specification for the stored procedure is:
'*
'*  Procedure get_generic_user
'*                     (pin_user_id            IN VARCHAR2
'*                     ,pin_auth_key           IN VARCHAR2
'*                     ,pout_role             OUT VARCHAR2
'*                     ,pout_generic_user_id  OUT VARCHAR2
'*                     ,pout_generic_password OUT VARCHAR2
'*                     ,pout_ret_code         OUT INTEGER
'*                     ,pout_sqlcode          OUT INTEGER
'*                     ,pout_sqlerrm          OUT VARCHAR2
'*                     ,pin_debug_mode         IN BOOLEAN := FALSE)
'*
'*  Params:     sUserId
'*
'*  Returns:    sGenericUserid
'*              sGenericPassword
'*              iRetCode
'*
'*  History
'*  Date:       Author:     Comments:
'*  05/02/2008  D.Francis      Created
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Dim l_Reff As Long
Dim cmd As ADODB.Command
GetGenericUser = True
 
On Error GoTo HandleErr
 
    ' Open command object
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = objConn
        .CommandType = adCmdStoredProc
        .CommandText = "oduser.get_generic_user"
        .Prepared = False
        .Parameters.Append .CreateParameter("pin_user_id", adVarChar, adParamInput, Len(sUserId), sUserId)
        .Parameters.Append .CreateParameter("pin_auth_key", adVarChar, adParamInput, Len(CAUTH_KEY), CAUTH_KEY)
        .Parameters.Append .CreateParameter("pout_role", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("pout_generic_user_id", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("pout_generic_password", adVarChar, adParamOutput, 100)
        .Parameters.Append .CreateParameter("pout_ret_code", adInteger, adParamOutput)
        .Parameters.Append .CreateParameter("pout_sqlcode", adInteger, adParamOutput)
        .Parameters.Append .CreateParameter("pout_sqlerrm", adVarChar, adParamOutput, 120)
        Call .Execute(l_Reff, , adExecuteNoRecords)
        ' Check the return values
        If .Parameters("pout_ret_code") = CSUCCESS Then
            ' oduser.get_generic_user was successfull
            ' Store the generic User Id and Password for use later on
            iRetCode = CSUCCESS
 
            'refer to the output parameters by name - much easier to understand
            sRole = .Parameters("pout_role")
            sGenericUserId = .Parameters("pout_generic_user_id")
            sGenericPassword = .Parameters("pout_generic_password")
        Else
            ' oduser.get_generic_user failed
            iRetCode = CFAILURE
            sRole = ""
            sGenericUserId = ""
            sGenericPassword = ""
            MsgBox "A system error has been detected.  " & _
                   "The generic User Id for this user could not be obtained.  " & _
                   "Please report the error to the Help Desk.  " & _
                   "Diagnostic information: " & _
                   "oduser.get_generic_user sqlcode: " & .Parameters("pout_sqlcode") & " " & _
                   "sqlerrm: " & .Parameters("pout_sqlerrm")
        End If
    End With
 
    ' Clean up
    Set cmd = Nothing
 
ExitHere:
    Exit Function
 
HandleErr:
 
    GetGenericUser = False
    Select Case Err.Number
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "GetGenericUser"
    End Select
    Resume ExitHere
    ' End Error handling block.
End Function

Kind Regards
darrylf290567
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what made the actual difference, then?
0
darrylf290567Author Commented:
Hi angle111

Just look at the code change from the old code.

kind regards
darrylf290567
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I asked, because the main difference I see is that you specified the parameter names...
and used adVarchar instead of adChar
and added the .prepared = false...

what made the difference actually?
because, the parameter names and advarchar is what I suggested in my first comment

I want to understand what really was wrong, so that I learn from your experience.
0
darrylf290567Author Commented:
Hi angellll,

No the parameter names and the cahnge to varchar did nothing. It was adding in the .Prepared = false that did it. I only added the parameter names for tidyness and to make the function more readable for the next developer who will maintain it. The parameter names and the advarchar had no bearing whatsoever on the right solution. It did not make it work as I explained to you before in my previous comment.

kind regards
darrylf290567
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Thanks for clarifying.

Questions PAQed with points refunded
angelIII, zone advisor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.