* * *

Author Topic: ODBC MSSQL  (Read 209 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: 963
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: 7087
Re: ODBC MSSQL
« Reply #3 on: November 07, 2018, 11:31:28 am »
The c# code is utf16. Use unicodestring.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

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

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus