http://www.perlmonks.org?node_id=491231

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

Hi,

I want to insert new values into a table of a MS-Access-"database"

The table looks like
ID => integer Description => memo


And I use the following (test-)code:
#! /usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); my $statement = 'INSERT INTO table (ID,description) VALUES(?,?)'; my $sth = $dbh->prepare($statement); for(2..1000){ $sth->execute($_,'x' x $_) or die $dbh->errstr(); }


All values up to a description length of 255 were inserted, but the the entry with a length of 256 lets the script die with the following message:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver +]Invalid precision value (SQL-HY104)(DBD: _rebind_ph/SQLBindParameter + err=-1)

Access says that a memofield can handle up to ~ 65000 characters...
What can I do to store descriptions with a length greater than 255 characters?

Replies are listed 'Best First'.
Re: Accessing MS-Access memofield via DBI
by inman (Curate) on Sep 12, 2005 at 13:09 UTC
    You need to import the SQL type constants from DBI and specify that SQL_LONGVARCHAR as the type of the data to be added to the memo field.

    The code below extracts the news from the Google RSS feed and inserts the stories into an Access database via DBI and ODBC. The database connection is made on the fly without needing configuration. The description item is a memo field in the Access database.

      It's great! Thanks a lot!
      Can I do the binding dynamically depending on the datatype of the field??

      I tried
      #! /usr/bin/perl use strict; use warnings; use DBI ':sql_types'; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); my $col = 'Testcol'; my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; $sth->bind_param(1,$var,$coltype); $sth->execute($var) or die $dbh->errstr(); } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; }


      But I got the following errormessage:
      DBI::st=HASH(0x3e9c2dc)->bind_param(...): attribute parameter 'SQL_INTEGER' is not a hash ref at C:/access.pl line 19
        This piece of code works:
        #! /usr/bin/perl use strict; use warnings; use DBI ':sql_types'; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); my $col = 'Testcol'; my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; no strict 'refs'; $sth->bind_param(1,$var,&{"DBI::$coltype"}); use strict 'refs'; $sth->execute($var) or die $dbh->errstr(); } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; }


        But when I split the code in "script" and "module" it does not work.
        I've tried this:
        #! /usr/bin/perl use strict; use warnings; use lib qw(.); use DbiTest; use DBI ':sql_types'; my $col = 'Testcol'; DbiTest->new($col);


        module:
        package DbiTest; use strict; use warnings; use DBI ':sql_types'; sub new{ my ($class,$col) = @_; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*. +mdb);dbq=db1.mdb',user,pass); my $statement = "INSERT INTO table (`$col`) VALUES(?)"; my $coltype = get_type($col,$dbh); $coltype = 'SQL_'.$coltype; my $sth = $dbh->prepare($statement); for(2..1000){ my $var = <STDIN>; chomp $var; no strict 'refs'; $sth->bind_param(1,$var,&{"DBI::$coltype"}); use strict 'refs'; $sth->execute($var) or die $dbh->errstr(); } } sub get_type{ my ($name,$dbh) = @_; my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name); my $hashref = $sthcolinfo->fetchrow_hashref(); return $hashref->{TYPE_NAME}; } 1;


        With that code the script dies with the following errormessage:
        Usage: SQL_LONGVARCHAR() at DbiTest line 21
Re: Accessing MS-Access memofield via DBI
by InfiniteSilence (Curate) on Sep 12, 2005 at 12:22 UTC
    Well, I'm not sure what DBI's issue is with MSAccess, but I use Win32::ODBC to write to memo fields in Access using a DSN without a problem:

    my ($xsql) = new Win32::ODBC('VER2004R2'); $xsql->Sql($sql); $xsql->FetchRow(); my (%ahash) = $xsql->DataHash(); if (($ahash{Status} eq 'Solved') && ($ahash{'Assigned_to'} ne '') && ($ahash{'Resolved_by'} ne '') && ($ahash{'Verified_by'} ne '') ) { $sql = 'INSERT INTO tblBasicProblems_history ( PGNFIL +ENAME, '; $sql .= ' [Date], Description, Author, Status, Assign +ed_to, Resolution, '; ... $xsql->Sql($sql); ...

    Celebrate Intellectual Diversity

      I do not use Win32::ODBC, because the Tool should connect to several database systems and writing plug-ins should also be easy. To simplify a cross-database-script it is very useful to use DBI, because you have unified methods for (nearly) all db-systems...
Re: Accessing MS-Access memofield via DBI
by holli (Abbot) on Sep 12, 2005 at 12:36 UTC
    255 is the max length of a string field. Are you sure you really have a memo field?

    Also you might give the DBD::ADO module a try. No need to use ODBC when a "native" driver is available.


    holli, /regexed monk/
      Yes, I'm sure that I have a memofield. And dumping the table (extract info with DBI), I get the following information:
      columnname: description datatype: LONGCHAR(1073741823)

      And to ensure that I use the right datatype, I had a look at the table - it is memo. I will give DBD::ADO a try...
      Yes, I'm sure that I have a memofield. And dumping the table (extract info with DBI), I get the following information:
      <code> columnname: description datatype: LONGCHAR(1073741823) <code>

      And to ensure that I use the right datatype, I had a look at the table - it is memo.
Re: Accessing MS-Access memofield via DBI
by marto (Cardinal) on Sep 12, 2005 at 12:21 UTC
    Hi,

    You need to set DBIs LongReadLen() attribute.
    Have a read a the the documentation.
    Try something like:
    !/usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber (*.md +b);dbq=db1.mdb',user,pass); $dbh->{'LongReadLen'} = 6000; # For have a maximum of 6000 chars... #... and so on....

    Hope this helps,

    Martin
      The LongReadLen attribute is not for inserting values (from documentation: The LongReadLen attribute only relates to fetching and reading long values; it is not involved in inserting or updating them.)
        Ack,

        Thanks for pointing out my brain burp of the day.
        InfiniteSilence and holli have both replied to the OPs first post with solutions.

        Martin