We are using Oracle 8.1.7 and I keep getting the above error with the following query (executed via Cold Fusion 5.0):

Oracle Error Code = 1036

ORA-01036: illegal variable name/number

SQL = "

SELECT P.Disabled,
P.SiteID,
PC.EmailAddress,
P.PersonID AS PID,
PC.PrimaryEmail,
P.Password

FROM tbl_Person P,
tbl_Person_Contact PC

WHERE (P.PersonID = PC.PersonID)
AND (P.SiteID = :1)
AND (PC.PrimaryEmail = :2)
AND (PC.EmailAddress = ':3')
AND (P.Password = ':4')

ORDER BY P.PersonID

"

Query Parameter Value(s) -

Parameter #1 = 2
Parameter #2 = 1
Parameter #3 = test@something.com
Parameter #4 = test

From what I've read, the error could be caused by one or all of the following reasons:

1) Use of "reserved word" in the column name of "Password".
2) Not properly "binding" columns in the SQL statement with variables in the output.

I have tried using brackets around Password in the above SQL statement, but the error persists. I have also tried removing the column alias of PID, but the error persists.

Any suggestions would be greatly appreciated.

Thanks in advance.

Are you sure that
PC.EmailAddress = ':3'
is being substitued by test@something.com ??

Shoud this be somthing like
PC.EmailAddress = char(39) + :3 + char(39)
to add the ansi ' before and after the variable :3

clear as mud

Thank you very much for your reply! However, that is incorrect in terms of how Cold Fusion parameterizes variables within SQL statements. The parameterization is correct, but again, the error persists.

I think it may have something to do with the column type for "Password", since it was set to RAW rather than VARCHAR. I am going to try to change the column type and also the SQL type in the Cold Fusion parameterization codes.

Thanks!