Prepare statement with null values

[Former Member]

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( ).

[former_member201285]

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

[Former Member]

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.

[former_member201285]

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.

[Former Member]

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.