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

1 Answer 1

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$
2
  • $\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, 2015 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, 2015 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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