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

Hi, I am using the Perl DBI for some simple communication with a mysql database. One of my data fields consists of fairly long strings and each time I try to insert one of these strings into the database like so: 
my $inserthandle = $dbh->prepare("INSERT INTO DATA (LongData) VALUES (
+'$longstring');

$inserthandle->execute or die $DBI::errstr;

[download]
I get the following error: 
DBD::mysql::st execute warning: at ./script line 121
Died at ./crcdb line 121

[download]
I am using a longtext data type within the DB so I know this is ok. Is there an issue with passing long strings via the DBI? I should mention that the code works fine if I pass shorter strings to the $longstring variable
Thanks in advance,
tb34

Replies are listed 'Best First'.
Re: perl DBI String length problem
by Fletch (Bishop) on Sep 06, 2005 at 15:19 UTC

    You probably should be using a proper placeholder rather than the literal string in your INSERT. Aside from your apparent length problem you also minimize exposure to SQL injection attacks in a CGI context. See perldoc DBI for more details.

    --
    We're looking for people in ATL

[reply]
[d/l]
Re: perl DBI String length problem
by ChrisR (Hermit) on Sep 06, 2005 at 15:24 UTC
    $dbh->errstr is probably what you need to return the correct error. $DBI::errstr is a variable that holds errors regardless of the database handle. $dbh->errstr is a method that returns the last error for the given handle.

    Just how long is $longstring??

[reply]
[d/l]
[select]
      $longstring is approximately 130,000 chars.
      I'm not getting anything from $dbh->errstr either...
[reply]
[d/l]
        I fully agree with Fletch that you should be using placeholders. It could be an unescaped character in your string that is causing problems. I would think you should get an error in your sql syntax if that was the case though.

        There is another thing that I have run into in the past. The connection to the server could have timed out or the maximum packet size may have been exceeded. I ran into this when trying to store large images in the database but I got a MySQL server has gone away error. This was due to the maximum packet size being exceeded. I think the default is 2MB so this may not be your problem either.

        Perhaps, if you give us more of the code, we might be able to help a little more. Chris

[reply]
[d/l]
Re: perl DBI String length problem
by monkey_boy (Priest) on Sep 06, 2005 at 16:21 UTC
    I think you are checking the wrong error, this is a statement-handle method, not a database-handle method, try: 

    $inserthandle->execute or die $inserthandle->errstr;

    [download]

    *UPDATE*
    You could also check the quoting is correct: 
    my $inserthandle = $dbh->prepare("
INSERT INTO CHIPDATA (RawData) 
VALUES (". $dbh->quote($longstring) .")
");

    [download]


    This is not a Signature...
[reply]
[d/l]
[select]
      I have opened the connection to the database prior to the subroutine shown here so that is not a problem.

      I tried the other suggestions too i.e. the quoting and the $inserthandle->errstr suggestion but neither make any difference. By doing $inserthandle->execute or die $! I get the error connection reset by peer.
[reply]
[d/l]
[select]
        In fact, I've just attempted the same thing directly through mysql and it also dies when I increase the string length from 1000,000 chars to 1100,000 chars. Not DBI's fault after all!
[reply]

Back to Seekers of Perl Wisdom