sqlPSUpdate doesn't seem to work with Oracle?

Poster Content
nk4um Moderator
Posts: 901
October 26, 2012 08:15

The update to the package database-relational is now in the repositories.

Thanks again for reporting this.

P.

Like · Post Reply
nk4um Moderator
Posts: 901
October 23, 2012 14:17

OK - good stuff. Thanks for letting us know. We'll release that module as an update in the repositories on Friday.

Like · Post Reply
nk4um User
Posts: 24
October 22, 2012 18:24

BTW, if you want to run some tests against oracle without paying for a license you can probably get away with some using oracle express edition (http://www.oracle.com/technetwork/products/express-edition/overview/index.html)

Like · Post Reply
nk4um User
Posts: 24
October 22, 2012 18:22

That jar seems to resolve the issue. Oracle appears to automatically translate '8' to the number 8 internally so you can cheat somewhat by passing in strings but I don't know that I'd want to rely heavily on that behavior.

Like · Post Reply
nk4um Moderator
Posts: 901
October 20, 2012 09:50

Hmmm - looks like we can't rely on JDBC API being completely implemented - even by top-tier vendors.

Here's an update that works around this and if this interface is not supported defaults to adding the parameters as POJOs without any attempt to manage type via transreption. The implication is that the requestor providing the parameters must ensure they are correctly typed.

http://temp.1060research.com/packages/urn.org.netkernel.mod.db-trunk-psdriver-workaround.jar

Can you give this a try - its a two line workaround. If it works for you we'll release it to the apposite repositories.

Cheers,

P.

Like · Post Reply
nk4um User
Posts: 24
October 19, 2012 22:58

I tried the latest ojdbc6.jar from oracle but no luck. The other sql commands like sqlBatch work because they don't use that method but I'd like to start using sqlPSUpdate because of the oracle built in protection from SQL injection.

Posted by pjr (View)
Hi Keith,

This is unfortunate it looks like the Oracle driver you're using is not reporting back a classname for the field that is to receive the prepared statement substitution.

The way that endpoint works is to ensure the requested "param" argument is sourced with the correct class so that any necessary transreption can happen...

ParameterMetaData pm=preparedStatement.getParameterMetaData();
String classname=pm.getParameterClassName(paramindex);   //Your Oracle driver is not supporting this
Object p=context.source(req.getArgumentValue(i), Class.forName(classname));
preparedStatement.setObject(paramindex, p);

We have many other people using Oracle who've not reported any problem. Is it possible to try an alternative more comprehensive JDBC driver?

Sorry for the trouble - in the worst case its pretty simple to workaround - but automatic transreption of the parameter really solves a multitude of other pitfalls.

Cheers,

Peter

Like · Post Reply
nk4um Moderator
Posts: 901
October 19, 2012 20:51

Hi Keith,

This is unfortunate it looks like the Oracle driver you're using is not reporting back a classname for the field that is to receive the prepared statement substitution.

The way that endpoint works is to ensure the requested "param" argument is sourced with the correct class so that any necessary transreption can happen...

ParameterMetaData pm=preparedStatement.getParameterMetaData();
String classname=pm.getParameterClassName(paramindex);   //Your Oracle driver is not supporting this
Object p=context.source(req.getArgumentValue(i), Class.forName(classname));
preparedStatement.setObject(paramindex, p);

We have many other people using Oracle who've not reported any problem. Is it possible to try an alternative more comprehensive JDBC driver?

Sorry for the trouble - in the worst case its pretty simple to workaround - but automatic transreption of the parameter really solves a multitude of other pitfalls.

Cheers,

Peter

Like · Post Reply
nk4um User
Posts: 24
October 19, 2012 20:05sqlPSUpdate doesn't seem to work with Oracle?

I've been trying to use sqlPSUpdate but when I do I get the following exception. Can you shed some light on what needs to be done to resolve this?

<ex>
  <ex>
    <id>RequestFrameException</id>
    <request> SOURCE active:sqlPSUpdate+operand@pbv%3Aoperand+param@pbv%3Aparam as IBinaryStreamRepresentation </request>
  </ex>
  <ex>
    <id>SubrequestException</id>
    <spaceHash>iu/W</spaceHash>
    <space>RDBMS Accessors (private)</space>
    <endpoint>SQLPSUpdateAccessor</endpoint>
    <endpointHash>iu/W/tW</endpointHash>
    <logicalEndpointHash>iu/W/mod-db:sqlPSUpdate</logicalEndpointHash>
    <logicalEndpoint>sqlPSUpdate</logicalEndpoint>
    <ex>
      <id>java.sql.SQLException</id>
      <message>Unsupported feature</message>
      <stack>
        <level> oracle.jdbc.driver.OracleParameterMetaData.getParameterClassName() line:211 </level>
        <level> org.netkernel.rdbms.endpoint.AbstractRDBMSAccessor.parameterizePreparedStatement() line:44 </level>
        <level> org.netkernel.rdbms.endpoint.SQLPSUpdateAccessor.onSource() line:67 </level>
        <level> org.netkernel.module.standard.endpoint.StandardAccessorImpl.onRequest() line:222 </level>
        <level> org.netkernel.layer0.nkf.impl.NKFEndpointImpl.onAsyncRequest() line:93 </level>
        <level>... 53 more</level>
      </stack>
    </ex>
  </ex>
</ex>
Like · Post Reply