About using Parambyname in Toraquery.....

jang · Post by **jang** » Fri 28 Aug 2009 01:06

Hello,
I am considering upgrade Delphi( from Delphi6.0 to Delphi2009)
so I am testing ODAC 6.80.0.48 Trial version
I got some problem with Using Toraquery's Parambyname in direct mode

For example
select statments like this
select * from tab_aaa where field1 =:field1;

delphi6.0 using
parambyname('field1').asstring := 'aaa';

it's correct result ( seleted 2 rows)

but delphi2009 + ODAC 6.80.0.48 Trial version returns nothing( selected 0 row)

I got to know something

follows retruns correct result

delphi2009 + ODAC 6.80.0.48 Trial version using

parambyname('field1').datatype := ftFixedChar;
parambyname('field1').size := 32;
parambyname('field1').asstring := 'aaa'

or

parambyname('field1').asansistring := 'aaa'

why datatype,size options described by user only upgrade ODAC?
Is it Trial Version's limits?

I think it's not a good thing
I can't change huge sourcecode
helps, What can I do same result ?

Plash · Post by **Plash** » Fri 28 Aug 2009 08:48

We cannot reproduce the problem. Please provide us the following information:

- character set of your database;
- data type of the column in the database;
- your Windows regional settings.

jang · Post by **jang** » Sat 29 Aug 2009 00:09

Thanks for reply

Well, there is no changing developer environment except upgrade ODAC
requested information like this
- character set of your database;
=> NLS_RDBMS_VERSION 8.1.7.4.0
NLS_LANGUAGE AMERICAN
NLS_CHARACTERSET KO16KSC5601
NLS_NCHAR_CHARACTERSET KO16KSC5601
- data type of the column in the database
=> data type is varchar2 (differ size from each column)
- your Windows regional settings
=> south Korea

and additional question
when update statement excute using TOraQuery
following error occur
ora-22295:cannot bind more than 4000 bytes data to LOB and LONG columns in 1 statement

but update columns data type is varchar2

also this problem occurs only parambyname's Datatype,Size not assigned

Is this problem Character set relate ?
Is there no way to solve the problem from TOraSession or TOraQuery's options ?

Please Help..

Plash · Post by **Plash** » Mon 31 Aug 2009 09:29

We could not reproduce the problem. Please send to support*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

jang · Post by **jang** » Mon 07 Sep 2009 09:13

Is there no way to solve the problem With Torasession or ToraQeury's options Without provide sample code including database object script?

example Torasession's UseUnicode option is True
if this Option changs True select statements retruns correct result

However ..
I wondering
Why parambyname's datatype and size options decribe by user?

Plash · Post by **Plash** » Tue 08 Sep 2009 08:55

In our test the AsString property works without problems. So you don't need to set the Size and DataType properties.

The sample will help us to find the problem. You don't need to use your actual database objects in the sample. You can create a small test table with two fields and use it in the sample.

jang · Post by **jang** » Wed 09 Sep 2009 00:06

Thanks for reply.

sample code like this...

oracle version is 8.1.7.4.0
characterset KO16KSC5601
odac in direct mode. (ODAC 6.80.0.48 Trial version, Delphi2009 )

create table temp
(
userid varchar2(10),
sdate date,
edate date,
rmk varchar2(100)
)

[select query]
with toraquery1 do
begin
close;
SQL.Clear;
SQL.Add('select rmk from temp where userid =:userid');
ParamByName('userid').Asstring := '111';
open; ==> no date found
end;

if changed to
with toraquery1 do
begin
close;
SQL.Clear;
SQL.Add('select rmk from temp where userid =:userid');
----------------------------------------------------------
ParamByName('userid').AsAnsistring := '111';
----------------------------------------------------------
open; ==> 1 row found
end;

others
--------------------------------------------------
OraSession1.Options.UseUnicode := True;
--------------------------------------------------
with toraquery1 do
begin
close;
SQL.Clear;
SQL.Add('select rmk from temp where userid =:userid');
ParamByName('userid').Asstring := '111';
open; ==> 1 row found
end;

[update query]
with toraquery1 do
begin
close;
SQL.Clear;
SQL.Add('update temp set edate = to_date(:edate,''yyyy-mm-dd'') -1, rmk=:rmk');
SQL.Add(' where userid =:userid');

ParamByName('edate').Asstring := '2009-09-09';
ParamByName('rmk').AsString := 'remark';
ParamByName('userid').Asstring := '111';
Execsql; ==> ora-22295 error occur
end;

if changed to
with toraquery1 do
begin
close;
SQL.Clear;
SQL.Add('update temp set edate = to_date(:edate,''yyyy-mm-dd'') -1, rmk=:rmk');
SQL.Add(' where userid =:userid');
--------------------------------------------------
ParamByName('edate').datatype := ftfixedchar;
ParamByName('rmk').datatype := ftfixedchar;
ParamByName('userid').datatype := ftfixedchar;
ParamByName('edate').size := 32;
ParamByName('rmk').size := 32;
ParamByName('userid').size := 32;
--------------------------------------------------
ParamByName('edate').Asstring := '2009-09-09';
ParamByName('rmk').AsString := 'remark';
ParamByName('userid').Asstring := '111';
Execsql; ==> It works fine
end;

Plash · Post by **Plash** » Wed 09 Sep 2009 08:21

We have found a problem related to this code, and we are working on it. You will be notified when we fix the problem.

jang · Post by **jang** » Thu 10 Sep 2009 23:04

Thanks a lot
When it will be fixed or released ?

Plash · Post by **Plash** » Tue 15 Sep 2009 09:19

Please check if the error occurs when you set the Size property but leave the default value for the DataType property.

Also try to use set DataType to ftString (in Delphi 2009 the default DataType is ftWideString when using the AsString property).

jang · Post by **jang** » Wed 16 Sep 2009 01:33

Well..
when size property leave the default , the error occurs.
it's contains my previous question.

and use Datatype to ftString acts well
But only Datatype property is set by user