Thread: ECPG: inserting NULL values when using prepared statements

The situation:
    A loop that inserts thousands of values into a table.
    In hopes of optimizing the bunches of inserts, I prepared
    a statement like the follows:

    "insert into some_table_name ( value, id, date ) values ( ?, ?, ? )"

    then executing the prepared statement identifier numerous times
using already declared host variables.

The problem:
     Some of these values are null. Is there a way to specify a null
     value without having to rewrite the statement with the explicit NULL
     replacing the "?" ?
     The section on indicator values (30.6.4 in the 8.1.3 help) does
     mention:

    "To be able to pass null values to the database or retrieve null
values from the database, you need to append a second host variable
specification to each host variable that contains data."

     (If this works) if I were to use indicator variables when
inserting, what would the syntax be? So far, indicator variables work
great when fetching, though I just can't seem to get it right when
inserting / updating. Or, if there is another method to specify NULL
values, that would be great as well.

Any help would be appreciated.

Thanks,
-Anders Nilsson

On Mon, Apr 02, 2007 at 11:53:50AM -0500, Anders Nilsson wrote:
> The situation:
>    A loop that inserts thousands of values into a table.
>    In hopes of optimizing the bunches of inserts, I prepared

Sorry, but that won't work. ECPG only simulates statement preparation.

>    a statement like the follows:
>
>    "insert into some_table_name ( value, id, date ) values ( ?, ?, ? )"
>
>    then executing the prepared statement identifier numerous times
> using already declared host variables.
>
> The problem:
>     Some of these values are null. Is there a way to specify a null
>     value without having to rewrite the statement with the explicit NULL
>     replacing the "?" ?

Yes, use an indicator when instanciating the statement.

>     (If this works) if I were to use indicator variables when
> inserting, what would the syntax be? So far, indicator variables work
> great when fetching, though I just can't seem to get it right when
> inserting / updating. Or, if there is another method to specify NULL
> values, that would be great as well.

Just an example from the regression suite:

/* use indicator in insert */
exec sql insert into test (id, str, val) values ( 2, 'Hi there', :intvar :nullind);

So this is essantially the same as with fetch.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!