Highlighted

Coldfusion 2018 - Error Executing Database Query

Explorer ,
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)

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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?

Views

2.4K

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

Coldfusion 2018 - Error Executing Database Query

Explorer ,
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)

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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?

Views

2.4K

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
Apr 24, 2019 0
LEGEND ,
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
Reply
Loading...
Apr 24, 2019 1
Explorer ,
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
Reply
Loading...
May 06, 2019 0
LEGEND ,
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
Reply
Loading...
May 06, 2019 0
Explorer ,
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
Reply
Loading...
May 06, 2019 0
LEGEND ,
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
Reply
Loading...
May 06, 2019 0
Explorer ,
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
Reply
Loading...
May 06, 2019 0
LEGEND ,
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
Reply
Loading...
May 06, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 06, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 06, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Advocate ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 07, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 07, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 07, 2019 0
Explorer ,
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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 08, 2019 0
Explorer ,
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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

Data type is 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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

I get

Error Executing Database Query.            

            ORA-06502: PL/SQL: numeric or value error 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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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?

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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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 ?

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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

IQ1  wrote

yes the line number of error is different

Could you then show us the corresponding code?

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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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>

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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

still the same result when it is AMERICAN

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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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.

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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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.

/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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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.

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
Reply
Loading...
May 08, 2019 1
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

Changing  VARCHAR to CF_SQL_VARCHAR does not help, the error continues to remain 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
Reply
Loading...
May 08, 2019 0
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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.

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
Reply
Loading...
May 08, 2019 1
Explorer ,
May 08, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 08, 2019 0
Explorer ,
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
Reply
Loading...
May 08, 2019 2
Adobe Community Professional ,
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
Reply
Loading...
May 08, 2019 0
Adobe Community Professional ,
May 08, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 08, 2019 1
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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: 

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
Reply
Loading...
May 09, 2019 0
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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>

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
Reply
Loading...
May 09, 2019 0
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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

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
Reply
Loading...
May 09, 2019 0
Adobe Community Professional ,
May 09, 2019

Copy link to clipboard

Copied

Hi, IQ!

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

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
Reply
Loading...
May 09, 2019 0
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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>

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
Reply
Loading...
May 09, 2019 0
Advocate ,
May 09, 2019

Copy link to clipboard

Copied

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>

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
Reply
Loading...
May 09, 2019 1
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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

try_catch.png

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
Reply
Loading...
May 09, 2019 0
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

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.

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
Reply
Loading...
May 09, 2019 2
Adobe Community Professional ,
May 09, 2019

Copy link to clipboard

Copied

Indeed, I also noticed that. What IQ1​ could do, to be on the safe side, is to first convert to ColdFusion datetime objects.

Something like this

<cfset fromDateObject=parsedateTime(datefrom)>

<cfset toDateObject=parsedateTime(dateto)>

<cfset fromDateTimeString=dateFormat(fromDateObject, "dd-mm-yyyy") & " " & timeFormat(fromDateObject, "HH:nn:ss")>

<cfset toDateTimeString=dateFormat(toDateObject, "dd-mm-yyyy") & " " & timeFormat(toDateObject, "HH:nn:ss")>

Then proceed either with

<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="#fromDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),

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

      #period#);

END;

</cfquery>

or with the ColdFusion stored-procedure code.

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
Reply
Loading...
May 09, 2019 1
Explorer ,
May 09, 2019

Copy link to clipboard

Copied

Thanks both. I will try out your suggestions and will get back with the results by tomorrow morning.

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
Reply
Loading...
May 09, 2019 0
Explorer ,
May 10, 2019

Copy link to clipboard

Copied

Thanks for your suggestions so I did try it out.

Thanks again for your help and patience.

I used the following code

<cfset fromDateObject=parsedateTime(datefrom)>

<cfset toDateObject=parsedateTime(dateto)>

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

</cfquery>

----------------------------------------------------------------------------------------

The Error I get is

Error Executing Database Query.

ORA-06550: line 1, column 50: PLS-00103: Encountered the symbol "" when expecting one of the following: ( - + case mod new not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternatively-qu  
 
The error occurred in C:/inetpub/wwwroot/eOPS_test/MultiSectionReportsResults.cfm: line 85

83 :       TO_DATE(<cfqueryparam value="#fromDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),
84 :       TO_DATE(<cfqueryparam value="#toDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),        
85 :       #period#);
86 : END;
87 : </cfquery>

SQLSTATE   65000
DATASOURCE   histt
VENDORERRORCODE   6550
SQL    BEGIN dbo.multi_report_pkg.run_multi_report(4567, 'FQURESH2', 7, TO_DATE( (param 1) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE( (param 2) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), 2); 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.

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

Stack Trace 
at cfMultiSectionReportsResults2ecfm1997440540.runPage(C:/inetpub/wwwroot/eOPS_test/MultiSec tionReportsResults.cfm:85) at cfMultiSectionReportsResults2ecfm1997440540.runPage(C:/inetpub/wwwroot/eOPS_test/MultiSec tionReportsResults.cfm:85)

java.sql.SQLException: ORA-06550: line 1, column 50:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character set specification>
   <an alternatively-qu

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3 820)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper. java:1385)
at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101)
at coldfusion.sql.Executive.executeQuery(Executive.java:1583)
at coldfusion.sql.Executive.executeQuery(Executive.java:1317)
at coldfusion.sql.Executive.executeQuery(Executive.java:1247)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211)
at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:794)
at cfMultiSectionReportsResults2ecfm1997440540.runPage(C:\inetpub\wwwroot\eOPS_test\MultiSec tionReportsResults.cfm:85)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:262)
at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:729)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:565)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:597)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43)
at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:162)
at coldfusion.filter.IpFilter.invoke(IpFilter.java:45)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:96)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:60)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)
at coldfusion.CfmServlet.service(CfmServlet.java:226)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:311)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:46 )
at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:491)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:422)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:764)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1388)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:834)

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
Reply
Loading...
May 10, 2019 0
Advocate ,
May 10, 2019

