3
$\begingroup$

I want to insert a matrix into a database, preferably with syntax like this:

SQLExecute[con, "INSERT INTO TABLE (x,y) VALUES (`1`, `2`)", {{1, 
   1,5,3,4}, {"A", "B", "C","B","D"}}]

so that I can write it like this:

SQLExecute[con, "INSERT INTO VARIABLE (x,y) VALUES (`1`, `2`)", 
    Matrix[[All,1]],Matrix[[All,2]]]

The problem is that Mathematica only allows the same number of values example:

SQLExecute[con, "INSERT INTO VARIABLE (x,y) VALUES (`1`, `2`)", {{1, 
   1}, {2, 2}}] 

the number of values is two, so it requires the number columns as well as the number of rows to be two.

The boring way to do this is with For or While, reading each row and inserting it in database one by one. I want to avoid this.

$\endgroup$

1 Answer 1

4
$\begingroup$

To pass batch parameters to SQLExecute, we must specify the arguments as a single list-of-lists. Each sublist is expected to contain one value for each parameter placeholder. Thus, there is no need to split Matrix into its component columns using Matrix[[All,1]],Matrix[[All,2]]. It is enough to say:

SQLExecute[con, "INSERT INTO VARIABLE (x,y) VALUES (`1`, `2`)", Matrix]

Here is a self-contained example, using an in-memory HSQL database:

Needs["DatabaseLink`"]

$matrix = {{1, "A"}, {1, "B"}, {5, "C"}, {3, "B"}, {4, "D"}};

$connection = 
  OpenSQLConnection[JDBC["org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:."]];

SQLExecute[$connection, "CREATE TABLE myTable (x INTEGER, y VARCHAR(16))"];

SQLExecute[$connection, "INSERT INTO myTable (x, y) VALUES (`1`, `2`)", $matrix];

SQLExecute[$connection, "SELECT * FROM myTable"]
(* {{1, "A"}, {1, "B"}, {5, "C"}, {3, "B"}, {4, "D"}} *)

SQLExecute[$connection, "SHUTDOWN"];

CloseSQLConnection[$connection];

Note how the arguments to the INSERT statement were passed in the same format as the rows retrieved by the SELECT statement.

$\endgroup$

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.