Skip to main content
N
Not_Mandatory4-Participant
4-Participant
March 23, 2023
Solved

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

  • March 23, 2023
  • 2 replies
  • 4087 views

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?

Best answer by jensc

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

2 replies

B
bchaudhary_4480085-Regular Member
5-Regular Member
March 29, 2023

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

N
Not_Mandatory4-ParticipantAuthor
4-Participant
March 30, 2023

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.

J
jensc17-Peridot
17-Peridot
March 29, 2023

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

    N
    Not_Mandatory4-ParticipantAuthor
    4-Participant
    March 30, 2023

    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*

      J
      jensc17-PeridotAnswer
      17-Peridot
      March 30, 2023

      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

        Terms & ConditionsAccessibility statement