Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53

    Unanswered: CLI0129E No more handles.

    DBD:B2::db prepare failed: [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 at ./comm_mim.pl line 35, <FD> line 5807.
    Can't prepare statement: [IBM][CLI Driver] CLI0129E No more handles. SQLSTATE=HY014 at ./comm_mim.pl line 35, <FD> line 5807.

    Thanks.
    -Jianrong

    My program is:

    #!/usr/bin/perl -w
    use DBI;
    use DBD:B2::Constants;
    use DBD:B2;
    my $database='dbiB2:gene';

    my $dbh = DBI->connect($database)
    or die "Can't connect to $database: $DBI::errstr";

    $first = shift(@ARGV) ;
    $second= shift(@ARGV) ;
    $column= shift(@ARGV) ;

    open(FD,"<$first") || die "Couldn't open sourcefile\n";
    open(FD1,">$second") || die "Couldn't open output file\n";
    while($line= <FD>)
    {
    chop($line);
    @a = split(/\|/,$line);
    my $st = "SELECT NAME FROM U.SCT_CON_072005 WHERE SCTID=$a[$column] WITH UR";
    my $sth = $dbh->prepare($st) or die "Can't prepare statement: $DBI::errstr";
    my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr";
    if (($name) = $sth->fetchrow())
    {
    print FD1 "$line|$name\n";
    } else
    {
    print "ERROR SCT ID=$a[$column]\n"
    }

    $rc = $sth->finish;
    $st ="";


    }
    $dbh->disconnect();
    close(FD);
    close(FD1);

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    May be you should prepare the statement outside the loop, instead of creating 5000+ statement handles.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53
    I can't prepare the statement outside the loop, because it was changed within loop.

    Thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    May be you should read DBI documentation then...
    Code:
    my $st = "SELECT NAME FROM U.SCT_CON_072005 WHERE SCTID=? WITH UR";
    my $sth = $dbh->prepare($st) or die "Can't prepare statement: $DBI::errstr";
    
    while($line= <FD>)
    {
      chop($line);
      @a = split(/\|/,$line);
      sth->bind_param(1,$a[$column]);
      my $rc = $sth->execute() or die "Can't execute statement: $DBI::errstr";
      ...
    }
    
    $rc = $sth->finish;
    $st ="";
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53
    Thank you n_i, it works great.
    BTW, if the WHERE condition is variable, what can I do.
    Anyway, thanks you so much.

    -Jianrong

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, if the query (its WHERE clause) changes you can't use this approach, obviously. If the number of query variants is small you can still prepare all of them ahead of time and execute the one you need in the loop, depending on some condition.

    Otherwise you will need to make sure that the statement handle is destroyed at the end of each cycle.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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