UniDac/Oracle Request failed

[M.Schmidt-E.]

Hello,
i use D2009 with UniDac 2.00.0.2 and Oracle.Connection Property[Oracle] useUnicode is enabled.
The select Select * from where code='xxxÉxxx'
returns 0 rows but data allready exists.

What is wrong?
Any idea?
best Mathias

[bork]

Hello

Please specify data type in the Oracle database for the "code" field.

[M.Schmidt-E.]

The Datatype of code is
varchar2(10)

Oracle Charset is al32utf8

:best

[bork]

Please specify which mode do you use: OCI or Direct. In the OCI mode all works correctly. In the Direct mode I found a bug in the UniDAC version 2.0.0.2. But the current version is 3.00.0.8 and this bug was fixed a long time ago.

[M.Schmidt-E.]

i use the OCI mode.
Best mathias

[bork]

I created a new table with varchar2(10) field, inserted 'xxxÉxxx' to this field and executed a query: select * from my_table where code='xxxÉxxx'. The query returned record correctly.

Can you provide us a sample application that will perform the following steps:
1. Create a new table with varchar2(10) field
2. Insert 'xxxÉxxx' value to this table
3. Execute a query that will select a record with this value

Also try to download and install the latest version of UniDAC components. Maybe this bug was fixed already. We don't release any fixes for previous versions. If we are able to reproduce your issue, the fix will be included into next UniDAC version.

[M.Schmidt-E.]

The following code illustrate my problem.
is a copy of original project

Code: Select all

var query: TUniQuery;
begin
    query:= TUniQuery.Create(nil);
    query.Connection:=;
    query.sql.text:= format(
           'Select * from  where  code=%s',
           ['xxxxÉxxxxx]);
       end;
       try
         Query.Open;
         if Query.RecordCount>=1 then begin  where code='xxxÉxxxx' with no errors. 

The same results, if i use parameters.

Data in table allready exists :-)

mathias

[bork]

In your first post you wrote the value: 'xxxÉxxx'
In you last post you wrote two values: 'xxxxÉxxxxx' (in the code) and 'xxxÉxxxx' (below).
I cannot understand which value do you want to select: 'xxxÉxxx', or 'xxxxÉxxxxx', or 'xxxÉxxxx'. Also I don't know what value is stored in your database. Maybe the value in your database differs from the previous three values.

Please execute the following code and provide us three results that will be shown by ShowMessage:

Code: Select all

var
  query: TUniQuery;
  str_value: string;
begin
  str_value := 'xxxxÉxxxxx';

  query:= TUniQuery.Create(nil);
  try
    query.Connection:=UniConnection1;

    query.SQL.Text := 'create table my_test_table(id number, name varchar2(10))';
    query.Execute;

    try
      query.SQL.Text := 'insert into my_test_table(id, name) values (1,  ''' + str_value + ''')';
      query.Execute;

      query.SQL.Text:= 'Select * from my_test_table';
      query.Open;
      ShowMessage(query.FieldByName('name').AsString);

      query.Close;
      query.SQL.Text:= 'Select * from my_test_table where name=''' + str_value + '''';
      query.Open;
      ShowMessage(IntToStr(query.RecordCount));

      query.Close;
      query.SQL.Text:= 'Select * from my_test_table where name=:name';
      query.ParamByName('name').AsString := str_value;
      query.Open;
      ShowMessage(IntToStr(query.RecordCount));

    finally
      query.SQL.Text := 'drop table my_test_table';
      query.Execute;
    end;

  finally
    query.Destroy;
  end;
end;

[M.Schmidt-E.]

Hello again,
the 'xxxÉxxx' is a sample. What i mean, is the following letter " É " (#00c9) is a problem.
I try your sample.

[M.Schmidt-E.]

Hello again,
the three results are:
1. Messagebox with 'xxxx□xxxx'
2. 1
3. 1

many thanks
Mathias

[bork]

Hello

The VARCHAR2 data type doesn't allow to save text to database in the Unicode encoding. When you set the UseUnicode property to True it means that Unicode is used for sending text in the Unicode encoding from your application to Oracle client. But Oracle client converts text from Unicode encoding to sever charset and sends the converted text to the server. Some characters can be lost if server charset doesn't support them. If you want to store text in the Unicode encoding to the Oracle database then you should use the NVARCHAR2 data type.