Re: Cursors (foiled again)

gbostock@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. 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. 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".

--
Jeff

--
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? ... 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)
  • Dynamic public property generation
    ... I am not quite sure what I am getting into but I would like to have a loop ... that read from a flat file to create some public properties in a class. ... Any thoughts on this or successful ... Prev by Date: ...
    (microsoft.public.dotnet.languages.vb)
  • 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: 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)