Front page | perl.dbi.dev |
Postings from August 2002
RE: Multiple Result sets in DBD::ODBC (and others?)
Thread Previous
|
Thread Next
From:
Joe Tebelskis
Date:
August 23, 2002 03:40
Subject:
RE: Multiple Result sets in DBD::ODBC (and others?)
Message ID:
FB28766098D144499BE5F30D497EA0D51BEA35@BODDINGTONS.inspinc.ad
Well, Jeff, I guess your question has been answered. Whether or not I personally would use the number of rows affected by the insert/update, there are clearly people out there who would.
-- Joe
-----Original Message-----
From: Jeff Urlwin [mailto:jurlwin@bellatlantic.net]
Sent: Thursday, August 22, 2002 1:14 PM
To: Paul G. Weiss; Joe Tebelskis; Jeff Urlwin; martin@easysoft.com
Cc: dbi-dev@perl.org; Tim Bunce
Subject: RE: Multiple Result sets in DBD::ODBC (and others?)
>
> >
> > I agree that your "insane" example is unjustifiable, and I
> > wouldn't complain if you choose to disregard it. I don't
> > think I would ever use "odbc_force_rebind".
> >
> > > Now -- a question for you: would you *like* to know how
> > many rows were
> > > affected by the inserts/updates inside your stored procs or
> > would you prefer
> > > to ignore them as now..?
> >
> > I have never yet needed to know how many rows were affected
> > by any insert/update, and I don't expect I ever will, unless
> > I were to see a good example of when it's really useful.
> >
> > -- Joe
> >
>
> Here's an example of where it is useful (not that there
> aren't other good ways to do this ).
>
> I have a table with primary key K, and other
> columns C1,...,Cn.
>
> I have the tuple (k,c1,...,cn) that I want to add to
> the table, i.e if the key k already exists in the table
> I want to update the row, otherwise I want to add the
> new row.
>
> Here's how to do it:
>
> $my @parms = (c1,...,cn,k);
> $rows = $db->do("update table set C1=? .... CN=?
> where K=?", undef, @parms);
> if ($rows == 0) {
> # i guess key wasn't there
> $db->do("insert into table (C1,...,Cn,K)
> values (?,...,?,?)", @parms);
> }
>
> otherwise I have to do a select first, and then
> an update or an insert, or do the insert and
> watch for an error and then go ahead and do the
> update -- a little more complicated.
But "do" happens to be different. It will return the number of rows. Where
this is, particularly, a problem is with multiple result sets. For example,
think of the following (ignore the syntax, using your general principle):
DBMS STORED Procedure is:
update table1 blah where primary_key = ?
if (update failed) then
insert into table blah
end if
Then, in perl, call the proc with a bind_parameter. What may happen (since
I haven't tested this scenario exactly on SQL Server) is that you may get
TWO result sets. One, the empty update telling you that you 0 rows were
affected and the insert result set. Whereas, if the insert succeeds, you
only have one result set. Now, re-call that with a different value in the
bind parameter and "bingo" you get only one result set this time, since the
update succeeds. However, you may not need to know or care, in that
circumstance from the PERL side, that the update failed and the insert took
over....
Regards,
Jeff
>
>
Thread Previous
|
Thread Next