Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ORA-00911: invalid character -runs fine in SQLPlus

75 views
Skip to first unread message

bill_doe

unread,
Apr 26, 2004, 7:31:49 AM4/26/04
to
I am new to Cold Fusion. I am creating a search page so users can search a
database table's records based on a number of fields. The query executes fine
when using numeric fields, but when using a character field, it's giving me an
"ORA-00911: invalid character" error every time. When using date fields, it's
giving me the "ORA-00905: missing keyword" error every time. I have printed
the actual query to the screen and copied it to the SQLPlus command prompt and
it works without fail, so I know the query is built correctly. I have looked
high and low on the Web and can't seem to find any answers. Any ideas? My
code for the form handler is below. I really appreciate any help you can
provide.

Thanks,

Bill

<!--- include the header.cfm file --->
<CFINCLUDE template="header.cfm">

<!--- Include the database connections file --->
<CFINCLUDE template="dbconnect.cfm">

<!--- Set the defaults for the form fields --->
<cfparam name="form.exp_desc" default="">
<cfparam name="form.exp_cat" default="">
<cfparam name="form.begin_date" default="">
<cfparam name="form.end_date" default="">
<cfparam name="form.low_exp_amt" default="">
<cfparam name="form.high_exp_amt" default="">
<cfparam name="form.reimbursement" default="">

<!--- Set the variables equal to the field values --->
<cfset exp_desc=#form.exp_desc#>
<cfset exp_cat=#form.exp_cat#>
<cfset begin_date=#DateFormat(form.begin_date, 'dd-mmm-yy')#>
<cfset end_date=#DateFormat(form.end_date, 'dd-mmm-yy')#>
<cfset low_exp_amt=#form.low_exp_amt#>
<cfset high_exp_amt=#form.high_exp_amt#>
<cfset reimbursement=#form.reimbursement#>

<!--- Begin the process of building a query based on user search criteria--->
<cfset getquery = "SELECT EXPDESC, DATEINCURRED, CATDESC, EXPAMOUNT,
FORREIMBURSEMENT, DATEREIMBURSED,
EXPENSES.EMPID, EXPENSES.CATID, EXPID
FROM EXPENSES, EXPCATEGORIES, EMPLOYEES
WHERE EXPENSES.CATID = EXPCATEGORIES.CATID AND EXPENSES.EMPID =
EMPLOYEES.EMPID ">

<!--- If the exp_desc form field is not empty, add the report criteria to the
query --->
<cfif #form.exp_desc# NEQ "">
<cfset getquery = #getquery# & " AND EXPDESC LIKE " & "'%" & #exp_desc# &
"%'">
</cfif>

<!--- If the exp_cat form field is not empty, add the report criteria to the
query --->
<cfif #form.exp_cat# NEQ "Unknown">
<cfset getquery = #getquery# & " AND EXPENSES.CATID = " & #exp_cat#>
</cfif>

<!--- If the reimbursement form field is not empty, add the report criteria to
the query --->
<cfif #form.reimbursement# NEQ "">
<cfset getquery = #getquery# & " AND FORREIMBURSEMENT = '" & #reimbursement#
& "'">
</cfif>

<!--- If the begin_date form field is not empty and the end_date form field is
empty,
add a single date match to the query --->
<cfif #form.begin_date# NEQ "" AND #form.end_date# EQ "">
<cfset getquery = #getquery# & " AND DATEINCURRED = '" & #begin_date# & "'">
<!--- Else if the begin_date form field and the end_date form field are not
empty,
add a between date match to the query --->
<cfelse>
<cfif #form.begin_date# NEQ "" AND #form.end_date# NEQ "">
<cfset getquery = #getquery# & " AND DATEINCURRED BETWEEN '" & #begin_date#
& "' AND '" & #end_date# & "'">
</cfif>
</cfif>

