ODBC MSSQL
lazarus / 16-Databases

----------------------------------------------------------------------
luca · 2018-11-07 07:50

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
ODBCConnection1.Open;  ODBCConnection1.ExecuteDirect('Update TABLE set
FIELD1= ' + QuotedStr(Edit1.text) + ' Where ID =2'); 
ODBCConnection1.Close; 
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  project1 
     b254-a528       ENTER SQLPrepare                 HSTMT          
   0x094F1ED0                UCHAR *            0x0256C43C [      62]
"Update TABLE set FIELD1='\ff\ff\ff\ff\ff\ff' Where ID =2"           
   SDWORD                    62 
While in C#

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
WindowsFormsApp ad38-afd8       ENTER SQLExecDirectW                
HSTMT               0x064DED20                WCHAR *            
0x02D8684C [      -3] "Update TABLE set FIELD1='\ff\ff\ff' where ID=
2\ 0"                SDWORD                    -3 
How can I fix the problem?
Regards
Luca

----------------------------------------------------------------------
ASerge · 2018-11-07 09:07

Quote from: luca on November 07, 2018, 08:50:11 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
ODBCConnection1.Open;  ODBCConnection1.ExecuteDirect('Update TABLE set
FIELD1= ' + QuotedStr(Edit1.text) + ' Where ID =2'); 
ODBCConnection1.Close;  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 · 2018-11-07 09:49

Agree with you.
But my question is why this statement is not working?

Regards

----------------------------------------------------------------------
Thaddy · 2018-11-07 10:31

The c# code is utf16. Use unicodestring.

----------------------------------------------------------------------
luca · 2018-11-07 10:45

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