Cursors (foiled again)

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.

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.

Re: Cursors (foiled again)

gbostock@excite.com 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.
>
>
>
>

Re: Cursors (foiled again)

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

Re: Cursors (foiled again)

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

Re: Cursors (foiled again)

On Dec 4, 10:21 am, j...@vpservices.com (Jeff Zucker) wrote:
> gbost...@excite.com wrote:
> > On Dec 3, 6:37 pm, j...@vpservices.com (Jeff Zucker) wrote:
>
> >> gbost...@excite.com 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.