unidac - oracle - behaviour changes in direct mode
unidac - oracle - behaviour changes in direct mode
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.
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.
Re: unidac - oracle - behaviour changes in direct mode
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)
Re: unidac - oracle - behaviour changes in direct mode
For the ones that would be interested, the only thing to do to make it work in nearly all cases is:
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!!!!
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();
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!!!!
Re: unidac - oracle - behaviour changes in direct mode
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
Re: unidac - oracle - behaviour changes in direct mode
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();
Can you tell me how to make this second insert to work?
Re: unidac - oracle - behaviour changes in direct mode
Please specify the error you get when executing the second query, which inserts data
Re: unidac - oracle - behaviour changes in direct mode
error is "ORA-01461: can bind a LONG value only for insert into a LONG column."
PS: both queries insert data, the same way.
PS: both queries insert data, the same way.
Re: unidac - oracle - behaviour changes in direct mode
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 :
and the code below :
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
- 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))
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();
...
Re: unidac - oracle - behaviour changes in direct mode
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.
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.
Re: unidac - oracle - behaviour changes in direct mode
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
Re: unidac - oracle - behaviour changes in direct mode
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.
(10.2 uodate 1)
Windows 10 Build 16299 64 bits
Region is Belgium, utc+1, languages french and english.
Re: unidac - oracle - behaviour changes in direct mode
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
Re: unidac - oracle - behaviour changes in direct mode
I am really glad to read this!!!!! Thanks.
Re: unidac - oracle - behaviour changes in direct mode
Hi,
any news? The new unidac release doesn't fix this issue.
Regards.
any news? The new unidac release doesn't fix this issue.
Regards.
Re: unidac - oracle - behaviour changes in direct mode
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