Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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)

share|improve this question
    
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 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 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 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 at 21:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.