Devart Forums

Hi,

why I'm not see DesignTimeVisible property in UniCommand ?


Marco

Hi,

I think use of dbMonitor only in a debug context. I need to log statement error in a release context. That is I execute a statement. If it's all ok then I go to next instruction. If data source raise an exception then, in a catch block I insert a row in a log table with the sql statement that generates the exception. Following your hint I need to create and activate the instance of dbMonitor before to execute the statement, anyway (also if the execution don't raise exceptions)?

Marco

Hi

is there access to read from UniCommand the statement effectively executed agaist a data provider (what I assign to CommandText may has some characters specific to UniSQL)? I could use these "true" statements in log messages.

Thank you

Best Regard

Marco Lagasio

Hi

with the following piece of code

UniConnection conn = new UniConnection(cs.ConnectionString);

UniCommand cmd = new UniCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
//cmd.ParameterCheck = true;
cmd.CommandText = "MyStoredProcedure";

UniParameter par1 = new UniParameter("@v_Parameter", UniDbType.VarChar, 50);
par1.Value = ParameterName;
cmd.Parameters.Add(par1);


UniParameter par2 = new UniParameter("@v_Area", UniDbType.VarChar, 50);
par2.Value = Area;
cmd.Parameters.Add(par2);

UniParameter par3 = new UniParameter("@v_Profile", UniDbType.VarChar, 50);
par3.Value = Profile;
cmd.Parameters.Add(par3);

UniParameter par4 = new UniParameter("@v_ACTIVATED", UniDbType.Int);
par4.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par4);


UniParameter par5 = new UniParameter("@v_ConfigValue", UniDbType.VarChar, 261);
par5.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par5);

UniParameter par6 = new UniParameter("@v_ConfigMin", UniDbType.VarChar, 50);
par6.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par6);

UniParameter par7 = new UniParameter("@v_ConfigMax", UniDbType.VarChar, 50);
par7.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par7);


conn.Open();
cmd.ExecuteNonQuery();

Activated = (int)cmd.Parameters["v_Activated"].Value;

all runs fine; if I decomment cmd.ParameterCheck = true, I've a problem on instruction

cmd.ExecuteNonQuery();

the connection in this case is to SQL Server and the error is

"Procedure or Function 'MyStoredProcedure' expects parameter '@v_ACTIVATED', which was not supplied."

The error vanish if I put a default to OUTOUT parameters (in the stored procedure)


The scripts for the stored procedure and the table it uses are

CREATE PROCEDURE MyStoredProcedure
@v_PARAMETER VARCHAR(50) ,

@v_AREA VARCHAR(50) ,
@v_PROFILE VARCHAR(50) ,
@v_ACTIVATED INT OUTPUT,
@v_ConfigValue VARCHAR(261) OUTPUT ,
@v_ConfigMin VARCHAR(50) OUTPUT,
@v_ConfigMax VARCHAR(50) OUTPUT

-- @v_ACTIVATED INT = 0 OUTPUT,
-- @v_ConfigValue VARCHAR(261) = null OUTPUT ,
-- @v_ConfigMin VARCHAR(50) = null OUTPUT,
-- @v_ConfigMax VARCHAR(50) = null OUTPUT

AS
BEGIN

SET @v_ACTIVATED = 0
SET @v_ConfigValue = NULL
SET @v_ConfigMin = null
SET @v_ConfigMax = null

SELECT @v_ACTIVATED = Activated, @v_ConfigValue = ConfigValue, @v_ConfigMin = ConfigMin, @v_ConfigMax = ConfigMax
FROM ConfigParam
WHERE Parameter = @v_PARAMETER AND Area = @v_area AND [Profile] = @v_profile;


END


