add support to insert boolean data into TDV even though backend database does not support boolean data type

We are developing a Spotfire custom module which connects to TDV 8.7 and run DDL(create table & insert) against it. TDV then performs DDL against backend database so the actual data from Spotfire is stored in the backend database and published in TDV.

The purpose of this custom module is to run a unified DDL against TDV and TDV can perform appropriately "translated" DDL towards backend database.

One thing we noticed that if backend database does not support boolean data type(e.g. MS SQL Server and Oracle), then DDL(e.g. below) against TDV would fail.

create or replace table mssql."public".b1 (b1 boolean);

insert into mssql."public".b1 values(true);

Running above insert DDL against MS SQL Server fails with error "Invalid column name 'true' ", and in case of Oracle it also fails with error "ORA-00984: column not allowed here".

From Spotfire custom module side we don't know what kind of backend database is used so we can not modify the DDL accordingly.

We understand this(above errors) is a limitation due to backend database doesn't support boolean type, however we still hope TDV can translate the DDL according to the different type of backend databases, to make above DDL works properly.

For example, below SQL(pass true and false as string) works fine for MS SQL Server

INSERT INTO "SQL_Cat"."SQL_Schema".b12 values('false'); ---> saves as FALSE

INSERT INTO "SQL_Cat"."SQL_Schema".b12 values('0'); ---> saves as FALSE

INSERT INTO "SQL_Cat"."SQL_Schema".b12 values('true'); ---> saves as TRUE

INSERT INTO "SQL_Cat"."SQL_Schema".b12 values('1'); ---> saves as TRUE

Please refer to support case 02210853 for more details.