I'm converting an application from MySQL to Oracle. 95% of the SQL
syntax works for either db, I'm just trying to fix the 5% that doesn't.
Specs: CFMX 7 J2EE & Oracle 10g Linux
In a thread on CF-Talk, someone pointed out that you could use
YesNoFormat() to set the null attribute of <cfqueryparam>. I've rarely
ever used YesNoFormat(), so it was like a "well, duh" moment for me.
tableOne.FOO is an integer value, NOT NULL and FOO is PK of tableOne.
tableTwo.FOO is an integer value, NULL and references tableOne.FOO as FK
constraint.
So code that had been
<cfif arguments.foo EQ 0>
AND FOO IS NULL
<cfelse>
AND FOO = <cfqueryparam value="#arguments.FOO#"
cfsqltype="cf_sql_integer" />
</cfif>
could be slimmed down to one line like this:
AND FOO <cfif arguments.FOO EQ 0>IS<cfelse>=</cfif> <cfqueryparam
value="#arguments.FOO#" cfsqltype="cf_sql_integer"
null="#YesnoFormat(arguments.FOO EQ 0)#"/>
This works on MySQL and SQL Server, but oracle's having a fit. It tells
me "missing NULL keyword".
<cfdump>ing the <cfcatch> contents shows
AND FOO IS (param 1)
. . .
(param 1) = [type='IN', value='null', sqltype='cf_sql_integer']
so it's sending null in correctly.
It even throws an error using
AND FOO IS <cfqueryparam value="" cfsqltype="cf_sql_integer" null="yes"/>
It only works if I go back to the original, verbose code and
specifically write "AND FOO IS null".
Anyone have an idea why Oracle can't handle this?
Thanks,
Adrian
----------------------------------------------------------
To post, send email to [email protected]
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm
