topic Re: TOS - MS SQL Spaces in Column Names in Talend Studio

TOS - MS SQL Spaces in Column Names

_AnonymousUser — Sat, 16 Nov 2024 13:09:49 GMT

I have a MS SQL database which contains spaces and non-alphanumeric characters in the column names (e.g. "First Name", ") in some tables and I'm unsure how to create a table schema to properly handle them.
Inside Talend Open Studio I have made a Db Connection to the database. After this I right click the connection and select "Retrieve Schemas". I leave the default settings for "Filter for the Table" screen and click next. From the "Add a Schema on repository" screen I choose the table I would like and click next. On the final page I've tried a few things with the following results:
I've left it as generated, with spaces in the column names -> Compilation fails because spaces/special chars aren't valid in Java identifiers.
I've tried replacing the spaces with underscores ('_') in the "Column" column -> Use of the schema fails because there are no columns named with underscores.
I've tried replacing the spaces with underscores ('_') in the "Column" column and changing the "Db Column" to retain spaces -> Use of the schema fails because there are no columns named with underscores. Apparently "Db Column" isn't used the way I thought it would.
I've tried enclosing the column names with square brackets (''), single-quotes ('''), and double-quotes ('"') -> I get an alert saying the column name is invalid.
How might I got correctly defining the schema?

Re: TOS - MS SQL Spaces in Column Names

_AnonymousUser — Thu, 30 Dec 2010 15:45:21 GMT

If it makes any difference, I'm using Talend Open Studio 4.1.1.r50363 on Windows 7

Re: TOS - MS SQL Spaces in Column Names

Anonymous — Fri, 31 Dec 2010 04:04:26 GMT

Hi,
In the schema definition, for "Column" replace space with underscore and for "Db Column" retain the column name as is. It is the column name which is used in Talend for row schema definition. The Db Column is used while accessing the database.
Attaching screen shots (this is for an Oracle schema).
Regards,
Abhi

Re: TOS - MS SQL Spaces in Column Names

_AnonymousUser — Tue, 04 Jan 2011 16:49:04 GMT

Thank you, iterator. What you describe is actually what I had already tried:

I've tried replacing the spaces with underscores ('_') in the "Column" column and changing the "Db Column" to retain spaces -> Use of the schema fails because there are no columns named with underscores. Apparently "Db Column" isn't used the way I thought it would.

But I went back to try again anyway and it worked!
It seems my problem was that when you edit the "Db Name" column in a schema, the changes DO NOT get propagated to existing components as I was expecting. Removing and re-inserting the components I was using (tMSSqlInput) showed the new updated "Db Name"s
Do you think this is bug worthy or is this something (Db Column name changes not propagating to components) I should have expected to happen?

Re: TOS - MS SQL Spaces in Column Names

alevy — Wed, 05 Jan 2011 03:51:55 GMT

I would expect such a changes to propagate, but then it does for me (v4.0.3). Time to try again?

Re: TOS - MS SQL Spaces in Column Names

_AnonymousUser — Sat, 02 Aug 2014 23:18:37 GMT

Same problem I am having setting Metadata in TOS integration studio, for some reason the TOS studio seems to encounter error in JDBC driver when parsing a space between table and column names even though metadata itself is mapped to column_0 and column name is mapped as "column 0", I have tried moving it to still the same problem.