Oracle JDBC via FR is running in an not existing error

[Peer Wermuth]

Hello,

i have an FusionReactor Trial Version for the next 10 days and i got an error if i use the FusionReactor JDBC.

In ACF9 is my Datasource configured with the driver class "oracle.jdbc.driver.OracleDriver" and the URL
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=xe)(__fusionreactor_name=RACRESVW)
(__fusionreactor_exclude=false)))".

Everythings works fine, but on a simple insert i got an error.
If i Copy%Paste the generated SQL in my SQL Developer, then is runs without an error. Also if i use "(__fusionreactor_exclude=true)".

The Statement is

insert into schema.table ( MID, FID, KTO, CHECK_FLAG, CHECK_TS, ITS, CTS ) select y.mid, 35, y.kto , y.CHECK_FLAG, y.CHECK_TS, y.ITS, y.CTS from schema.table y where y.mid = 1 and y.fid = 3

The Error Details are:
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended


Detailed Stack:

oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)
oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:5513)
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
com.intergral.fusionreactor.jdbc.StatementSurrogate.execute(StatementSurrogate.java:65)
coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348)
coldfusion.sql.Executive.executeQuery(Executive.java:1420)
coldfusion.sql.Executive.executeQuery(Executive.java:1183)
coldfusion.sql.Executive.executeQuery(Executive.java:1114)
coldfusion.sql.SqlImpl.execute(SqlImpl.java:341)
coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:915)
coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:590)


I can't find the error in the configuration.

Any ideas of what to do about this?

Thanks,

Peer

[Neil Wightman]

Hi Peer,

1. You need to have an Oracle PKI driver to get the Oracle driver to work (attached) 

2. You need to have ojdbc*.jar and the attached pki-provider-1.4.2_04.jar the same folder (typically cfusion\lib)

Note that you also have to turn off autogenerated keys:

In FusionReactor we stick to the JDBC standard for autogenerated keys but ColdFusion has a special for handling turning off autogeneratedkeys automatically behind the scenes when you use the Oracle thin driver. There should be a checkbox on the JDBC datasource Advanced settings in the ColdFusion Administrator -> Disable Autogenerated Keys

You then need to use a special -D option to turn off the AutoGenerated keys for a datasource. No spaces or brackets are allowed e.g:

-Dcoldfusion.noautogenkeydsns=ora8,ora8_2,oraadmin,oraadmin_2

Regards,
Neil

--

Neil Wightman Senior Software Engineer Email: neil_w...@intergral.com Company Web http://www.intergral.com

This e-mail and any files transmitted with it are confidential and may be protected by legal privilege. If you are not the intended recipient, please notify the sender and delete the e-mail from your system. This e-mail has been scanned for malicious content but the internet is inherently insecure and Intergral GmbH cannot accept any liability for the integrity of this message or its attachments.

[Peer Wermuth]

Hi Neil, 

Thanks for your answer.

Since I checked the "Disable Autogenerated Keys" the Application works and we can test and try the FR.

Regards, 

Peer

[Michele Chiapello]

Il giorno lunedì 15 giugno 2015 13:51:09 UTC+2, Neil Wightman ha scritto:
> Hi Peer,
>
>
>
>
>
>
> You need to have an Oracle PKI driver to get the Oracle
> driver to work (attached) 
>
>
>
>
> You need to have ojdbc*.jar and the attached
> pki-provider-1.4.2_04.jar the same folder (typically
> cfusion\lib)
>
>
>
>
>
>
> Note that you also have to turn off autogenerated keys:
>
> In FusionReactor we stick to the JDBC standard for autogenerated
> keys but ColdFusion has a special for handling turning off
> autogeneratedkeys automatically behind the scenes when you use the
> Oracle thin driver. There should be a checkbox on the JDBC
> datasource Advanced settings in the ColdFusion Administrator ->
> Disable Autogenerated Keys
>
> You then need to use a special -D option to turn off the
> AutoGenerated keys for a datasource. No spaces or brackets are
> allowed e.g:
>
> -Dcoldfusion.noautogenkeydsns=ora8,ora8_2,oraadmin,oraadmin_2
> Regards,
>
> Neil
>
>
>
>
> On 15/06/15 13:28, Peer Wermuth wrote:
>
>
>
> Hello,
>
> i have an FusionReactor Trial Version for the next 10 days and i got an error if i use the FusionReactor JDBC.
>
> In ACF9 is my Datasource configured with the driver class "oracle.jdbc.driver.OracleDriver" and the URL

