Board index Database Programming

problem in executing an Oracle stored procedure by ADO

problem in executing an Oracle stored procedure by ADO

Post by Tan » Fri, 16 Nov 2001 12:50:09



I have a problem of getting a recordset from a Oracle stored procedure.
The procedure has one IN and two OUT parameter as follows:

procedure sp_pix_test (hiredate IN char(10),
        o_lastname OUT employee.LAST_NAME%type,
        o_date OUT employee.HIRE_DATE%type) AS
BEGIN
SELECT
 EMPLOYEE.LAST_NAME,
 EMPLOYEE.HIRE_DATE,
INTO
  o_lastname,  o_date
FROM
 EMPLOYEE
WHERE
 EMPLOYEE.HIRE_DATE > TO_DATE(hiredate,'yyyy-mm-dd');
END;

here is basicaly what I am doing in code:
'suppose I already had the connection and procedure name
'now I need to retireve the procedure's parameter list
'in order to present to user
With adoCmd
            .ActiveConnection = conn
            .CommandType = adCmdStoredProc
            .CommandText = procName
            .Parameters.Refresh
 End With
'Now, parameters are retrieved and user can modify
'the IN parameters from UI, for example:
adoCmd.Parameters("hiredate").Value = "1992-02-12"
'after user input all the IN parameters
Set rs = adoCmd.Execute

The problem is: rs is always nothing. I don't know what I should do with the
OUT parameters
I would apprecate if you can help me out.

 
 
 
Top

problem in executing an Oracle stored procedure by ADO

Post by dave » Sun, 18 Nov 2001 02:47:51


I'm an Oracle newbie, but I think your Procedure has to look more
like:

procedure sp_pix_test (hiredate IN char(10),
                       curResults OUT cur) AS
BEGIN
OPEN curResults FOR
SELECT
 EMPLOYEE.LAST_NAME,
 EMPLOYEE.HIRE_DATE,
FROM
 EMPLOYEE
WHERE
 EMPLOYEE.HIRE_DATE > TO_DATE(hiredate,'yyyy-mm-dd');
END sp_pix_test;

It seems you're confusing two ideas.  You can either retrieve ONE
record from a query, and return those values as output parameters, or
you can do a query and return all results (may be one record, may be
many, may be none) in a recordset.  The above method returns a
recordset.  Your method may work, but you wouldn't expect a recordset,
instead you would inspect the parameters().value after the execute to
get back the results.


> I have a problem of getting a recordset from a Oracle stored procedure.
> The procedure has one IN and two OUT parameter as follows:

> procedure sp_pix_test (hiredate IN char(10),
>         o_lastname OUT employee.LAST_NAME%type,
>         o_date OUT employee.HIRE_DATE%type) AS
> BEGIN
> SELECT
>  EMPLOYEE.LAST_NAME,
>  EMPLOYEE.HIRE_DATE,
> INTO
>   o_lastname,  o_date
> FROM
>  EMPLOYEE
> WHERE
>  EMPLOYEE.HIRE_DATE > TO_DATE(hiredate,'yyyy-mm-dd');
> END;

> here is basicaly what I am doing in code:
> 'suppose I already had the connection and procedure name
> 'now I need to retireve the procedure's parameter list
> 'in order to present to user
> With adoCmd
>             .ActiveConnection = conn
>             .CommandType = adCmdStoredProc
>             .CommandText = procName
>             .Parameters.Refresh
>  End With
> 'Now, parameters are retrieved and user can modify
> 'the IN parameters from UI, for example:
> adoCmd.Parameters("hiredate").Value = "1992-02-12"
> 'after user input all the IN parameters
> Set rs = adoCmd.Execute

> The problem is: rs is always nothing. I don't know what I should do with the
> OUT parameters
> I would apprecate if you can help me out.


 
 
 
Top

1. Problem with executing stored procedures using ADO

ADO COMMAND (Sample code (1) below) is used in VB6.0 to execute a stored
procedure to return a recordset from SQL Server 7.0 database. However, After
the Command is executed, no data is returned for the recordset and the
recordcount property of the returned recordset is -1.

Not knowing what is going on, I checked the returned recordset's Fields
property in the Watch window. It has all the fields from the underlying
database table, but its CursorType property is set at adOpenForwardOnly,
which doesn't support adBookmark and adHoldRecords.

Also strangely, if a SQL string rather than a stored procedure (Sample code
(2) below) is used to return a recordset, it works only if the CursorType
for the Open method is set at adOpenStatic or adOpenKeyset, and it didn't
work (recordcount = -1 again) if the CursorType is not set (Sample code (3)
below).

I don't know whether the problem is with the ADO object, or with the ODBC
connection, or with the SQL Server 7.0 database settings. Anyone has any
idea?

The sample codes looks like this,

(1)     Using Stored Procedure (Not Working)

   Public cmd1 As ADODB.Command
   Public rstRec As ADODB.Recordset

   Set cmd1= New ADODB.Command
   Set rstRec = New ADODB.Recordset

   cmd1.ActiveConnection = cn
'Connection
   cmd1.CommandText = "SP_SELECT"                      'Stored procedure
   cmd1.CommandType = adCmdStoredProc
   cmd1.CommandTimeout = 10

   Set param1 = cmd1.CreateParameter("Status", adVarChar, adParamInput, 1)
   cmd1.Parameters.Append param1
   param1.Value = "C"

   Set rstRec = cmd1.Execute

(2)     Using SQL string (Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, adOpenStatic, , adCmdText

(3)     Using SQL string (Not Working)

   Public rstRec As ADODB.Recordset
   Dim sSQL As String

   Set rstRec = New ADODB.Recordset

   sSQL = "SELECT * FROM STATUS WHERE MS_STATUS = 'C' "

   rstRec.Open sSQL, cn, , , adCmdText

2. Trigger SQL Query...In Data Driven Task Properties

3. attaching paradox tables into access97

4. Is Oracle offerring a trial version for Sun Solaris x86 platform?

5. VB and Sybase SQL

6. executing a stored procedure in oracle using ADO

7. ADO executing oracle stored procedure/function

8. Problems executing executing store procedure on different environments

9. Oracle 8.1.5 on NT Problem Executing Stored procedures

10. problem in executing an Oracle stored procedure


All times are UTC
Board index