UniParameter positional or not

mlagasio · Post by **mlagasio** » Mon 10 Oct 2011 14:46

Hi,

in a test application I've called a sp with the following code

=========================================
Devart.Data.Universal.UniCommand uniCommand = new Devart.Data.Universal.UniCommand();

string connectionString =
"Provider=Oracle" +

//Username
";User Id=" + "elektra" +

//Password
";Password=" + "elektra" +

//Replace with your datasource value
";Data Source=" + "RISDB"; //+

UniMonitor um = new UniMonitor();
um.IsActive = true;

uniCommand.Connection = new Devart.Data.Universal.UniConnection(connectionString);
uniCommand.Connection.Open();

uniCommand.CommandText = "WS_AMWRK_BOO_DTASC_NOLAST";
uniCommand.CommandType = CommandType.StoredProcedure;

uniCommand.Parameters.Add(new UniParameter("v_RETURN_VALUE", UniDbType.Int, 4, ParameterDirection.Output, true, 10, 0, null, DataRowVersion.Current, null));

uniCommand.Parameters.Add(new UniParameter("v_STARTDATE", UniDbType.TimeStamp, 8));
uniCommand.Parameters.Add(new UniParameter("v_ENDDATE", UniDbType.TimeStamp, 8));
uniCommand.Parameters.Add(new UniParameter("v_CSVWEEKDAYS", UniDbType.NVarChar, 100));

uniCommand.Parameters.Add(new UniParameter("v_RADIOLOGISTCODE", UniDbType.NVarChar, 10));
uniCommand.Parameters.Add(new UniParameter("v_SITECODE", UniDbType.NVarChar, 10));
uniCommand.Parameters.Add(new UniParameter("v_ROOMCODE", UniDbType.NVarChar, 10));
uniCommand.Parameters.Add(new UniParameter("v_MODALITYCODE", UniDbType.NVarChar, 10));
uniCommand.Parameters.Add(new UniParameter("v_ORIGINTYPECODE", UniDbType.NVarChar, 10));
uniCommand.Parameters.Add(new UniParameter("v_PRIORITYCODE", UniDbType.VarChar, 10));

uniCommand.Parameters.Add(new UniParameter("v_STATUS", UniDbType.Int, 4));

uniCommand.Parameters["v_STARTDATE"].Value = new DateTime(2011, 7, 14, 0, 0, 0);
uniCommand.Parameters["v_ENDDATE"].Value = new DateTime(2011, 7, 14, 23, 59, 00);
uniCommand.Parameters["v_STATUS"].Value = 0;
uniCommand.Parameters["v_CSVWEEKDAYS"].Value = "0,1,2,3,4,5,6";
uniCommand.Parameters["v_RADIOLOGISTCODE"].Value = DBNull.Value;
uniCommand.Parameters["v_SITECODE"].Value = DBNull.Value;
uniCommand.Parameters["v_ROOMCODE"].Value = DBNull.Value;
uniCommand.Parameters["v_MODALITYCODE"].Value = DBNull.Value;
uniCommand.Parameters["v_ORIGINTYPECODE"].Value = DBNull.Value;
uniCommand.Parameters["v_PRIORITYCODE"].Value = DBNull.Value;

Devart.Data.Universal.UniDataReader dataReader = uniCommand.ExecuteReader();

object obj = null;

int recCount = 0;

if (dataReader.FieldCount > 0)
{

while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
obj = dataReader.GetValue(i);

recCount++;
}

}

MessageBox.Show(recCount.ToString());

dataReader.Close();
uniCommand.Connection.Close();
============================================

The signature of the sp is