<!--- If the low_exp_amt form field is not empty and the high_exp_amt form
field is empty,
add an exact amount match to the query --->
<cfif #form.low_exp_amt# NEQ "" and #form.high_exp_amt# EQ "">
<cfset getquery = #getquery# & " AND EXPAMOUNT = " & #low_exp_amt#>
<!--- Else if the low_exp_amt form field and the high_exp_amt form field are
not empty,
add a between amounts match to the query --->
<cfelse>
<cfif #form.low_exp_amt# NEQ "" and #form.high_exp_amt# NEQ "">
<cfset getquery = #getquery# & " AND EXPAMOUNT BETWEEN " & #low_exp_amt# &
" AND " & #high_exp_amt#>
</cfif>
</cfif>

<!--- Add the order by dateincurred clause to the query --->
<cfset getquery = #getquery# & " ORDER BY DATEINCURRED">

<cfoutput>#getquery#</cfoutput>

<!--- Query to retrieve the recordcount from the expenses table --->
<cfquery name="getdata1" datasource="novaoracle" username=#dblogin#
password=#dbpassword#>
#getquery#
</cfquery>

<!--- If the recordcount is greater than 0, get the maximum expense id --->
<cfif getdata1.recordcount EQ 0>
<p>No records returned. Please refine your search criteria by clicking <a
href="search.cfm">here</a>.</p>
<cfelse>

<table width="750" border="0px" cellpadding="0px" cellspacing="3px">
<tr>
<td colspan="4" align="center"><h3>Search Results:</h3></td>
</tr>
</table>
<hr>
<!--- Loop through the getdata1 query results --->
<cfloop query="getdata1">
<table width="750" border="0px" cellpadding="0px" cellspacing="3px">
<tr>
<td width="300"><b>Date Expense Incurred:</b>
<cfoutput>#DateFormat(getdata1.dateincurred, 'mm-dd-yyyy')#</cfoutput></td>
<td width="300"><b>Category:</b>
<cfoutput>#getdata1.CatDesc#</cfoutput></td>
<td width="150"><form method="post" action="edit_expense2.cfm">
<input type="hidden" name="exprecord"
value="<cfoutput>#getdata1.expid#</cfoutput>">
<input type="submit" value="Edit Record">
</form>
</td>
</tr>
<tr>
<td><b>Expense Amount:</b>
<cfoutput>#DollarFormat(getdata1.expamount)#</cfoutput></td>
<td><b>For Reimbursement/Date:</b>
<cfif #getdata1.forreimbursement# EQ "Y">
<cfoutput>Yes/#DateFormat(getdata1.datereimbursed, 'mm-dd-yyyy')#</cfoutput>
<cfelse>
<cfoutput>No</cfoutput>
</cfif>
</td>
<td width="150"><form method="post" action="delete_expense2.cfm">
<input type="hidden" name="exprecord"
value="<cfoutput>#getdata1.expid#</cfoutput>">
<input type="submit" value="Delete Record">
</form>
</td>
</tr>
<tr>
<td colspan="2"><b>Expense Description:</b>
<cfoutput>#getdata1.ExpDesc#</cfoutput></td>
<td>&nbsp;</td>
</tr>
</table>
<hr>
</cfloop>
</cfif>

<!--- Include the footer.cfm file --->
<CFINCLUDE template="footer.cfm">


bill_doe

unread,
Apr 27, 2004, 3:09:50 PM4/27/04
to
You, Sir, are a savior. I can't thank you enough! Bill

OldCFer

unread,
Apr 27, 2004, 3:02:44 PM4/27/04
to
The probable reason is that single quotes used around SQL values in the
SQL statement you are building are getting escaped. Try:

<cfquery name="getdata1" datasource="novaoracle" username=#dblogin#
password=#dbpassword#>

#PreserveSingleQuotes(getquery)#
</cfquery>


OldCFer

unread,
Apr 27, 2004, 3:36:08 PM4/27/04
to
No problem. For future reference the rule is if single quotes are inside a cf
variable in a cfquery tag,
and they are part of the SQL statemant itself (around text values, IN lists
etc), then you have to
use Preservesinglequotes(). If they are embedded in the data like "O'Brian",
then CF will automatically
escape them, double them up, for you and Preservesinglequotes() will cause the
error. So guess
what? If you build an SQL statement variable AND the data inside that has
single quotes in it then
it gets tricky.

bill_doe

unread,
Apr 27, 2004, 5:44:15 PM4/27/04
to
Well, isn't that fun! Thanks again!
0 new messages