Re: Cursors (foiled again)

On Dec 4, 10:21 am, j...@xxxxxxxxxxxxxx (Jeff Zucker) wrote:
gbost...@xxxxxxxxxx wrote:
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.

Ok, I'm still lost as to what the issue is. 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.

I think that's what I'm going to wind up doing. I found an example at:
http://sqlrelay.sourceforge.net/sqlrelay/programming/perldbi.html

Without
knowing the RDBMS you are using and without knowing the size of the
datasets, it's hard to say anything more specific. 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.

Yes, I had thought of that, but I think the data set is large enough
that it isn't quite feasible. Also, I won't need the entire set, I
don't know in advance which ones I will need or what order they will
be in.

Maybe you should just tell
us what RDBMS and DBD you are using, the relative size of the datasets,
and what kind of memory constraints you're working under so we don't
have to guess.

Oh, and not that it's necessarily relevant, but DBD::CSV (or
DBD::AnyData) handle most types of "flat files" regardless of whether
they conform to some definition of "CSV".

Yes, I kind of deduced that from your first post. At the time I
thought the file would be a different format, so I hadn't considered
it, but I just found that the file will be a CSV so DBD::CSV could be
quite helpful.

Thanks.

.

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? ... 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: what script will do my job
    ... mysql database instead. ... I'm not sure what your back end flat file format is like but let's say ... "tune" the number of concurrent connections and pool connections, ... In an RDBMS it's easy to to find all the Wildlife programs ...
    (uk.net.web.authoring)
  • 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? ... but there's got to be a way to set up a select cursor ... happens and how a different value is used each time a fetch is done. ...
    (perl.dbi.users)
  • Re: Question for Microsoft guys
    ... you might not need an RDBMS. ... faster with a well-designed database than with a file system. ... >Flat file system is much faster and I think if I had possibility to send to ...
    (microsoft.public.sqlserver.programming)
  • Re: Just say no to threads [Was: Software architecture]
    ... > start on a project that has the need for persisting its object model. ... > a) Go straight for RDBMS cause you have done it before. ... > b) Use a flat file cause you have done it before. ...
    (comp.object)