KNIME DataBase Writer Issue: Using UcanAcces to write Columns containing Colons to da Access DB

Hello Everyone,

I'm trying to write some datasets to a Access Database by using the UcanAccess-Driver as recommended here in the forum. This works quite well. The Database-File is used as an Input-File for another Software-Tool which checkes for the required data columns.

On table in this database is used to draw different schematics in the UI of the Software-Tool.

Therefor the required schema looks like:

ID, rX:1, rX:2, rX:3, rY:1, rY:2, rY:3

Using the "Database SQL Exectutor" Node with the following Statement:

CREATE TABLE [IntGrfcon]

		<p>([ID] varchar(50) NULL ,[rX:SIZEROW] INT NULL ,[rY:SIZEROW] INT NULL ,</p>

		<p>[rX:0] DOUBLE NULL ,</p>

		<p>[rX:1] DOUBLE NULL ,</p>
		..... and so one</td>
	</tr>
</tbody>

 

The Table is succesfully generate within the Access Database. But when writing the Data via the DatabaseWriter with the same Column names I get the Error Message:

Execute failed: UCAExc:::3.0.4 unexpected token: rX:1

I tested also the "Database SQL Exectutor" Node  with the INSERT Statement. Using the Quatation Characters "[" & "]" like in the example below, everything works.

INSERT INTO IntGrfcon ([colon:1])
VALUES (2);

 

At the moment I use some VBA Code in Access to rename the column after the data is write to the database and before importing into the softwaretool.

 

So is there a possiblity adapt the DatabaseWriter to use "[ ]" as quation charators ?

 

Kind Regrads

FEbe

 

 

 

Hello FEbe,

have you tryed to add the "[ ]" to the column names in KNIME e.g. having "[colon:1]" as column name in KNIME instead of "colon:1". KNIME generates the insert statement based on the column name of the KNIME table. To rename all columns at once you can use the Column Rename (Regex) node with (.+) as search string and [$1] as replacement.

To check the statement that is send to the database have a look at the KNIME log file which you can open in KNIME via View->Open KNIME log file. The log file contains all sql statements that are send to the db including the insert statement.

Bye

Tobias