Thread: Re: :PgSQL: More Queestions
On Wednesday, November 20, 2002, at 08:36 AM, Jeff Urlwin wrote:
> See the other posts. They did a better job of describing it.
Right, thanks.
> I'm not sure what it's really trying to do, either, really...
Heh. Thank God we have Tim Bunce to explain it to use mere mortals. ;-)
>> Maybe it's just too complex, because, looking at DBD::ODBC's
>> dbd_preparse(), the handling of literals in the query seems a good
>> deal
>> more straight-forward (though it doesn't appear to handle '\'' or "\""
>> -- am I reading that right?
>
> Nope, it handles " or '.
>
> if (*src == '"' || *src == '\'') {
> etc...
> }
It doesn't appear to handle "...""...", though, right? Or am I missing
it?
>> Ah, that makes sense. Not sure if it's an issue for PostgreSQL, but I
>> doesn't appear to be much of an overhead to set it on a per-execute
>> basis...
>
> Actually, if you can get away with doing it only once, the first
> execute, go
> with it. DBD::ODBC tries to do that, but rechecks under two
> conditions:
> 1) we "know" there are multiple result sets in this query via already
> experiencing it
> 2) the user sets a DBD::ODBC private attributed to recheck the result
> set
> types (this is to support nasty things like stored procedures
> returning only
> one result set per call, but a different result set based upon the
> input
> (yes, I've seen this!).
Bleh!
> My advice: if you don't have to support multiple result sets, do it
once per
> execute. If you setup that "flag" to avoid re-doing work and find
> that you
> need to support multiple-result sets, you can always clear the flag...
I'll have to check with the PostgreSQL folks on this.
PostgreSQL folks, can the same statement return a different number of
fields on different executes? I'm guessing yes for something like this,
though:
CREATE TABLE foo ( bar int, bat, text);
SELECT * FROM foo; -- Returns two fields.
ALTER TABLE foo ADD COLUMN fat int;
SELECT * FROM foo; -- Returns three fields.
> I would make the statement that DBD::Oracle may provide a better
> reference
> on the pre-parse stuff. DBD::ODBC's is probably a bit watered down
> from
> DBD::Oracle -- especially because I'm avoiding comments.
Yep, thanks, I'll check it out.
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e Jabber:
Theory@jabber.org
On Wed, Nov 20, 2002 at 07:09:34PM -0800, David Wheeler wrote: > > PostgreSQL folks, can the same statement return a different number of > fields on different executes? I'm guessing yes for something like this, > though: > > CREATE TABLE foo ( bar int, bat, text); > > SELECT * FROM foo; -- Returns two fields. > > ALTER TABLE foo ADD COLUMN fat int; > > SELECT * FROM foo; -- Returns three fields. I suspect there are quite a few drivers that wouldn't do the right thing in that situation (schema change between two executes of a prepared statement). You could either arrange the code to ignore an extra column (on the right), or make it re-describe if the column count changes. But they'll always be ways to cause problems - such as changing the type of a column. Increasing paranoia yields decreasing performance. I doubt it's worth worrying about. In general people should avoid "select *" if there's a risk that the schema will change. Tim.
On Wed, 20 Nov 2002, David Wheeler wrote:
>
> >> Maybe it's just too complex, because, looking at DBD::ODBC's
> >> dbd_preparse(), the handling of literals in the query seems a good
> >> deal
> >> more straight-forward (though it doesn't appear to handle '\'' or "\""
> >> -- am I reading that right?
> >
> > Nope, it handles " or '.
> >
> > if (*src == '"' || *src == '\'') {
> > etc...
> > }
>
> It doesn't appear to handle "...""...", though, right? Or am I missing
> it?
>
So you have a "". On the first " of the medial "" cluster, the code will
set in_literal to 0, but the very next character is a " so it will set
in_literal right back to '"'. Now, if there were something between the "s
it would not be in_literal, see? || am I just serving to confuse?
> PostgreSQL folks, can the same statement return a different number of
> fields on different executes? I'm guessing yes for something like this,
> though:
>
> CREATE TABLE foo ( bar int, bat, text);
>
> SELECT * FROM foo; -- Returns two fields.
>
> ALTER TABLE foo ADD COLUMN fat int;
>
> SELECT * FROM foo; -- Returns three fields.
>
But using prepared statements:
test=# create table foo (foo integer);
CREATE TABLE
test=# prepare cached (varchar) AS select * from foo where foo= $1;
PREPARE
test=# insert into foo values (1);
INSERT 16982 1
test=# execute cached (1);foo
----- 1
(1 row)
test=# alter table foo add column bar varchar;
ALTER TABLE
test=# execute cached (1);foo
----- 1
(1 row)
test=# select * from bar;
ERROR: Relation "bar" does not exist
test=# select * from foo;foo | bar
-----+----- 1 |
(1 row)
-r
> >> Maybe it's just too complex, because, looking at DBD::ODBC's
> >> dbd_preparse(), the handling of literals in the query seems a good
> >> deal
> >> more straight-forward (though it doesn't appear to handle '\'' or "\""
> >> -- am I reading that right?
> >
> > Nope, it handles " or '.
> >
> > if (*src == '"' || *src == '\'') {
> > etc...
> > }
>
> It doesn't appear to handle "...""...", though, right? Or am I missing
> it?
Actually, it does, but completely "accidentally". DBD::ODBC is not
concerned with the fact that there are " inside the ", so:the first quote sets in_literal=1 (true)the second quote sets
in_literal=0(false)the third puts us right back in_literal=1, so we can keep processing.
Since there should be an even number of " marks, I think we're OK here.
Seems too simple, but, it works. There may be a case that gets me, but I
don't think so.
>
> > My advice: if you don't have to support multiple result sets, do it
> once per
> > execute. If you setup that "flag" to avoid re-doing work and find
> > that you
> > need to support multiple-result sets, you can always clear the flag...
>
> I'll have to check with the PostgreSQL folks on this.
>
> PostgreSQL folks, can the same statement return a different number of
> fields on different executes? I'm guessing yes for something like this,
> though:
>
> CREATE TABLE foo ( bar int, bat, text);
>
> SELECT * FROM foo; -- Returns two fields.
>
> ALTER TABLE foo ADD COLUMN fat int;
>
> SELECT * FROM foo; -- Returns three fields.
You probably wouldn't have that prepared as one statement.
Using, say, SQL Server, you can do something like (pseudo code here with
some exaggeration, but I can actually provide test code that someone sent):
sp_pain_to_deal_with(int i) as
if (i == 1) select a, b, c from foo; /* returns a, b, c as result set */ elseif (i == 2) delete
fromfoo; /* returns a count, not a result set */ else select d, a, b, f from foo; /* returns
somethingcompletely different */end;
$sth = $dbh->prepare({ call sp_pain_to_deal_with(?)});
$sth->execute(1);
$sth->execute(2);
$sth->execute(3);
all return different result sets.
Or, the more "normal" case:$sth = $dbh->prepare("insert a, b, c into foo values (?, ?, ?); select
@@identidy;");
which, in one "shot" insert into the table and gets back the
auto-incremented id for the table (again, there is probably a lot of syntax
issue with the above, but the concept is there).
Regards,
Jeff