How to change the column name from UPPER case to lower case without using tMap

[Anonymous]

I'm trying to import data from MySQL to PostgreSQL but seems there is a case sensitive issue with column names.
MySQL has:
Id | OrgId | EmailID

PostgreSQL has:
id | orgid | emailid

To make it easy for Talend, I selected column names renaming them to lowercase in the tDBInput and then added them to tDBOutput
" select Id as id,OrgId as orgid,EmailId as emailid from employee "

However, it still fails with below error
ERROR: column "Id" of relation "employee" does not exist

I was dumping data from 75 tables and do not want to create views or tMap(this components seems staging data and takes longer time).
Is there any other way to achieve this using Talend Open Studio 7.0.1?

[fdenis]

your problem is on existence of column not on name.
check your select query. did you request n multi table or on view…..

[Anonymous]

Can you give us a screenshot of your job and your db component configurations? This doesn't quite give enough information.

FYI the tMap does not stage the data anywhere and will not add a great deal of time to the processing at all. 

[Anonymous]

I guess there is not an issue with existence of column.
When I cast the column as below it throws error on the "OrgId" column that means the "id" column has been accepted.
" select cast(Id as unsigned)as id,OrgId as orgid,EmailId as emailid from employee "

I also checked this in the SQL Builder using only one column which doubles my doubt on the case sensitive of the column name.

[Anonymous]

@rhall

I have attached the screenshots in a document.

To illustrate, I'm not using the actual data tables and only playing with dummy tables.

Let me know if something is not clear.

Thank you.

casting.docx

[Anonymous]

It is not clear where your error is originating (the input or output component) but your response to @fdenis hints that actually your column types between your databases are precisely the same. Can you disconnect your DBOutput component and add a tLogRow. Try running that.

Also, how did you get your database schemas (both sides)? Did you allow Talend to do this or did you create them manually?

[Anonymous]

@rhall

Error seems to be generating at destination because when I add tLogRow it succeeded without errors and gave me expected result.

I think schema is retrieved from connection because I explicitly asked the Talend to retrieve.

To give more background on this error:

Yes, the source and destination tables exists already with similar datatypes and same column names. The only issue is with the column name case, at source table all columns have mixed case but at destination all columns are created in lowercase.

Thank you.

[fdenis]

you can get columns to dynamic and then rename all column to lower case.
you have to use tJavaRow or tJavaFlex.
dynamic types is allowed only on enterprise version.

[Anonymous]

@fdenis

Yes but I'm using Talend Open Studio 7.0.1

Thank you.

[fdenis]

so you can generate insert statement "insert into xxx (xxx,xxx,...) values (yyy,yy,...)"
and run it.