Re: Cursors (foiled again)

On Dec 3, 6:37 pm, j...@xxxxxxxxxxxxxx (Jeff Zucker) wrote:
gbost...@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.

Close, but not quite. The file that needs to be updated isn't even a
csv, so that I can do with old fashioned perl manipulation (no update
query needed). What I want is something like what you've written for
your update query. I need a select cursor that will fetch with a
different value in the where clause each time. How different would
that be from what you wrote for your update query?

Thanks.

--
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.- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Cursors (foiled again)
    ... What I want to do is read a flat file that has a column value in it, ... that be from what you wrote for your update query? ... memory and an RDBMS that supports large IN clauses, ... Yes, I had thought of that, but I think the data set is large enough ...
    (perl.dbi.users)
  • Re: Cursors (foiled again)
    ... What I want to do is read a flat file that has a column value in it, ... that be from what you wrote for your update query? ... You can use prepare-with-placeholders outside a loop and then execute-with-values inside the loop with SELECT just like I did with UPDATE. ... If you have enough memory and an RDBMS that supports large IN clauses, you could read all the db values into a hash with a single IN query and loop through the hash rather than looping through a fetch. ...
    (perl.dbi.users)
  • Re: A question about performing UPDATE and multiple tables
    ... writes them to a flat file (which is uploaded to a mainframe system ... CURSOR GetTrans_cur IS ... The easiest way to process the new table would be to create a second cursor ... Can someone point me to where I can find the differences between a join and a UNION of two tables? ...
    (comp.databases.oracle.misc)
  • Re: A question about performing UPDATE and multiple tables
    ... writes them to a flat file (which is uploaded to a mainframe system ... CURSOR GetTrans_cur IS ... FOR v_gettrans IN GetTrans_cur LOOP ... The easiest way to process the new table would be to create a second cursor ...
    (comp.databases.oracle.misc)
  • Re: A question about performing UPDATE and multiple tables
    ... writes them to a flat file (which is uploaded to a mainframe system ... CURSOR GetTrans_cur IS ... The easiest way to process the new table would be to create a second cursor ... Can someone point me to where I can find the differences between a join and a UNION of two tables? ...
    (comp.databases.oracle.misc)