Hi Nitai,
No, in the actual statement used in the CFQUERY the trailing ; is
removed.
As soon as I remove the where in ( ) statements in my query it runs.
So my assumption is tat OpenBD parses the statement and simply add
doubble single quotes, and with that I'm receing the same Error
message also in the SQL-Developer which also uses jdbc to connect to
the DB...
It's a template that dynamically generate a DataSource, CFQUERY and
cfoutput statement. It's ment to be an Add-on to our Nagios Monitoring
system where we monitor several Oracle DB's. In the Nagios System
we're having a shell script that is beeing called with an oracle
connect string e.g user/pas...@orasid and a Key. Ths shell is reading
a .cfg file which simply is a text file with several SQL statements
prefixed with a key value. each line representing a key and a select
statement eg.
pending_orders select ordnumber from some_oracle_db where some_field =
some_value
Here the pending_order is the key and...
What I'm doing is simple reading the same cfg file using CFLOOP and
delimiters chr(10) to parse this file. Loading the key, sql statements
and a column header into 3 Array's
Need to handle the "header's" for my cfoutput so my output finally
referencing the query properly.
Removing the first select and getting the reast of the line up to the
first from statement so I can properly output my query using Evaluate
(also in a CFLOOP)
It's a bit of find and replace to get that working for example
replacing select count(*) as iCount from some_db where...
Replacing all "count(*) as" with nothing so I only have the iCount
left..
The routine is working just fine... it's just gets messed up when I
have these single quotes in the where statements.
This is the array result result from the above problem:
Key: otms_pend_order
Sql: select to_char(cobjekt) as cobjekt from os_kopf k where k.RCPHASE
in ('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','HXDVETR10') union
select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
where k.iauftrag=b.iauftrag and k.RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000') and k.dchange <
sysdate-1/1440 union select to_char(k.iauftrag) as iauftrag from
os_kopf k, bo_auftrag b, os_tradingplaceese e where
k.iauftrag=b.iauftrag and b.CBROKER=e.cbroker and RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
k.dchange < sysdate-1/1440 and b.CBOERSE = e.CCODE and
k.CDESTINATION=e.CDESTINATION and DOPENINGTIME < sysdate-2/1440 union
select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
where k.iauftrag=b.iauftrag and k.RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
(b.fboanzahl - b.fboplazierungsumme) > 0
Hdr: cobjekt
The Key what Im passing in to the template together with the oracle
connect string. Sql: is the statement it picks up from the cfg file
and Hdr: is what I'm using in the CFOUTPUT.
I wouldn't mind posting the code here but I really don't think my code
is doing anything wrong (atleast not this time :) )
Regards,
Mats
On Dec 27, 10:04 am, "Nitai @ Razuna" <[email protected]> wrote:
> Can't really see anything wrong, but if you have a ";" at the end of
> your SQL then remove it. Else post your CFML code.
>
> Kind Regards,
> Nitai
>
>
>
>
>
> On Mon, Dec 27, 2010 at 9:39 AM, Mats <[email protected]> wrote:
> > Hi everyone,
> > I'm having a wierd problem with a select statement in OpenBD
> > connecting to an Oracle 10gR2 DB.
> > The actual statement is:
>
> > select to_char(cobjekt) as cobjekt from os_kopf k where k.RCPHASE in
> > ('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','HXDVETR10') union
> > select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
> > where k.iauftrag=b.iauftrag and k.RCPHASE in
> > ('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000') and k.dchange <
> > sysdate-1/1440 union select to_char(k.iauftrag) as iauftrag from
> > os_kopf k, bo_auftrag b, os_tradingplaceese e where
> > k.iauftrag=b.iauftrag and b.CBROKER=e.cbroker and RCPHASE in
> > ('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
> > k.dchange < sysdate-1/1440 and b.CBOERSE = e.CCODE and
> > k.CDESTINATION=e.CDESTINATION and DOPENINGTIME < sysdate-2/1440 union
> > select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
> > where k.iauftrag=b.iauftrag and k.RCPHASE in
> > ('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
> > (b.fboanzahl - b.fboplazierungsumme) > 0;
>
> > The error I'm getting is an ORA-907: missing right parenthesis
>
> > This statement is working just fine in SQL*plus and in Toad. I also
> > checked it in SQL-Developer to confirm if it was a jdbc or sqlnet
> > problem and it worked also there.
>
> > What I did discover was that in my "IN ( )" clauses if I had double
> > single quotes I got the same error messege in SQL Developer.
> > This is also what OpenBD returns in the error page
>
> > Native Error Code 907
> > SQL State 42000
> > SQL select to_char(cobjekt) as cobjekt from os_kopf k where k.RCPHASE
> > in
> > (''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''HXDVETR10'')
> > union select to_char(k.iauftrag) as iauftrag from os_kopf k,
> > bo_auftrag b where k.iauftrag=b.iauftrag and k.RCPHASE in
> > (''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'') and
> > k.dchange < sysdate-1/1440 union select to_char(k.iauftrag) as
> > iauftrag from os_kopf k, bo_auftrag b, os_tradingplaceese e where
> > k.iauftrag=b.iauftrag and b.CBROKER=e.cbroker and RCPHASE in
> > (''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''AHBOESE01'')
> > and k.dchange < sysdate-1/1440 and b.CBOERSE = e.CCODE and
> > k.CDESTINATION=e.CDESTINATION and DOPENINGTIME < sysdate-2/1440 union
> > select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
> > where k.iauftrag=b.iauftrag and k.RCPHASE in
> > (''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''AHBOESE01'')
> > and (b.fboanzahl - b.fboplazierungsumme) > 0
>
> > All my single quotes is replaced by doubble single quotes.
> > So... my question is... Is OpenBD doing this also in the CFQUERY or
> > just in the error message?
>
> > I'm on a OpenBD 1.5 Nightly Build from 16th of December 2010
>
> > Thanks in Advance
> > Mats
>
> > --
> > Open BlueDragon Public Mailing List
> > http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
> > official manual:http://www.openbluedragon.org/manual/
> > Ready2Run CFMLhttp://www.openbluedragon.org/openbdjam/
>
> > mailing list -http://groups.google.com/group/openbd?hl=en
>
> --
> See for yourself how easy it is to manage files today. Join the revolution!
>
> Razuna SaaS On-Demand - Hosted Digital Asset Management
> Solutionhttp://www.razuna.com/
>
> Razuna - Open Source Digital Asset Managementhttp://www.razuna.org/
>
> Follow us on Twitterhttp://twitter.com/razunahq- Hide quoted text -
>
> - Show quoted text -
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
official manual: http://www.openbluedragon.org/manual/
Ready2Run CFML http://www.openbluedragon.org/openbdjam/
mailing list - http://groups.google.com/group/openbd?hl=en
