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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly