Recent

Author Topic: ODBC MSSQL  (Read 811 times)

luca

  • Jr. Member
  • **
  • Posts: 51
ODBC MSSQL
« on: November 07, 2018, 08:50:11 am »
I've a big application using MSSQL as DBMS.
One table contains a field of type Varchar(30)

I use this code to update the field:

Code: Pascal  [Select]
  1.  
  2.   ODBCConnection1.Open;
  3.   ODBCConnection1.ExecuteDirect('Update TABLE set FIELD1= ' + QuotedStr(Edit1.text) + ' Where ID =2');
  4.   ODBCConnection1.Close;
  5.  

All is fine if the Text doesn't contain accented chars (generally chars having ASCII code > 128).
When I try to insert accented chars what is inserted is not correct.

I made same test using C# and everything works.

The ODBC tracert when Lazarus project1 executes is like this

Code: Pascal  [Select]
  1.  
  2. project1        b254-a528       ENTER SQLPrepare
  3.                 HSTMT               0x094F1ED0
  4.                 UCHAR *            0x0256C43C [      62] "Update TABLE set FIELD1='\ff\ff\ff\ff\ff\ff' Where ID =2"
  5.                 SDWORD                    62
  6.  

While in C#

Code: Pascal  [Select]
  1. WindowsFormsApp ad38-afd8       ENTER SQLExecDirectW
  2.                 HSTMT               0x064DED20
  3.                 WCHAR *             0x02D8684C [      -3] "Update TABLE set FIELD1='\ff\ff\ff' where ID= 2\ 0"
  4.                 SDWORD                    -3
  5.  

How can I fix the problem?
Regards
Luca

ASerge

  • Hero Member
  • *****
  • Posts: 1029
Re: ODBC MSSQL
« Reply #1 on: November 07, 2018, 10:07:16 am »
Code: Pascal  [Select]
  1.  
  2.   ODBCConnection1.Open;
  3.   ODBCConnection1.ExecuteDirect('Update TABLE set FIELD1= ' + QuotedStr(Edit1.text) + ' Where ID =2');
  4.   ODBCConnection1.Close;
  5.  
Never build an SQL string from variable values. Use a parameters: it's faster, safer and avoids errors like in your case. 'update TableName set FieldName=:Param1 where ID=:Param2'.

luca

  • Jr. Member
  • **
  • Posts: 51
Re: ODBC MSSQL
« Reply #2 on: November 07, 2018, 10:49:41 am »
Agree with you.
But my question is why this statement is not working?

Regards

Thaddy

  • Hero Member
  • *****
  • Posts: 7337
Re: ODBC MSSQL
« Reply #3 on: November 07, 2018, 11:31:28 am »
The c# code is utf16. Use unicodestring.
Brexit. My Indonesian and Dutch friends know what " Tempo doeloe" means....There is no empire.

luca

  • Jr. Member
  • **
  • Posts: 51
Re: ODBC MSSQL
« Reply #4 on: November 07, 2018, 11:45:42 am »
I think the problem could be inside odbcconn.pas:
the procedure TODBCConnection.PrepareStatement uses SQLPrepare instead of SQLPrepareW.

First one pass the command as PSQLCHAR
Second one pass the command as PSQLWCHAR

Regards