• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

6.7K

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

Community Expert , 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?

Votes

Translate

Translate
Community Expert ,
May 09, 2019 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.

Votes

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 09, 2019 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.

Votes

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 10, 2019 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)

Votes

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

Copy link to clipboard

Copied

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

Votes

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 10, 2019 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.

Votes

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
Community Expert ,
May 10, 2019 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

Votes

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 10, 2019 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.

Votes

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
Community Expert ,
May 11, 2019 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>

Votes

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 11, 2019 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 ?

Votes

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 13, 2019 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

Votes

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
Community Expert ,
May 13, 2019 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?

Votes

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
Community Expert ,
May 14, 2019 May 14, 2019

Copy link to clipboard

Copied

IQ1

Is it working now?

Votes

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

Copy link to clipboard

Copied

No not yet, its still the same issues. I have raised the same errors with Oracle support and they have confirmed by testing the stored procedure outside ColdFusion that it works without any problem , so the issue is in ColdFusion somewhere , any further help will be much appreciated. I am kind of stuck now and this has to be implemented soon, our client has been waiting for this since a long time now. How can ColdFusion (Adobe) completely remove the driver from their Standard version and leave users like me stranded without any help from them ?

I am thankful to friends like you for providing me with so much help and guidance , I appreciate all your efforts and thank you all, please continue to help out as much as you can.

Votes

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
Community Expert ,
May 14, 2019 May 14, 2019

Copy link to clipboard

Copied

It is unclear from your response whether you specifically verified the last 2 suggestions. If so, what was the result?

In addition, did you try the ColdFusion stored procedure? It's been suggested at least 4 times but you have given us no feedback yet about it.

Votes

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
Community Expert ,
May 14, 2019 May 14, 2019

Copy link to clipboard

Copied

Let's go further.

IQ1  wrote

Error message:

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

Mark the location (line 1, column 50) with a *:

   

    BEGIN dbo.multi_report_pkg.run_multi_report(4581,*

This suggests that the error has to do with the arguments. The * indicates the location of the second argument.

It is now clear why your query works on SQL Developer, but not in ColdFusion. There is one difference between running the query on SQL Developer and in ColdFusion. In SQL Developer you insert the actual value of each argument whereas, in ColdFusion, you have to evaluate the arguments.

Putting everything together, the cause of the error seems to be '#Session.UserName#'. New suggestions:

1) Test by passing the actual name as argument (for example, 'BKBK') instead of '#Session.UserName#'. If that works, then test using '#trim(Session.UserName)#'.

2)

<cftry>

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

BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#,'#trim(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>

    <cfcatch type="any">

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

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

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

    </cfcatch>

</cftry>

Votes

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

Copy link to clipboard

Copied

The BEGIN END; stuff inside a <CFQUERY> has never worked for me with ORACLE.  I have seen it work with MS SQL server connections but not with ORACLE.  Please use the <CFPROC> tag and see if that works.

Hell, if you hardcode you currect attempt to call the stored proc, I believe you will find that it will not work as fore mentioned.

IQ1

Votes

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

Copy link to clipboard

Copied

Hello All,

   I have tried hardcoding variables but that does not work, I have also tried using trim with the variables still does not work. Same errors as before.

I have tried using the cold fusion stored procedure approach, that gives the following error. The code I have used is give below as well.

Thanks

IQ

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

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

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

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

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

  </cfstoredproc>

  <cfcatch type="any">

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

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

  </cfcatch>

</cftry>

error.png

Votes

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

Copy link to clipboard

Copied

SO the cause ORA-06512 error message in Oracle.

This is an issue in the PL/SQL.  If I were a gambling man, I bet your inputs for the to and from date are dates and not defined as VARCHARS in the PROC.

change

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

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

to

<cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#TestToDate#">

<cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateFromCreate#">

That is if the PL/SQL is accpting the input parameters as dates instead of VarChar.

Votes

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
Community Expert ,
May 14, 2019 May 14, 2019

Copy link to clipboard

Copied

IQ1  wrote

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

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

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

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

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

  </cfstoredproc>

  <cfcatch type="any">

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

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

  </cfcatch>

</cftry>

There is no puzzle this time. ColdFusion has reported what the error is:

The lines in bold are passing strings, not dates, to the stored procedure. This confirms the point made by Matthew C. Parks

So use either the modified ColdFusion stored-procedure or the following cfquery:

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

      <cfqueryparam value="#parsedateTime(fromDateTimeString)#" cfsqltype="CF_SQL_TIMESTAMP">,

      <cfqueryparam value="#parsedateTime(toDateTimeString)#" cfsqltype="CF_SQL_TIMESTAMP">,  

      #period#);

END;

</cfquery>

<cfcatch type="any">

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

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

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

</cfcatch>

</cftry>

Votes

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

Copy link to clipboard

Copied

Hi friends,

    I have used the code as suggested by BKBK but with in a CF stored procedure, here is the code I have used and the error I get now is slightly different from the previous ones.

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

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

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

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

    <cfprocparam  value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#parsedateTime(fromDateTimeString)#">

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#parsedateTime(toDateTimeString)#">

    <cfprocparam  value="#period#">

  </cfstoredproc>

<cfcatch type="any">

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

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

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

</cfcatch>

</cftry>

err.png

Votes

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
Community Expert ,
May 14, 2019 May 14, 2019

Copy link to clipboard

Copied

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?

Votes

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

Copy link to clipboard

Copied

Thanks BKBK and all others for helping me out. I don't think there is any problem with the Table or the column name, the reason I say this is, because the same code works fine from another server where there is a Developer version of coldfusion 2018 and an Oracle Driver is available from coldfusion. In our case we are using Standard version of ColdFusion 2018 and so the JDBC connection string has its limitations. To prove my point I copied over the same application files from Standard version of ColdFusion 2018 to the other server with the Developer version of coldfusion 2018, the same code and the same files work perfectly fine from there with the same database at the backend.

So the main culprit here is the fact that there is no Oracle Driver available in ColdFusion 2018 standard version. JDBC connectivity requires JDBC drivers, Instead of leaving the Developer community high and dry the least Adobe should do is, recommend us to use a particular set of JDBC drivers, they don't even do this and so are forcing their customers to switch on to their costlier Enterprise edition.

I appreciate each one of you and thank you all  for sparing your precious time to help me with this issue. Thank you all.

Votes

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

Copy link to clipboard

Copied

I know this thread is completed and well answered. I had a quick question, I promise this will be the final one on this thread.

Does Coldfusion recommend  or provide JDBC drivers to work with Coldfusion 2018 ?  If yes can someone please send me the URL to download these drivers.

I appreciate your help.

Regards

IQ

Votes

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

Copy link to clipboard

Copied

ColdFusion 2018 Enterprise ships with drivers that you can use for Oracle. See for example ColdFusion 2016 Enterprise: dB Connection to Oracle 12 via Service Name (no SID) . As you can see from the ColdFusion 2018 Support Matrix,  ColdFusion 2018 Standard does not support Oracle.

In any case, you can always download and install the Oracle driver yourself. See for example ColdFusion 2016 standard and Oracle adventures - ColdFusion

Votes

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

Copy link to clipboard

Copied

Thanks for your reply BKBK, we are actually using the same JDBC connection string(BTW, actually its not a full fledged Oracle Driver but a workaround) you are suggesting for our Standard version of ColdFusion. But as stated previously functionality wise this is not the exact same as Oracle Driver available from ColdFusion as in Enterprise edition or Developer full version.

   For basic stuff the JDBC connection string works fine but as soon as it gets to something more complex like datetime strings , the JDBC connection string fails. So my question was IS there a specific version of the JAR file recommended by ColdFusion for JDBC connection ?

Votes

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