Problem with TUniLoader

PixAndMore · Post by **PixAndMore** » Thu 09 Sep 2010 06:47

Hello,
for multiple inserts I have used TUniLoader. The performance is very good and it works for me on Firebird, MySQL, Oracle (DirectPath = False) and PostgreSQL....

But when I use the same routines for MS SQL 2005 Server, it works, but all characters in the Table have 2 Bytes! I can't see what for bytes, I see only a square, but I think its a null byte because when I load the record the length is limited on the first character...

Using TUniSQL, TUniQuery and TUniScript is ok and works, but not TUniLoader

Any Ideas?

Dimon · Post by **Dimon** » Thu 09 Sep 2010 12:26

UniDAC maps the VARCHAR fields of SQL Server to the ftString field type and NVARCHAR to ftWideString. If you use NVARCHAR fields you should set value on loading data as WideString.

Code: Select all

  if Column.FieldType = ftWideString then
    Value := WideString('ABCD');

PixAndMore · Post by **PixAndMore** » Thu 09 Sep 2010 12:36

Hello,

Dimon wrote:UniDAC maps the VARCHAR fields of SQL Server to the ftString field type and NVARCHAR to ftWideString. If you use NVARCHAR fields you should set value on loading data as WideString.
Code: Select all
  if Column.FieldType = ftWideString then
    Value := WideString('ABCD');

no I'm using normal VARCHAR Fields. Like said it works with all other components:

Code: Select all

   uniquery.sql.text = 'insert into table(str) values(:str)';
   uniquery.parambyname('str').Value := 'Hallo';
   uniquery.execSQL;

   unisql.sql.text = 'insert into table(str) values(:str)';
   unisql.parambyname('str').Value := 'Hallo';
   unisql.execute;

   uniscript.sql.text = 'insert into table(str) values(:str);';
   uniscript.statements[0].parambyname('str').Value := 'Hallo';
   uniscript.exec;

That all works for all database, including MS SQL 2005.

Only TUniLoader on MS SQL makes problems! It looks like an UTF-16 problem? I don't use unicode...

PixAndMore · Post by **PixAndMore** » Thu 09 Sep 2010 12:51

An additional info:

AndreyZ · Post by **AndreyZ** » Fri 10 Sep 2010 13:09

I could not reproduce the problem. Please try to compose a small sample to demonstrate the problem and send it to andreyz*devart*com.

AndreyZ · Post by **AndreyZ** » Thu 16 Sep 2010 07:43

Was the problem solved? If not, please, contact me.

PixAndMore · Post by **PixAndMore** » Thu 16 Sep 2010 07:50

No it was not, I had send an email with an example to you...
(13.09.2010 / 12:00h)

I had also get a reading confirmation from you!

AndreyZ · Post by **AndreyZ** » Thu 16 Sep 2010 09:13

This problem appears because RAD Studio takes strings as the WideString type since 2009. To solve the problem you have to cast strings to AnsiString explicitly. Here is an example:

Code: Select all

UniLoader.PutColumnData('updated', 1, AnsiString('00000000000000'));

PixAndMore · Post by **PixAndMore** » Thu 16 Sep 2010 11:19

Yes, this works for me, thank you...

But I think it is only a workaround, because:

I have a method "GetAsParamValue: Variant", this methods works for

TUniQuery.Params[0].Value := GetAsParamValue
TUniSQL.Params[0].Value := GetAsParamValue
TUniScript.Statements[0].Params[0].Value := GetAsParamValue
TUniLoader.PutColumnData('f', 1, GetAsParamValue)

for Firebird, MySQL, MS SQL, Oracle and PostgreSQL. Only TUniLoader does not work for MS-SQL!

And: If want to use unicode in future, i have to fix again!?

AndreyZ · Post by **AndreyZ** » Thu 16 Sep 2010 13:09

You can solve this problem in two ways:
- change field type in database to nvarchar instead varchar (nvarchar type support work with Unicode);
- do column type check when setting the value of the field like that:

Code: Select all

   if UniLoader.Columns[1].FieldType = ftString then
     UniLoader.PutColumnData(1, 1, AnsiString(GetAsParamValue))
   else
     UniLoader.PutColumnData(1, 1, GetAsParamValue);