Once you have associated a type with a placeholder in a statement, DBI
remembers the type, so you could do something like this (untested):
use DBI ( :sql_types );
# _Always_ check for errors
$dbh -> {RaiseError} = 1;
my $sth = $dbh -> prepare( ... );
# This value gets replaced in the execute(), but the type remains
$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
while( $row=$sth1->fetchrow_arrayref ) {
$sth2->execute( @$row );
}
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Henrik Tougaard" <[EMAIL PROTECTED]>
To: "Kevin Cornmell" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, May 10, 2001 04:58
Subject: RE: how do I insert a null value into an Ingres table ?
> From: "Kevin Cornmell" <[EMAIL PROTECTED]>
> > I need to select * from x, and then insert into y (an
> identical table on
> > another database), nullvalues are
> > causing me grief...
> >
> > Can somebody explain if you can do this with placeholders.
> ( if at all ).
> >
> > The message "Sorry NULLS not allowed unless TYPE defined"
> > is trying to tell me something,
> > do I just need to define a TYPE, how do I do that ?
> >
> You can do this in two ways:
>
> 1) the simple: use the Ingres COPY statement.
> 2) the complicated: the problem is that the placeholder
> binding has no way of knowing what datatype your variables
> are when they are NULL (ie undef). You need to add the third
> argument to bind_params,
> ie. $sth->bind_param($no, $value, $type)
> where $type is SQL_INTEGER, SQL_VARCHAR or so on.
> From the docs:
> Data Types for Placeholders
>
> The `\%attr' parameter can be used to hint at the data type the
> placeholder should have. Typically, the driver is only interested in
> knowing if the placeholder should be bound as a number or a string.
>
> $sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
>
> This makes the use of $sth->execute(@row) impossible, that
> is you cannot do:
> while(@row=$sth1->fetchrow_array) {
> $sth2->execute(@row);
> }
> but have to do a more complicated loop.
>
> I would go for the COPY solution.
