topic Re: Prepare statement with null values in Application Development and Automation Discussions

Prepare statement with null values

Former Member — Wed, 29 Oct 2014 12:27:06 GMT

Hello,

I'm using cl_sql_prepared_statement from cl_sql_connection classes to insert data to a non SAP database. The problem I'm finding is that I'm not able to inert null values to a column: if the value is initial it sets an empty string or if I try to set 'NULL' to the column it sets 'NULL' as the content of a varchar.

Is there any way to insert null value to a column using prepared statements ?

An example of the code I'm using, I want to insert name1 as NULL, not '' or 'NULL'.


 CLEAR lv_insert-name1.
 lv_insert-status_sap = 1.

 p_con_ref = cl_sql_connection=>get_connection( cv_con_name ).
 l_stmt = 'INSERT dbo.contacts ( Name1, Status_SAP ) VALUES ( ?, ? )'.
 l_prepstmt_ref = p_con_ref->prepare_statement( statement = l_stmt ).
 *  CATCH cx_sql_exception.    "

 GET REFERENCE OF lv_insert INTO l_dref. " set output table
 l_prepstmt_ref->set_param_struct( l_dref ).

 l_prepstmt_ref->execute_update( ).

Re: Prepare statement with null values

former_member201285 — Wed, 29 Oct 2014 12:59:25 GMT

Hi Andrés,

this code snippet could help you:

DATA: dref_null_indicator TYPE REF TO int2.

CONSTANTS: gc_null_indicator TYPE int2 VALUE '-1'.

* Pass a null value

GET REFERENCE OF my_field INTO dref.

GET REFERENCE OF gc_null_indicator INTO dref_null_indicator.

prepstmt_ref->set_param( data_ref = dref

ind_ref = dref_null_indicator ).

Regards,

Ulrich

Re: Prepare statement with null values

Former Member — Wed, 29 Oct 2014 14:55:02 GMT

It stills uses the empty value of my_field and not of the ind_ref with the null indicator.

Besides it would be nice something that does it from SET_PARAM_STRUCTURE so I don't have to go field by field. Or at least if I could use the SET_PARAM after pasing the structure so I just have to reset the initial fields to null.

Re: Prepare statement with null values

former_member201285 — Thu, 30 Oct 2014 06:58:57 GMT

I'm surprised about this behaviour, because the code is a snippet of a productive program. I haven't got any idea why the null value is not set in your case.

Regarding your wishes for more convenience, I agree with you that it is rather much overhead. Sometimes, coding the native sql (without OO-approach) is faster and there you can specify the null values directly - but this is only possible if the null values are set for the same fields in each case.

Re: Prepare statement with null values

Former Member — Fri, 31 Oct 2014 10:23:39 GMT

Thank you for your help, finally we have decided to use native SQL, the 'only' reason we were trying to use the OO approach was to avoid SQL Injection and give the code a clean and more standarized look.