SQL Server doesn't like the 'b' prefix for BIT fields

[lakebranch]

Steps to reproduce this issue

1. Create database in SQL Server
2. Create table with atleast one BIT field
3. Insert some rows
4. Try to edit the data using the "Data" tab in HeidiSQL

Current behavior

SQL Error (102): Incorrect syntax near '0'

Expected behavior

No error

Possible solution

The problem seems to be that the generated SQL command prefixes bit values with the character 'b'. This is valid in MySQL it seems but Microsoft SQL Server doesn't like it.

UPDATE TOP(1) "TestDB"."dbo"."TestTable" SET "Locked"='1' WHERE "Name"='TestPerson' AND "Locked"=b'0';

Removing the 'b' before the '0' (for BIT field "Locked") makes SQL Server happy again.

Environment

• HeidiSQL version: 10.3.0.5848
• Database system and version: Microsoft SQL Server 2014
• Operating system: Windows 10

[lakebranch]

It's literally been DECADES since I knew my way around Pascal/Delphi but I'm guessing this has something to do with dbconnection.pas, around line 7699 7749 (in the TDBQuery.SaveModifications function):

else case Datatype(i).Category of
  dtcInteger, dtcReal: begin
      Val := Connection.EscapeString(Cell.NewText);
      if (Datatype(i).Index = dtBit) and FConnection.Parameters.IsMySQL then
          Val := 'b' + Val;
    end;
[...]

I'm guessing that the only explanation for this behavior is that FConnection.Parameters.IsMySQL somehow is set to True - even though I'm connecting to an MSSQL database.

[lakebranch]

Hold on. I was wrong. The above code snippet deals with creating the SET part of the UPDATE if I'm not mistaken. I'm guessing that the WHERE clause comes from TDBQuery.GetWhereClause and there's no guard against MSSQL in there. The "b" prefix is always added if I'm reading this correctly.

But there's another problem emerging here as well. The table I originally was trying to edit has a PRIMARY KEY, which should mean that the WHERE clause shouldn't need ALL fields?

[mcdado]

There's an older issue for the same problem: #264