in reply to Re^2: perl DBI String length problem
in thread perl DBI String length problem

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

Replies are listed 'Best First'.
Re^4: perl DBI String length problem
by travisbickle34 (Beadle) on Sep 06, 2005 at 16:24 UTC
    I'm primarily a scientist and don't know much at all about the DBI/Database side of things so this could well explain the absence of placeholders!
    Here's the main sub from the script. The longstring variable is $RawData here.
    An example input file consists of thousands of entries of 3 colums each. e.g.
    idnumber56373<tab>69.3<tab>P<newline> 
    # .chp file parsing/db updating subroutine
sub dataparse {

    # Extract arguments passed to sub and define input file and bg var
+iables
    my $input = shift @_;
    my $background = shift @_;
    my $pcall=0;
    my $acall=0;
    my $mcall=0;
    my $pandabovecall=0;
    my $mandabovecall=0;
    my $rawdatastring="";
    # Open input file or die
    open (INPUT, $input) or die "Cannot open infile!$!";
    # Enter while loop for file parse
    while (<INPUT>) {

      # Skip header and Affray control lines
      next if (/^\s*$/) || (/^Gene/) || (/^AFFX/) || (/^2000/);

      # Split line on tabs, assign to array and chomp
      chomp (my @linearray = split "\t", $_);

      # Extract 3 required values
      my $name = shift @linearray;
      my $signal = shift @linearray;
      my $affraycall = shift @linearray;
      $rawdatastring .="$name:$signal:$affraycall:";

      # Increment Present count fot sequence if above bg and present e
+lse increment absent count

        if ($affraycall eq "P") {
                $pcall++;
                if ($signal > $background) {$pandabovecall++;}
        }
        elsif ($affraycall eq "M") {
                $mcall++;
                if ($signal > $background) {$mandabovecall++;}
        }
        elsif ($affraycall eq "A") {$acall ++;}
     }
    close INPUT;
    my $pmandaboves = $pandabovecall+$mandabovecall;


my $inserthandle = $dbh->prepare ("INSERT INTO CHIPDATA (RawData) VALU
+ES ('$rawdatastring')");

$inserthandle->execute;

    [download]
[reply]
[d/l]
[select]
      I may be missing something here but I can't see where you defined $dbh. You need to open a connection to the database before preparing you statement handle. if you print $! in you die statement, I think you'll get a meaningful error. Anyway, here is the syntax for opening: 
      my $dbh = DBI->connect($data_source, $username, $auth, \%attr);

      [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom