cannot insert accentuated characters in clob in direct mode

[albourgz]

C++ builder XE10, unidac 7.0.2, server oracle 12.1.0.2 (SE2), direct mode.

Code: Select all

SQL> select parameter, value from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 AL32UTF8
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE
SQL> desc cec.messagesservice
 Name                     Null?    Type
 ------------------------ -------- -------------------
 DTMESS                   NOT NULL TIMESTAMP(6)
 PRIORITY                 NOT NULL NUMBER(2)
 MESSAGE                  NOT NULL CLOB
 ID                       NOT NULL NUMBER(20)
 STATUS                            VARCHAR2(20 CHAR)

I have an app , a direct connection to oracle, a TUniSQL QInsOraClob with specificOption TemporaryLobUpdate set to false.
TUniSQL sql is

Code: Select all

INSERT INTO MessagesService(ID,priority, message) 
VALUES (:1,:2,:3)

(dtmess has a default value)
and code is:

Code: Select all

AnsiString sMess="é";
QInsClob->Prepare();
QInsClob->Params->Items[0]->AsInteger=1;
QInsClob->Params->Items[1]->AsInteger=1;
QInsClob->Params->Items[2]->AsMemo=sMess;
QInsClob->Execute();

I get Exception:
Project cec.exe raised exception class EOraError with message 'ORA-01461: can bind a LONG value only for insert into a LONG column'.

[*]There is no LONG value
[*]Setting "e" instead of "é" works
[*]DB charset is AL32UTF8=> "é" should work (it works from sqlplus).

Accentuated chars don't work, the ° also doesn't work.

Worse: On another database, 12.1.0.2EE RAC, characterset is WE8ISO8859P1, and there it works!

Any hint?

[MaximG]

We received your sample and started its investigation. Please specify the charset used in Oracle Client, using which you run your test application. What Regional and Language Windows settings are used in this case?

[albourgz]

I don't specify anything at oracle client level.
NLS_LANG is not set.
On windows (10 pro), Time zone is UTC+1, DST active, windows is english, keyboard is belgian french, language for non-unicode programs is english, date format is French (Belgium),

[JeanR]

Hi, same issue here, working fine in normal/tns mode but fail under direct mode, may you PLEASE answer to this kind of support question publicly, not in PM to allow other customers to benefit from the answer ?

We suffer this kind of issues since the version 4, really hard to not update to the latest (as it fix generaly a lot of major bugs) but also really hard to do the update as new or old bugs are always coming back !

[MaximG]

