LONG, CLOB UPDATE COLUMN PROBLEM

LONG, CLOB UPDATE COLUMN PROBLEM

Postby erik78 » Mon 28 Apr 2008 14:29

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");
}
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11

Postby Alexey.mdr » Mon 05 May 2008 09:28

Hi!

Could you please post a table definition script?
We will investigate the problem.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Re: LONG, CLOB UPDATE COLUMN PROBLEM

Postby erik78 » Mon 05 May 2008 09:48

erik78 wrote: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.
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11

Postby Alexey.mdr » Mon 05 May 2008 13:47

We reproduced the problem and now we are fixing it.
Look forward to the next build.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby erik78 » Fri 23 May 2008 13:03

Alexey.mdr wrote: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.
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11

Postby Alexey.mdr » Fri 23 May 2008 14:41

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
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Alexey.mdr » Fri 30 May 2008 14:17

The problem is fixed in the new build.

Alexey.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby erik78 » Mon 23 Jun 2008 16:03

Alexey.mdr wrote: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.
erik78
 
Posts: 23
Joined: Thu 14 Feb 2008 10:11

Postby anton.connect » Tue 24 Jun 2008 10:42

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.
anton.connect
 
Posts: 43
Joined: Thu 19 Jun 2008 14:30


Return to dotConnect Universal