topic Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name in Talend Studio

Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Sat, 16 Nov 2024 04:03:04 GMT

Has anyone encountered invalid column name errors using the MSSQL component recently? I'm running into this error in my job:

Exception in component tMSSqlInput_1
java.sql.SQLException: Invalid column name 'Balance'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)

Here's my current query:

"SELECT
SiteId,
Name,
Comments,
Contact,
ContactPhone,
InstallDate,
Address,
Email,
LogoPath,
TimeZone,
Logo,
Factor,
CustomerId,
Balance,
date_update
FROM
sites (nolock)"

I've tried using Talend Open Studio v7.1.1 and v7.2.1 and both are throwing the same error. From what I can tell, "Balance" or "date_update" are not reserved names in Java or MSSQL.

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Fri, 22 Nov 2019 01:20:26 GMT

Can you show us your table schema?

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Fri, 22 Nov 2019 02:08:20 GMT

Thanks for the quick response! I've included details about the MSSQL database table and the Talend schema below:

Database SchemaTalend Schema

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Fri, 22 Nov 2019 19:15:57 GMT

Sorry, I meant a schema DDL dump. The column is clearly there, but it could be defined in your DDL in a weird way

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Mon, 25 Nov 2019 00:31:42 GMT

Gotcha. DDL below but nothing about the date_update column seems unusual.

CREATE TABLE sites (

SiteId int IDENTITY(1,1) NOT NULL,

Name nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Comments nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Contact nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

ContactPhone nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

InstallDate datetime NULL,

Address nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Email nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

LogoPath nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

TimeZone int NULL,

Logo image NULL,

Factor int NULL,

CustomerId int NULL,

Balance int NULL,

date_update datetime NULL,

CONSTRAINT Sites_PK PRIMARY KEY (SiteId)

)

In the meantime, I've tried using a CTE and/or aliasing the date_update column to a different string but I'm still running into the same error.

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Mon, 25 Nov 2019 08:36:44 GMT

Does this still happen if you remove the (nolock) hint?

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Fri, 20 Mar 2020 02:53:35 GMT

The column was clearly there, in the production environment. I'm a little ashamed to admit I was connected to a lower environment when encountering this "error". Thanks for your help!

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Tue, 07 Jul 2020 10:34:17 GMT

Exception in component tDBInput_1 (Copy_Citi_POC)
java.sql.SQLException: Invalid column name 'ADDRESS'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
at project_01.copy_citi_poc_0_1.Copy_Citi_POC.tFixedFlowInput_1Process(Copy_Citi_POC.java:966)
at project_01.copy_citi_poc_0_1.Copy_Citi_POC.runJobInTOS(Copy_Citi_POC.java:1904)
at project_01.copy_citi_poc_0_1.Copy_Citi_POC.main(Copy_Citi_POC.java:1754)

Hi, I'm having a similar issue, my DbInput query looks like this: ""SELECT STUFF((SELECT ', ' + column_name FROM information_schema.columns WHERE
table_name ="+((String)globalMap.get("DBTable"))+" and table_schema = 'SRC' order by ordinal_position FOR XML PATH('')),1,2,'') as col1""

I have two table names with the same schema in a fixed flow component which iterates over a global variable "DBTable" on tflowtoiterate. I can help you with my job if someone can confirm if they understood my problem.

Thanks, in advance.

Re: Exception in component tMSSqlInput_1 java.sql.SQLException: Invalid column name

Anonymous — Tue, 07 Jul 2020 10:34:17 GMT

Thanks, in advance.