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

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

Hello! Here is what I am trying to do. I want users of my website to sign my guestbook which has to textarea fields for comments. What I have come to realize is that when I post more than around 256 characters into a Memo field in an MS Access database, it doesn't insert anything (no record at all). There was a similar post a looong time ago but it doesn't sound like there was any definite solution. I know most people will not be using MS Access, but surely someone else has needed to use it and come across this problem. For your reference, the previous post was: http://www.perlmonks.com/index.pl?node_id=39296&lastnode_id=3989 Now, for my code:
#!/usr/bin/perl use strict; use CGI::Carp qw(fatalsToBrowser); use CGI qw(:all); use CGI; use DBI; my $myCGI = new CGI; print "Content-type: text/html\r\n\r\n"; my $name = $myCGI->param('txtName'); my $email = $myCGI->param('txtEmail'); my $country = $myCGI->param('txtCountry'); my $website = $myCGI->param('txtWebsite'); my $publicmessage = $myCGI->param('txtPublic'); my $privatemessage = $myCGI->param('txtPrivate'); my $dbh = DBI->connect('DBI:ODBC:whatever', 'whatever', 'whatever'); #$dbh->do("set textsize 25000"); #$dbh->{LongReadLen} = 50000; #$dbh->{LongTruncOk} = 1; my $sth = $dbh->prepare(q{ INSERT INTO tblGuestbook (name, email, country, website, publicmessage, privatemessage, IP) values (?,?,?,?,?,?,?) }); $sth->execute($name,$email,$country,$website,$publicmessage,$privateme +ssage,$ENV{REMOTE_ADDR}); $sth->finish; $dbh->disconnect;
Notice the three commented fields which I have tried to use with no luck. I would greatly appreciate any comments (and code!) from anyone who has a solution to this. Thank you! Andrew

Replies are listed 'Best First'.
Re: Cannot insert into MS Access Memo field with DBI
by simon.proctor (Vicar) on Jan 06, 2002 at 18:37 UTC
    In their infintite wisdom, Microsoft decided to make Memo fields like Blobs but with some additional bits and bobs on top.

    What this means is that you have to connect to the database in a totally different way. This is not a fault of the DBI or Perl as it catches you out in ASP code just as easily. To solve this, as far as I can see, you should use Win32::OLE and build recordsets to access your data. If not, then you'll have to save your data to disk and reference the filename in the database.

    If you want the memo fields, the code (in VBScript) is as follows:
    Set RS = Server.CreateObject("ADODB.RecordSet") RS.CursorLocation = adUseClient RS.Open table_name_or_sql_query, your_connection_object, adOpenKeyset

    I took that from here. Now looking at the constants stuff, we need to define adUseClient and keyset which we can set as :
    use constant adUseClient => 3; use constant adOpenKeyset => 1;


    So all we need to do now is create the recordset and the connection object. Full code
    use Win32::OLE; use strict; use constant adUseClient => 3; use constant adOpenKeySet => 1; my $db_connection = new Win32::OLE('ADODB.Connection'); my $rs = new Win32::OLE("ADODB.Recordset"); my $db_datasource = 'Driver={Microsoft Access Driver (*.mdb)}; +'; # My database is called test.mdb and is in the same dir. $db_datasource .= 'DBQ=test.mdb'; # Connect to the database and tie the recordset object to # the database. $db_connection->Open($db_datasource); # Set the connection doohickey $rs->CursorLocation(adUseClient); $rs->Open('test', $db_connection, adOpenKeySet);

    I did a quick test on my win2k machine with an access2000 database and it ran without error (note that this is a DSN less connection). I hope that gives you a starting point :)
    Cheers - Simon
Re: Cannot insert into MS Access Memo field with DBI
by drewbert2000 (Initiate) on Jan 09, 2002 at 02:51 UTC
    I managed to find a solution to this problem without using another module (but thank you for the suggestion anyway). I imagine that not many people are writing TO an Access DB which is probably why there are no posts regarding this exact situation. Here is the code to make it work:
    my $dbh = DBI->connect('DBI:ODBC:whatever', 'whatever', 'whatever'); my $sql = q{ INSERT INTO tblGuestbook (name, email, country, website, publicmessage, privatemessage, IP) values (?,?,?,?,?,?,?) }; my $sth = $dbh->prepare($sql); # Do this line for each one being inserted into a Memo field. $sth->bind_param( 4, $publicmesage, DBI::SQL_LONGVARCHAR ); $sth->bind_param( 5, $privatemesage, DBI::SQL_LONGVARCHAR ); $sth->execute($name,$email,$country,$website,$publicmessage,$privateme +ssage,$ENV{REMOTE_ADDR}); $sth->finish; $dbh->disconnect;
    Hope that helps someone out there... =) Drewbert2000 08-Jan-2002 ===========
      I don't know if something changed in subsequent versions of DBI:ODBC, but I got this to work only after counting the order of my fields starting with 1 instead of 0. I.E. for the above example to work for me it would need to be:
      $sth->bind_param( 5, $publicmesage, DBI::SQL_LONGVARCHAR ); $sth->bind_param( 6, $privatemesage, DBI::SQL_LONGVARCHAR );
        This sounds crazy but I had this problem trying to put a string into the ms access memo field and after pulling out my hair I got the data in by putting it in an array. edit: array is NOT NEEDED. just do the blob support i.e.:

        $dbh->{LongReadLen} = 20000; #(change 20000 to your needs)
        $dbh->{LongTruncOk}=1; #(truncate at 20000)

        ***update: the blob support fixes it for going in too so the array is not needed. you can put the big string in if LongReadLen is active. d'oh.
      It did help - I came across the same problem and found your solution which worked for me also! Cheers, Bill
        same here... thanks for posting.