Page 1 of 2
unidac - oracle - behaviour changes in direct mode
Posted: Tue 10 Apr 2018 09:46
by 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.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Thu 12 Apr 2018 09:29
by 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)
Re: unidac - oracle - behaviour changes in direct mode
Posted: Wed 25 Apr 2018 14:47
by 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!!!!
Re: unidac - oracle - behaviour changes in direct mode
Posted: Fri 27 Apr 2018 13:58
by 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
Re: unidac - oracle - behaviour changes in direct mode
Posted: Fri 27 Apr 2018 14:44
by 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?
Re: unidac - oracle - behaviour changes in direct mode
Posted: Sat 05 May 2018 06:16
by MaximG
Please specify the error you get when executing the second query, which inserts data
Re: unidac - oracle - behaviour changes in direct mode
Posted: Tue 08 May 2018 09:13
by 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.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Thu 17 May 2018 08:22
by 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
Re: unidac - oracle - behaviour changes in direct mode
Posted: Fri 18 May 2018 08:51
by 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.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Thu 24 May 2018 07:42
by 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
Re: unidac - oracle - behaviour changes in direct mode
Posted: Thu 24 May 2018 09:36
by 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.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Tue 05 Jun 2018 14:30
by 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
Re: unidac - oracle - behaviour changes in direct mode
Posted: Fri 08 Jun 2018 09:05
by albourgz
I am really glad to read this!!!!! Thanks.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Wed 11 Jul 2018 09:04
by albourgz
Hi,
any news? The new unidac release doesn't fix this issue.
Regards.
Re: unidac - oracle - behaviour changes in direct mode
Posted: Thu 12 Jul 2018 14:17
by 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