Comments below.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "Michael A. Chase" <[EMAIL PROTECTED]>; "Xiaoxia Dong"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, March 15, 2001 5:35 PM
Subject: RE: [Fwd: how to check to a database to see if i need update or
insert]]
> I'm not sure if the reason for this is oracle specific, or something
coming
> from PERL, but I know I hate placeholders, and have yet to run into a
> situation why they are needed (although I will acknowledge that they are
> preferred by many). If I understand the cause of the error to be the
single
> or double quote nested within other quotes, would it not be easier to use:
>
> $sql = qq{UPDATE uptime SET up_time=$uphours
> WHERE hostname = $host and
----------------------------------------^^^^^
> startdate between
> (TO_DATE('$yy:$month:$sday:$shour:$sminute:00',
> 'YYYY:MM:DD:HH24:MI:SS') and
> TO_DATE('$yy:$month:$sday:$shour:$sminute:59',
> 'YYYY:MM:DD:HH24:MI:SS')
> )};
>
> and thus eliminate the need for placeholders here? or am I missing
something
> else in this?
You missed something and left in the exact same problem that started this
thread; the value in $host is not a valid column name. You can put as many
single quotes (') as you want inside double quotes (") or vice-versa. The
problem is that by pasting literal strings into SQL instead of using
placeholders you leave yourself wide open to this problem and worse.
$dbh->quote() can reduce the problem a bit, but it is not guaranteed to work
if some sick person sends you non-ASCII characters.
The real reason for placeholders, though is that they allow you to prepare a
statement once and execute it many times. Preparing tends to be expensive
in time and resources so you want to do it as seldom as possible. Normally
you prepare() a statement once before a loop and then execute() it many
times inside the loop. Even when you don't repeatedly execute a statement
in one instance of your program, Oracle caches SQL it prepares and can
re-use the execution plan if it sees the exact same SQL (including spaces
and capitalization) again.
At one site I tuned, the system was re-parsing the same query very
frequently because the program was using a literal string for the user name
as part of login processing. When the query was changed to use a bind
variable login time dropped from most of a minute to under ten seconds.
Run 'perldoc DBI' and read the sections marked 'Placeholders and Bind
Values', 'Performance', 'do' and, 'bind_param' for examples and discussions
of why placeholders are a good idea.
