Database error when logging to alarm journal

Ignition
1

We’ve set up an alarm journal and are getting a database error every time the tag goes into its alarm state. The error as logged on the console is:

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

We’ve managed to track down the SQL being sent to the database and it’s:

SELECT "alarm_events"SEQ.CURRVAL FROM DUAL

I think there are two problems here and they’re both related to the double quotes. One, the end quote in the middle of the sequence name, rather than at the end. Two, the sequence was not created with double quotes so its name is ALARM_EVENTSSEQ and the case-sensitive “alarm_eventsSEQ” wouldn’t match it.

We tried changing the table name for the alarm journal to all caps, but the quote mark is still in the wrong place.

This is Ignition 7.6.3 under Oracle 11gR2. I know our version is a bit behind, but I looked in the change logs and didn’t see anything relevant.

2

shouldn’t your query be something like
SELECT ALARM_EVENTSSEQ.CURRVAL FROM DUAL

stackoverflow.com/questions/7425 … -sensitive

Unless this link is wrong it says that the values passed to select are always case insensitive, so you shouldn’t need the double quotes in any case.

3

[quote=“matt.eaton”]shouldn’t your query be something like
SELECT ALARM_EVENTSSEQ.CURRVAL FROM DUAL

That’s correct. The double quotes actually make the query case sensitive, while Oracle is case insensitive by default. If I wasn’t clear, this isn’t my query; this is coming from within the Ignition alarm logging framework itself when it tries to log an alarm.

4

I remember seeing options for this in the gateway.
It’s under Databases>Drivers>Translators

The settings for the Oracle Translator show the pattern and escape character it’s using.
Maybe you can just remove the column escape character as a workaround.
But this definitely looks like a bug to me.