From: "Jeff Urlwin" <jurlwin@bellatlantic.net>
To: "David Wheeler" <david@wheeler.net>,
	"Jeff Urlwin" <jurlwin@bellatlantic.net>
Cc: <dbi-dev@perl.org>, <pgsql-interfaces@postgresql.org>
Subject: Re: :PgSQL: More Queestions
Date: Thu, 21 Nov 2002 09:45:46 -0500
Message-ID: <OEEMJFLOJPABMFADAPIKKEDIEDAA.jurlwin@bellatlantic.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <A94E346C-FCFE-11D6-8943-0003931A964A@wheeler.net>
Importance: Normal


> >> 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 from foo;			/* returns a count, not a result set */
		else
			select d, a, b, f from foo;	/* returns something completely 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