Well, this "atleast not this time..." didn't work :) It actually was my own fault. Since I was passing in the sql-string from my Array I had to use the Preservesinglequotes( ) and my Error was gone. Sorry about that :) Thanks anyway Nitai!!!
Regards, Mats On Dec 27, 10:39 am, Mats <[email protected]> wrote: > 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 -- 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
