Differing SQL syntax for Update statements - from the fronte

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

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.

Posted by marian.edu on 18-Oct-2017 13:52

Ha, have to admit never saw any update statement using aliases… even so I still think this is not valid SQL even it might well be supported by a number of vendors out there, the table alias can only be used in the FROM clause.

update "alias" set “alias”.acc_desc=? from “account” [as] “alias” where "alias".gl_account = ? 

I do believe this is somehow MS SQL specific, the SQL92 BNF I have doesn’t allow using ‘from’ clause:

UPDATE <table name> SET <set clause list> [ WHERE <search condition> ]

Anyway, there is really no reason for them to use an alias in this case and it looks like they are not doing that for every table so… if it hurts doing so, stop doing it :)

 

All Replies

Posted by marian.edu on 18-Oct-2017 13:00


Rom, is this statement generated somehow by the framework (ORM)? It sure ain’t a valid SQL statement, nothing wrong with the JDBC driver… none of the names there are keywords, wonder why the table name got quoted for account and not for employee?

Posted by Rom Elwell on 18-Oct-2017 13:14

Agreed Marian.  The vendor informed us that that quoted table name for account (with quoted alias name) is valid SQL.  My post(s) here are due diligence to see if anyone was aware of any issues with the JDBC driver before our conference call with the Vendor on Thursday.  Thanks for taking the time to respond my friend!

Posted by marian.edu on 18-Oct-2017 13:52

Ha, have to admit never saw any update statement using aliases… even so I still think this is not valid SQL even it might well be supported by a number of vendors out there, the table alias can only be used in the FROM clause.

update "alias" set “alias”.acc_desc=? from “account” [as] “alias” where "alias".gl_account = ? 

I do believe this is somehow MS SQL specific, the SQL92 BNF I have doesn’t allow using ‘from’ clause:

UPDATE <table name> SET <set clause list> [ WHERE <search condition> ]

Anyway, there is really no reason for them to use an alias in this case and it looks like they are not doing that for every table so… if it hurts doing so, stop doing it :)

 

This thread is closed