| Subject: | Problems with Access memo fields when using DBD::ODBC 1.21 |
Hello. Whenever I try to insert data in a memo field in an Access
database, I get the error
DBD::ODBC::st bind_param failed: Microsoft ODBC Microsoft Access Driver
Invalid precision value (SQL-HY104)
when I try to bind the variable using DBI::SQL_LONGVARCHAR. I am trying
to bind it dynamically by using column_info to look up the field type,
but even the simplified version of the code doesn't work (see attached).
It used to before I upgraded DBD-ODBC to version 1.21 and DBI to 1.608.
The string I am trying to insert is about 3000+ characters long. Any
help is appreciated. Thanks and best wishes.
| Subject: | snnipet.txt |
#!/usr/bin/perl -w
use strict;
use DBI;
# Defines paths to databases
my $dbpath = 'C:\\MyPath\\MyDB.mdb';
# Establish connection with databases
my $sth = DBI->connect("dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb); dbq=$dbpath ", {RaiseError => 1})
|| print "Cannot connect to database.\n$DBI::errstr";
$sth->{LongReadLen} = 7000;
# Data to be inserted
my @data = (
"Locus",
"Organism",
"1",
"3199",
"GAAGCGGGATGAGCTCAAACTTAAAATCTCTGTTGCTTGCAACAGCGAATTGTAGTCTCGAGAAGCGTTTTCAAGGCGGATGCACAGTGCTCAAGTTGCTTGGAACGGCACATCGTAGAGGGTGACAATCCCGTACGTGGCACTGTGTACTGTTCACGATTCGCTTTCTATGAGTCGGGTTGCTTGGGAATGCAGCCCAAAATGGGAGGTAAACTCCTTCTAAAGCTAAATATTGGCACGAGACCGATAGCGAACAAGTACCGTGAGGGAAAGATGAAAAGCACTTTGAAAAGAAAGTTAATAGTACGTGAAACCGTTAGTAGGGAAGCGCATGGAATTAGCAATACACTGTCGAGATTCAGGCGGGCGGCGATTGGTACGGCTGTTGTACGGATCTGAATGGACCGTCGGTGGTCGTCACTGGTTGCTGCCTGTTGCATTTCCCGGCAGTGTTCGTCAACAGGTGTTGGAACCGAGCGATAAGCCCCGCAGGAAGGTGGCTGGCTTCGGCTAGTGTTATAGCCTGTGGTGTGCGAGCTCGGGTCCGACAGAGGGGTTGCGGCACATGCTCTTTTGGGCTGGTCTCGTCTCTCTCGGCTGGTTGTCGACTATGGCGGACTGCGTGCAGTGCGCTTGAACTGCTGCCGGTCGTCGAGGGGCATCGGACACACATTGTGCCAAGGTTGTTGGCGGTCATATGGTTTCATACGACCCGTCTTGAAACACGGACCAAGGAGTCTAACATGTGTGCGAGTCTTTGGGTGATCGAAACCCGCAGGCACAATGAAAGTAAAGGCTGCTTGCAGCTGAAGTGAGATCTCCCGGTCTCGGCCGGGGGCGCATCATTGACCGACCTATTCTACTCCTAGAAAGGTTTGAGTAAGAGCACATCTGTTGGGACCCGAAAGATGGTGAACTATGCTTGAGTAGGGCGAAGCCAGAGGAAACTCTGGTGGAGGCTCGTAGCGATTCTGACGTGCAAATCGATCGTCAAACTTGAGTATAGGGGCGAAAGACTAATCGAACCATCTAGTAGCTGGTTCCCTCCGAAGTTTCCCTTAGGATAGCTGGAACTCGGAACAGTTTTATCAGGTAAAGCGAATGATTAGAGGTCTTAGGATTGAAACAATCTTAACCTATTCTCAAACTTTAAATTGGTAAGAAGCCCGGCTTGCTTAACTGAAGCAGGGCACAGAATGAGAGTTCTTAGTGGGCCATTTTTGGTAAGCAGAACTGGCGATGCGGGATGAACCGAACGCTGAGTTAAGGCGTCTAAATCGACGCTCATCAGACCCCACAAAAGGTGTTGGTTGATCTAGACAGCAGGACGGTGGCCATGGAAGTCGGAATCCGCTAAGGAGTGTGTAACAACCCACCTGCCGAATCAACTAGCCCTGAAAATGGATGACGCTCAAGCGTCGTGCCTATACTCAGCCGTCAACGTAAATAGCGAAGCGTTGACGAGTAGGAGGGCGTGGGGATCGTGACGCAGCCTTTGGCGTGAGCCTGGGTGAAACGGTCTCTAGTGAAGATCTTGGTGGTAGTAGCAAATATTCAAATGAGAACTTTGAAGACCGAAGTGGAGAAAGGTTCCATGTGAACAGCAGTTGGACATGGGTTAGTCGATCCTAAGAGATAGGGAAACTCCGTTTCAAAGTGTCCGATCTTGGACCGTTTATCGAAAGGGAATCGGGTTAATATTCCCGAACCAGAACGCGGATATTCTAGCCTCTCGGGGTTAGATGTGCGGTAACGCAACTGAACTCGGAGACGTCGGCAGGGGCCCTGGGAAGAGTTCTCTTTTCTTGTTAACGACCTGACACCATGGAATCTGATTGCCAGGAGATATGGTTTGATGGTCGGTAAAGCACCACACTTCTTGTGGTGTCCGGTGCGCTCCTGAAGGCCCTTGAAAATCCGAGGGAAAGATTGATTTTCGCGTCTGTTCGTACTCATAACCGCAGCAGGTCTCCAAGGTGAGCAGCCTCTGGTCGATAGAACAATGTAGGTAAGGGAAGTCGGCAAAATAGATCCGTAACTTCGGGAAAAGGATTGGCTCTAAGGATTGGGTCTGTCGGGCTGAGACTTGAAGCGGGCGGCACCGACTCGGACTGGCTGTGGCCTCTCGGGGCTATGGTTGGACTGGGAAGGAACTGCGCGTGGATTGGCCCAGCTATGCTCGCAAGAGCAGTTCGGCAGGCAATTAACAATCAACTTAGAACTGGTACGGACAAGGGGAATCCGACTGTTTAATTAAAACAAAGCATTGCGATGGCCGGAAACGGTGTTGACGCAATGTGATTTCTGCCCAGTGCTCTGAATGTCAAAGTGAAGAAATTCAACCAAGCGCGGGTAAACGGCGGGAGTAACTATGACTCTCTTAAGGTAGCCAAATGCCTCGTCATCTAATTAGTGACGCGCATGAATGGATTAACGAGATTCCCACTGTCCCTATCTACTATCTAGCGAAACCACAGCCAAGGGAACGGGCTTGGCAAAATCAGCGGGGAAAGAAGACCCTGTTGAGCTTGACTCTAGTCTGACTCTGTGAAAAGACATAGGAGGTGTAGAATAGGTGGGAGCAGCAATGCAACAGTGAAATACCACTACTCTTATAGTTTTTTTACTTATTCGATTGAGCGGAAGCGAGCTTCACGGCTCATTTTCTAGAATTAAGGCCCCATTGGCGGGTCGATCCGTGTCGAAGACACTGTCAGGTTGGGAGTTTGGCTGGGGCGGCACATCTGTCAAATGATAACGCAGGTGTCCTAAGGTGAGCTCAATGAGAACGGAAATCTCATGTAGAACAAAAGGGTAAAAGCTCACTTGATTTTGATTTTCAGTATGAATACAAACTGTGAAAGCATGGCCTATCGATCCTTTAGTCTTTAGGAGTTTTAAGCTAGAGGTGTCAGAAAAGTTACCACAGGGATAACTGGCTTGTGGCAGCCAAGCGTTCATAGCGACGTTGCTTTTTGATCCTTCGATGTCGGCTCTTCCTATCATTGTGAAGCAGAATTCACCAAGTGTTGGATTGTTCACCCACTAATAGGGAACGTGAGCTGGGTTTAGACCGTCGTGAGACAGGTTAGTTTTACCCTACTGATGAAGTGTTGTTGCAATAGTAATTCTGCTCAGTACGAGAGGAACCGCAGATTCAGACAATTGGCATTTGCACTTGCTTGAAAAGGCAATGGTG");
# Bind line
my $dbInsert = $sth->prepare("INSERT INTO Table VALUES ?, ?, ?, ?, ?", {RaiseError => 1}) || die ($DBI::errstr);
$dbInsert->bind_param(1, $data[0], DBI::SQL_VARCHAR);
$dbInsert->bind_param(2, $data[1], DBI::SQL_VARCHAR);
$dbInsert->bind_param(3, $data[2], DBI::SQL_INTEGER);
$dbInsert->bind_param(4, $data[3], DBI::SQL_INTEGER);
$dbInsert->bind_param(5, $data[4], DBI::SQL_LONGVARCHAR);
# Inserts and disconnect
$dbInsert->execute(@data) || die "$DBI::errstr\n";
$dbInsert->finish;
$sth->disconnect;