topic Re: Update query with CL_SQL_STATEMENT class in Application Development and Automation Discussions

Update query with CL_SQL_STATEMENT class

Former Member — Wed, 05 Oct 2011 16:50:23 GMT

Hi all,

I'm trying to execute an update query with ADBC classes in order to modify two parameters (n_doc and processed) from a row with the following instructions:

l_con_ref = cl_sql_connection=>get_connection( 'DB_7879' ).

l_stmt_ref = l_con_ref->create_statement( ).

CONCATENATE 'update' gv_table ' set n_doc= ? processed = ''X'' where id_ordn = ? ' INTO l_stmt SEPARATED BY space.

GET REFERENCE OF materialdocument INTO l_dref.

l_stmt_ref->set_param( l_dref ).

GET REFERENCE OF ps_zmm_mov-id_ordn INTO l_dref.

l_stmt_ref->set_param( l_dref ).

l_stmt_ref->execute_update( l_stmt ).

However, I got the error ORA-0933: SQL command not properly ended. I guess this error occurs because I am trying to update two parameters with a single query since using the following CONCATENATE instruction

CONCATENATE 'update' gv_table ' set n_doc= ? where id_ordn = ? ' INTO l_stmt SEPARATED BY space.

results ok.

Does anybody know if it is possible to modify two parameters with only one update query with ADBC classes?

Thank you in advance!

Re: Update query with CL_SQL_STATEMENT class

Sandra_Rossi — Wed, 05 Oct 2011 20:17:59 GMT

Hi,

I think you should add a comma between the 2 column names -> update <tablename> set n_doc= ?, processed = ''X'' where id_ordn = ?

Sandra

Re: Update query with CL_SQL_STATEMENT class

Former Member — Thu, 06 Oct 2011 07:38:04 GMT

Thanks Sandra!

Re: Update query with CL_SQL_STATEMENT class

SuhaSaha — Thu, 06 Oct 2011 09:54:43 GMT

Hello,

Sandra, out-of-curiosity, is this DB specific? Or this is kind of a thumb-rule?

BR,

Suhas

Re: Update query with CL_SQL_STATEMENT class

Sandra_Rossi — Thu, 06 Oct 2011 18:21:41 GMT

Hi Suhas,

Based on Oracle, I see it conforms almost completely ISO/IEC 9075 ([Oracle and Standard SQL|http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_standard_sql.htm#10293])

UPDATE seems to conform completely [UPDATE SET clause|http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2087215]

So using a comma to separate columns is true for every SQL language that conforms ISO/IEC 9075

Sandra

PS: of course SAP's "Open SQL" language does not conform at all

Re: Update query with CL_SQL_STATEMENT class

SuhaSaha — Fri, 07 Oct 2011 10:33:19 GMT

Hello Sandra,

So basically we need to check the semantics of the SQL statement beforehand.

of course SAP's "Open SQL" language does not conform at all

Lol

Cheers,

Suhas