Differing SQL syntax for Update statements - from the frontend UI framework or from DataDirect JDBC driver? - Forum - DataDirect Connect - Progress Community

Differing SQL syntax for Update statements - from the frontend UI framework or from DataDirect JDBC driver?

 Forum

Differing SQL syntax for Update statements - from the frontend UI framework or from DataDirect JDBC driver?

This question is not answered

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
  • 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 = ?

  • 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