Coldfusion 2018 - Error Executing Database Query

Report · Apr 24, 2019

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)

Report · May 09, 2019

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

Then proceed either with

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.

Report · May 09, 2019

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

Report · May 10, 2019

Faiz Qureshi 10-May-2019 8:48 AM (in response to Faiz Qureshi)

Thanks for your suggestions so I did try it out.

Thanks again for your help and patience.

I used the following code

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

Report · May 10, 2019

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

Report · May 10, 2019

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.

Report · May 10, 2019

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

Report · May 10, 2019

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

Report · May 11, 2019

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.

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>

Report · May 11, 2019

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 ?

Report · May 13, 2019

Hi,

As suggested I have used this code,

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

Report · May 13, 2019

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

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?

Report · May 14, 2019

IQ1

Is it working now?

Report · May 14, 2019

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.

Report · May 14, 2019

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.

Report · May 14, 2019

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>

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>

</cftry>

Report · May 14, 2019

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

Report · May 14, 2019

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

<cftry>

</cfstoredproc>

</cfcatch>

</cftry>

Report · May 14, 2019

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

to

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

Report · May 14, 2019

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:

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>

</cftry>

Report · May 14, 2019

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>

</cfstoredproc>

</cfcatch>

</cftry>

Report · 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?

Report · May 14, 2019

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.

Report · May 16, 2019

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

Report · May 16, 2019

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

Report · May 16, 2019

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 ?

Coldfusion 2018 - Error Executing Database Query

Error Executing Database Query.

1 Correct answer

Photos