Coldfusion 2018 - Error Executing Database Query

Explorer ,
Apr 24, 2019 Apr 24, 2019

Copy link to clipboard

Copied

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)

Views

3.0K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , May 14, 2019 May 14, 2019
We're getting there. This error says there is a problem with a column name.Check whether there is a column name having an illegal character. Did you perhaps use a column name that is a reserved word in Oracle?

Likes

Translate

Translate
LEGEND ,
Apr 24, 2019 Apr 24, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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 (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 06, 2019 May 06, 2019

Copy link to clipboard

Copied

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?


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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.


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

how to find the values for cfqueryparams ?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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 ?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 07, 2019 May 07, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

What did you get when you output time1?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines