Re: ECPG: inserting NULL values when using prepared statements

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(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!