SQLInsert'ing Null into a table (not SQLite)



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.


Posted 2015-08-17T18:14:56.173

Reputation: 8 252

I have tested this on MySQL and it works with Mathematica 10.2. – C. E. – 2015-08-18T06:52:46.977

I'm on 10.2 also. Are you using a local MySQL instance? I am using RDS, I did not try spinning up a local DB. Maybe it is specific to RDS? – mfvonh – 2015-08-20T03:45:14.130

Yes, maybe it is. I'm using a local instance. – C. E. – 2015-08-20T06:04:33.810



Using the built in SQLite you may try the following

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

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"];

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.


Posted 2015-08-17T18:14:56.173

Reputation: 896

Thank you for your answer; I had not tried SQLite. The columns are nullable in my situation (and SQLInsert still does not work) -- I will add more information. – mfvonh – 2015-08-18T01:20:17.987

@mfvonh if you change "Nullable" -> False the insert does not work in SQLite. More information may be helpful. – Hans – 2015-08-18T01:25:22.733