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)

[Dave Watts]

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

[BKBK]

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>

[IQ1]

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

You have a great weekend.

[Matthew C. Parks]

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

[BKBK]

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.