LONG, CLOB UPDATE COLUMN PROBLEM

[erik78]

Hello Everybody,

I'm a software engineer and my company buy UniDirect.NET for migrate one application from Delphi to C#.

The database of application is Oracle and I have a problem with a few columns.

This columns are of type "Long" and I use the type Varchar of Unidirect for update fields firstly. When the application execute the "ExecuteNonQuery" command for update fields only update or insert the first 4000 chars in this kind of columns(Long).

After I try to use the UniBlobs objects (type CLOB) for update this columns, but the result is the same.
I try to use this Uniblobs objects with CLOB columns too, and the result is the same (only updated the first 4000 chars).

In Delphi application, use the DBExpress Compononet for access to database. And use the type ftMemo for manage this kind of columns and works perfectly.

What is the good or best solution?

Thanks for your attention.

I pass you at example of code that initialize at Binary Field:

public override void SetValueTo(TDBConnection pConnection,string pParameterName)
{
UniParameter vParameter;
UniBlob vClob;
byte[] vBuffer;

if (!ReferenceEquals(pConnection.SQLQuery.Parameters, null))
{
//check is value is null
if (IsNull)
vParameter = new UniParameter(pParameterName, DBNull.Value);
else
{
vBuffer=Encoding.UTF8.GetBytes(vValue);
vClob = new UniBlob(pConnection.SQLConnection, UniDbType.Clob);
vClob.Write(vBuffer, 0, vBuffer.Length);

vParameter = new UniParameter(pParameterName, UniDbType.Clob, vBuffer.Length);
vParameter.Value = vClob;
}
pConnection.SQLQuery.Parameters.Add(vParameter);
}
else
throw new Exception("Sql Parameters not assigned");
}

[Alexey.mdr]

Hi!

Could you please post a table definition script?
We will investigate the problem.

[erik78]

Hello Everybody,

I'm a software engineer and my company buy UniDirect.NET for migrate one application from Delphi to C#.

The database of application is Oracle and I have a problem with a few columns.

This columns are of type "Long" and I use the type Varchar of Unidirect for update fields firstly. When the application execute the "ExecuteNonQuery" command for update fields only update or insert the first 4000 chars in this kind of columns(Long).

After I try to use the UniBlobs objects (type CLOB) for update this columns, but the result is the same.
I try to use this Uniblobs objects with CLOB columns too, and the result is the same (only updated the first 4000 chars).

In Delphi application, use the DBExpress Compononet for access to database. And use the type ftMemo for manage this kind of columns and works perfectly.

What is the good or best solution?

Thanks for your attention.

I pass you at example of code that initialize at Binary Field:

public override void SetValueTo(TDBConnection pConnection,string pParameterName)
{
UniParameter vParameter;
UniBlob vClob;
byte[] vBuffer;

if (!ReferenceEquals(pConnection.SQLQuery.Parameters, null))
{
//check is value is null
if (IsNull)
vParameter = new UniParameter(pParameterName, DBNull.Value);
else
{
vBuffer=Encoding.UTF8.GetBytes(vValue);
vClob = new UniBlob(pConnection.SQLConnection, UniDbType.Clob);
vClob.Write(vBuffer, 0, vBuffer.Length);

vParameter = new UniParameter(pParameterName, UniDbType.Clob, vBuffer.Length);
vParameter.Value = vClob;
}
pConnection.SQLQuery.Parameters.Add(vParameter);
}
else
throw new Exception("Sql Parameters not assigned");
}

Hello and thanks for reply question:

I pass our the script of table:

DROP TABLE TABLEX;
CREATE TABLE TABLEX (
ID NUMBER,
USERNAME VARCHAR2(20),
ACCESSDATA LONG
);

The most important column is "ACCESSDATA".

Thanks.

[Alexey.mdr]

We reproduced the problem and now we are fixing it.
Look forward to the next build.

[erik78]

We reproduced the problem and now we are fixing it.
Look forward to the next build.

Hello to everybody,

When will be the solution published?

Because I'm waiting it. I like to have at response at soon as posible for comunicate it to my boss.

Thanks a lot.

[Alexey.mdr]

The build is not ready yet.
The bug fixing takes more time than expected.
We plan to issue a new build in 5-7 days. Thank you for your patience.

[Alexey.mdr]

The problem is fixed in the new build.

Alexey.

[erik78]

The problem is fixed in the new build.

Alexey.

Hello to everybody,

I test this case in the same code and CoreLab produces an error when execute the method ExecuteNonQuery() -ORA-01483: invalid length for DATE or NUMBER bind variable-

The Error reproduced it, when parameter has more than 4000 characters, otherwise works perfectly. I tested using VARCHAR and CLOB UniDirect types.

Can you pass me an example of code that update at ORACLE LONG column that has more than 400 characters?

Thanks a lot.

[anton.connect]

Unfortunately, we couldn't reproduce the problem on our side. If possible, please send us a test project showing this error.

As an alternative, you can try one of the following ways to update the LONG columns:
1. using (UniConnection connection = new UniConnection("...")) {

UniCommand cmd = connection.CreateCommand();
cmd.CommandText = "UPDATE TABLEX SET ACCESSDATA = :ACCESSDATA WHERE (ID = :id)";
UniParameter paramLong = cmd.Parameters.Add("accessData", UniDbType.Clob);
cmd.Parameters.Add("id", 1);
paramLong.Value = GetLongValue();

connection.Open();
cmd.ExecuteNonQuery();
}

string GetLongValue() {

return "..."; // long text here
}

2. using (UniConnection connection = new UniConnection("...")) {

UniCommand uc = new UniCommand("UPDATE TableX SET ID = 1, accessdata = :accessdata", connection);
string text = GetLongValue();
uc.Parameters.Add(new UniParameter("accessdata", UniDbType.Clob));
uc.Parameters[0].Value = CreateClob(uniConnection, text);

connection.Open();
cmd.ExecuteNonQuery();
}

private UniBlob CreateClob(UniConnection uniConnection, string value) {

UniBlob uniBlob = new UniBlob(uniConnection, UniDbType.Clob);
byte[] chars = Encoding.Default.GetBytes(value);
uniBlob.Write(chars, 0, chars.Length);
return uniBlob;
}

But, the key point is that UniDirect.NET will insert only first 4000 characters in the LONG column.
This is caused by the fact that UniDirect.NET doesn't have full support for deprecated LONG columns.
To avoid such problems use CLOB type in your database table instead of LONG. With CLOB type the text will be inserted in full length.