Foxpro bind_param
From: Stephen Toney (toney_at_systemsplanning.com)
Date: 09/23/04
- Next message: Jeff: "Re: nvarchar2 issues with DBD::Oracle 1.16RC7"
- Previous message: Ron Reidy: "RE: DBI/DBD for Oracle9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 23 Sep 2004 09:46:26 -0400 To: dbi-users@perl.org
Hi everyone,
I'm using DBD::ODBC to insert into a Visual FoxPro table. Foxpro
misunderstands the param type if the statement is prepared with
placeholders. I'm reading values from one sth and using another (sth2)
to insert.
This works but is inefficient:
$sth = $::dbh->prepare ($mainquery);
$sth->execute;
my ($key, $subset);
$sth->bind_columns(\$key, \$subset);
my $seq = 0;
while ($sth->fetch)
{
$seq++;
my $insertquery = sprintf
("insert into sruser (userid, seq, key, subset) " .
"values ('%s', %lu, %lu, %lu)",
cookie ("user"), $seq, $key, $subset);
my $sth2 = $::dbh->prepare ($insertquery);
$sth2->execute ();
}
Naturally I should be preparing the query outside the loop. But this
doesn't work:
$sth = $::dbh->prepare ($mainquery);
$sth->execute;
my ($key, $subset);
$sth->bind_columns(\$key, \$subset);
my $insertquery = sprintf
("insert into sruser (userid, seq, key, subset) " .
"values ('%s', ?, ?, ?)",
cookie ("user"));
my $sth2 = $::dbh->prepare ($insertquery);
my $seq = 0;
while ($sth->fetch)
{
$seq++;
$sth2->bind_param (1, $seq, {TYPE => SQL_INTEGER});
$sth2->bind_param (2, $key, {TYPE => SQL_INTEGER});
$sth2->bind_param (3, $subset, {TYPE => SQL_INTEGER});
$sth2->execute ();
}
The Foxpro driver returns the error "Data type mismatch". The columns
in the table are specified as I (integers).
By temporarily changing the columns to CHAR in the table, the correct
values are inserted, even when the type is specified as SQL_INTEGER in
the bind_param! So clearly driver is misunderstanding or ignoring the
TYPE in the bind_param.
The CodeBase ODBC driver handles the second example correctly, so it's
not a table issue or a DBI issue but a driver issue.
Any suggestions would be greatly appreciated!
Stephen Toney
Systems Planning
toney@systemsplanning.com
http://www.systemsplanning.com
- Next message: Jeff: "Re: nvarchar2 issues with DBD::Oracle 1.16RC7"
- Previous message: Ron Reidy: "RE: DBI/DBD for Oracle9i"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