CREATE TABLE ConfigParam(
[Parameter] [varchar](50) NOT NULL,
[Area] [varchar](50) NOT NULL,
[Profile] [varchar](50) NOT NULL,
[Activated] [int] NOT NULL,
[ConfigValue] [varchar](261) NULL,
[ConfigMin] [varchar](50) NULL,
[ConfigMax] [varchar](50) NULL,
CONSTRAINT [PK_ConfigParam] PRIMARY KEY CLUSTERED
(
[Parameter] ASC,
[VirtualHostSetID] ASC,
[Area] ASC,
[Profile] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


In SQL Server parameter marked as OUTPUT are also input param, but in c# code I've marked parameter.direction as Output!

Best Regards

Marco

Hi,

waiting your news about this topic, we have think a bad patch like

Activated = Convert.ToInt(((UniDecimal)cmd.Parameters["v_Activated"].Value).Value);

but we hope that your patch arrive as soon as possible.

I've also found another problem that we report I another post.

Regards, Marco

Hi

with the following piece of code

UniConnection conn = new UniConnection(cs.ConnectionString);

UniCommand cmd = new UniCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
//cmd.ParameterCheck = true;
cmd.CommandText = "MyStoredProcedure";

....some parameter defs

UniParameter par4 = new UniParameter("v_Activated", UniDbType.Int);
par4.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par4);

conn.Open();
cmd.ExecuteNonQuery();

Activated = (int)cmd.Parameters["v_Activated"].Value;

all runs fine; if I decomment cmd.ParameterCheck = true, I've a cast exception in the last line (the connection is to Oracle and the stored procedure parameter v_Activated is defined as number). I've seen that the c# type of Value is UniDecimal type.

Best Regard
Marco

Hi, thanks to you.
I'd want to clarify my scenario.

We have in

-Oracle db a procedure defined by

create or replace
PROCEDURE "INSERT_ORDER"
(
v_SURNAME IN NVARCHAR2 DEFAULT NULL ,
v_NAME IN NVARCHAR2 DEFAULT NULL ,
v_cur OUT Types.Cursor_Type
)
AS
v_orderCode nvarchar2(10);
BEGIN
INSERT INTO ORDERS
( ORDERCODE, SURNAME, NAME)
VALUES ( GENERATEKEYORDER(), v_SURNAME, v_NAME)
returning ORDERCODE into v_orderCode;

OPEN v_cur FOR
SELECT v_orderCode from DUAL;

END;

-in SQL Server db a procedure defined by

CREATE PROCEDURE [Insert_Order]
@v_SURNAME varchar (255),
@v_NAME varchar (255),
AS
BEGIN
insert into ORDER
(ORDERCODE , SURNAME, NAME)
values (GENERATEKEYORDER() ,@v_SURNAME ,@v_NAME)
END

with the following trigger

CREATE TRIGGER [ORDER_INSERT] ON [ORDERS] FOR INSERT
AS
BEGIN
SELECT ORDERCODE FROM INSERTED
END


The following c# code call both sps, without define in c# the cursor parameter.


UniConnection conn = new UniConnection("Data Source=MLAGASIO;Initial Catalog=MyDB;User ID=ML;Password=ML");
conn.Open();

UniCommand cmd = new UniCommand();
cmd.Connection = conn;
cmd.CommandText = "Insert_Order";
cmd.CommandType = CommandType.StoredProcedure;

UniParameter param;

param = cmd.Parameters.Add("@v_SURNAME", UniDbType.VarChar);
param.Direction = ParameterDirection.Input;
param = cmd.Parameters.Add("@v_NAME", UniDbType.VarChar);
param.Direction = ParameterDirection.Input;

int ordercode = (int)cmd.ExecuteScalar();

conn.Close();

conn.Dispose();

In the same way with ExecuteScalar I could obtain the number of affected rows for updating stored procedure (in the ref cursor param I could return as I want).

But the same (that is the handle of a refcursor) perhaps it's not possible with ExecuteNonQuery (in your example the Oracle Stored procedure have one more parameter of integer type defined explicitly
in c# code)?

Best Regard
Marco

Hi,

I agree with you:

when working with SQL Server as well dotConnect Universal returns the number of rows affected

My question is:

when working with Oracle, is there a modality to return the number of rows affected?

From your answer, I could understand that the answer is no.

In this scenario, the c# code to call the SQL stored procedure and the Oracle stored procedure differs for the need to define one more out parameter in the second case. Is it correct?

We have see that UniCommand (or wrapped providers) handle implicitly refcursor parameter in Oracle Store Provedure with executescalar and executeReader. Is there an implicit handling of row affected parameter as well?

Best Regard

Hi,

is it possible to return the number of affected rows calling

aUniCommand.ExecuteNonQuery

where aUniCommand is associated to an Oracle Stored procedure (containing only an update statement), without defining an out parameter to return this value, at least on c# side?
With SqlCommand it's possible.
We have see that UniCommand handle implicitly refcursor parameter in Oracle Store Provedure with executescalar and executeReader

Best regard Marco

Hi,

I refer to properties

Code in Devart.Data.Oracle.OracleException
Number in System.Data.SqlClient.SqlException

The names and values are obviously different but in some case the mean of the errors are the same (i.e. Primary Key Violation

Code=1;
Number=2657;

)

Bye, Marco

Hi,

perhaps the effect at database level is that you have tested, but
our code look like this

TransactionOptions txOptions = new TransactionOptions();
txOptions.Timeout = new TimeSpan(0, 1, 00);
txOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (TransactionScope transactionSc = new TransactionScope(TransactionScopeOption.Required, txOptions))
{
using (Connection = new SqlConnection("Data Source=MyDataSource;Initial Catalog=MyCatalog;User ID=MyUserID;Password=MyPassword"))
{
Connection.Open();
try
{
........(DoSomething).......

transactionSc.Complete();
}
finally
{
Connection.Close();
}
}
}

Thanks, Marco

In our application we have use implicit transactions (we have not explicit SqlTransaction instances).
How could we migrate these cases to dotConnect Universal?

Many Thanks

Marco

Hi,

when I say

"a common subset of error category"

I mean

primary key violation
foreign key violation
.....

I think all rdb have these concepts, so I would think possible have a property in UniException that give a unified enumerated for these errors (the property ErrorCode is probably different).
Anayway your previous answer have given to me hints to create the enumerated by myself.

Many thanks
Marco

Hi,

thank you for the answer. Some feedbacks.

1) all servers have not also an error code and a common subset of error category?

2) now all work (your answer has been resolutive)

3) good

Bye, Marco

Hi. Yes my project has a reference to Devart.Data.Oracle.dll from dotConnect for Oracle (not the free version). As you say (if I've understood) then provider specific classes are available...
(It runs when I connect to Sql Server, the cast has no problem).

I remain in standby, waiting your investigation results.

Many thanks
Marco Lagasio