Re: [GENERAL] ECPG: inserting NULL values when using prepared statements

[Michael Meskes]

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: [EMAIL PROTECTED]
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 [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] ECPG: inserting NULL values when using prepared statements

[Anders Nilsson]

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