I’m using the following code to run a SQL query.
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
This runs SQL stored in a dataprovider and sometimes the SQL will be wrong (badly formed or missing - its only ever a SELECT query BTW). Currently I get the Servoy error that the SQL has an error - but I want to test for this and show my own error dialog. Whats the syntax to check for this error?
Appreciate feedback.
Hi Ian,
You can get the error from a dataset using the following syntax:
if ( ds.getExceptionMsg() ) {
application.output(ds.getExceptionMsg()); // the error message
}
Hope this helps.
ROCLASI:
Hi Ian,You can get the error from a dataset using the following syntax:
if ( ds.getExceptionMsg() ) {
application.output(ds.getExceptionMsg()); // the error message
}
Hope this helps.
Thanks for that feedback Robert - Question:
Wont the Error fire and display the Servoy error message before I run this if()?
Should I wrap the entire getDatasetByQuery in this if?
Hi Ian,
Kahuna:
Wont the Error fire and display the Servoy error message before I run this if()?Should I wrap the entire getDatasetByQuery in this if?
If the error is a SQL error (i.e. an error returned from the database server) then it will never fire an exception in Servoy. You need to actively check if an error was returned.So the IF needs to be after you use the getDataSetByQuery().
Or are checking for database errors using an onError global method?
ROCLASI:
If the error is a SQL error (i.e. an error returned from the database server) then it will never fire an exception in Servoy. You need to actively check if an error was returned.So the IF needs to be after you use the getDataSetByQuery().
Or are checking for database errors using an onError global method?
I see what you’re suggesting Robert but I’m not sure if I’ve misled you on what I’m doing (or if I’m having a particularly dumb day - wholly possible )
Heres what I’m doing:
//Get a dataset based on query
var maxReturnedRows = -1;
var query = vSQL // Query MUST be well formed - Taken from Threats Form SQL field
var dataset = ‘’
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
Where there is legal SQL this runs as expected. But in my test case with illegal SQL I get the following error:
com.sevoy.j2b.dataprocesing.DataException: You have an error in your SQL syntax…
If after the above I then add:
if ( dataset.getExceptionMsg() ) {
application.output(dataset.getExceptionMsg()); // the error message}
I still receive the same error message - and the error is NOT output to the console? Is there some way to test the SQL before assigning it to the dataset variable?
Really do appreciate your feedback and help Robert - Cheers
Hi Ian,
Can you show what SQL gives you this error ?
Also can you make sure the error comes from that line of code ? Use the debugger to step through it.
ROCLASI:
Hi Ian,Can you show what SQL gives you this error ?
Also can you make sure the error comes from that line of code ? Use the debugger to step through it.
Robert this is really just junk SQL as a test - I know there will be times when the SQL input is wrong.
I’ve run the debugger and it comes from the var setting on databaseManager…
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
I’ve tried it with just broken SQL: SELECT * From xyz (where xyz does not exist) and also
SELECT (and no other string)
Essentially the SQL string could be completely rubbish Robert. Does that make sense?
Hi Ian,
I believe you
You also don’t have any onError global methods in use in your solution ?
You are using Servoy 4.1.1. for this ? If so that this seems to be a new behavior then because up to 3.5.x every SQL error the back-end threw when using the databaseManager you had to check yourself.
Unless of course the JDBC driver itself was throwing the error. But normally with malformed SQL the driver won’t do this.
ROCLASI:
Hi Ian,I believe you
You also don’t have any onError global methods in use in your solution ?You are using Servoy 4.1.1. for this ? If so that this seems to be a new behavior then because up to 3.5.x every SQL error the back-end threw when using the databaseManager you had to check yourself.
Unless of course the JDBC driver itself was throwing the error. But normally with malformed SQL the driver won’t do this.
I’ve got no other error handling running that could catch this one Robert!
I cant actually see how I could catch the error if I cant check for it post ‘databaseManager…’ unless there is some what to say something like
if(! databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows))
{
error
}
???
Perhaps one of the dev team could pick-up on this and comment??
Ian,
You can either catch the sql error in a try-catch construct around the databaseManager.getDataSetByQuery() call or configure a solution onError method (property on Solution) that will be fired on the error.
Rob
rgansevles:
Ian,You can either catch the sql error in a try-catch construct around the databaseManager.getDataSetByQuery() call or configure a solution onError method (property on Solution) that will be fired on the error.
Rob
Thanks Rob - I’ll try that tomorrow.
Cheers