Problem inserting data into a mysql table with column names having a space

3

1

I have a mysql table whose column name has space.

I tried inserting the data into this column. I got an error

I tried a sample code, which tests accessing the columns with a space and that returns the same error.

Needs["DatabaseLink`"];
conn = OpenSQLConnection["demo"]
SQLCreateTable[conn, 
SQLTable["TEST1"], {SQLColumn["COL 1", "DataTypeName" -> "VARCHAR"]}]

I got this error at the last line.

JDBC::error: ""Wrong data type: 1 in statement [CREATE TABLE TEST1 ( COL 1]">>

I tried other combinations to access that column names, such as

"`COL 1`"
"COL\ 1"
"`COL\ 1`"

and they din't work either.

What is the proper way to create/access/insert data into columns with a space?

(I am on Windows 7, using Mathematica 8)

my account_ram

Posted 2014-03-11T16:43:31.903

Reputation: 2 068

Have you tried "[Col 1]" to read {SQLColumn["[COL 1]", "DataTypeName" -> "VARCHAR"]}. If the JDBC MODE is minicking MSSQL, else try ""Col 1"" to read {SQLColumn[""COL 1"", "DataTypeName" -> "VARCHAR"]}] – Hans – 2014-03-11T19:32:24.520

""Col 1"" works for creating column - but is problematic while inserting using SQLInsert. Tried [Col 1] - it does not help either – my account_ram – 2014-03-12T19:09:17.047

SQLInsert[conn,table, {columns},{values},opts] I believe the columns list is expecting a list of columns, so for each quoted or unquoted identifier, try SQLColumn[""COL 1""]. Instead of just ""Col 1"" -> String[""Col 1""] – Hans – 2014-03-13T01:45:13.270

Again problematic. I tried to create the table manually ,outside Mathematica, since I got some error when I was trying to set the default value of the string to be ''. But even after that addressing columns with a space in the name throws error. @Hans - how do I find what mode the JDBC is mimicking. I am wondering if its a problem with MMA and the Uniserver (I am able to access these columns from PhP on the same machine though) – my account_ram – 2014-03-13T21:27:45.020

No answers