CF MX and Oracle 9i Problem

[sachin_td]

I developed some templates using cf 5.0 and ms sql server 2000 and all worked well.

Now i migrate the same set of templates to cold fusion MX and oracle 9i. Now i am getting this message where ever i used query in the template
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended

[Daverms]

Hi,

Do you have multiple queries running under a <cfquery> </cfquery> blocks. If so, check whether each statements are separated with ";" (or else) make them as separate queries.

[Dan_Bracuk]

quote:

---

Originally posted by: Daverms
Hi,

Do you have multiple queries running under a <cfquery> </cfquery> blocks. If so, check whether each statements are separated with ";" (or else) make them as separate queries.

---

If that's the case, you have to make them separate cfqueries. With oracle you can only have 1 sql query per cfquery tag and you can not have a semi colon at the end.

[paross1]

Without an example of what is failing, it is real hard to offer much advice. Since you are converting from MS SQL Server to Oracle, it could be many things, including things like SQL Server functions that are not compatible with Oracle.

Phil

[Dan_Bracuk]

I get that message a lot. The most frequent reason is that I am missing a comma somewhere.

[sachin_td]

Thanks all for a quick reply


This is the complete code. I have been using different <cfquery> tag for each query then too i am getting the same error.



<cfinclude template="includes/ChkAdmin.cfm">
<cfparam name="marqueeMess" default="Good Morning">

<!---Query to retrieve record of logged in user--->
<cfquery name="qryRec" datasource="#Application.DataSourceName#">
SELECT * FROM tblusers WHERE vQLID like '#Session.QlkId#'
</cfquery>



<cfif login_type EQ "Admin">
<cfquery name="qryRetAllAct" datasource="#Application.DataSourceName#">
SELECT * FROM tblActivities AS A
INNER JOIN
tblActivityStatus AS B
ON
A.nActivityId=B.nActivityId
WHERE
(A.vActivityStatus LIKE 'open' OR vActivityStatus LIKE 'new')
AND
A.dplannedenddate<=#CreateODBCDate(DateFormat(now(),'mm/dd/yyyy'))#
</cfquery>
<cfelse>
<cfquery name="qryRetAllAct" datasource="#Application.DataSourceName#">
SELECT * FROM tblActivities AS A
INNER JOIN
tblActivityStatus AS B
ON
A.nActivityId=B.nActivityId
WHERE
(A.vActivityStatus LIKE 'open' OR vActivityStatus LIKE 'new')
AND
A.dplannedenddate<=#CreateODBCDate(DateFormat(now(),'mm/dd/yyyy'))#
AND
B.vQLID LIKE '#Session.Qlkid#'
</cfquery>
</cfif>






I found that its just because i am executing multiple queries which is not supported in oracle .Is there is any other way around through which i can execute multiple queries apart from storing in another file. As i am using the result of queries and queries are also using the form data.

Plz help its urgent

[Dan_Bracuk]

quote:

---

Originally posted by: sachin_td
Thanks all for a quick reply


This is the complete code. I have been using different <cfquery> tag for each query then too i am getting the same error.
I found that its just because i am executing multiple queries which is not supported in oracle .Is there is any other way around through which i can execute multiple queries apart from storing in another file. As i am using the result of queries and queries are also using the form data.

Plz help its urgent

---

Your 2nd paragraph contradicts the first.

This might not be your problem, but it's still a good idea to replace this:
A.dplannedenddate<=#CreateODBCDate(DateFormat(now(),'mm/dd/yyyy'))#
with this
A.dplannedenddate <= sysdate

Date format returns a string, but createodbcdate should convert it back. However, 0 functions are more efficient than 2.

Your error message should be telling you what query is having the problem.

[Daverms]

Hi,

Can you also post your "includes/ChkAdmin.cfm" code too?..

[sachin_td]

Hi all,

i wrote this query
SELECT count(*) FROM tblActivities A
INNER JOIN
tblActivityStatus B
ON
A.nActivityId=B.nActivityId
WHERE
(A.vActivityStatus LIKE 'open' OR A.vActivityStatus LIKE 'new')
AND
dplannedenddate<=sysdate

it worked fine in oracle. But when i embedd it in cold fusion i am getting the error
"Complex object types cannot be converted to simple values. "

[Newsgroup_User]

> SELECT count(*) FROM tblActivities A
> INNER JOIN
> tblActivityStatus B
> ON
> A.nActivityId=B.nActivityId
> WHERE
> (A.vActivityStatus LIKE 'open' OR A.vActivityStatus LIKE 'new')
> AND
> dplannedenddate<=sysdate
>
> it worked fine in oracle. But when i embedd it in cold fusion i am getting the
> error
> "Complex object types cannot be converted to simple values. "

That error will not be being caused by the query, because it's a CF error,
and there's no CF expressions in the query. All CF is doing with that SQL
is passing it straight to the DB driver; CF itself does not execute any
SQL.

The error should also have highlighted the line of code erroring, and the
exact code it doesn't like (which won't be any of the above).

--
Adam

[Karzons]

thanks so much for posting this

[oyunara]

teşekkürler

[OrDiNaRYuS-jsb6nL]

Çok teşekkürler.
Oyun | Sohbet | Video Sinema | Foto Galeri Eleman | Seni Seviyor