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

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)

-
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 Mar 11 '14 at 19:32
"\"Col 1\"" works for creating column - but is problematic while inserting using SQLInsert. Tried [Col 1] - it does not help either – my account_ram Mar 12 '14 at 19:09
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 Mar 13 '14 at 1:45
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 Mar 13 '14 at 21:27