5
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ I have tested this on MySQL and it works with Mathematica 10.2. $\endgroup$ – C. E. Aug 18 '15 at 6:52
  • $\begingroup$ 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? $\endgroup$ – mfvonh Aug 20 '15 at 3:45
  • $\begingroup$ Yes, maybe it is. I'm using a local instance. $\endgroup$ – C. E. Aug 20 '15 at 6:04
4
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – mfvonh Aug 18 '15 at 1:20
  • $\begingroup$ @mfvonh if you change "Nullable" -> False the insert does not work in SQLite. More information may be helpful. $\endgroup$ – Hans Aug 18 '15 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.