Issue #2250: use N(ational) prefix in MS SQL for any text value going through the second version of EscapeString(), supporting Unicode now when importing a file into a table.

ansgarbecker · ansgarbecker · commit c01a439ce5f3 · 2025-10-07T20:30:10.000+02:00
Issue #41: use N(ational) prefix on MS SQL strings on all quoted strings, regardless of their exact data type, to support Unicode in meta queries as well
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -5141,6 +5141,11 @@ function TDBConnection.EscapeString(Text: String; ProcessJokerChars: Boolean=fal
   if DoQuote then begin
     // Add surrounding single quotes
     Result := FStringQuoteChar + Result + FStringQuoteChar;
+
+    // Support international characters with National prefix on MSSQL, see #1115, #2250, #41.
+    // Previously only done in some callers of EscapeString(), and only for column types dbdtNchar, dbdtNvarchar, dbdtNtext.
+    if FParameters.IsAnyMSSQL and (ServerVersionInt >= 1100) then
+      Result := 'N' + Result;
   end;
 end;
 
@@ -9773,14 +9778,10 @@ function TDBQuery.SaveModifications: Boolean;
         end;
         dtcBinary, dtcSpatial:
           Val := FConnection.EscapeBin(Cell.NewText);
-        else begin
-          if Datatype(i).Index in [dbdtNchar, dbdtNvarchar, dbdtNtext] then
-            Val := 'N' + Connection.EscapeString(Cell.NewText)
-          else if Datatype(i).Category = dtcTemporal then
-            Val := Connection.EscapeString(Connection.GetDateTimeValue(Cell.NewText, Datatype(i).Index))
-          else
-            Val := Connection.EscapeString(Cell.NewText);
-        end;
+        dtcTemporal:
+          Val := Connection.EscapeString(Connection.GetDateTimeValue(Cell.NewText, Datatype(i).Index))
+        else
+          Val := Connection.EscapeString(Cell.NewText, Datatype(i));
       end;
       sqlUpdate := sqlUpdate + Connection.QuoteIdent(FColumnOrgNames[i]) + '=' + Val;
       sqlInsertColumns := sqlInsertColumns + Connection.QuoteIdent(FColumnOrgNames[i]);
@@ -10166,13 +10167,7 @@ function TDBQuery.GetWhereClause: String;
           Result := Result + '=' + FConnection.EscapeBin(ColVal);
         else begin
           // Any other data type goes here, including text:
-          case DataType(j).Index of
-            // Support international characters with N-prefix on MSSQL, see #1115:
-            dbdtNchar, dbdtNvarchar, dbdtNtext:
-              Result := Result + '=N' + Connection.EscapeString(ColVal);
-            else
-              Result := Result + '=' + Connection.EscapeString(ColVal);
-          end;
+          Result := Result + '=' + Connection.EscapeString(ColVal, DataType(j));
         end;
       end;
     end;
