Why don't you try to debug this stuff in SQL*Plus before you go to DBI.
Issue 1 isn't really a DBI issue - it's an SQL issue.
Instead of "LIMIT 1" try adding "and rownum <= 1" to the where clause.
For the issue 2 - I'm not sure but it might be worth checking there are no
non-visible characters in the statement. I would also suggest you start
formatting these things for readability: e.g.
my $measurementssth2=$dbh->prepare("
SELECT TYPE, SPORES
FROM $measurements_table
WHERE (
POLLENTRAP_ID = ?
AND ALLER_ID = ?
AND ( TO_CHAR(SYSDATE,'IW')= TO_CHAR(DATECOLLECTION,'IW') )
)
");
Although I fear some of this formatting may get a little distorted by email.
Most of the brackets are redundant although not illegal so you could do:
my $measurementssth2=$dbh->prepare("
SELECT TYPE, SPORES
FROM $measurements_table
WHERE
POLLENTRAP_ID = ?
AND ALLER_ID = ?
AND TO_CHAR(SYSDATE,'IW')= TO_CHAR(DATECOLLECTION,'IW')
");
and see if it makes any difference.
Regards
Chris
----- Original Message -----
From: "spiros" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 26, 2004 6:41 PM
Subject: Two simple Oracle DBI issues(Oracle NEWBIE)
> Two problems:
> First, I tried:
> my $measurementssth=$dbh->prepare("SELECT POLLENTRAP_ID, ALLER_ID, TYPE,
> SPORES,FID, (SYSDATE-DATECOLLECTION) FROM $measurements_table WHERE
> (SYSDATE-DATECOLLECTION) <15 ORDER by 6 LIMIT 1 ") ;
>
> (trying to get the one line of data for which DATECOLLECTION IS closest to
> SYSDATE PROVIDED these are no older than 14days, else I shoul get NULL)
>
> and got:
> DBD::Oracle::db prepare failed: ORA-00933: SQL command not properly ended
(DBD
> ERROR: error possibly near <*> indicator at char 154 in 'SELECT
> POLLENTRAP_ID, ALLER_ID, TYPE,
> SPORES,FID, (SYSDATE-DATECOLLECTION) FROM mysc.MEASUREMENTS WHERE
> (SYSDATE-DATECOLLECTION) <15 ORDER by 6 <*>LIMIT 1 ') [for Statement
> "SELECT POLLENTRAP_ID, ALLER_ID, TYPE,
> SPORES,FID, (SYSDATE-DATECOLLECTION) FROM mysc.MEASUREMENTS WHERE
> (SYSDATE-DATECOLLECTION) <15 ORDER by 6 LIMIT 1 "] at alarmchk.pl line
176.
>
> IT looks like it does not like LIMIT. Is this not legal in Oracle? Surely
> there must be a way to do this
>
> **** SECOND PROBLEM ***
> Another statement that fails is when I try to bind params
>
> my $measurementssth2=$dbh->prepare("SELECT TYPE,
> SPORES FROM $measurements_table WHERE (POLLENTRAP_ID = ? AND ALLER_ID=?
AND
> ( TO_CHAR(SYSDATE,'IW')=
> TO_CHAR(DATECOLLECTION,'IW') ) ) ");
>
> and got:
> DBD::Oracle::db prepare failed: ORA-00907: missing right parenthesis (DBD
> ERROR: error possibly near <*> indicator at char 70 in 'SELECT TYPE,
> SPORES FROM mysc.MEASUREMENTS WHERE ( POLLENTRAP_ID<*>=:p1 AND FID=:p2
AND
> ( TO_CHAR(SYSDATE,'IW')=
> TO_CHAR(DATECOLLECTION,'IW') ) ') [for Statement "SELECT
> TYPE,SPORES FROM mysc.MEASUREMENTS WHERE ( POLLENTRAP_ID=? AND FID=? AND
> ( TO_CHAR(SYSDATE,'IW')=
> TO_CHAR(DATECOLLECTION,'IW') ) "] at alarmchk.pl line
180.
>
>
> Again, is there something I am missing about bind params in Oracle?
>
>
>
