How to get a decent error message for stored procs that fail?

[Anonymous]

Hi there,
I have a job that runs a stored proc using tMysqlSP and it is currently failing with the following error message.
There is zero information in this error message that actually tells me what went wrong.  When I run this proc from PHP I get an error message that includes a message from the database telling me what the actual problem is: 
"Unknown column 'param_ErrorMessage' in 'field list'".
How do I get that level of information using Talend?  The database will be passing this message in the exception it throws to Talend.  Why is it not passing this on?  Or is there a component I need to use to get this, or an option I need to turn on?

Starting job VerifySupplierForDIFImport at 11:09 08/10/2015.
 connecting to socket on port 3347
 connected
Exception in component tMysqlSP_1
java.sql.SQLException: boo!
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.CallableStatement.setOutParams(CallableStatement.java:2413)
	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:919)
	at test.verifysupplierfordifimport_0_1.VerifySupplierForDIFImport.tFixedFlowInput_1Process(VerifySupplierForDIFImport.java:1401)
	at test.verifysupplierfordifimport_0_1.VerifySupplierForDIFImport.tMysqlConnection_1Process(VerifySupplierForDIFImport.java:508)
	at test.verifysupplierfordifimport_0_1.VerifySupplierForDIFImport.runJobInTOS(VerifySupplierForDIFImport.java:2061)
	at test.verifysupplierfordifimport_0_1.VerifySupplierForDIFImport.main(VerifySupplierForDIFImport.java:1883)
 disconnected
Job VerifySupplierForDIFImport ended at 11:09 08/10/2015. 

Thanks for your time,
Scott

[Anonymous]

Hi 
This is the Java exception thrown by Mysql Java API, so it is impossible to get more detailed error message from Talend console.
Best regards
Shong

[Anonymous]

Shong,
Thanks for the response mate.  Ok so the Talend job is generated java code which makes the request from MySQL server.  So the exception will be thrown from MySQL back to that java code, the same way it is when you make a request from PHP.  
Here is a screen shot of the job that calls a stored proc that I have put an error in on purpose to test this.  I have debugged the Talend job calling this proc.  
You can clearly see in the bottom left region the exception is being caught, and that exception object (e) is being wrapped in a TalendException.
You can also see in the top right where I have put the exception object e in the Variables window that it contains the error message from MySQL server.
Clearly Talend could pass this message on to be logged.  However instead the only message I get is:
"java.sql.SQLException: boo!"
Which may appear funny the first time you see it, is really not useful at all...


I really think it would save developers a lot of time if meaningful error messages were piped through to the logs instead of hidden in TalendExceptions.
Regards,
Scott

[Anonymous]

Hi  Scott
I understand the problem right now, I have seen the same issues reported for other database such as Postgresql before, I usually suggest the user to debug the Java code of job to see the real error message as you did, we have nothing to do on the component. I don't understand why  the detailed message is hidden  in TalendException, I would like you to report a jira issue on our bugtracker and discuss with our developers.
Best regards
Shong

[Anonymous]

Shong,
Thanks for taking a second look.  No problems mate I will log the issue.
While I have the ear of one of the Talend Team, can you take a look at this post for me please mate.
https://community.talend.com/t5/Design-and-Development/resolved-The-correct-way-to-assign-the-value-...
We really need to get an official response from Talend on this, as there seems to be a lot of conflicting thoughts on this in the forum.
Thanks for your time,
Scott

[Anonymous]

Hi Scott
OK, let's discuss your new question in your another topic. 
Shong

[Anonymous]

Shong,
Ok great thanks mate.  We use the post I created for that topic here:
https://community.talend.com/t5/Design-and-Development/resolved-The-correct-way-to-assign-the-value-...
You can respond to that post as it is dedicated to the new topic 😉
The subject of that post is:
"Context Variables -Can we get an official response from Talend please?"
Thanks for your time,
Scott