Copy link to clipboard

Copied

What is the value of #period#? It is not in your variables scope.

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
Reply
Loading...
May 10, 2019 0
Explorer ,
May 10, 2019

Copy link to clipboard

Copied

It will be 1 or 2,it comes from above in the code,I have not included that part of the code. But clearly errors are not because of 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
Reply
Loading...
May 10, 2019 0
Adobe Community Professional ,
May 10, 2019

Copy link to clipboard

Copied

Could it be that the the stored-procedure SQL is incorrect? Test it in Oracle (away from ColdFusion):

BEGIN dbo.multi_report_pkg.run_multi_report(4549,

      'fquresh2',

      7,

      TO_DATE('2019/05/01 00:00:00','YYYY/MM/DD HH24:MI:SS','AMERICAN'),

      TO_DATE('2019/05/01 23:59:00','YYYY/MM/DD HH24:MI:SS','AMERICAN'),         

      2);

END;

If it fails, then you still have the ColdFusion version of the stored procedure, courtesy of Matthew C. Parksand EddieLotter

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
Reply
Loading...
May 10, 2019 3
Explorer ,
May 10, 2019

Copy link to clipboard

Copied

The oracle stored procedure compiled and executed successfully from SQL Developer so it’s something in cold fusion that’s not working as expected.

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
Reply
Loading...
May 10, 2019 0
Adobe Community Professional ,
May 11, 2019

Copy link to clipboard

Copied

IQ1  wrote

The oracle stored procedure compiled and executed successfully from SQL Developer so it’s something in cold fusion that’s not working as expected.

This is turning out to be a head-scratcher. Here is another idea: making the masks to match.

<cfset fromDateObject=parsedateTime(datefrom)>

<cfset toDateObject=parsedateTime(dateto)>

<cfset fromDateTimeString=dateFormat(fromDateObject, "yyyy/mm/dd") & " " & timeFormat(fromDateObject, "HH:nn:ss")>

<cfset toDateTimeString=dateFormat(toDateObject, "yyyy/mm/dd") & " " & timeFormat(toDateObject, "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="#fromDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),

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

      #period#);

END;

</cfquery>

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
Reply
Loading...
May 11, 2019 1
Explorer ,
May 11, 2019

Copy link to clipboard

Copied

Thanks for your suggestion, I will try it out on Monday and will let you know the outcome. Thanks again.

I was also thinking since the problem is in the time part of the Datefrom variable , how about we set the time part by default to anything else other than 00:000:00, may be something like 00:00:01. Becasue one thing I have noticed is that it does not not give me this error when I change the datefrom field's time part to anything else other than 00:00:00. I am sure users dont worry about 1 sec difference in the datefrom field. Any suggestions ?

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
Reply
Loading...
May 11, 2019 0
Explorer ,
May 13, 2019

Copy link to clipboard

Copied

Hi,

  As suggested I have used this code,

<cftry>
<cfset fromDateObject=parsedateTime(datefrom)>

<cfset toDateObject=parsedateTime(dateto)>

<cfset fromDateTimeString=dateFormat(fromDateObject, "yyyy/mm/dd") & " " & timeFormat(fromDateObject, "HH:nn:ss")>

<cfset toDateTimeString=dateFormat(toDateObject, "yyyy/mm/dd") & " " & timeFormat(toDateObject, "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="#fromDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),

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

      #period#);

END;

</cfquery>

------------------------------------------------------------------------------

The error I get is shown below

error.png

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
Reply
Loading...
May 13, 2019 0
Adobe Community Professional ,
May 13, 2019

Copy link to clipboard

Copied

Two suggestions:

1) Please check to make sure that the datetype of the respective arguments of run_multi_report() is

integer (or should this be a string?)

string

integer

date

date

integer

in that order.

2) Remove any extra spaces in the query. That is, use:

BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#,'#Session.UserName#',#multi_report_def_id#,TO_DATE(<cfqueryparam value="#fromDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),TO_DATE(<cfqueryparam value="#toDateTimeString#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),#period#);END;

Did you also try the alternative suggested by Matthew C. Parks and EddieLotter?

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
Reply
Loading...
May 13, 2019 1
Adobe Community Professional ,
May 14, 2019

Copy link to clipboard

Copied

IQ1

Is it working now?

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
Reply
Loading...
May 14, 2019 1