Thread: Perl DBI and placeheld values
First off this is not really postgresql specific but it is driving me nuts.
I thought I was using DBI to avoid the issues involved in constructing a SQL
query string using values held in variables. It turns out I'm not I'm using it
because it let's me write fetchrow_blah instead of some DB specific function
that does the samething, like the nice simple API of Pg that no one likes to
suggest people use.
Anyway, back on to the subject. I'm a little stuck and I'm wondering how people
handle the situation where a variable can contain a value _or_ a function
call. For example:
psql> create table mytab ( thetime timestamptz );
perl:
$sth = $dbh->prepare('insert into mytab values ( ? )');
$sth->execute($thetime);
where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
Obviously these are just going to be normal string scalars in perl and DBI is
just going to stick them in place as constant strings. Indeed it's difficult to
see how it could do otherwise without going to great lengths. Even if it did,
what then would it do if the column type was text? The trouble being is guess
what happens when you do:
insert into mytab values ('current_timestamp');
Yep, it doesn't like trying to insert an incorrect timestamp representation
into a timestamp field.
So just how do others manage this situation without resorting to special casing
everything?
--
Nigel J. Andrews
I construct the ? into a variable before I ever call the prepare. I go
through an array and check for things like if the info is all numbers
then I don't need qoutes.. but if it is numbers and :'s then you'll need
quotes.. in you situation I would specifically look for
current_timestamp and make sure it doesn't have quotes.
Travis
-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Wednesday, January 29, 2003 4:31 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Perl DBI and placeheld values
First off this is not really postgresql specific but it is driving me
nuts.
I thought I was using DBI to avoid the issues involved in constructing a
SQL
query string using values held in variables. It turns out I'm not I'm
using it
because it let's me write fetchrow_blah instead of some DB specific
function
that does the samething, like the nice simple API of Pg that no one
likes to
suggest people use.
Anyway, back on to the subject. I'm a little stuck and I'm wondering how
people
handle the situation where a variable can contain a value _or_ a
function
call. For example:
psql> create table mytab ( thetime timestamptz );
perl:
$sth = $dbh->prepare('insert into mytab values ( ? )');
$sth->execute($thetime);
where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
Obviously these are just going to be normal string scalars in perl and
DBI is
just going to stick them in place as constant strings. Indeed it's
difficult to
see how it could do otherwise without going to great lengths. Even if it
did,
what then would it do if the column type was text? The trouble being is
guess
what happens when you do:
insert into mytab values ('current_timestamp');
Yep, it doesn't like trying to insert an incorrect timestamp
representation
into a timestamp field.
So just how do others manage this situation without resorting to special
casing
everything?
--
Nigel J. Andrews
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Thanks for the response. Damn quick as well.
This is exactly what I was meaning about special casing and is the route I'd
finally decided I couldn't avoid.
I have tried to generalise it a bit hoping to be able to catch more expressions
by matching against:
/cur|now|int|'/
and quoting if it fails. The 'catch-all' in this is probably the single
quote. If it's already got a quote in it then it's unlikely we should be adding
more all around the value. Obviously this is targeted at timestamp types and
will fail miserably should it ever be used to try and use with things like text
columns.
Nigel Andrews
On Wed, 29 Jan 2003, Williams, Travis L, NPONS wrote:
> I construct the ? into a variable before I ever call the prepare. I go
> through an array and check for things like if the info is all numbers
> then I don't need qoutes.. but if it is numbers and :'s then you'll need
> quotes.. in you situation I would specifically look for
> current_timestamp and make sure it doesn't have quotes.
>
> Travis
>
> -----Original Message-----
> From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
>
> ...
>
> First off this is not really postgresql specific but it is driving me
> nuts.
>
>..
>
> Anyway, back on to the subject. I'm a little stuck and I'm wondering how
> people
> handle the situation where a variable can contain a value _or_ a
> function
> call. For example:
>
> psql> create table mytab ( thetime timestamptz );
>
> perl:
> $sth = $dbh->prepare('insert into mytab values ( ? )');
> $sth->execute($thetime);
>
> where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
>
> ...
>
> So just how do others manage this situation without resorting to special
> casing
> everything?
I write it into my script also if I know that the second and 37th value
of an array are always quoted.. or not quoted.. I do that right off of
the bat. I have a couple of systems that dump the time/date together as
01/28/200300:00:000 (it comes from a wierd system!).. so I always know I
have to changed those values before I push into the db..
Travis
-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Wednesday, January 29, 2003 7:17 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Perl DBI and placeheld values
Thanks for the response. Damn quick as well.
This is exactly what I was meaning about special casing and is the route
I'd
finally decided I couldn't avoid.
I have tried to generalise it a bit hoping to be able to catch more
expressions
by matching against:
/cur|now|int|'/
and quoting if it fails. The 'catch-all' in this is probably the single
quote. If it's already got a quote in it then it's unlikely we should be
adding
more all around the value. Obviously this is targeted at timestamp types
and
will fail miserably should it ever be used to try and use with things
like text
columns.
Nigel Andrews
On Wed, 29 Jan 2003, Williams, Travis L, NPONS wrote:
> I construct the ? into a variable before I ever call the prepare. I
go
> through an array and check for things like if the info is all numbers
> then I don't need qoutes.. but if it is numbers and :'s then you'll
need
> quotes.. in you situation I would specifically look for
> current_timestamp and make sure it doesn't have quotes.
>
> Travis
>
> -----Original Message-----
> From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
>
> ...
>
> First off this is not really postgresql specific but it is driving me
> nuts.
>
>..
>
> Anyway, back on to the subject. I'm a little stuck and I'm wondering
how
> people
> handle the situation where a variable can contain a value _or_ a
> function
> call. For example:
>
> psql> create table mytab ( thetime timestamptz );
>
> perl:
> $sth = $dbh->prepare('insert into mytab values ( ? )');
> $sth->execute($thetime);
>
> where $thetime could hold 2003-01-29 13:45:06+00 _or_
current_timestamp.
>
> ...
>
> So just how do others manage this situation without resorting to
special
> casing
> everything?
One option would be to check the output of a function from the Date::Manip package, which could be used to check for a valid timestamp; if it's undef(), return the original string; otherwise, return a timestamp. Then you could write a custom quote() that would be sensitive to the function vs. literal issue. ap ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu On Wed, 29 Jan 2003, Nigel J. Andrews wrote: > > First off this is not really postgresql specific but it is driving me nuts. > > I thought I was using DBI to avoid the issues involved in constructing a SQL > query string using values held in variables. It turns out I'm not I'm using it > because it let's me write fetchrow_blah instead of some DB specific function > that does the samething, like the nice simple API of Pg that no one likes to > suggest people use. > > Anyway, back on to the subject. I'm a little stuck and I'm wondering how people > handle the situation where a variable can contain a value _or_ a function > call. For example: > > psql> create table mytab ( thetime timestamptz ); > > perl: > $sth = $dbh->prepare('insert into mytab values ( ? )'); > $sth->execute($thetime); > > where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp. > > Obviously these are just going to be normal string scalars in perl and DBI is > just going to stick them in place as constant strings. Indeed it's difficult to > see how it could do otherwise without going to great lengths. Even if it did, > what then would it do if the column type was text? The trouble being is guess > what happens when you do: > > insert into mytab values ('current_timestamp'); > > Yep, it doesn't like trying to insert an incorrect timestamp representation > into a timestamp field. > > So just how do others manage this situation without resorting to special casing > everything? > > > -- > Nigel J. Andrews > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
At 10:30 PM 1/29/03 +0000, Nigel J. Andrews wrote:
>psql> create table mytab ( thetime timestamptz );
>
>perl:
> $sth = $dbh->prepare('insert into mytab values ( ? )');
> $sth->execute($thetime);
>
>where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
>So just how do others manage this situation without resorting to special
>casing
>everything?
I use 'now' instead of current_timestamp. Despite current_timestamp being
the SQL standard, I figure 'now' is the way to go. Based on recent posts I
gather that the developers have no plans to break that, so it'll be fine to
use it.
Naturally it doesn't work if the field type is text.
Loosely looking for current_timestamp and then not filtering can be
dangerous in uncontrolled environments. e.g. webapps - users could put
current_timestamp in a cgi parameter followed by not so nice SQL.
I suggest you put the functions and stuff in the main SQL, and leave the
placeholders for the data/variables. That way the changeable stuff gets
quoted, and the static stuff is known to be safe.
Hope this helps,
Link.
On Wed, Jan 29, 2003 at 22:30:49 +0000, "Nigel J. Andrews" <nandrews@investsystems.co.uk> wrote: > > So just how do others manage this situation without resorting to special casing > everything? In this particular case you could use 'now' instead of current_timestamp. In some other cases you might be able to use default to use the default value. In general you probably want two different sql statements and not try to mix constant data and function calls in the same prepared statement.
It occurs to me that you are sort of trying to bypass / defeat the purpose
of "timestamp" -- I never try to create a home-grown timestamp -- Always
use the postgreSQL CURRENT_TIME and NOW() for postgreSQL timestamps...
ALSO: you probably already know but not all integers are integers -- There's
int2, int4, int4, bigint, tinyint, longint, integer, serial, auto_increment,
sequence, auto_number -- It depends on whose database you are using... The
same thing applies for all "data types"... floats, reals, strings...
As for "special casing" -- Who says Perl times are compatible with postgres
times... Almost every time is system dependent -- Therefore -- you can
either hard-code your perl scripts to match the OS or comply with
postgreSQL's implementation (making them less portable) -- OR -- You can
have yourself an epiphany and rethink your database strategy...
your table might better be:
create sequence "mytable_seq";
create table mytable (
id int4 default nextval "mytable_seq",
thetime varchar(100) not null,
create_dt timestamptz default 'NOW()'
);
$SQLSTMT = "INSERT INTO mytable (thetime) VALUES ('$thetime');";
$SQL = $DBH->prepare($SQLSTMT);
$result = $SQL->execute();
""Nigel J. Andrews"" <nandrews@investsystems.co.uk> wrote in message
news:Pine.LNX.4.21.0301292217590.2839-100000@ponder.fairway2k.co.uk...
>
> First off this is not really postgresql specific but it is driving me
nuts.
>
> I thought I was using DBI to avoid the issues involved in constructing a
SQL
> query string using values held in variables. It turns out I'm not I'm
using it
> because it let's me write fetchrow_blah instead of some DB specific
function
> that does the samething, like the nice simple API of Pg that no one likes
to
> suggest people use.
>
> Anyway, back on to the subject. I'm a little stuck and I'm wondering how
people
> handle the situation where a variable can contain a value _or_ a function
> call. For example:
>
> psql> create table mytab ( thetime timestamptz );
>
> perl:
> $sth = $dbh->prepare('insert into mytab values ( ? )');
> $sth->execute($thetime);
>
> where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
>
> Obviously these are just going to be normal string scalars in perl and DBI
is
> just going to stick them in place as constant strings. Indeed it's
difficult to
> see how it could do otherwise without going to great lengths. Even if it
did,
> what then would it do if the column type was text? The trouble being is
guess
> what happens when you do:
>
> insert into mytab values ('current_timestamp');
>
> Yep, it doesn't like trying to insert an incorrect timestamp
representation
> into a timestamp field.
>
> So just how do others manage this situation without resorting to special
casing
> everything?
>
>
> --
> Nigel J. Andrews
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
First, I've not managed to keep up with the traffic this week so I'm only just
getting around ot seeing some messages, plus an upstream network fault had me
offline all morning the other day and that always plays havoc with email
delivery from the lower priority servers.
However, I see nothing wrong with using timestamp to hold a timestamp. That's
what it's there for. However, I do see your argument that if it's not needed as
a timestamp but merely to hold a representation of a timestamp that someone has
entered then there's nothing lost storing it as a text type. That's fine if you
know that that is all you need from the column but what about if you want to
see how many widgets are expected to delivered by suppliers next month?
In short, a timestamp is a timestamp, it's not just there for recording
now() in a 'created at' or 'updated at' column.
Interesting point about epoch, which I assume your compatibility remark is
meaning, though. Given the result of time() system call should this always be
run through localtime()/gmtime() etc. to place into a textual representation
postgres understands before handing it over? I've seen on the list, and I
believe used myself, sql which gives the server time since epoch and let's it
determine the value of a timestamp from that.
BTW, thanks for bearing with me on this not so postgres specific question I
asked.
--
Nigel Andrews
On Thu, 30 Jan 2003, codeWarrior wrote:
> It occurs to me that you are sort of trying to bypass / defeat the purpose
> of "timestamp" -- I never try to create a home-grown timestamp -- Always
> use the postgreSQL CURRENT_TIME and NOW() for postgreSQL timestamps...
>
> ALSO: you probably already know but not all integers are integers -- There's
> int2, int4, int4, bigint, tinyint, longint, integer, serial, auto_increment,
> sequence, auto_number -- It depends on whose database you are using... The
> same thing applies for all "data types"... floats, reals, strings...
>
> As for "special casing" -- Who says Perl times are compatible with postgres
> times... Almost every time is system dependent -- Therefore -- you can
> either hard-code your perl scripts to match the OS or comply with
> postgreSQL's implementation (making them less portable) -- OR -- You can
> have yourself an epiphany and rethink your database strategy...
>
> your table might better be:
>
> create sequence "mytable_seq";
> create table mytable (
>
> id int4 default nextval "mytable_seq",
> thetime varchar(100) not null,
> create_dt timestamptz default 'NOW()'
>
> );
>
> $SQLSTMT = "INSERT INTO mytable (thetime) VALUES ('$thetime');";
> $SQL = $DBH->prepare($SQLSTMT);
> $result = $SQL->execute();
>
>
> ""Nigel J. Andrews"" <nandrews@investsystems.co.uk> wrote in message
> news:Pine.LNX.4.21.0301292217590.2839-100000@ponder.fairway2k.co.uk...
> >
> > First off this is not really postgresql specific but it is driving me
> nuts.
> >
> > I thought I was using DBI to avoid the issues involved in constructing a
> SQL
> > query string using values held in variables. It turns out I'm not I'm
> using it
> > because it let's me write fetchrow_blah instead of some DB specific
> function
> > that does the samething, like the nice simple API of Pg that no one likes
> to
> > suggest people use.
> >
> > Anyway, back on to the subject. I'm a little stuck and I'm wondering how
> people
> > handle the situation where a variable can contain a value _or_ a function
> > call. For example:
> >
> > psql> create table mytab ( thetime timestamptz );
> >
> > perl:
> > $sth = $dbh->prepare('insert into mytab values ( ? )');
> > $sth->execute($thetime);
> >
> > where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
> >
> > Obviously these are just going to be normal string scalars in perl and DBI
> is
> > just going to stick them in place as constant strings. Indeed it's
> difficult to
> > see how it could do otherwise without going to great lengths. Even if it
> did,
> > what then would it do if the column type was text? The trouble being is
> guess
> > what happens when you do:
> >
> > insert into mytab values ('current_timestamp');
> >
> > Yep, it doesn't like trying to insert an incorrect timestamp
> representation
> > into a timestamp field.
> >
> > So just how do others manage this situation without resorting to special
> casing
> > everything?
> >
> >
> > --
> > Nigel J. Andrews