pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: :PgSQL: More Queestions
22+ messages / 5 participants
[nested] [flat]
* Re: :PgSQL: More Queestions
@ 2002-11-19 23:51 ` David Wheeler <david@wheeler.net>
1 sibling, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-19 23:51 UTC (permalink / raw)
To: Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
On Tuesday, November 19, 2002, at 03:42 PM, Jeff Urlwin wrote:
> David -- I'll pipe in where I can...
Thanks Jeff. I'll read your comments tonight and send a reply in the
morning.
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
@ 2002-11-20 07:03 ` David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 10:53 ` Re: :PgSQL: More Queestions Adam Witney <awitney@sghms.ac.uk>
1 sibling, 3 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-20 07:03 UTC (permalink / raw)
To: Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
On Tuesday, November 19, 2002, at 03:42 PM, Jeff Urlwin wrote:
> You probably only need dTHR to support older, pre-threading perls. I
> don't
> believe you need the #ifdef, but it can't hurt (except visually in your
> code).
Okay. What is it?
>>
>> * In dbd_st_prepare(), is there a way to determine NUM_OF_FIELDS,
>> NAME,
>> etc. -- that is, before executing the statement?
>
> Only if you want to fully parse the SQL :)
Okay, that makes sense. Thanks.
> DBD::ODBC, specifically doesn't handle comments. Reasoning: comments
> are
> (typically) DB vendor specific and I do not believe that ODBC itself
> declares a comment capability. Therefore, it's really not safe for
> something like DBD::ODBC to look for them. It may be for you. I do
> not see
> *much* benefit in adding comments to the queries themselves, within
> perl,
> but then again, if you had perl read a file of SQL Statements and have
> it
> generically prepare() and execute() them, then, there's probably value
> there.
Yeah. We have a Bricolage script that does that, but, as it happens, we
were parsing out comments in Perl before passing them to DBD:Pg's
prepare(). I didn't notice that there were any tests in DBD::Pg's test
suite for comments, either. And it's not documented. So the way I look
at it, if I leave the current parser, I'll leave the comment parsing
part. But if I change it (as I'm seriously considering, in light of
PostgreSQL 7.3's support for prepared statements), I'll probably do no
parsing for comments.
> In the preparse(), we're looking for placeholders to notify DBI that
> we need
> specific parameters to execute the query and, in the case of DBD::ODBC,
> later notify the ODBC Driver that we are binding parameters (and what
> type
> they are, VARCHAR, etc). Then the Driver does the binding in whatever
> DBMS
> specific way it needs to. You may have to do more, as you *are* the
> driver.
> Note that there is also a way in DBD::ODBC to ignore :foo style
> parameters
> because some databases use that for syntax in stored procedures or
> triggers.
> For example, with Oracle a trigger can access :old.column_name or
> :new.column_name and DBD::ODBC allows you to turn off treating
> :anything as
> a bind variable to support that. You may not need that...
I understand that the goal is to convert the placeholders from '?' or
':nn' to the PostgreSQL internal version (':pn'). What I'm referring to
specifically, however, is this snippet from DBD::Pg:
if (in_literal) {
/* Check if literal ends but keep quotes in literal */
if (*src == in_literal) {
int bs = 0;
char *str;
str = src-1; /* Back a character. */
while (*(str - bs) == '\\')
bs++;
if (!(bs & 1))
/* bs is an even number? */
in_literal = 0;
}
*dest++ = *src++;
continue;
}
in_literal is set when the last character wasn't a placeholder
character ('?' or ':') and is either a single or a double quotation
mark. So while I understand that one might want to ignore placeholder
characters, I don't really understand what the above code is doing.
Probably'll be easier for me after I've been looking a C for a while...
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?
> It's going to depend upon what you need to handle. For the most part,
> it
> shouldn't change after the prepare, but in DBD::ODBC, for example,
> it's more
> complex because some statements can return multiple result sets.
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...
> svp is a temporary reference to obtain a pointer to a scalar value
> (scalar
> value pointer). You are then casting it to a pointer to a phs_t, which
> holds your parameter information. You'll "create" the phs_t instances
> when
> you preparse the query. In DBD::ODBC, the ftype is queried from the
> driver
> itself (may go back to the database for information) to determine if
> it's
> numeric, varchar, etc. The phs_t instance can hold whatever you need
> to
> track the parameter (type, scale, etc). Some drivers assume
> everything is a
> varchar and the database itself performs the conversion.
>
> So: all_params_hv is in your statement handle and you actually put the
> information in the all_params_hv hash when you preparse the statement
> (and
> update it during execute/bind_param). Each "phs_t" represents a
> parameter
> in the query. The information contained in the phs_t instance comes
> from
> your parsing the query and, possibly querying the database to
> determine the
> type of the parameter.
>
> Also, the all_params_hv is handy in handling the relatively new DBI
> attribute ParamValues...
Yes, thank you. I'm spending a lot of time right now just studying the
code in dbd_preparse() and dbd_st_execute() in both DBD::Pg and
DBD::ODBC. I think I'll keep at it until I feel I understand it as well
as I can, and then post my thoughts on what to do next.
Thanks,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-20 14:53 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-20 16:17 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 06:30 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2 siblings, 2 replies; 22+ messages in thread
From: Tim Bunce @ 2002-11-20 14:53 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Tue, Nov 19, 2002 at 11:03:23PM -0800, David Wheeler wrote:
> On Tuesday, November 19, 2002, at 03:42 PM, Jeff Urlwin wrote:
>
> But if I change it (as I'm seriously considering, in light of
> PostgreSQL 7.3's support for prepared statements), I'll probably do no
> parsing for comments.
I think that would be a bad move.
> I understand that the goal is to convert the placeholders from '?' or
> ':nn' to the PostgreSQL internal version (':pn'). What I'm referring to
> specifically, however, is this snippet from DBD::Pg:
>
> if (in_literal) {
> /* Check if literal ends but keep quotes in literal */
> if (*src == in_literal) {
> int bs = 0;
> char *str;
> str = src-1; /* Back a character. */
> while (*(str - bs) == '\\')
> bs++;
> if (!(bs & 1))
> /* bs is an even number? */
> in_literal = 0;
> }
> *dest++ = *src++;
> continue;
> }
>
> in_literal is set when the last character wasn't a placeholder
> character ('?' or ':') and is either a single or a double quotation
> mark. So while I understand that one might want to ignore placeholder
> characters, I don't really understand what the above code is doing.
If it's seen the start of a string ("..." or '...') then it just
keeps copying the string till it finds the same type of quote
character to mark the end of the string. The 'fiddly bit in the
middle' is handling backslashes used to escape quote chars in the
middle of the string: "...\"..." and "...\\" (odd vs even number).
> 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?
ANSI standard doesn't use backslashes, it uses doubling: "...""..."
Take a look at dbd_preparse in DBD::Oracle.
There's also a preparse() in DBI.xs which was destined to become a
standard service offered to drivers - but isn't quite ready yet.
Tim.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-20 16:17 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 03:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
1 sibling, 1 reply; 22+ messages in thread
From: Rudy Lippan @ 2002-11-20 16:17 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; +Cc: David Wheeler <david@wheeler.net>; Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Wed, 20 Nov 2002, Tim Bunce 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?
>
> ANSI standard doesn't use backslashes, it uses doubling: "...""..."
>
Postgres does, however ugly it may be. So this needs to be suported lest
a \" thow off placeholder counting and thus breaking execute(). But at
least pg supports geminate "s.
Later,
-r
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-20 16:17 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
@ 2002-11-21 03:03 ` David Wheeler <david@wheeler.net>
0 siblings, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-21 03:03 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: Tim Bunce <Tim.Bunce@pobox.com>; Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Wednesday, November 20, 2002, at 08:17 AM, Rudy Lippan wrote:
> Postgres does, however ugly it may be. So this needs to be suported
> lest
> a \" thow off placeholder counting and thus breaking execute(). But
> at
> least pg supports geminate "s.
Um, sorry (revealing my ignorance again): What's 'geminate "s"'?
Thanks,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-21 06:30 ` David Wheeler <david@wheeler.net>
2002-11-21 09:23 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
1 sibling, 1 reply; 22+ messages in thread
From: David Wheeler @ 2002-11-21 06:30 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Wednesday, November 20, 2002, at 06:53 AM, Tim Bunce wrote:
>> But if I change it (as I'm seriously considering, in light of
>> PostgreSQL 7.3's support for prepared statements), I'll probably do no
>> parsing for comments.
>
> I think that would be a bad move.
Yes, your last post makes very clear why. Thanks.
> If it's seen the start of a string ("..." or '...') then it just
> keeps copying the string till it finds the same type of quote
> character to mark the end of the string. The 'fiddly bit in the
> middle' is handling backslashes used to escape quote chars in the
> middle of the string: "...\"..." and "...\\" (odd vs even number).
Seems rather opaque. Maybe I'm just reflecting my C-newbieness, but why
wouldn't this work as well? Borrowing a bit from DBD::ODBC here (but
allowing for both kinds of escaping of the quote characters):
if (*src == '"' || *src == '\'') {
if (!in_literal) {
literal_ch = *src;
in_literal = 1;
} else {
if (*src == literal_ch && *(src-1) != '//'
&& *(src-1) != literal_ch) {
in_literal = 0;
}
}
}
> ANSI standard doesn't use backslashes, it uses doubling: "...""..."
Yeah, as Rudy pointed out, however, PostgreSQL uses backslashes as well
as doubling.
> Take a look at dbd_preparse in DBD::Oracle.
Will do, when I'm more awake again and find the tuits...
> There's also a preparse() in DBI.xs which was destined to become a
> standard service offered to drivers - but isn't quite ready yet.
Hrm, yes, that could be quite handy, since dbd_preparse() seems to be
one of the more complex driver functions...
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 06:30 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-21 09:23 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 23:05 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-22 00:21 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
0 siblings, 2 replies; 22+ messages in thread
From: Tim Bunce @ 2002-11-21 09:23 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; +Cc: Tim Bunce <Tim.Bunce@pobox.com>; Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Wed, Nov 20, 2002 at 10:30:00PM -0800, David Wheeler wrote:
> On Wednesday, November 20, 2002, at 06:53 AM, Tim Bunce wrote:
>
> >>But if I change it (as I'm seriously considering, in light of
> >>PostgreSQL 7.3's support for prepared statements), I'll probably do no
> >>parsing for comments.
> >
> >I think that would be a bad move.
>
> Yes, your last post makes very clear why. Thanks.
>
> >If it's seen the start of a string ("..." or '...') then it just
> >keeps copying the string till it finds the same type of quote
> >character to mark the end of the string. The 'fiddly bit in the
> >middle' is handling backslashes used to escape quote chars in the
> >middle of the string: "...\"..." and "...\\" (odd vs even number).
>
> Seems rather opaque. Maybe I'm just reflecting my C-newbieness, but why
> wouldn't this work as well? Borrowing a bit from DBD::ODBC here (but
> allowing for both kinds of escaping of the quote characters):
>
> if (*src == '"' || *src == '\'') {
> if (!in_literal) {
> literal_ch = *src;
> in_literal = 1;
No need for in_literal as literal_ch can serve as a flag as well.
> } else {
> if (*src == literal_ch && *(src-1) != '//'
> && *(src-1) != literal_ch) {
> in_literal = 0;
> }
> }
> }
Can't distinguish between
"....\\\\" - final double quote ends the string
"...\\\\\" - final double quote is escaped and string continues...
If the original code isn't broken it doesn't need fixing, maybe
just commenting.
Tim.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 06:30 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 09:23 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-21 23:05 ` Jeff Urlwin <jurlwin@bellatlantic.net>
1 sibling, 0 replies; 22+ messages in thread
From: Jeff Urlwin @ 2002-11-21 23:05 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; David Wheeler <david@wheeler.net>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
>
> On Wed, Nov 20, 2002 at 10:30:00PM -0800, David Wheeler wrote:
> > On Wednesday, November 20, 2002, at 06:53 AM, Tim Bunce wrote:
> >
> > >>But if I change it (as I'm seriously considering, in light of
> > >>PostgreSQL 7.3's support for prepared statements), I'll probably do no
> > >>parsing for comments.
> > >
> > >I think that would be a bad move.
> >
> > Yes, your last post makes very clear why. Thanks.
> >
> > >If it's seen the start of a string ("..." or '...') then it just
> > >keeps copying the string till it finds the same type of quote
> > >character to mark the end of the string. The 'fiddly bit in the
> > >middle' is handling backslashes used to escape quote chars in the
> > >middle of the string: "...\"..." and "...\\" (odd vs even number).
> >
> > Seems rather opaque. Maybe I'm just reflecting my C-newbieness, but why
> > wouldn't this work as well? Borrowing a bit from DBD::ODBC here (but
> > allowing for both kinds of escaping of the quote characters):
> >
> > if (*src == '"' || *src == '\'') {
> > if (!in_literal) {
> > literal_ch = *src;
> > in_literal = 1;
>
> No need for in_literal as literal_ch can serve as a flag as well.
True...I was hacking that in and overlooked it.
>
> > } else {
> > if (*src == literal_ch && *(src-1) != '//'
> > && *(src-1) != literal_ch) {
> > in_literal = 0;
> > }
> > }
> > }
>
> Can't distinguish between
> "....\\\\" - final double quote ends the string
> "...\\\\\" - final double quote is escaped and string
> continues...
>
> If the original code isn't broken it doesn't need fixing, maybe
> just commenting.
I'm tempted to leave it, since nobody is complaining, at the moment...but
I'll put it on the list.
Jeff
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 06:30 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 09:23 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-22 00:21 ` David Wheeler <david@wheeler.net>
1 sibling, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-22 00:21 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Thursday, November 21, 2002, at 01:23 AM, Tim Bunce wrote:
>> if (*src == '"' || *src == '\'') {
>> if (!in_literal) {
>> literal_ch = *src;
>> in_literal = 1;
>
> No need for in_literal as literal_ch can serve as a flag as well.
Right.
>> } else {
>> if (*src == literal_ch && *(src-1) != '//'
>> && *(src-1) != literal_ch) {
>> in_literal = 0;
>> }
>> }
>> }
>
> Can't distinguish between
> "....\\\\" - final double quote ends the string
> "...\\\\\" - final double quote is escaped and string continues...
Right...I realized just as I was passing out last night that it would
miss-parse terminal escapes. and such.
> If the original code isn't broken it doesn't need fixing, maybe
> just commenting.
Absolutely.
Best,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-20 16:36 ` Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2 siblings, 1 reply; 22+ messages in thread
From: Jeff Urlwin @ 2002-11-20 16:36 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
>
>
> On Tuesday, November 19, 2002, at 03:42 PM, Jeff Urlwin wrote:
>
> > You probably only need dTHR to support older, pre-threading perls. I
> > don't
> > believe you need the #ifdef, but it can't hurt (except visually in your
> > code).
>
> Okay. What is it?
See the other posts. They did a better job of describing it.
>
> > In the preparse(), we're looking for placeholders to notify DBI that
> > we need
> > specific parameters to execute the query and, in the case of DBD::ODBC,
> > later notify the ODBC Driver that we are binding parameters (and what
> > type
> > they are, VARCHAR, etc). Then the Driver does the binding in whatever
> > DBMS
> > specific way it needs to. You may have to do more, as you *are* the
> > driver.
> > Note that there is also a way in DBD::ODBC to ignore :foo style
> > parameters
> > because some databases use that for syntax in stored procedures or
> > triggers.
> > For example, with Oracle a trigger can access :old.column_name or
> > :new.column_name and DBD::ODBC allows you to turn off treating
> > :anything as
> > a bind variable to support that. You may not need that...
>
> I understand that the goal is to convert the placeholders from '?' or
> ':nn' to the PostgreSQL internal version (':pn'). What I'm referring to
> specifically, however, is this snippet from DBD::Pg:
>
> if (in_literal) {
> /* Check if literal ends but keep quotes in literal */
> if (*src == in_literal) {
> int bs = 0;
> char *str;
> str = src-1; /* Back a character. */
> while (*(str - bs) == '\\')
> bs++;
> if (!(bs & 1))
> /* bs is an even number? */
> in_literal = 0;
> }
> *dest++ = *src++;
> continue;
> }
>
> in_literal is set when the last character wasn't a placeholder
> character ('?' or ':') and is either a single or a double quotation
> mark. So while I understand that one might want to ignore placeholder
> characters, I don't really understand what the above code is doing.
> Probably'll be easier for me after I've been looking a C for a while...
I'm not sure what it's really trying to do, either, really...
>
> 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's going to depend upon what you need to handle. For the most part,
> > it
> > shouldn't change after the prepare, but in DBD::ODBC, for example,
> > it's more
> > complex because some statements can return multiple result sets.
>
> 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!).
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...
>
> > svp is a temporary reference to obtain a pointer to a scalar value
> > (scalar
> > value pointer). You are then casting it to a pointer to a phs_t, which
> > holds your parameter information. You'll "create" the phs_t instances
> > when
> > you preparse the query. In DBD::ODBC, the ftype is queried from the
> > driver
> > itself (may go back to the database for information) to determine if
> > it's
> > numeric, varchar, etc. The phs_t instance can hold whatever you need
> > to
> > track the parameter (type, scale, etc). Some drivers assume
> > everything is a
> > varchar and the database itself performs the conversion.
> >
> > So: all_params_hv is in your statement handle and you actually put the
> > information in the all_params_hv hash when you preparse the statement
> > (and
> > update it during execute/bind_param). Each "phs_t" represents a
> > parameter
> > in the query. The information contained in the phs_t instance comes
> > from
> > your parsing the query and, possibly querying the database to
> > determine the
> > type of the parameter.
> >
> > Also, the all_params_hv is handy in handling the relatively new DBI
> > attribute ParamValues...
>
> Yes, thank you. I'm spending a lot of time right now just studying the
> code in dbd_preparse() and dbd_st_execute() in both DBD::Pg and
> DBD::ODBC. I think I'll keep at it until I feel I understand it as well
> as I can, and then post my thoughts on what to do next.
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.
Regards,
Jeff
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
@ 2002-11-21 03:09 ` David Wheeler <david@wheeler.net>
2002-11-21 09:32 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 10:52 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 14:45 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
0 siblings, 3 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-21 03:09 UTC (permalink / raw)
To: Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-21 09:32 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-22 01:08 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2 siblings, 1 reply; 22+ messages in thread
From: Tim Bunce @ 2002-11-21 09:32 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 09:32 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-22 01:08 ` David Wheeler <david@wheeler.net>
0 siblings, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-22 01:08 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Thursday, November 21, 2002, at 01:32 AM, Tim Bunce wrote:
> 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.
Agreed. I'll not worry about it now, then, especially in light of
Rudy's demonstration of how PostgreSQL 7.3's prepared statements work.
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-21 10:52 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-22 01:25 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2 siblings, 1 reply; 22+ messages in thread
From: Rudy Lippan @ 2002-11-21 10:52 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 10:52 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
@ 2002-11-22 01:25 ` David Wheeler <david@wheeler.net>
0 siblings, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-22 01:25 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: Jeff Urlwin <jurlwin@bellatlantic.net>; dbi-dev@perl.org; pgsql-interfaces
On Thursday, November 21, 2002, at 02:52 AM, Rudy Lippan wrote:
> 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?
No, you're absolutely right. I love the beauty of side-effects like
that.
> But using prepared statements:
<snip data="proof that table changes are okay" />
Well then, I'm definitely going to have to use prepared statements! :-)
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-21 14:45 ` Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-22 00:11 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2 siblings, 1 reply; 22+ messages in thread
From: Jeff Urlwin @ 2002-11-21 14:45 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
> >> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 14:45 ` Re: :PgSQL: More Queestions Jeff Urlwin <jurlwin@bellatlantic.net>
@ 2002-11-22 00:11 ` David Wheeler <david@wheeler.net>
0 siblings, 0 replies; 22+ messages in thread
From: David Wheeler @ 2002-11-22 00:11 UTC (permalink / raw)
To: Jeff Urlwin <jurlwin@bellatlantic.net>; +Cc: dbi-dev@perl.org; pgsql-interfaces
On Thursday, November 21, 2002, at 06:45 AM, Jeff Urlwin wrote:
>> 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.
No, that makes a *lot* of sense. Allows it to be much more elegant than
when checking for "\"" and '\'' as escapes!
> 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;
Aaah! Get away, get away!!! ;-)
> 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).
Hrm, yes. I'm not sure that that's an issue for PostgreSQL.
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory@jabber.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
@ 2002-11-21 10:53 ` Adam Witney <awitney@sghms.ac.uk>
2002-11-21 19:20 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
2 siblings, 1 reply; 22+ messages in thread
From: Adam Witney @ 2002-11-21 10:53 UTC (permalink / raw)
To: David Wheeler <david@wheeler.net>; +Cc: pgsql-interfaces
> I understand that the goal is to convert the placeholders from '?' or
> ':nn' to the PostgreSQL internal version (':pn'). What I'm referring to
> specifically, however, is this snippet from DBD::Pg:
>
> if (in_literal) {
> /* Check if literal ends but keep quotes in literal */
> if (*src == in_literal) {
> int bs = 0;
> char *str;
> str = src-1; /* Back a character. */
> while (*(str - bs) == '\\')
> bs++;
> if (!(bs & 1))
> /* bs is an even number? */
> in_literal = 0;
> }
> *dest++ = *src++;
> continue;
> }
>
> in_literal is set when the last character wasn't a placeholder
> character ('?' or ':') and is either a single or a double quotation
> mark. So while I understand that one might want to ignore placeholder
> characters, I don't really understand what the above code is doing.
> Probably'll be easier for me after I've been looking a C for a while...
One thing you may want to keep in mind is the situation concerning array
slices
If you send a statement like this
my $sth = $dbh->prepare("select cube[1:2][1][1] from test4");
Then the :2 gets treated as a placeholder and gets substituted. From the
trace
dbd_st_execute: statement = >select cube[1NULL][1][1] from test4<
ERROR: parser: parse error at or near "NULL" at character 14 error 7
recorded: ERROR: parser: parse error at or near "
NULL" at character 14
As no parameters have been bound to the placeholder then a NULL is
substituted and so it fails.
Or maybe I was doing it wrong and there is a way to get around this already?
Thanks
adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 10:53 ` Re: :PgSQL: More Queestions Adam Witney <awitney@sghms.ac.uk>
@ 2002-11-21 19:20 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 19:52 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rudy Lippan @ 2002-11-21 19:20 UTC (permalink / raw)
To: Adam Witney <awitney@sghms.ac.uk>; +Cc: David Wheeler <david@wheeler.net>; dbi-dev@perl.org; pgsql-interfaces
On Thu, 21 Nov 2002, Adam Witney wrote:
> One thing you may want to keep in mind is the situation concerning array
> slices
>
> If you send a statement like this
>
> my $sth = $dbh->prepare("select cube[1:2][1][1] from test4");
>
> Then the :2 gets treated as a placeholder and gets substituted. From the
> trace
Ugg.
Maybe as a possible idea, what do you think?
inline patch 1: dbdimp.c Thu Nov 21 15:56:24 2002 (+4/-0 lines) download inline-1.patch
--- dbdimp.orig Thu Nov 21 15:51:06 2002
+++ dbdimp.c Thu Nov 21 15:56:24 2002
@@ -692,6 +692,8 @@
if (*src != ':' && *src != '?') {
if (*src == '\'' || *src == '"') {
in_literal = *src;
+ } else if ('[' == *src) {
+ in_literal = ']';
}
*dest++ = *src++;
continue;
@@ -1139,6 +1141,8 @@
if (*src != ':' && *src != '?') {
if (*src == '\'' || *src == '"') {
in_literal = *src;
+ } else if ('[' == *src) {
+ in_literal = ']';
}
*dest++ = *src++;
continue;
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 10:53 ` Re: :PgSQL: More Queestions Adam Witney <awitney@sghms.ac.uk>
2002-11-21 19:20 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
@ 2002-11-21 19:52 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 20:54 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
0 siblings, 1 reply; 22+ messages in thread
From: Tim Bunce @ 2002-11-21 19:52 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: Adam Witney <awitney@sghms.ac.uk>; David Wheeler <david@wheeler.net>; pgsql-interfaces
Or ignore colons that have a digit as the previous character
as that would never be a placeholder.
Tim.
On Thu, Nov 21, 2002 at 02:20:52PM -0500, Rudy Lippan wrote:
> On Thu, 21 Nov 2002, Adam Witney wrote:
>
> > One thing you may want to keep in mind is the situation concerning array
> > slices
> >
> > If you send a statement like this
> >
> > my $sth = $dbh->prepare("select cube[1:2][1][1] from test4");
> >
> > Then the :2 gets treated as a placeholder and gets substituted. From the
> > trace
>
> Ugg.
>
> Maybe as a possible idea, what do you think?
>
> --- dbdimp.orig Thu Nov 21 15:51:06 2002
> +++ dbdimp.c Thu Nov 21 15:56:24 2002
> @@ -692,6 +692,8 @@
> if (*src != ':' && *src != '?') {
> if (*src == '\'' || *src == '"') {
> in_literal = *src;
> + } else if ('[' == *src) {
> + in_literal = ']';
> }
> *dest++ = *src++;
> continue;
> @@ -1139,6 +1141,8 @@
> if (*src != ':' && *src != '?') {
> if (*src == '\'' || *src == '"') {
> in_literal = *src;
> + } else if ('[' == *src) {
> + in_literal = ']';
> }
> *dest++ = *src++;
> continue;
>
>
> Untested, but compiles. :) (well I did do a quick test on your above stmt
> and it works for that one, but may break other things. YMMV)
>
>
> >
> > dbd_st_execute: statement = >select cube[1NULL][1][1] from test4<
> > ERROR: parser: parse error at or near "NULL" at character 14 error 7
> > recorded: ERROR: parser: parse error at or near "
> > NULL" at character 14
> >
> > As no parameters have been bound to the placeholder then a NULL is
> > substituted and so it fails.
> >
> > Or maybe I was doing it wrong and there is a way to get around this already?
> >
>
> As a quick workaround $dbh->prepare("cube[1:?][1][1]"); make '2' your
> first param to execute()
>
>
> -r
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 10:53 ` Re: :PgSQL: More Queestions Adam Witney <awitney@sghms.ac.uk>
2002-11-21 19:20 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 19:52 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
@ 2002-11-21 20:54 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-22 09:31 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
0 siblings, 1 reply; 22+ messages in thread
From: Rudy Lippan @ 2002-11-21 20:54 UTC (permalink / raw)
To: Tim Bunce <Tim.Bunce@pobox.com>; +Cc: Rudy Lippan <rlippan@remotelinux.com>; Adam Witney <awitney@sghms.ac.uk>; David Wheeler <david@wheeler.net>; dbi-dev@perl.org; pgsql-interfaces
On Thu, 21 Nov 2002, Tim Bunce wrote:
>
> Or ignore colons that have a digit as the previous character
> as that would never be a placeholder.
>
Since DBD::Pg emulates place holders, it is possible to have them after a
digit.
I thought that using the [ as a literal marker might break less existing
code, for example $d->prepare->("insert into foo (int_date)
VALUES(200301:1"); Or any such other silly uses. And besides the array
data type will probably be used less than other types.
The best solution might be to treat [] as a literal wrt placeholders, but
allow [:1 for the [:1] case, which is the only case that would work with
the existing code. Currently, For [:1:2] preparse would see 2 place
holders and for [:1::2] preparse would see a place holder and a cast.
Thougths?
-r
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: :PgSQL: More Queestions
2002-11-20 07:03 ` Re: :PgSQL: More Queestions David Wheeler <david@wheeler.net>
2002-11-21 10:53 ` Re: :PgSQL: More Queestions Adam Witney <awitney@sghms.ac.uk>
2002-11-21 19:20 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 19:52 ` Re: :PgSQL: More Queestions Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 20:54 ` Re: :PgSQL: More Queestions Rudy Lippan <rlippan@remotelinux.com>
@ 2002-11-22 09:31 ` Tim Bunce <Tim.Bunce@pobox.com>
0 siblings, 0 replies; 22+ messages in thread
From: Tim Bunce @ 2002-11-22 09:31 UTC (permalink / raw)
To: Rudy Lippan <rlippan@remotelinux.com>; +Cc: Tim Bunce <Tim.Bunce@pobox.com>; Adam Witney <awitney@sghms.ac.uk>; David Wheeler <david@wheeler.net>; pgsql-interfaces
On Thu, Nov 21, 2002 at 03:54:56PM -0500, Rudy Lippan wrote:
> On Thu, 21 Nov 2002, Tim Bunce wrote:
>
> > Or ignore colons that have a digit as the previous character
> > as that would never be a placeholder.
>
> Since DBD::Pg emulates place holders, it is possible to have them after a
> digit.
Possible sure, but place holder values should always be viewed and used
as complete lexical tokens. Being over-clever with 'token concatenation'
by doing things like "SELECT * FROM log_:1" and binding a date like
20021122 as a number to select from table log_20021122 is a bad idea.
(FYI I'm not familiar with PostgresSQL's dialect of SQL.)
> I thought that using the [ as a literal marker might break less existing
> code, for example $d->prepare->("insert into foo (int_date)
> VALUES(200301:1"); Or any such other silly uses.
Such silly uses deserve to be broken and replaced with
$d->prepare->("insert into foo (int_date) VALUES(:1)")
or
$d->prepare->("insert into foo (int_date) VALUES(200301$foo)")
> And besides the array
> data type will probably be used less than other types.
>
> The best solution might be to treat [] as a literal wrt placeholders, but
> allow [:1 for the [:1] case, which is the only case that would work with
> the existing code. Currently, For [:1:2] preparse would see 2 place holders
Viewing place holder values as complete lexical tokens make that
not a sensible thing to do. And, for the only valid uses of
concatenated placeholders, the same effect could be achieved with
one placeholder and concatenating the values bound to it.
> and for [:1::2] preparse would see a place holder and a cast.
Is "::2" a valid cast that people would actually use in real code?
So far I think I'd just extend my original proposal to say
"Ignore colons that have a digit or colon as the previous character".
Tim.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2002-11-22 09:31 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-19 23:51 ` David Wheeler <david@wheeler.net>
2002-11-20 07:03 ` David Wheeler <david@wheeler.net>
2002-11-20 14:53 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-20 16:17 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 03:03 ` David Wheeler <david@wheeler.net>
2002-11-21 06:30 ` David Wheeler <david@wheeler.net>
2002-11-21 09:23 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 23:05 ` Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-22 00:21 ` David Wheeler <david@wheeler.net>
2002-11-20 16:36 ` Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-21 03:09 ` David Wheeler <david@wheeler.net>
2002-11-21 09:32 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-22 01:08 ` David Wheeler <david@wheeler.net>
2002-11-21 10:52 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-22 01:25 ` David Wheeler <david@wheeler.net>
2002-11-21 14:45 ` Jeff Urlwin <jurlwin@bellatlantic.net>
2002-11-22 00:11 ` David Wheeler <david@wheeler.net>
2002-11-21 10:53 ` Adam Witney <awitney@sghms.ac.uk>
2002-11-21 19:20 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-21 19:52 ` Tim Bunce <Tim.Bunce@pobox.com>
2002-11-21 20:54 ` Rudy Lippan <rlippan@remotelinux.com>
2002-11-22 09:31 ` Tim Bunce <Tim.Bunce@pobox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox