From: Henrik Tougaard <[EMAIL PROTECTED]>
To: Kevin Cornmell <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Subject: RE: how do I insert a null value into an Ingres table ?
Date sent: Thu, 10 May 2001 13:58:27 +0200
> 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.
but what about this one:
my @row;
$sth->bind_param(1, $row[0], {TYPE => SQL_INTEGER};
$sth->bind_param(2, $row[1], {TYPE => SQL_VARCHAR};
..and so on... and then
while(@row=$sth1->fetchrow_array) {
$sth2->execute(@row);
}
or this one
$sth->bind_param(1, $value1, { TYPE => SQL_INTEGER });
$sth->bind_param(1, $value2, { TYPE => SQL_VARCHAR });
.............
while ( ($value2, $value2, .....) = $sth1->fetchrow_array) {
$sth2->execute($value1, $value2, .....);
}
I don't know if the first one works (haven't tried yet), but the
second one works for sure. If it doesn't, the driver may expect the
sql data types as an integer, not as a constant.
> I would go for the COPY solution.
If portability is not an issue, so would I.
> Henrik Tougaard, FOA, Denmark.
Bodo
[EMAIL PROTECTED]
Dr. med. Bodo Eing
Institut fuer Medizinische Mikrobiologie
Klinische Virologie
v.-Stauffenbergstr. 36
48151 Muenster
Germany
Phone: ++49 251 7793 111 Fax: ++49 251 7793-104
