unidac - oracle - behaviour changes in direct mode

[albourgz]

Table testclob(id number(10), texte clob)
TUniSQL QInsClob: INSERT INTO TESTCLOB(id, texte) VALUES (:1, :2)
(temporarylobupdate is false, TUniSession connects to oracle 12.1, charset not specified, UnicodeEnvironment is true, UseUnicode is true)

QInsClob->Params->Items[0]->AsInteger=1;
// sVarC holds a 70Kb String with accentuated (unicode) characters
QInsClob->Params->Items[1]->AsWideString=sVarC;
QInsClob->Execute();

This works perfectly when Direct mode is set to false and oracle client is used. When direct mode is set to true, it only works when sVarC has less than 32Kb. Is it possible to have a consistent behaviour in direct mode and non-direct mode, and independantly of the length of the clob data?

Regards.

[MaximG]

We could not reproduce the error according to your description. To further investigate the issue, compose and send us a small sample, execution of which causes the issue. You can do it via the e-support form ( https://www.devart.com the "Support"\"Request Support" menu)

[albourgz]

For the ones that would be interested, the only thing to do to make it work in nearly all cases is:

Code: Select all

QInsClob->Params->Items[2]->Clear();
if (sVarC.Length()>65535 && m_iDirect) 
    QInsClob->Params->Items[2]->AsMemo=sVarC;
else 
    QInsClob->Params->Items[2]->AsString=sVarC;
QInsClob->Execute();

It works "nearly" always. Why nearly?
1. If, in batch, I make a direct connection, run statements, commit, change connect mode, re-run the connection, it will fail.
If I stop and restart the app to go from OCI to direct or direct to OCI it works.
2. If the connection is always the same on one computer, it seems to always work using OCI.
3. In direct mode it always works to WE8ISO8859P1.
To UTF8 if you run one time AsMemo with 70Kb accentuted chars, then run AsString <70Kb accentuated chars, it fails. (even if the statement is unprepared then re-prepared).
So once you insert a 70Kb memo with accentuated chars to utf8 in direct mode, you have to restart the app!!!!

[MaximG]

When working with the CLOB type fields, in all cases, we recommend referring to the corresponding parameters using the AsMemo method. If for some reasons you need to use the AsString method, then use 32768 value instead of 65535 in your code

[albourgz]

Code: Select all

String s;
s=L"éééééééééééééééééééééééééééééééééé";
while (s.Length()<50000) s+=s;
OurDB->StartTransaction();
QInsClob->Prepare();
QInsClob->Params->Items[0]->AsInteger=0;
QInsClob->Params->Items[1]->AsMemo=s;
QInsClob->Execute();
QInsClob->Params->Items[0]->AsInteger=1;
QInsClob->Params->Items[1]->AsMemo=L"éé"; // this one fails!!!!!
QInsClob->Execute();
OurDB->Commit();

The second insert fails connected in direct mode to an AL32UTF8 database, no matter if using AsMemo or AsString.
Can you tell me how to make this second insert to work?

[MaximG]

Please specify the error you get when executing the second query, which inserts data

[albourgz]

error is "ORA-01461: can bind a LONG value only for insert into a LONG column."

PS: both queries insert data, the same way.

[MaximG]

We tested the described behavior in the following environment :

- Microsoft Windows 10 with English (USA) codepage
- Oracle 12c database with AL32UTF8 codepage
- C++Builder 10.2
- UniDAC 7.2.7

We investigated the following table :

Code: Select all

CREATE TABLE LOADER_TYPES1 (ID NUMBER, F_CLOB CLOB, CONSTRAINT PK_LOADER_TYPE1S PRIMARY KEY (ID))

and the code below :

Code: Select all

  ... 
  UniConnection -> SpecificOptions -> Values["Direct"]="True";
  UniConnection -> SpecificOptions -> Values["UseUnicode"]="True";
  UniConnection -> SpecificOptions -> Values["UnicodeEnvironment"]="True";

  UniConnection -> Server = "oracle12c:1521:orclpdb";
  UniConnection -> Username = "scott";
  UniConnection -> Password = "tiger";

  String TxtValue;
  TxtValue=L"éééééééééééééééééééééééééééééééééé";
  while (TxtValue.Length()<50000) TxtValue+=TxtValue;

  UniConnection -> Connect ();
  UniConnection -> ExecSQL("Truncate Table Loader_Types");
  UniConnection -> StartTransaction();

  UniQuery -> SQL -> Text="INSERT INTO Loader_Types(ID, F_CLOB) VALUES (:1, :2)";
  UniQuery -> Prepare();
  UniQuery -> Params -> Items[0] -> AsInteger=0;
  UniQuery -> Params -> Items[1] -> AsMemo=TxtValue;
  UniQuery -> Execute();
  UniQuery -> Params -> Items[0] -> AsInteger=1;
  UniQuery -> Params -> Items[1] -> AsMemo=L"éé";
  UniQuery -> Execute();
  UniConnection -> Commit();
  ...

We successfully inserted the test data into the Loader_Types table. Please repeat the same steps in your test environment and let us know about the result. Also provide the information about the IDE version and Windows regional settings

[albourgz]

CREATE TABLE LOADER_TYPES...
instead of CREATE TABLE LOADER_TYPES1...

---------------------------
Debugger Exception Notification
---------------------------
Project Project93.exe raised exception class EOraError with message 'ORA-01461: can bind a LONG value only for insert into a LONG column
'.
---------------------------
Break Continue Help
---------------------------
Same error.

[MaximG]

Perhaps, we cannot reproduce the described error due to the differences between the used environments. Please specify the IDE version you are using, as well as provide the information about Windows regional settings of your environment

[albourgz]

Embarcadero® RAD Studio 10.2 Version 25.0.27659.1188
(10.2 uodate 1)
Windows 10 Build 16299 64 bits
Region is Belgium, utc+1, languages french and english.

[MaximG]

We reproduced the issue when working with IDE Embarcadero® RAD Studio 10.2 C++Builder and we've started to investigate it. We will let you know the results as soon as we get them

[albourgz]

I am really glad to read this!!!!! Thanks.

[albourgz]

Hi,

any news? The new unidac release doesn't fix this issue.

Regards.

[MaximG]

Currently, we are still engaged in investigating the described issue, therefore, the latest build of UniDAC does not contain any changes related to this question. We hope to get the fast results shortly