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

Im reading a SQL table of files to upload. "while" i read each record I upload it and THEN I would like to update the timestamp in the database record, but it seems that I have to have two connections to do this otherwise I get an error stating that the connection is already busy (going thru the while loop). Anyone know how I can read each line, uplad the file in that record and update that same record? Thanks

Replies are listed 'Best First'.
Re: Update while reading Database
by mpeppler (Vicar) on Jun 17, 2002 at 17:16 UTC
    You don't give a whole lot of information, so it's a little hard to give a good answer. However:

    The official DBI way of doing this might look like this:

    my $sth = $dbh->prepare("select name from file_table where <some condi
+tion>");
my $upd_sth = $dbh->prepare("update file_table set timestamp = ? where
+ name = ?");

while(my $d = $sth->fetch) {
    # do the upload, etc;
    # if all is well:
    $sth_upd->execute(time, $d->[0]);
}

    [download]
    Note that some database servers may open a second connection for you anyway when creating the second statement handle..

    Depending on the database server there may be different ways of doing this, as well.

    Michael

[reply]
[d/l]
Re: Update while reading Database
by mfriedman (Monk) on Jun 17, 2002 at 17:52 UTC
    Are you attempting to use the same statement handle ($sth) for both queries? You'll need to use a seperate one for each. My only experience is with MySQL and SQL Server, but I have had no trouble doing simultaneous queries with this method. (And really, the queries are not simultaneous. The first one is over and done with before you begin the loop.)
[reply]
Re: Update while reading Database
by metadatum (Scribe) on Jun 17, 2002 at 17:08 UTC
    What database are you using?
[reply]
Re: Update while reading Database
by Anonymous Monk on Jun 17, 2002 at 21:08 UTC
    Someone shouts out: Use a trigger! Use a trigger!
[reply]
      What if the database doesn't support triggers?
      *cough*MySQL*cough*
      Who says that programmers can't work in the Marketing Department?
      Or is that who says that Marketing people can't program?
[reply]
        Not to mention that it'd have to be a select trigger, which is even less standard.

        And the user may not have DBA priviledges to create a trigger in the first place...

        Michael

[reply]

Back to Seekers of Perl Wisdom