Hi
Sorry for the inconvenience of yat another request for help on long blob
insertion:
I cannot manage to get MS access accept strings over 255 in a field
defined as TYPE MEMO;
despite the FAQS etc telling me to use placeholders, and to use
bind_param;
both the bind and the execute fail; the dbase has the field set up
correctly (see table_info output)
Any suggestions ?
By the way, please also note that I also never see referenced that the
DB field names need [BRACKETS]
to be recognized by MS ACCESS as valid field names: reason ???.
Are all the failures due to way I connect?
Thanks for your help
Frederik AA de Jonge
(non prof programmer in medical environement)
[EMAIL PROTECTED]
CODE =>
use DBI qw(:sql_types);
my $intable = "DateSR";
my @fieldnames = qw(Date StrNum RstNum LongStr);
my
$dsn="E:/MS_Office_docs_2002/access_db/Fred_Private/t_p_dbi.mdb";
#direct connect to DB file
my $dbh = DBI->connect("DBI:ODBC:driver=Microsoft Access Driver
(*.mdb);dbq=$dsn",
{ RaiseError => 1, AutoCommit => 0 })
or die "Connect Error ODBC : $DBI::errstr";
#MAKE SQL STATEMENT
#IMPORTANT : THE DB field names need [BRACKETS] to be
recognized by MS ACCESS
#flds are already available in @fieldnames
my $fields = join(', ', map {"[$_]"} @fieldnames);
my $places = join(', ', ('?') x @fieldnames);
my $sql = "INSERT INTO $intable ($fields) VALUES ($places)";
my $sth = $dbh->prepare($sql);
#INSERT THE RECORDS
#THIS BIT inserts OK, I get the data in access
for my $dt (sort keys %hdata) {
my @tmp = ($dt, $hdata{$dt}->{StrNum}, $hdata{$dt}->{RstNum});
#undef is NULL
$sth->execute(@tmp, undef);
}
##256 bytes WILL NOT work
my $longstr =
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccceeee
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
AAAAAAAAAAAAAAxxx";
$sth->bind_param(1,"2002-04-01", SQL_DATE);
$sth->bind_param(2,"S11", SQL_VARCHAR);
$sth->bind_param(3,"R167", SQL_VARCHAR);
(line 130) $sth->bind_param(4, $longstr, SQL_LONGVARCHAR);
line (134) $sth->execute();
ERROR =>
DBD::ODBC::st bind_param failed: [Microsoft][ODBC Microsoft Access
Driver]Inval
id precision value (SQL-S1104)(DBD: _rebind_ph/SQLBindParameter
err=-1) at F:\
myperl\pl\tp\tp031.pl line 130.
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access
Driver]Invalid
use of null pointer (SQL-S1009)(DBD: st_execute/SQLExecute err=-1)
at F:\myper
l\pl\tp\tp031.pl line 134.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
OS =>
Windows XP
MS Acces 97
perl =>
F:\myperl\pl\tp>perl -v
This is perl, v5.6.1 built for MSWin32-x86-multi-thread
(with 1 registered patch, see perl -V for more detail)
Copyright 1987-2001, Larry Wall
Binary build 631 provided by ActiveState Tool Corp.
http://www.ActiveState.com
Built 17:16:22 Jan 2 2002
ppm =>
DBD-ODBC [0.28 ] ODBC driver for the DBI module.
DBI [1.27 ] Database independent interface for Perl
output from dbh->table_info
NUM_OF_FIELDS = 5
FldName Type Prec Scale Null?
ID 4 10 0 no
DATE 11 19 0 yes
STRNUM 12 50 0 yes
RSTNUM 12 50 0 yes
LONGSTR -1 2147483647 0 yes