Coldfusion 2018 - Error Executing Database Query

[IQ1]

Hello,

   I am using Coldfusion 2018 with Oracle Database. One of my pages in the application gives me an error as shown below

How can this be resolved ?   I am not getting the same error on Test server where the same version of Cold fusion 2018 is installed.

Thanks

IQ

Error Executing Database Query.

ORA-01858: a non-numeric character was found where a numeric was expected

The error occurred in C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 134 Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 1 Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 134 Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 1

132 :        and dd.data_date = TO_DATE(<cfqueryparam 133 :                                   value="#time1#" 134 :                                   cfsqltype="VARCHAR" 135 :                           maxlength="28">,'YYYY/MM/DD hh24') 136 :        WHERE lu.IPN in (304305,304350,304354,304352,304309,304310,304311,304312, 304313, 304314)

and dd.data_date = TO_DATE(<cfqueryparam

133 : value="#time1#"

134 : cfsqltype="VARCHAR"

135 : maxlength="28">,'YYYY/MM/DD hh24')

136 : WHERE lu.IPN in (304305,304350,304354,304352,304309,304310,304311,304312, 304313, 304314)

[WolfShade]

I'm not sure if it's still done the same way in CF2018, but I know that in CF10 and CF11, the "line number" isn't always the correct line number when a query is involved.  It's usually (but not always) the last line of the query, itself.  So, in a nutshell, line #134 most likely isn't the issue.  Non-numeric character where a numeric character was expected makes me think that something is in the value that shouldn't be.

HTH,

^ _ ^

[IQ1]

So I understand it does not have to do with the line number but what is the solution to this issue, can someone please please get back to me.

[WolfShade]

Could you post the whole query as you currently have it?  I'd like to see where the issue might be.  I don't think it's in the section you provided, as it's dealing with a date object, not a numeric.

V/r,

^ _ ^

UPDATE: Have you tried changing the VARCHAR to a DATE or DATETIME data type?

[IQ1]

This is the error. Its about a package , when I check the package it compiles well nd no issues.

Browser   Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko
Remote Address   ::1
Referrer   http://localhost/eOPS/MultiSectionReports.cfm
Date/Time   06-May-19 10:35 AM

Stack Trace 
at cfMultiSectionReportsResults2ecfm2027920610.runPage(C:/inetpub/wwwroot/TOPS/MultiSectionReportsResults.cfm:69) at cfMultiSectionReportsResults2ecfm2027920610.runPage(C:/inetpub/wwwroot/TOPS/MultiSectionReportsResults.cfm:69)

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
ORA-06512: at "DBO.REPORT_PKG", line 490
ORA-06512: at "DBO.REPORT_PKG", line 48
ORA-06512: at "DBO.MULTI_REPORT_PKG", line 31
ORA-06512: at line 1

[WolfShade]

I was hoping to see the query, but what you provided might help.

I Googled ORA-00936 and it's, just as is stated in the stacktrace, a missing expression.  Part of your query is missing.

V/r,

^ _ ^

https://www.techonthenet.com/oracle/errors/ora00936.php

[IQ1]

Sorry the error is as shown below and not what I posted earlier, I googled the error code but my package does not seem to have this issue of invalid user.table.column, table.column

Browser   Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko
Remote Address   ::1
Referrer   http://localhost/eOPS/MultiSectionReports.cfm
Date/Time   06-May-19 02:37 PM

Stack Trace 
at cfMultiSectionReportsResults2ecfm2027920610.runPage(C:/inetpub/wwwroot/eOPS/MultiSectionReportsResults.cfm:69) at cfMultiSectionReportsResults2ecfm2027920610.runPage(C:/inetpub/wwwroot/eOPS/MultiSectionReportsResults.cfm:69)

java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

[WolfShade]

If you could provide your query, this would go a long way in helping to pin this down.

According to Oracle:

Cause

You tried to reference a column name, but the column name used is a reserved word in Oracle.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try redefining your table so that none of your column names are reserved words.

Option #2

Try enclosing the reserved word in double quotes.

V/r,

^ _ ^

[Charlie Arehart]

And more than just the sql as it may appear with the cfquery, do be sure to show us the values of any variables used in the sql, as they exist at the time of the error. 

Indeed, you may find that the error is caused by unexpected values in those variables. 

[Charlie Arehart]

Separate from the discussion about the sql, I would ask also:

Can you clarify what has changed–between when it last worked, and now, when it does not.

If you may say you applied a cf2018 update, do check out https://coldfusion.adobe.com/2019/03/problems-applying-cf-update-check-first/.

Also, as for the jdbc driver for oracle, is it the one that Adobe provides with CF, or one you implemented.

Finally, what cf2018 update are you on?

[IQ1]

Hi Charlie,

    I have installed cf2018 fresh on a windows 2016 server for test and prod environments. The same setup works without any errors on Test environment and on Prod environment I get the errors I have mentioned. So nothing really has changed and its not a cf update its a fresh installation of cf2018.

Thanks

IQ

[Charlie Arehart]

Ok, and we couldn't have known so had to ask.

But clearly something is different between the two tests run on the two different environments.

I would recommend you now focus on the two most recent other requests from me and BKBK, numbers 8 and 10 as they are tracked here in the forums ui: what are the values of the cfqueryparams, in prod?

They may vary from what you'd expect them to be, because of perhaps some OTHER issue/difference between your test and prod.

[IQ1]

how to find the values for cfqueryparams ?

[IQ1]

The only difference as I can see is I had to use JDBC driver connection string to create the datasource in Admin portal whereas in thye test environment I could directly select the Oracle option from the drop down list so did not have to use JDBC connection string in test, do you think this could be the reason ?

[EddieLotter]

What is the value of the time1 variable on the development server, and what is its value on the production server?

[IQ1]

How Can I find and use the JDBC driver which Adobe provides with cf ?

[IQ1]

Ok , I think I have noticed a trend in the error, in the Date from field if I change the time from 00:00 to anything else it does not throw any error. What does this mean, how can I fix the code to include time part of 00:00 as well.

[BKBK]

IQ1  wrote

Ok , I think I have noticed a trend in the error, in the Date from field if I change the time from 00:00 to anything else it does not throw any error. What does this mean, how can I fix the code to include time part of 00:00 as well.

Did you see the suggestion I made about the value of the string time1 and the mask you use in the to_Date function?

[BKBK]

IQ1  wrote

...

133 : value="#time1#"

...

135 : maxlength="28">,'YYYY/MM/DD hh24')

Two points you may have to look into.

1. Ensure that the string, time1, is of the form yyyy/mm/dd hh:mm:ss
2. Use the full mask in the to_Date function: YYYY/MM/DD HH24:MI:SS

[IQ1]

Hi BKBK, This was originally how the issue started but further work on this does not seem to give the Time part separately, for now I am unable to understand why it is not working for a time format 00:00:00 it works for any other time format other than this.

[BKBK]

I still wonder whether you have had a look at my suggestion. Specifically,

1. output the string time1 and verify whether it is in the format yyyy/mm/dd hh:mm:ss
2. in your code, replace YYYY/MM/DD HH24 with YYYY/MM/DD HH24:MI:SS

[IQ1]

I have already tried both the suggestions you mentioned and it does not seem to help, the error still remains the same.

[BKBK]

What did you get when you output time1?

[IQ1]

I got 2019/05/01 00:00:00

[BKBK]

The error implies that either the value or the mask is incorrect. What is the datatype of the column data_date?