Capturing the external Oracle DB error from a SQL Query service call

[Not_Mandatory]

Greetings!

I have an API endpoint that fetches data from an external Oracle database that I have developed in Thingworx. Naturally, it utilizes the Database Thing, on which are 2 services: a Local (JavaScript) service for the endpoint's business logic, and a SQL (Query) service that executes the query against the external database using SQL that gets defined in the JavaScript service based upon the values of the input parameters that are passed. For example, the endpoint can be called to fetch user information by passing EITHER an ID number or an Email address, and the SQL is adjusted accordingly before the SQL(Query) service is called to execute it.

Overall, the endpoint works well. The challenge I am facing is that I want to trap for specific Oracle DB errors, and since I'm passing my SQL query off from the TWX Local (JavaScript) service to the TWX SQL (Query) service, I "lose" the Oracle error object and instead get a generic JavaScript error.

To be more specific, I use the following try/catch code in the JavaScript service and then call the SQL(Query) service to execute my SELECT statement:

var dbResults = {};
try {
	dbResults = me.RunDatabaseQuery( {query:dbQuery} );
} catch( queryError ) {
	logger.debug("database-controller.get-user.executeDBQuery(dbQuery): DB Query Error: " + 
	"'queryError.name' = '" + queryError.name + "': " + 
	"'queryError.message' = \"" + queryError.message + "\" " );
}
return( dbResults );

 If the external database is offline or my Schema doesn't have access, Oracle throws a "table or view does not exist" error. That's the information I want returned to the API caller as part of the result set.

But my service only gets a generic JavaScript error saying the query failed, as shown in my debug log:

database-controller.get-user.executeDBQuery(dbQuery): DB Query Error: 'queryError.name' = 'JavaException': 'queryError.message' = "java.lang.Exception: Execute Query failed. Please check Database logs for more details."

However, the TWX DatabaseLog.log does show the Oracle error info I want, so I know TWX has the info somewhere:

"Unable to Invoke Service RunDatabaseQuery on database-controller : Execute Query failed. java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist_"

How can I get that Oracle exception info back to my JavaScript service when I call the "RunDatabaseQuery" SQL (Query) service?...other than having to parse the databaselog file in real time, which seems rather absurd?

[bchaudhary]

Hello @Not_Mandatory ,

We have a community post discussing the similar issue. Pls check out the link:

https://community.ptc.com/t5/ThingWorx-Developers/Execute-Query-failed-java-sql-SQLSyntaxErrorException-ORA-00933/td-p/543818?posno=3&q=Execute%20Query%20failed.%20java.sql.SQLSyntaxErrorException&source=search

Regards

Bhawna

[Not_Mandatory]

Bhawna:

The issue isn't the query; the query works fine. The issue first arose when the DBAs disabled my access to the View the query calls without warning. But if my Schema's permissions were changed to where I no longer had visibility to a table, for example, Oracle would throw a similar error. And that Oracle error is what I want to be able to trap for in my service call. Instead I get a generic JavaScript service error telling me to manually look at the DatabaseLog.log entries for those details it just logged there but doesn't want to show me...

So what I was actually looking for was visibility into the Thingworx DatabaseLog.log or ErrorLog.log files to properly capture such Oracle errors.

What I've discovered is that TWX only exposes 5 of its 9 log files as objects...and those 5 are the ones I am not at all concerned with (ConfigurationLog, SecurityLog, CommunicationLog, etc?...meh.) They DON'T expose the DatabaseLog.log, ScriptErrorLog.log, ErrorLog.log, or AuthLog.log. So basically...none of the ones that would actually be useful are available. Which is pretty disappointing.

My colleagues and I have discussed it, and we'll probably have to write a custom Java extension to monitor those files and reference them as objects so we can capture errors like this programmatically, since TWX doesn't do so for some reason.

[jensc]

Hello,

Not sure if it is different for Oracle, but for MSSQL there is a property to show the connectivity for the DB thing.

So my suggestion would be to check that before you do your query.

For the MSSQL thing the property name is "isConnected".

This would only solve the connection check though. So for if there are any other issues I can't really say how it could be done.

Regards,

Jens

[Not_Mandatory]

An excellent tip, but in this particular case, the TWX service is still fully connected to the DB, so the isConnected property still returns true.  It just can't see the table or view it needs to in order to execute its query is the problem.

See my response to Bhawna, above, for more details about how what I wanted was access to the DatabaseLog.log file in order to trap for such instances programmatically.

I think a custom Java extension is the only answer, which is frustrating since I honestly don't understand why PTC exposes 5 of the 9 log files as objects but not the critical error logs!  *shrug*

[jensc · Accepted solution]

Hello,

Yeah, I knew it wouldn't cover all of your use case which is unfortunate.

It would be great if the original error was given in the catch statement, I don't know if this is the case in "normal" JS?

As you say, it seems to me like the only option would be to create your own extension. 

But before doing this though, I would recommend opening a support ticket just to see if there is some hidden services that could be used.

Regards,

Jens

[Not_Mandatory]

Another excellent suggestion. Thank you.

I figured I'd reach out to the community first to see if there might be something I was missing before bothering the support folks, but opening a ticket would definitely be the next step before taking the plunge on developing a whole new extension.

I'm relatively new to TWX...do other developers ever make their extensions public via Sourceforge, Bitbucket, or the like? I could see a "public" library of TWX Java extensions being pretty useful...

[jensc]

Hello,

There are some public extension libraries on GitHub and I have seen some people in the community that have shared their own personal repositories.

There might also be some on the PTC marketplace, although I have never used it.

There is also a company called IQNOX that has some free/payed extensions and I think they also build them for customers.

My assumption is that many people who actually create extensions does this on company time, which in turn makes it a little difficult to publish the code.

But I think it would be a great if more people shared their extensions.

Regards,

Jens