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)

[IQ1]

Data type is Date

[BKBK]

Thanks for your reply. To rule options out, what happens when you test with:

dd.data_date = TO_DATE(<cfqueryparam

value="#time1#"

cfsqltype="VARCHAR"

maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN')

[IQ1]

I get

Error Executing Database Query.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

[BKBK]

IQ1  wrote

I get

Error Executing Database Query.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

The error is no longer at line 134?

[IQ1]

yes the line number of error is different,

Charlie, is there anyway for us to download or upgrade to Enterprise edition instead of Standard edition ?

[BKBK]

IQ1  wrote

yes the line number of error is different

Could you then show us the corresponding code?

[IQ1]

This is the code being used

<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#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#DateFormat(dateto, "dd-mm-yyyy")# #TimeFormat(dateto, "HH:mm:ss")#', 'DD-MM-YYYY HH24:MI:SS'), #period#); END;

 
</cfquery>

[BKBK]

What happens when you replace #period# with 'AMERICAN'?

[IQ1]

still the same result when it is AMERICAN

[BKBK]

Suggestion (note the nn in the timeFormat mask):

<cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "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#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END;

<cfquery

[IQ1]

Thanks, I tried your suggestion but get the following error.

Error Executing Database Query.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 74

72 : <cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "HH:nn:ss")>
73 : <cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
74 : BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END;
75 : </cfquery>
76 :

SQLSTATE   65000

DATASOURCE   histt

VENDORERRORCODE   6502

SQL    BEGIN dbo.multi_report_pkg.run_multi_report(4522, 'fsuresh2', 7, TO_DATE( (param 1) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('08-05-2019 23:59:00', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END; 

Resources: •Check the ColdFusion documentation to verify that you are using the correct syntax.

•Search the Knowledge Base to find a solution to your problem.

[Charlie Arehart]

If these efforts with bkbk don't work out, let's go back to an earlier point you made: the oracle dsn you have differs between prod and test. This could easily be the root cause of the issue, now that we know (you say) that the values of the vars are the same on both (and so the sql should be identical).

You said that you could not see an oracle driver to pick in prod, like you could in test. So I suspect your prod is running with a cf Standard license, while perhaps test is running the free Dev editon, which runs as basically the equivalent to cf Enterprise.

Sadly, that means your test env has access to features your prod does not, like that built-in Adobe-provided Oracle driver (This is not a problem unique to CF: it happens with SQL Server as well, and perhaps also with oracle, where free Dev editions offer enterprise features but don't allow for emulation of standard features).

So you mentioned implementing some jdbc connection string to get thins working in prod. What is that? More important, what is the jdbc driver being used? And as you or someone seemingly had to download and implement an oracle driver jar, which was it?

Finally, have you considered implementing that as a new dsn in test, simply for testing to see if the same problem would happen there? (You could give it a new dsn name, so as to have no impact on use of the existing one in test.) If it then had the same error, you know it's about the driver.

Then you could focus your question on THAT, knowing not it's not really a CF question. You could even take the question to an oracle forum, but note you'd talk about it being a "parameterized query", as that's the generic term for what cfqueryparam does. That said, it would help to prove if your error with the 0 time happens even as a hard-coded sql statement, to avoid complicating things with mention of parameterized queries, if you may ask about it elsewhere.

You may well find, perhaps in the docs of whatever driver you are using, that there's some connection string that would fix your problem. Or  maybe there's an oracle plsql function which could fix it while workibg ok with either driver, and so could work in either test or prod.

Let us know how it goes. I really think this may be where you should turn next.

[Matthew C. Parks]

Inside your cfqueryparam you have .....cfsqltype="VARCHAR"....  it needs to be  CF_SQL_VARCHAR, so it reads cfsqltype="CF_SQL_VARCHAR".

I think that should do it.

[IQ1]

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

[Matthew C. Parks]

TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),

could be changed to this

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

This way the whole to_date() function is sent in the (param 1)

OR why dont you do like you did for the toDateTime, set a varable fromDateTIme and remove the cfqueryparam altogether.

Another item i was screching my head on is the BEGIN and END (anonymous SQL block) that is allowed in ORACLE, I have never had luck with that personally.

[IQ1]

when I make the change you suggest, I now get an error . So looks like we are getting closer to resolving this.

Error Executing Database Query.

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at line 1

[BKBK]

I found the cause of the error: an extra bracket ")". Sorry, IQ1

This should do it.

<cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "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#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END;

<cfquery

[IQ1]

Thanks, I tried your suggestion. This time the error is

Error Executing Database Query.

ORA-06550: line 1, column 182: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ The symbol ")" was substituted for ";" to continue.  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 74

72 : <cfset toDateTime=dateFormat(datefrom, "dd-mm-yyyy") & " " & timeFormat(datefrom, "HH:nn:ss")>
73 : <cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
74 : BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END;
75 : </cfquery>
76 :

SQLSTATE   65000
DATASOURCE   histt
VENDORERRORCODE   6550
SQL    BEGIN dbo.multi_report_pkg.run_multi_report(4534, 'fquresh2', 7, TO_DATE( (param 1) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('09-05-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END; 

Resources: 

[Matthew C. Parks]

Well, that error is consistant with the error I would expect when attempting to execute Anonomsys blocks of code inside an <CFQUERY> tag.

Please try this <cfstoredproc> to call and see what happens.  OH, and what is the value of the todateTime variable? I only as that due to the other error of  "(full) year must be between -4713 and +9999, and not be ".

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

[IQ1]

Sorry , I am new with coldfusion., can you also let me know the code to call a stored procedure from coldfusion.

Thanks

IQ

[Dave Watts]

Hi, IQ!

Matthew has included that in his response. Use the CFSTOREDPROC, CFPROCPARAM and CFPROCRESULT tags to call stored procedures.

Dave Watts, Eidolon LLC

[IQ1]

So I replaced the code with the stored procedure as suggested and as shown below

<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')">

<cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">

</cfstoredproc>

The error I get now is the same as was obtained earlier

Error Executing Database Query.

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at line 1  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 88

86 :     <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">
87 : 
88 :  <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">
89 :
90 : </cfstoredproc>

[EddieLotter]

Please wrap your query in a try/catch block and post the result.

For example:

<cftry>
  <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')">
    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">
  </cfstoredproc>
  <cfcatch type="any">
  <cfdump var="#cfcatch#" label="cfcatch" >
  <cfdump var="#variables#" label="variables" showudfs="false" >
  </cfcatch>
</cftry>

[IQ1]

I have attached an output after the try catch was added.

[Matthew C. Parks]

so I am assuming you don't see it. The first date is coming in as 09-MAY-2019 12:00 AM and you mask for the ORACLE TO_DATE() function are not correct.  nothing to do with CF but the data you are pushing to call the proc.