SQLInsert'ing Null into a table (not SQLite)

Question

Is it possible to insert Null values using DatabaseLink, without formatting the query string manually?

Hans found (below) that this works for SQLite, so it appears this may driver-specific. I have tested it on MySQL, Postgresql, and Amazon Redshift.

For example:

conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", host],
  "Username" -> user, "Password" -> pass];
SQLExecute[conn, "create table public.test (i integer null)"];
SQLInsert[conn, "public.test", {"i"}, {Null}]

[Amazon]JDBC Error converting data, invalid type for parameter: 1.

Answer 1

Using the built in SQLite you may try the following

    Needs["DatabaseLink`"]
conn = OpenSQLConnection[JDBC["SQLite(Memory)", ""]];
SQLCreateTable[conn, 
  SQLTable["TEST"], {SQLColumn["i", "DataTypeName" -> "INTEGER", 
    "Nullable" -> True]}];
SQLInsert[conn, "TEST", {"i"}, {Null}]
SQLSelect[conn, "TEST"]
SQLDropTable[conn, "TEST"];
CloseSQLConnection[conn];

The key here is the SQL table schema definition for the column to allow nulls, or to be or accept "Null". There is a Default attribute which can be set to Null also. If you were to use SQLExecute[] then try the following:

conn = OpenSQLConnection[JDBC["SQLite(Memory)", ""]];
SQLExecute[conn, "create table TEST (i integer null)"];
SQLInsert[conn, "TEST", {"i"}, {Null}]
SQLSelect[conn, "TEST"]
SQLDropTable[conn, "TEST"];
CloseSQLConnection[conn];

There may be a global setting to Allow Null on or off in some databases if I recall correctly.

I also checked this on an instance of MS SQL Server 2012 and had no issues. Also used SQLExecute[] to insert parameters.

SQLExecute[conn, "INSERT INTO TEST (i) VALUES (?)", {Null}]

All on Windows 8.1 M 10.0.