insert LONG / BLOB in MS ACCESS == Brackets around names?

[Frederik A.A. de Jonge]

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