[resolved] MSSQL Query works in SQL Builder but not in Job

[Anonymous]

I have a query

SELECT 	JOB_ID,
		PHYSICAL_DATASET,
		LOGICAL_DATASET,
		FACT_BREAK,
		ENTERPRISE_ID,
		BREAK_ID,
		SOURCE_TIMESTAMP,
		DATAFEED_BREAK,
		ETL_TRANSACTION,
		TARGET_STATE,
		Dataset_Type,
		Target_Dimension,
		Target_Category,
		--Dimension_Context,
		Context_ID,
		Reference_Composite,
		Dimension_ID,
		Control_Key,
		Placeholder_Flag,
		Unknown_Flag,
		Orphan_Flag,
		--SOURCE_COLUMN,
		--SOURCE_VALUE,
		CONTEXT_KEY_01,
		CONTEXT_KEY_02,
		CONTEXT_KEY_03,
		CONTEXT_KEY_04,
		CONTEXT_KEY_05,
		CONTEXT_KEY_06,
		CONTEXT_KEY_07,
		CONTEXT_KEY_08,
		CONTEXT_KEY_09,
		CONTEXT_KEY_10
FROM ( 
SELECT  JOB_ID,
		PHYSICAL_DATASET,
		LOGICAL_DATASET,
		FACT_BREAK,
		ENTERPRISE_ID,
		BREAK_ID,
		SOURCE_TIMESTAMP,
		DATAFEED_BREAK,
		ETL_TRANSACTION,
		TARGET_

To see the whole post, download it here

[Anonymous]

Hi David
I think the problem caused by "--SOURCE_COLUMN", is it a column name or just comment? Remove it should be ok.
Best regards
Shong

[Anonymous]

Hi Shong,
--Dimension_Context, --SOURCE_COLUMN, & --SOURCE_VALUE, are all actual colunms i just commented out in the sql code. I have now removed the them from the sql query altogether. However I'm still getting the same error:
Here is the error when i Execute the job:

Starting job xxxxxxtest at 01:11 15/06/2011.
Exception in component tMSSqlInput_1
java.sql.SQLException: Incorrect syntax near ','.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
	at poc_star.xxxxxxtest_0_1.xxxxxxtest.tMSSqlInput_1Process(xxxx

To see the whole post, download it here
OriginalPost.pdf

[Anonymous]

Also is there a way to see the Actual SQL Query that is being sent to the backend? I tried to use tJavaRow on the Main output but it would always fail before it even got the tJavaRow
The code i used in tJavaRow

System.out.println((String)globalMap.get("tMSSqlInput_1_QUERY"));

-David

[Anonymous]

Hi Shong,
I missed a commented out line from the SQL statement, once i removed it, it worked! Thx for the help on that.
However, can you reply to my other two questions:
1) are SQL Comments not allowed?
2) how can i view the actual SQL query that is generated by Talend.
Thx,
David

[Anonymous · Accepted solution]

David,
Comments are not allowed because of the restriction on multi-line strings in Java. If you take a look at your generated code, you will see that the query gets placed in a single line. This means the '--' will comment out everything after it, causing your syntax error.
Pulling the query from the globalMap is the correct way to see what exactly Talend will send to the DB.

[Anonymous]

Thanks John! Understood.
-David