Differing SQL syntax for Update statements - from the fronte

Posted by Rom Elwell on 18-Oct-2017 12:08

Note this question was also posted in the OE Development Community...

OpenEdge 11.5.1

DataDirect OpenEdge JDBC Driver (v5.1.3.000112)

The front-end framework we are using to update data in the OE DBs passes an exception when we attempt CUD operations on the table named 'account', however, this same framework works as expected when we attempt CUD operation on the table named 'employee'.  Updates to both tables come from the same method in the base class, thus, the only known differences between the two calls is the table name ('account' vs 'employee') and the column names being updated.

Using the command

set pro_server log on with (statement)

 for the DB with the 'account' table, we can see in the associated log the following UPDATE syntax and error:

Preparing SQL Statement: update "account" "account" set acc_desc=? where "account".gl_account = ?
20171016_102756 13896:
Returning from sql_fn() with code: -210056

Unknown errorCode 100 [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL Statment at or about ""account" set acc_desc? where "account"" (10713)

Using the command 

set pro_server log on with (statement)

for the DB with the 'employee' table, we can see in the associated log the following UPDATE syntax:

Executing SQL Statement: update employee set emp_first_name=? where emp_id = ?
20171018_092022 6848:
Input Parameters to Execute Statement: update employee set emp_first_name=? where emp_id = ?
[0]: = (Tom)
[1]: = (000001)
20171018_092022 6848:
Returning from sql_fn() with code: 0


Question:  Is it possible the syntactical difference between the two UPDATE statements is related to the DataDirect JDBC driver?  I have reviewed the documentation for the underlying Apache Hive DDL (upon which the PRG SQL92 spec is built upon) and could not draw a conclusion either way.  

Thanks for your feedback.

All Replies

Posted by Brian Derwart on 18-Oct-2017 12:50

I think the issue is that you've quoted the alias name that you would like to use for this table in the SQL referencing the account table. Does this SQL work without error:

update "account" account set acc_desc=? where "account".gl_account = ?

Posted by Rom Elwell on 18-Oct-2017 12:59

Thanks for the suggestion Brian.  If I alter the syntax to your suggestion, the driver returns error -210083;  it (driver) is expected the schema name to preface the table name.  i.e. pub."account"

Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Table/view/synonynm "ROM.account" cannot be found. (15814)

SQLState:  42S02

ErrorCode: -210083

This thread is closed