DBI problem with Access's Memo field

cromedome has asked for the wisdom of the Perl Monks concerning the following question:

I have encountered a problem and was wondering if the high priests could diagnose it and let me know how to fix. Basically, I am trying to write to an Access memo field and find that I cannot go beyond 255 characters (even with LongReadLen set to a higher value). I have included a sample script that demonstrates the problem along with the stdout. The specific error I get is: PERL DBD Error - Invalid precision value (SQL-S1104)(DBD: _rebind_ph/SQLBindParameter err=-1 I am running: Win98 DBI.pm (v1.14) ODBC (v0.28) PERL (5.6.0.618) Any help would be appreciated. Thanks.

use File::Basename;
use DBI;

my ($dbase) = @ARGV;

# Access Table definition
#    Table name=blob
#    field1=recid,integer
#    field2=name,text
#    field3=mymemo,memo
#
# Establish database and connect...
my $DSN = "driver=Microsoft Access Driver (*.mdb);dbq=$dbase";
my $dbh = DBI->connect("dbi:ODBC:$DSN","","") or die "$DBI::errstr\n";

# Set up longreadlen so we can do more than the default of 80...
# Really does not solve the problem, but here it is...
$dbh->{LongReadLen} = 2000;

# Build some strings
my $fifty = "12345678901234567890123456789012345678901234567890";
my $twoHundredFifty = "$fifty$fifty$fifty$fifty$fifty";
my $twoHundredFifty3 = join '',"$twoHundredFifty","123";
my $twoHundredFifty4 = join '',"$twoHundredFifty","1234";
my $twoHundredFifty5 = join '',"$twoHundredFifty","123**";
my $twoHundredFifty6 = join '',"$twoHundredFifty","123***";
my $fiveHundred3 = join '',"$twoHundredFifty","$twoHundredFifty","***"
+;

# Do some clean up...
$dbh->do("delete from blob");

# Define sql statement for updating memo field.
$sql = "insert into blob (name,mymemo) values(?,?)";

# Bind values to statement and execute...
# This one should work...
$dbh->do($sql,undef,"first",$twoHundredFifty5);
my $error = $dbh->errstr;
print("Errorstring 1 = >$error<\n");

# Bind values to statement and execute...
# This one will fail...
$dbh->do($sql,undef,"second",$twoHundredFifty6);
my $error = $dbh->errstr;
print("Errorstring 2 = >$error<\n");

# Define sql statement for updating memo field.
$sql = "update blob set mymemo=? where name=?";

# Bind values to statement and execute...
# This one fails...
$dbh->do($sql,undef,$twoHundredFifty6,"first");
my $error = $dbh->errstr;
print("Errorstring 3 = >$error<\n");

exit;

STDOUT
Errorstring 1 = ><
Errorstring 2 = >[Microsoft][ODBC Microsoft Access Driver]Invalid prec
+ision value  (SQL-S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)<
Errorstring 3 = >[Microsoft][ODBC Microsoft Access Driver]Invalid prec
+ision value  (SQL-S1104)(DBD: _rebind_ph/SQLBindParameter err=-1)<
[download]

Comment on DBI problem with Access's Memo field Download Code

Replies are listed 'Best First'.
Re: DBI problem with Access's Memo field by FouRPlaY (Monk) on Oct 31, 2000 at 23:04 UTC
I don't know how much help it'll be, but there's a good tutorial on interacting with Access here. FouRPlaY Learning Perl or Going To Die Trying	[reply]
Re: DBI problem with Access's Memo field by cadfael (Friar) on Nov 01, 2000 at 20:08 UTC
Why are you getting an "Invalid precision value" while dealing with character data? I have no experience using DBD::ODBC to query an Access database, but it seems to me that the memo field is somewhat like the "text" datatype in SQL server and Sybase. i.e. it holds quantities of alphanumeric data and is stored in a different manner than the rest of the table rows You might want to look at the fetchall_arrayref() in the DBI manual (pp 131-135 in Programming the Perl DBI. This will return a hash of all the data, and you can then access the rows sequentially, and the elements within the rows randomly. I use something similar with Sybase text fields, and it works just fine. It may be worth a try. Good Luck. ----- "Computeri non cogitant, ergo non sunt"	[reply]