Why am I unable to use FASTINSERT to write data to a table with non-standard column names using Database Toolbox 3.2 (R2006b)?

I have created a table in an MS Access database. The table has 14 fields which are named "1", "2", etc. Some of the fields have the data type set to double with 5 decimal points.

When I use FASTINSERT to write data into the table, I obtain only integers and rounded-off numbers without any fractions, even in the fields that are of a double data type.

colnames = {'1','2','3','4','5','6','7','8','9','10','11','12','13','14'}

fastinsert(conn, 'new_Table', colnames, myData)

Depending on the database type and the column name format, different behavior may be observed

Using FASTINSERT with a column name which has spaces in it with a MS SQL database, may result in this error:

 ??? Error using ==> database.fastinsert at 102

 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'column'.

Using FASTINSERT with a numeric column name in a MS SQL Database may result in this error:

 ??? Java exception occurred: 

 java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Table1'.

 at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

 at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

 at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)

 at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)

 Error in ==> database.fastinsert at 184

     StatementObject.execute;

Answer 1

[MathWorks Support Team]

The ability to use FASTINSERT with column names consisting of non-standard identifiers in Database Toolbox 3.2 (R2006b) is not available.

This is because FASTINSERT queries the table to determine the datatypes of each column. However in this example, since the fields have numeric names (eg "1", "2"), the field names are interpreted as literal numeric values instead of field names.

To work around this issue, you have two options.

1) Place quotes (" ") or brackets ([ ]) around the non-standard identifier. Whether to use quotes or brackets depends on which database you are using. For example, to do the insert described above within Microsoft Access you would use these commands:

colnames = {'"1"','"2"','"3"','"4"','"5"','"6"','"7"','"8"','"9"','"10"','"11"','"12"','"13"','"14"'}

fastinsert(conn, 'new_Table', colnames, myData)

2) Use the INSERT command. The INSERT command does not query the database to determine the datatype before performing the insert.

colnames = {'1','2','3','4','5','6','7','8','9','10','11','12','13','14'}

insert(conn, 'new_Table', colnames, myData)

The specifics of what constitutes a non-standard identifier for a column name depends on the database you are using. In general, there are the following restrictions:

• Reserved SQL keywords (such as "DATE" and "FROM") are not valid identifiers and cannot be used as table or column names, though some databases may allow them. See sample error message further below.
• Punctuation marks - some databases may interpret punctuation marks as operators (for example, "/" may be interpreted as a division operator).
• Spaces - using spaces in an identifier is considered poor style and will not work with FASTINSERT.
• All numeric names - Avoid using an all numeric identifier like "13", as this will often be interpreted as the number 13 instead of the column name "13".

Avoid using any of these features in your identifiers where possible.

Having a column named "Date" when using a Microsoft Access Database can result in the following error message:

 ??? Java exception occurred: 

 sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. 

         at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source) 

         at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source) 

         at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source) 

 Error in ==> database.fastinsert at 215 

 StatementObject.executeBatch;

The solution is to refer to the column as "[Date]".