============================================
CREATE OR REPLACE PROCEDURE "WS_AMWRK_BOO_DTASC_NOLAST"(v_RETURN_VALUE OUT number,

v_STARTDATE IN TIMESTAMP DEFAULT NULL,
v_ENDDATE IN TIMESTAMP DEFAULT NULL,
v_CSVWEEKDAYS IN NVARCHAR2 DEFAULT NULL,
v_RADIOLOGISTCODE IN NVARCHAR2 DEFAULT NULL,
v_SITECODE IN NVARCHAR2 DEFAULT NULL,
v_ROOMCODE IN NVARCHAR2 DEFAULT NULL,
v_MODALITYCODE IN NVARCHAR2 DEFAULT NULL,
v_ORIGINTYPECODE IN NVARCHAR2 DEFAULT NULL,
v_PRIORITYCODE IN NVARCHAR2 DEFAULT NULL,
v_STATUS IN NUMBER DEFAULT NULL,

v_cur IN OUT Types.cursor_type
========================================

As you could see the last sp param has not been added explicitly
to the UniCommand.Parameters collection.

Launching the application the messagebox show 14 records found.
If I change in c# code the name of the parameters substituting v_ with @ (in all places, that is adding parameter and assigning values)
then the message box return 0 records. I've leave checkParameter to false (the default).

The question is: the parameter definition is positional or not?

Many thanks

Best Regards
Marco

Shalex · Post by **Shalex** » Tue 18 Oct 2011 12:44

mlagasio wrote:The question is: the parameter definition is positional or not?

If parameters are added to the command collection in the order that is different from the function parameters order in database, it is necessary to describe the command by setting UniCommand.ParameterCheck to true to reoder parameters in a proper way (omitted parameters will be created and assigned DBNull value): http://www.devart.com/dotconnect/univer ... l#storproc.

mlagasio · Post by **mlagasio** » Tue 18 Oct 2011 14:28

Hi,

I've added the parameters in the same order as in the stored procedure header. Only I've not added the last parameter (the cursor).
In my case CheckParameter = false (the default). If I name the c# parameters as the sp parameter all run fine, if I use different names all run but the row retrieved are wrong (none against 14 rows).

Where is the problem?

Best Regards
Marco

Shalex · Post by **Shalex** » Thu 20 Oct 2011 16:40

The parameter definition is not positional, but the parameter name has to be specified correctly (as it is defined in stored procedure) with prefix @ or without @.

mlagasio · Post by **mlagasio** » Mon 24 Oct 2011 12:33

Hi,
I thank you but the misunderstanding is with the phrase in your help on parameter. Exactly

"When you invoke a stored procedure you have to create collection of parameters that corresponds strictly to set of arguments for the stored procedure in quantity and types. Names of parameters do not matter unless you set UniCommand.ParameterCheck property to true."

What could you say about?

Regards
Marco

Shalex · Post by **Shalex** » Wed 26 Oct 2011 16:37

The phrase is true (for MySQL, PostgreSQL, MSSQL, etc) but the situation with Oracle is an exception to the rule: names of parameters are always taken into account because the procedure is described before execution (like UniCommand.ParameterCheck=true). We will add this information to our documentation.

mlagasio · Post by **mlagasio** » Thu 27 Oct 2011 07:57

Hi,

this is a surprise for me because

1) I've migrated to Universal an entire business application leaving the names of the parameters (c# side) the same as defined before the migration from SQL Server (the names were and are of the form @name, but Oracle db side now are of the form v_name). This assuming positional the parameter definition (c# side)

2) I've make (with Oracle) a test like this: I've modified the sp above mentioned

a) duplicating the parameter (except the cursor), but changing to the new parameters, the parameter direction to out
b) changing the sp implementation to simple assignations to out pars of in pars.

C# side (with checkparameter = false) I read out pars with correct values assigned to in pars before call the stored procedure.

Sorry for the insistence

Best Regards
Marco

Shalex · Post by **Shalex** » Tue 01 Nov 2011 13:54

Sorry, it is not clear enough: is there any request to our support in your previous post?

mlagasio · Post by **mlagasio** » Wed 02 Nov 2011 08:49

Excuse me. I'll try to clarify my post.

There is no reference to request to your support in my post.

1) I think surprising that after I migrate an entire business application to SqlServer/Oracle from SqlServer only

-assuming positional the parameter definition in UniCommand
-using different names c# side and Oracle db side

I've never seen problems like that explained above.

Moreover

2) Before send to you my question, I've make the following test. I've call from c# by Universal an Oracle stored procedure passing to it some parameters a, b, c. In the store procedure I've assigned a, b, c to out parameters d, e, f (respectively), returned to c# application. The names of all parameters c# side are DIFFERENT from the corresponding parameter db side, but the parameter definition order is the same.
The values of the out parameters c# side are the same as in parameters.
That is

a==d
b==e
c==f

Also this surprise me with respect to your answer

I hope this is clear.

Regards
Marco

Shalex · Post by **Shalex** » Thu 03 Nov 2011 11:00

Please set the same names of parameters in the UniCommand.Parameters collection as the ones in stored procedure in Oracle.