OracleDataAdapter + ParameterDirection.InputOutput

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
DTSup
Posts: 5
Joined: Tue 02 Nov 2010 10:45

OracleDataAdapter + ParameterDirection.InputOutput

Post by DTSup » Wed 03 Nov 2010 16:19

Hi, I got an issue with OracleDataAdapter + ParameterDirection.InputOutput. I use OracleDataAdapter.Update(Dataset)), but it doesn't return any values. It works ok with ExecuteNonQuery. Here is my sample:

Code: Select all

Dim conn As OracleConnection = New OracleConnection("User Id=servtest;Password=***;Server=test;Unicode=True;")

conn.Open()

Dim cmd As New OracleCommand

 cmd.Connection = conn
        cmd.CommandText = "F.WARNINGS_CP.ins"
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("audit_id_in", OracleDbType.Number, 5)
        cmd.Parameters("audit_id_in").Direction = ParameterDirection.Input
       
        cmd.Parameters("audit_id_in").SourceColumn = "audit_id"

        cmd.Parameters.Add("warning_id_out", OracleDbType.Number, 0)
        cmd.Parameters("warning_id_out").Direction = ParameterDirection.InputOutput

        cmd.Parameters("warning_id_out").SourceColumn = "warning_id"
        Dim adapter As OracleDataAdapter = New OracleDataAdapter()
        Dim ds As DSScheduleForecastWarnings = New DSScheduleForecastWarnings(0,0)

adapter.InsertCommand = cmd
adapter.Update(ds.Tables("WARNINGS"))

I can provide more information if reproducing this is a problem. Thanks

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 04 Nov 2010 16:38

Could you please send us the DDL/DML script of your F.WARNINGS_CP.ins procedure with the corresponding database objects? Also please include the link to this post into your request.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 04 Nov 2010 16:43

A small complete test project will be appreciated (show us your DSScheduleForecastWarnings class).

DTSup
Posts: 5
Joined: Tue 02 Nov 2010 10:45

Post by DTSup » Mon 08 Nov 2010 15:22

I can't reproduce this issue with 5.35.62.0 (not trial) but i got the issue with 5.70.180 and 6.x beta. For the DSScheduleForecastWarnings it's a simple dataset. I think you can make dataset with 2 decimal rows. Use second row for mapping InputOutput parameter. I'll try to upload my project later

mkashyap
Posts: 11
Joined: Mon 09 Nov 2009 21:26

Post by mkashyap » Mon 08 Nov 2010 22:39

I am a coworker of DTSup. We are trying to figure this out. Below is the code I am trying to run. I am using dotConnect for Oracle version 5.70.190(trial version) and Visual Studio 2010. I am opening a connection in the direct mode. I don't know if that makes any difference but what I am seeing is very strange.

Dim cm As New OracleCommand
cm.Connection = conn
cm.CommandText = ssql
cm.ExecuteNonQuery()

cmd.Connection = conn
cmd.CommandText = "F21.FSF_SCHED_FCST_WARNINGS_CP.ins"
cmd.CommandType = CommandType.StoredProcedure


cmd.Parameters.Add("sched_forecast_audit_id_in", OracleDbType.Number, 5)
cmd.Parameters("Sched_forecast_audit_id_In").Direction = ParameterDirection.Input
cmd.Parameters("Sched_forecast_audit_id_In").SourceColumn = "sched_forecast_audit_id"


cmd.Parameters.Add("message_in", OracleDbType.VarChar, 4000)
cmd.Parameters("message_in").Direction = ParameterDirection.Input
cmd.Parameters("message_in").SourceColumn = "message"

cmd.Parameters.Add("severity_in", OracleDbType.Char, 1)
cmd.Parameters("severity_in").Direction = ParameterDirection.Input
cmd.Parameters("severity_in").SourceColumn = "severity"

cmd.Parameters.Add("created_datetime_in", OracleDbType.Date, 0)
cmd.Parameters("created_datetime_in").Direction = ParameterDirection.Input

cmd.Parameters.Add("created_by_in", OracleDbType.VarChar, 4000)
cmd.Parameters("created_by_in").Direction = ParameterDirection.Input

cmd.Parameters.Add("sched_fcst_warning_id_out", OracleDbType.Number, 0)
cmd.Parameters("sched_fcst_warning_id_out").Direction = ParameterDirection.InputOutput
cmd.Parameters("sched_fcst_warning_id_out").SourceColumn = "sched_fcst_warning_id"


Dim adapter As OracleDataAdapter = New OracleDataAdapter()
Dim ds As DSScheduleForecastWarnings = New DSScheduleForecastWarnings()
Dim row As DSScheduleForecastWarnings.FSF_SCHED_FCST_WARNINGSRow = ds.FSF_SCHED_FCST_WARNINGS.NewFSF_SCHED_FCST_WARNINGSRow

ds.FSF_SCHED_FCST_WARNINGS.AddFSF_SCHED_FCST_WARNINGSRow(0, 482, "qwe", "W", DateTime.Now, "asdsa")
adapter.InsertCommand = cmd
adapter.Update(ds.Tables("FSF_SCHED_FCST_WARNINGS"))
'end data adapter

Dim I As Integer = cmd.Parameters.Item("sched_fcst_warning_id_Out").Value



As I step through the code, in the watch window, I see the parameters being added correctly, the insert command takes place without any problems. I see the name of the adapter.insertcommand.Parameters(5) set to "sched_Fcst_warning_id_out" which is correct. As soon as I execute the adapter.update statement, I see that the adapter.insertcommand.Parameters(5) displays "sched_fcst_warning_id_in" in my watch window. Therefore, the next line will fail because "sched_fcst_warning_id_Out" is no longer the name of the parameter. I get a "A parameter is not contained by this Parameters collection" error. If I look at the value as adapter.insertcommand.parameters(5).value, it is 0.
I am not sure what is happening, why is the name of the parameter changing? This procedure works just fine with 5.35 version of Devart.

I think DTSup will upload the sample code but this must be easy enough to try. Is there a reason why the parameter name is changing and goes from being an inputout to only an input parameter? Is there something wrong in the way we are setting up the parameters?
I should add that the Database procedure itself is executed and I see the change in the relevant table.
Appreciate your help in this.

DTSup
Posts: 5
Joined: Tue 02 Nov 2010 10:45

Post by DTSup » Wed 10 Nov 2010 13:34

I sent my simple project to you. I resolved my problem, but i think you should see it because i didn't get this issue with 5.35 version. Thank you.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 10 Nov 2010 17:25

We will investigate why dotConnect for Oracle's behaviour is different in the 5.35 and 5.70 versions and notify you about the results. Generally, it is better to set the number of overload when using overloaded functions: http://www.devart.com/forums/viewtopic.php?t=19404 .

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 17 Jan 2011 14:44

We have added possibility to use overloaded stored procedures which do not return result set with ExecuteScalar() without setting a number of overload. This functionality will be available starting from the next build of dotConnect for Oracle. I will post here when it is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 27 Jan 2011 16:30

New version of dotConnect for Oracle 6.10 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=20115 .

Post Reply