Recent

Author Topic: Unicode parametric query  (Read 4912 times)

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Unicode parametric query
« on: August 25, 2016, 08:25:08 pm »
I don't know whether this question is for Lazarus or UniDAC forum. I have some Serbian Latin strings in my SQL Server. My query is simple one and is quoted in UniQuery SQL property at design time as: select * from mytable where name='Šeki'. I correctly receive the only record which satisfied condition. However when I change the query to: select * from mytable where name=:par0 and in code: query.parambyname(par0).asstring:= 'Šeki', I receive no record. Field "name" is "nvarchar" in database, collation Croatian, client OS- Windows10 (64- bit), database machine is Windows Server 2012 (64- bit) and Lazarus version is 1.6.

zeljko

  • Hero Member
  • *****
  • Posts: 1165
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Unicode parametric query
« Reply #1 on: August 25, 2016, 08:32:01 pm »
You written everything except fpc version and database type, anyway, I'm using postgresql + ZEOS + Lazarus trunk (QtLCL) + fpc 3.0.0 (2.6.4 also worked fine) and never had such problems (linux, mac, windows). eg
RQRY = zeos readonly query
RQRY.SQL.Add('SELECT * FROM names WHERE name=:P1');
RQRY.Params[0].AsString := 'Željko';
RQRY.Active := True;
.... works fine on any mentioned OS ...returns record

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #2 on: August 25, 2016, 09:28:34 pm »
FPC is 3.0 and database is SQL Server 2012 Express. No matter I will try my luck with Zeos components, but still sounds strange to me that UniDAC (I understood that you pointed the finger to it) behaves differently in two mentioned situations. 

paweld

  • Sr. Member
  • ****
  • Posts: 271
Re: Unicode parametric query
« Reply #3 on: August 25, 2016, 09:37:32 pm »
Code: Pascal  [Select][+][-]
  1. Query.SQL.Text:='select * from mytable where name=:par0';
  2. //if "name" field type is varchar
  3. Query.Params[0].AsString:=UTF8ToCP1250('Šeki'); //add LConvEncoding unit to uses section
  4. //or if "name" field type is nvarchar
  5. Query.Params[0].AsString:=UTF8ToUTF16('Šeki'); //add LazUTF8 unit to uses section
Best regards
paweld

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #4 on: August 25, 2016, 09:55:31 pm »
I've already tried that with no success. It sounds reasonable, because SQL Server's encoding format is UTF-16 (as much as I know) and Lazarus's one is UTF-8, but the problem is obviously somewhere else.

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #5 on: August 25, 2016, 09:59:44 pm »
My excuse to paweld - I didn't noticed line number 3, which I didn't try . It should be first thing tomorrow.


djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #6 on: August 26, 2016, 09:35:20 am »
The field is of "nvarchar" type in database and attempt with Utf8ToUtf16 doesn't work- returns no record. When building SQL query in source :
SQL.Add('select * from myTable where name=');
SQL.Add(QuotedStr('Šeki'));
it does work- returns one record.

zeljko

  • Hero Member
  • *****
  • Posts: 1165
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Unicode parametric query
« Reply #7 on: August 26, 2016, 12:57:44 pm »
Have you tried to add {$codepage utf8} into that unit ? Maybe 'Šeki' is interpreted as cp1250 ...

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #8 on: August 26, 2016, 07:26:30 pm »
Logical assumption, still it gives no solution. So I decided to build query in code- without parameters.

paweld

  • Sr. Member
  • ****
  • Posts: 271
Re: Unicode parametric query
« Reply #9 on: August 26, 2016, 08:16:23 pm »
try to use macros:
Code: Pascal  [Select][+][-]
  1. Query.SQL.Text:='select * from mytable where name=&mac0';
  2. Query.Macros[0].AsString:='Šeki';
Best regards
paweld

djdjdjole

  • Full Member
  • ***
  • Posts: 101
Re: Unicode parametric query
« Reply #10 on: August 27, 2016, 12:44:55 pm »
Simply no way- tried and returned no record. You guys are so kind and persistent - I have no words to express my gratitude. At the moment I found the way to override the problem (I already wrote about), but it still remains mystery to me.

 

TinyPortal © 2005-2018