> "jdbc:ora...@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=xe)(__fusionreactor_name=RACRESVW)

> Neil Wightman
>
>
> Senior Software Engineer
>
>
> Email: neil_w...@intergral.com
>
>
> Company Web http://www.intergral.com
>
>
>
>
>
>
>
>
>
> This e-mail and any files transmitted with it are confidential
> and may be protected by legal privilege. If you are not the
> intended recipient, please notify the sender and delete the
> e-mail from your system. This e-mail has been scanned for
> malicious content but the internet is inherently insecure and
> Intergral GmbH cannot accept any liability for the integrity of
> this message or its attachments.

Hi Neil,
i have the same problem that has explained Peer, but with some differences.
First of all, i have the Autogenerated Keys disabled as default.
When i performe a query like "INSERT INTO ... SELECT ..." i get in output the oracle error ORA-00933: SQL command not properly ended.
If i stop the fusion reactor service everythings goes well, so there is an incompatibility between fusionreactor, coldfusion and oracle.
Below, the tools I use to work:
- oracle v. 11.2.0.2.0
- cold Fusion 10, standard edition
- fusion reactor v. 6.2.8 (the latest version)
- jre 1.7.0_15
- ojdbc6.jar

Do you have some ideas for this issue?

Thanks,
Michele

[charlie arehart]

Michele, while you await a reply from Neil, when you say you have the autogenerated keys off, did you do it either or both ways Neil had referred to in his post (one was the checkbox in the CF Admin, the other was a JVM arg)?

And you refer to the odbc6.jar, but you don’t refer to the other pki jar he had mentioned. Had you implemented it as well? That said, this thread you're resurrecting if from 2015 so it could be that the jar Neil (or anyone at Intergral) may recommend would have changed.

/charlie

[michael...@intergral.com]

Hi Micele

I have spoken to our engineers and believe you may be running into a bug within FusionReactor 6.

This bug occurs when FusionReactor attempts to run the query plan on oracle DB connections. If you have the query plan enabled can you try disabling this feature and see if the issue no longer occurs.

In an upcoming major release of FusionReactor this issue has been fixed, however at this time I cannot confirm when this version will be released.

It is also worth mentioning that since the google groups post was created, we have removed the need for the PKI driver to be installed.

You may also want to ensure that you are actually using the ojdbc6.jar, ColdFusion 10 by default comes with the Macromedia oracle driver out of the box. If this driver is being used we recommend you change this to make sure your .jar file is being used.

In the past we have seen that using prepared statements within FusionReactor can cause issues, but we have performed a code review and this should not be the case.

If after trying the above suggestions you are still experiencing issues, could you provide me with an example query that is failing? I can then attempt to reproduce and work around the issue.

Michael Flewitt

FusionReactor Support Engineer

[michael...@intergral.com]

Hi Charlie,

Just so you are aware, FusionReactor 6 no longer requires the PKI driver so this should no longer be an issue.

The fact that this post is from 2015 means unfortunately some of the information provided is now outdated for the latest version.

Regards

Michael

FusionReactor Support Team

[charlie arehart]

Right. And that’s what I anticipated by my last sentence. :-) So thanks for clarifying that.

And thanks as well for the helpful additional info you offered in reply to Michele, which could benefit others of course. Looking forward to that next version. :-)

/charlie

 

From: fusion...@googlegroups.com [mailto:fusion...@googlegroups.com] On Behalf Of michael...@intergral.com
Sent: Tuesday, May 30, 2017 03:46 AM
To: FusionReactor <fusion...@googlegroups.com>
Subject: Re: [fusionreactor] Oracle JDBC via FR is running in an not existing error

 

Hi Charlie,

 

Just so you are aware, FusionReactor 6 no longer requires the PKI driver so this should no longer be an issue.

 

The fact that this post is from 2015 means unfortunately some of the information provided is now outdated for the latest version.

 

<snip>

On Monday, May 29, 2017 at 5:56:00 PM UTC+2, charlie arehart wrote:

<snip>