We could not reproduce the ORA-01461 error using the latest version of UniDAC 7.1.4 . Please test the above behavior using this exact UniDAC version. If the issue is reproduced, compose and send us a small sample, execution of which causes the ORA-01461 error. It is convenient to do it via the e-support form ( https://www.devart.com the "Support"\"Request Support" menu)

[JeanR]

Hi,

Using the latest 7.1.4 version the issue persist.

Error: ORA-01483 invalid length for DATE or NUMBER bind variable

• - Only under direct mode
  - have it among various oracle versions (10,11,12) and national settings.
  - Using a NCLOB instead of a CLOB doesn't help
  - Using transaction or not doesn't matter
  - Can fail or succeed with 2 chars as well as 12000 chars (so, not default 4000 varchar limit chars related).

Code: Select all

PARAMETER                                                        VALUE                                                           
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     FRENCH                                                          
NLS_TERRITORY                                                    FRANCE                                                          
NLS_CURRENCY                                                     €                                                               
NLS_ISO_CURRENCY                                                 FRANCE                                                          
NLS_NUMERIC_CHARACTERS                                           ,                                                               
NLS_CALENDAR                                                     GREGORIAN                                                       
NLS_DATE_FORMAT                                                  DD/MM/RR                                                        
NLS_DATE_LANGUAGE                                                FRENCH                                                          
NLS_CHARACTERSET                                                 AL32UTF8                                                        
NLS_SORT                                                         FRENCH                                                          
NLS_TIME_FORMAT                                                  HH24:MI:SSXFF                                                   
NLS_TIMESTAMP_FORMAT                                             DD/MM/RR HH24:MI:SSXFF                                          
NLS_TIME_TZ_FORMAT                                               HH24:MI:SSXFF TZR                                               
NLS_TIMESTAMP_TZ_FORMAT                                          DD/MM/RR HH24:MI:SSXFF TZR                                      
NLS_DUAL_CURRENCY                                                €                                                               
NLS_NCHAR_CHARACTERSET                                           UTF8                                                            
NLS_COMP                                                         BINARY                                                          
NLS_LENGTH_SEMANTICS                                             BYTE                                                            
NLS_NCHAR_CONV_EXCP                                              FALSE                                                           

Table:

Code: Select all

CREATE TABLE "MySchema"."TEST_TABLE" 
   (	"ID" NUMBER, 
	"MyClobField " CLOB, 	
	"MyNClobField " NCLOB
   )

Database connection:

Code: Select all

UniConnection := TUniConnection.Create(nil);
UniConnection.ProviderName := 'Oracle';
UniConnection.Server := '192.168.0.x:1521:**********';
UniConnection.UserName := '***************';
UniConnection.Password := '****************';
UniConnection.SpecificOptions.Values['Schema'] := '**************';
UniConnection.SpecificOptions.Values['Direct'] := 'True';
UniConnection.SpecificOptions.Values['UseUnicode'] := 'True';
UniConnection.SpecificOptions.Values['Charset'] := 'AL32UTF8';
UniConnection.AutoCommit := False;
UniConnection.Connect();

Query:

Code: Select all

with TUniQuery.Create(nil) do
    try
      Connection := UniConnection;
      Transaction.StartTransaction;

      SQL.Text := 'UPDATE TEST_TABLE SET MyClobField = :data Where 1 = :testInt';

      ParamByName('data').DataType := ftWideMemo;
      ParamByName('data').ParamType := ptInput;
      ParamByName('data').AsWideString := data;

      ParamByName('testInt').DataType := ftInteger;
      ParamByName('testInt').AsInteger := 1;

      ExecSQL();
    finally
      Transaction.Commit;
      Free;
    end;

Please take note of an important point:
If the clob update is the only param of the query, in general it succeed (in Russian below it succeed, not in japanese). If something else is present (params order doesn't matter), the query fail.

Work better if single param (make me crazy) :

Code: Select all

with TUniQuery.Create(nil) do
    try
      Connection := UniConnection;
      Transaction.StartTransaction;
      SQL.Text := 'UPDATE TEST_TABLE SET MyClobField = :data';

      ParamByName('data').DataType := ftWideMemo;
      ParamByName('data').ParamType := ptInput;
      ParamByName('data').AsWideString := data;

      ExecSQL();
    finally
      Transaction.Commit;
      Free;
    end;

Sample utf8 content leading to a failure (be careful, the exact same chars repeated a different time change the result, can work if you put more or less of it...) :

Code: Select all

Уважаемые пользователи портала Russia.ru!
С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.
Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!
С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.
Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!
С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.
Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!
С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.
Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!
С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.
Все данные пользователей будут сохранены.

same if Chinese or Japanese

Code: Select all

分かるように

Notice that i tried a lot of other methods, all of them leading to the same or different issues, for example like that it doens't fail but all the unicode content is lost, replaced with ? or other dead chars. :

Code: Select all

UniQuery.ParamByName(name).AsMemo := Value;

Loose unicode too:

Code: Select all

 
UniQuery.ParamByName(name).DataType := ftOraClob;
UniQuery.ParamByName(name).ParamType := ptInput;
UniQuery.ParamByName(name).AsBlobRef.AsWideString := Value;

same:

Code: Select all

ss := TStringStream.Create(Value);
try
   ss.Position := 0;
   UniQuery.ParamByName(name).ParamType := ptInput;
  UniQuery.ParamByName(name).LoadFromStream(ss, ftMemo);
finally
  ss.Free;
end;

I must insist on the fact that we have this kind of issues (specially using the direct mode) since years, a "not reproduced" answer wouldn't be appropriate as we (and other folks based on the forum content) suffer from difficulties to figure out how to use this component properly.

[MaximG]

To work with fields of the ORACLE CLOB type, use the parameter with the type ftOraCLOB :

Code: Select all

UniQuery.ParamByName('data').DataType := ftOraCLOB;

[JeanR]

Thanks for your reply, indeed this was my first implementation then i tried a lot of alternatives.
May you please try by yourself and see the result ?

Code: Select all

      ParamByName('data').DataType := ftOraCLOB;
      ParamByName('data').ParamType := ptInput;
      ParamByName('data').AsWideString := data;

= unicode replaced by ? or artifacts

i also tried without more luck like that :

Code: Select all

 ParamByName('data').AsBlobRef.AsWideString := Value;

& using loadfromstream, and specifying the param length .Size & asString, asMemo...

I reproduce with the latest version out of the box on a clean environment against any oracle database, was working with the exact same code on 6.1 100% sure (but the 6x other bugs was killing us).

Edit: Sample app here: http://bit.ly/2zb1BgW (.zip, no binaries, delphi xe & +)

[MaximG]

Thank you for the provided sample. We investigated it, commented one source code line :

Code: Select all

SpecificOptions.Values['Direct'] := 'True';
SpecificOptions.Values['UseUnicode'] := 'True';
// SpecificOptions.Values['Charset'] := 'AL32UTF8';
AutoCommit := False;

and received a correct result. Try performing the same actions in your test environment.

[JeanR]

Thanks, will try now and tell you how it goes

[JeanR]

The issue persist on my side, even by removing the Charset specification.

Is this param assign method the correct one ?

Code: Select all

      ParamByName('data').DataType := ftOraCLOB;
      ParamByName('data').ParamType := ptInput;
      ParamByName('data').AsWideString := data;

[MaximG]

The use of the 'data' parameter you provided is correct. We made a small example using the "TEST_TABLE" table that you described, which saves the correct value in the "MyClobField" field:

Code: Select all

var
  Connection: TUniConnection;
  Query: TUniQuery;
begin
  Connection := TUniConnection.Create(nil);
  Connection.ProviderName := 'Oracle';
  Connection.SpecificOptions.Values['Direct'] := 'True';
  Connection.Server := '*******:1521:******';
  Connection.UserName := '******';
  Connection.Password := '******';
  Connection.SpecificOptions.Values['UseUnicode'] := 'True';
  Connection.Connect;
  try
    Query := TUniQuery.Create(nil);
    try
      Query.Connection := Connection;
      Query.SQL.Text := 'UPDATE TEST_TABLE SET MyClobField = :data Where ID = :ID';
      Query.ParamByName('data').DataType := ftOraCLOB;
      Query.ParamByName('data').ParamType := ptInput;
      Query.ParamByName('data').AsWideString :=
        ' +1. Уважаемые пользователи портала Russia.ru!' +
        ' 2. С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.' +
        ' 3. Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!' +
        ' 4. С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.' +
        ' 5. Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!' +
        ' 6. С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.' +
        ' 7. Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!' +
        ' 8. С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.' +
        ' 9. Все данные пользователей будут сохранены.Уважаемые пользователи портала Russia.ru!' +
        '10. С 23 марта блоги Russia.ru переносятся на домен RussiaRu.net.' +
        '11. Все данные пользователей будут сохранены.';

      Query.ParamByName('ID').DataType := ftInteger;
      Query.ParamByName('ID').ParamType := ptInput;
      Query.ParamByName('ID').AsInteger := 1;
      Query.ExecSQL;
    finally
      Query.Free;
    end;
  finally
    Connection.Free;
  end;
end;

Implement this example in your environment and let us know about the results.

[JeanR]

Hello,

I tried again but i confirm, this is not working, at least not with the v7 and 7.1.4 trial.
Output is always equal to:

Code: Select all

 +1. ????????? ???????????? ??????? Russia.ru! 2. ? 23 ????? ????? Russia.ru ??????????? ?? ????? RussiaRu.net. 3. ??? ?????? ????????????? ????? ?????????.????????? ???????????? ??????? Russia.ru! 4. ? 23 ????? ????? Russia.ru ??????????? ?? ????? RussiaRu.net. 5. ??? ?????? ????????????? ????? ?????????.????????? ???????????? ??????? Russia.ru! 6. ? 23 ????? ????? Russia.ru ??????????? ?? ????? RussiaRu.net. 7. ??? ?????? ????????????? ????? ?????????.????????? ???????????? ??????? Russia.ru! 8. ? 23 ????? ????? Russia.ru ??????????? ?? ????? RussiaRu.net. 9. ??? ?????? ????????????? ????? ?????????.????????? ???????????? ??????? Russia.ru!10. ? 23 ????? ????? Russia.ru ??????????? ?? ????? RussiaRu.net.11. ??? ?????? ????????????? ????? ?????????.

Are you sure that it can't be a limitation in the trial version ? [edit: i just renewed a pro+source license to figure out]

[JeanR]

Hello,

Same with the licensed version, still not working.
I paid a particular attention to clear every single .bpl/dcp/dcu from my system to be sure to avoid an issue due to old files.

Using DBMonitor i notice that the dataType is HugeClob , i didn't noticed it with the v6 version (but i could be wrong, unsure), is it the expected dataType here ?

[MaximG]

The behavior of dbMonitor you described is absolutely correct. We have tested the example you sent in our test environment and are sure it is working. You can check it using this screenshot:

For further investigation, please specify the IDE version you use. In addition, make sure that you do not use the Charset (UniConnection.SpecificOptions.Values['Charset']) option in your code. As discussed earlier, disabling this option allowed us to execute your example without errors. Also, clarify how you tested the correctness of filling the MyClobField field in the example we sent.