Dear All,
I migrated a project from Talend DI 5.6.1 to Talend 6.0.0, but some the previously working jobs crash in the new environment.
One issue concerns SQL queries.
So the job in question tries to read an MS Access query LT_AFETYPES_TIS from an Access 2002-2003 database, but Talend does not find this query in the database saying:
net.ucanaccess.jdbc.UcanaccessSQLException:
user lacks privilege or object not found: LT_AFETYPES_TIS"
Here is the SQL for the query in question:
SELECT Null AS , Null AS , .TdfIds, .AfeType_Name, '(type-)instance' AS , Null AS , Null AS , .TdfIds, .EA_Uml_Names, Null AS third_TdfIds, Null AS third_Names FROM INNER JOIN ON .TypeInstance_LUIDs = .LUIDs;
After many tryings and failings I found out that the new Talend does not accept parentheses in the names of the database tables if they are used in SQL join queries.
Is this the correct diagnosis of the problem or perhaps something else is looming in the dark?
Kind regards,
Pawel
***
Exception in component tAccessInput_1
net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: LT_AFETYPES_TIS
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:202)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_linkstable_preprocessing_1_0.Datum360Bsxd_LinksTable_Preprocessing.tAccessInput_1Process(Datum360Bsxd_LinksTable_Preprocessing.java:1647)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_linkstable_preprocessing_1_0.Datum360Bsxd_LinksTable_Preprocessing.tRunJob_1Process(Datum360Bsxd_LinksTable_Preprocessing.java:830)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_linkstable_preprocessing_1_0.Datum360Bsxd_LinksTable_Preprocessing.tAccessConnection_1Process(Datum360Bsxd_LinksTable_Preprocessing.java:599)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_linkstable_preprocessing_1_0.Datum360Bsxd_LinksTable_Preprocessing.runJobInTOS(Datum360Bsxd_LinksTable_Preprocessing.java:2529)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_linkstable_preprocessing_1_0.Datum360Bsxd_LinksTable_Preprocessing.runJob(Datum360Bsxd_LinksTable_Preprocessing.java:2327)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_placeableobjects_converters_1_0.Datum360Bsxd_PlaceableObjects_Converters.tRunJob_5Process(Datum360Bsxd_PlaceableObjects_Converters.java:692)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_placeableobjects_converters_1_0.Datum360Bsxd_PlaceableObjects_Converters.runJobInTOS(Datum360Bsxd_PlaceableObjects_Converters.java:1721)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360bsxd_placeableobjects_converters_1_0.Datum360Bsxd_PlaceableObjects_Converters.runJob(Datum360Bsxd_PlaceableObjects_Converters.java:1515)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.tRunJob_3Process(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:5789)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.tJava_5Process(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:5601)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.tAccessInput_1Process(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:4797)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.tJava_7Process(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:968)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.tAccessConnection_1Process(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:856)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.runJobInTOS(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:6343)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_to_datum360bsxd_parameterised_converter_1_0.Datum360_to_Datum360Bsxd_Parameterised_Converter.runJob(Datum360_to_Datum360Bsxd_Parameterised_Converter.java:6125)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_parameterised_doublestroke_load_1_0.Datum360_Parameterised_DoubleStroke_Load.tRunJob_2Process(Datum360_Parameterised_DoubleStroke_Load.java:1288)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_parameterised_doublestroke_load_1_0.Datum360_Parameterised_DoubleStroke_Load.tRunJob_1Process(Datum360_Parameterised_DoubleStroke_Load.java:1087)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_parameterised_doublestroke_load_1_0.Datum360_Parameterised_DoubleStroke_Load.tJava_1Process(Datum360_Parameterised_DoubleStroke_Load.java:835)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_parameterised_doublestroke_load_1_0.Datum360_Parameterised_DoubleStroke_Load.runJobInTOS(Datum360_Parameterised_DoubleStroke_Load.java:3867)
at assimilate_gtl_jobs_v0_013_20150721_pg.datum360_parameterised_doublestroke_load_1_0.Datum360_Parameterised_DoubleStroke_Load.main(Datum360_Parameterised_DoubleStroke_Load.java:3614)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: LT_AFETYPES_TIS
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:199)
... 20 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: LT_AFETYPES_TIS
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 23 more
It is not correct. The new Talend release does nothing else here like the former releases. It simply send the query as statement to the database. It does no care about parentis or what ever as long as it is a String and can be used in the JDBC API.
If you get such errors, perhaps the user credentials used in the job are wrong.
Thanks for your quick reply.
But the user's credentials (user name and password) are set to empty in the job and there are no user credentials in the Access database.
Moreover, the job crashes only on some queries, i.e., some queries in the database are acceptable and the others are not.
And the jobs were fine in Talend 5.6.1.
