SQLInsert'ing Null into a table (not SQLite)

5

0

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.

mfvonh

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

Answers

4

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.

Hans

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