Re: Cursors (foiled again)

gbostock@xxxxxxxxxx wrote:
It's been a while since I did dbi, but I used to be quite proficient.
Now I'm back to it.
What I want to do is read a flat file that has a column value in it,
do a select on the database to find a corresponding column value and
then modify the flat file record based on that retrieved column
valuie.

Here's one example taking the values from a MySQL db and updating the corresponding values in a "flat file":

my $dbh_mysql = DBI->connect( ...MySQL connect args... );
my $dbh_csv = DBI->connect( ...CSV connect args... );
my $select_query = $dbh_mysql->prepare(
"SELECT colToChange, keyCol FROM someTable"
);
my $update_query = $dbh_csv->prepare(
"UPDATE someOtherTable SET colToChange=? WHERE keyCol=?"
);
$select_query->execute();
while(my $row = $select_query->fetch){
$update_query->execute( @$row );
}

Hope that helps, I'm not sure if exactly what you're asking.

--
Jeff
So I'd have a perl function that basically does "select column1 from
table where column2 = (value in file) and returns the column value
selected.

I can do this with a prepare, execute, and fetchrow_array OK (and it
is really slow), but there's got to be a way to set up a select cursor
that is more efficient. I'm figuring it starts with a prepare on
something like "select column1 from table where column2 = ?" but
exactly how that is done is eluding me in terms of when the bind param
happens and how a different value is used each time a fetch is done.
Can anybody write (or direct me to) a simple example for me?
Thanks.




.

Relevant Pages