Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


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.2K

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
Adobe Community Professional ,
May 16, 2019 May 16, 2019

Copy link to clipboard

Copied

Adobe doesn't provide or recommend any JDBC drivers other than the ones they ship. And all of them behave a little differently. In general, if you can't afford to pay for third-party drivers like the DataDirect Technology drivers that CF Enterprise includes, you go with the vendor's "pure JDBC" type 4 drivers. Oracle provides these directly. But there's a reason that DataDirect is in business, and it's because they provide functionality that the vendors' own drivers don't.

Dave Watts, Eidolon LLC

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 17, 2019 May 17, 2019

Copy link to clipboard

Copied

Hi IQ1

I have filed a Coldfusion bug report. It's almost weekend so, what the heck, here's yet another adventurous attempt:

<cftry>

<!--- formatDatetime() is a user-defined function (for convenience). See below--->

<cfset fromDateObject=formatDatetime(dateFrom, "yyyy-mm-dd HH:nn:ss")>

<cfset toDateObject=formatDatetime(dateTo, "yyyy-mm-dd HH:nn:ss")>

<cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#,

      '#Session.UserName#',

      #multi_report_def_id#,

      <cfqueryparam value="#fromDateObject#" cfsqltype="CF_SQL_DATE"> || ' ' || <cfqueryparam value="#fromDateObject#" cfsqltype="CF_SQL_TIME">,

      <cfqueryparam value="#toDateObject#" cfsqltype="CF_SQL_DATE"> || ' ' || <cfqueryparam value="#toDateObject#" cfsqltype="CF_SQL_TIME">,

      #period#);

END;

</cfquery>

<cfcatch type="any">

  <cfdump var="#cfcatch#" label="cfcatch" >

  <cfdump var="#variables#" label="variables">

  <cfdump var="#session#" label="session">

</cfcatch>

</cftry>

<cffunction name="formatDatetime" returntype="date" hint="Returns the datetime object having a given mask">

<cfargument name="dt" type="string" required="true" hint="Input datetime as string">

<cfargument name="mask" type="string" required="true" hint="For example: yyyy-mm-dd HH:nn:ss">

<!--- Convert from string to date object --->

<cfset var dateObject=parseDatetime(arguments.dt)>

<!--- Apply mask; result is a string --->

<cfset var formattedDatetime=datetimeFormat(dateObject, arguments.mask)>

<!---Return a datetime object --->

<cfreturn parseDatetime(formattedDatetime)>

</cffunction>

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 17, 2019 May 17, 2019

Copy link to clipboard

Copied

LATEST

Thanks BKBK, I will execute your suggestions on the weekend and will keep you posted.

You have a great weekend.

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

OR

<cfstoredproc procedure="dbo.multi_report_pkg.run_multi_report" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#Session.multi_rep_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Session.UserName#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#datefrom#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN')">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN')">

</cfstoredproc>

As i was typeing this up, I notice that the TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN')

is missing ) after the SS' so that may be it even.....

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

IQ1  wrote

Changing  VARCHAR to CF_SQL_VARCHAR does not help, the error continues to remain the same.

No surprise there. From ColdFusion 11 onwards, cfsqltype="VARCHAR" and cfsqltype="CF_SQL_VARCHAR" are equivalent.

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