Re: Perl DBI and placeheld values

From: "Williams, Travis L, NPONS" <tlw(at)att(dot)com>
To: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Perl DBI and placeheld values
Date: 2003-01-29 23:00:24
Message-ID: AB815D267EC31A4693CC24D234F8291603799BBF@ACCLUST02EVS1.ugd.att.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)investsystems(dot)co(dot)uk]
Sent: Wednesday, January 29, 2003 4:31 PM
To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Simon Mitchell 2003-01-29 23:07:17 Re: Status of tablespaces
Previous Message John Smith 2003-01-29 23:00:16 mass import to table with unique index