Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: Access Memo Field and Perl

    I have developed a database that uses memo fields from Access. I have developed a perl script that reads from the database and posts the info to an html page. For some reason I can't get the script to work when I include one of the memo fields.

    The script works great when the memo fields are not included, but as soon as one of the memo fields are added the script errors out during the execution of the SQL statement.

    I have tried to use DBI and Win32 neither of these works. They both error out on execution of the SQL statement when a memo field is included, but work fine when a memo field isn't included.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    I'd check the memo fields to see if they contain any single quotes or other SQL control characters.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    No the fields just have a default message in them right now, which only contains text. There are no special characters what so ever.

  4. #4
    Join Date
    Jan 2003
    Posts
    7

    Post Access Memo field

    There are certain differences when using large-size field types such as Memo (and I think this applies with MySQL's version of the Memo field as well, or so I've read) that can prevent them from being used in certain situations. I know I've run into this before and at the time it gave me some headaches, but it was some time ago. As I recall the solution was to use a regular text-type field instead. If you can deal with the 255 character limit, use a text field.

  5. #5
    Join Date
    Jan 2003
    Posts
    4
    In principle, there shouldn't be any problem using memo-type data in mySQL. What field type are you using? If you are using only textual data, you have 4 text column types to choose from (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT) with the latter able to story extremely large amounts of data. I frequently use MEDIUMTEXT to store fields which contain several pages of data such as HTML or RTF documents.

    You may need to check that the data is being quoted correctly. If you are using DBI (and if you're not, you should be), you can use quote(). For example, the following utility subroutine will quote the text for you or replace it with "NULL" if empty (which you will need for an INSERT statement):

    sub dbq{
    my $x=shift;
    if (length($x)>0) {$x=$db->quote($x);} else {$x = "null";}
    return $x;
    }

    e.g.

    $db = DBI->connect("dbi:mysql:$dbname$host$port",$user,$pwd) ;
    $mytext=dbq($mytext);
    $sql="insert into mytable (id, mytextfield) values ($myid, $mytext)";
    . . . etc.

  6. #6
    Join Date
    Apr 2004
    Posts
    1
    Try this sniplet (modify for your code)

    open connection to STD database
    $dbhSTD = DBI->connect('dbi:ODBC:test_database');
    $dbhSTD->{'LongTruncOk'} = 0; #use 1 if it is ok to trunc data
    $dbhSTD->{'LongReadLen'} = 512;

    The LongReadLen value should be set to the size of your largest memo field length.

  7. #7
    Join Date
    Mar 2009
    Posts
    1

    You are a genius

    This is exactly what I wanted...

    NOw, I have set it to:

    my $dbh = DBI->connect($data_source) #, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

    $dbh->{'LongTruncOk'} = 0; #use 1 if it is ok to trunc data
    $dbh->{'LongReadLen'} = 100000;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •