fix: BIT values in MS SQL grid queries prefixed with MySQL b'' style

ansgarbecker · ansgarbecker · commit 7456c38ae60c · 2026-02-12T18:18:05.000+01:00
Closes #264
diff --git a/source/dbconnection.pas b/source/dbconnection.pas
@@ -5148,11 +5148,13 @@ function TDBConnection.EscapeString(Text: String; ProcessJokerChars: Boolean=fal
 function TDBConnection.EscapeString(Text: String; Datatype: TDBDatatype): String;
 var
   DoQuote: Boolean;
+  ValuePrefix: String;
 const
   CategoriesNeedQuote = [dtcText, dtcBinary, dtcTemporal, dtcSpatial, dtcOther];
 begin
   // Quote text based on the passed datatype
   DoQuote := Datatype.Category in CategoriesNeedQuote;
+  ValuePrefix := '';
   case Datatype.Category of
     // Some special cases
     dtcBinary: begin
@@ -5162,11 +5164,13 @@ function TDBConnection.EscapeString(Text: String; Datatype: TDBDatatype): String
     dtcInteger, dtcReal: begin
       if (not IsNumeric(Text)) and (not IsHex(Text)) then
         DoQuote := True;
-      if Datatype.Index = dbdtBit then
+      if (Datatype.Index = dbdtBit) and FParameters.IsAnyMySQL then begin
         DoQuote := True;
+        ValuePrefix := 'b';
+      end;
     end;
   end;
-  Result := EscapeString(Text, False, DoQuote);
+  Result := ValuePrefix + EscapeString(Text, False, DoQuote);
 end;
 
 
@@ -9154,13 +9158,13 @@ function TSqlSrvQuery.Col(Column: Integer; IgnoreErrors: Boolean=False): String;
       except
         Result := String(FCurrentResults.Fields[Column].AsAnsiString);
       end;
-      if Datatype(Column).Index = dbdtBit then begin
-        if UpperCase(Result) = 'TRUE' then
-          Result := '1'
-        else
-          Result := '0';
-      end
     end;
+    if Datatype(Column).Index = dbdtBit then begin
+      if (UpperCase(Result) = 'TRUE') or (Result = '1') then
+        Result := '1'
+      else
+        Result := '0';
+    end
   end else if not IgnoreErrors then
     Raise EDbError.CreateFmt(_(MsgInvalidColumn), [Column, ColumnCount, RecordCount]);
 end;
@@ -9862,11 +9866,8 @@ function TDBQuery.SaveModifications: Boolean;
       else if Cell.NewIsFunction then
         Val := Cell.NewText
       else case Datatype(i).Category of
-        dtcInteger, dtcReal: begin
+        dtcInteger, dtcReal:
           Val := Connection.EscapeString(Cell.NewText, Datatype(i));
-          if (Datatype(i).Index = dbdtBit) and FConnection.Parameters.IsAnyMySQL then
-            Val := 'b' + Val;
-        end;
         dtcBinary, dtcSpatial:
           Val := FConnection.EscapeBin(Cell.NewText);
         dtcTemporal:
@@ -10251,7 +10252,7 @@ function TDBQuery.GetWhereClause: String;
       case DataType(j).Category of
         dtcInteger, dtcReal: begin
           if DataType(j).Index = dbdtBit then
-            Result := Result + '=b' + Connection.EscapeString(ColVal)
+            Result := Result + '=' + Connection.EscapeString(ColVal, DataType(j))
           else begin
             // Guess (!) the default value silently inserted by the server. This is likely
             // to be incomplete in cases where a UNIQUE key allows NULL here
diff --git a/source/exportgrid.pas b/source/exportgrid.pas
@@ -1045,7 +1045,7 @@ procedure TfrmExportGrid.btnOKClick(Sender: TObject);
               else if GridData.IsNull(ResultCol) then
                 Data := 'NULL'
               else if (GridData.DataType(ResultCol).Index = dbdtBit) and GridData.Connection.Parameters.IsAnyMySQL then
-                Data := 'b' + GridData.Connection.EscapeString(Data)
+                Data := GridData.Connection.EscapeString(Data, GridData.DataType(ResultCol))
               else if (GridData.DataType(ResultCol).Category in [dtcText, dtcTemporal, dtcOther])
                 or ((GridData.DataType(ResultCol).Category in [dtcBinary, dtcSpatial]) and Mainform.actBlobAsText.Checked)
                 then
diff --git a/source/tabletools.pas b/source/tabletools.pas
@@ -2078,12 +2078,6 @@ procedure TfrmTableTools.DoExport(DBObj: TDBObject);
               if Data.IsNull(i) then
                 Row := Row + 'NULL'
               else case Data.DataType(i).Category of
-                dtcInteger, dtcReal: begin
-                  if Data.DataType(i).Index = dbdtBit then
-                    Row := Row + 'b' + Quoter.EscapeString(Data.Col(i))
-                  else
-                    Row := Row + Data.Col(i);
-                end;
                 dtcBinary, dtcSpatial: begin
                   BinContent := Data.HexValue(i);
                   if Length(BinContent) > 0 then
