How can I set a connection property?

Question

I can connect fine to an Oracle 11.2 database using JDBC driver and the following Java code:

  import java.sql.*;
  import javax.sql.DataSource;
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import javax.naming.NamingException;
  import oracle.jdbc.OracleTypes;
  ...
  Connection conn=null;

  // connect to database
  Context context = new InitialContext();
  DataSource ds = (DataSource)context.lookup("jdbc/myPool");
  conn = ds.getConnection();

But now I need to set the option SetFloatAndDoubleUseBinary to true. See page 4-16 here

http://docs.oracle.com/cd/E14072_01/java.112/e10589.pdf

So I try to follow examples from here:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/urls.htm

and I modify the code as:

  import java.sql.*;
  import java.util.Properties;
  import javax.sql.DataSource;
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import javax.naming.NamingException;
  import oracle.jdbc.OracleTypes;
  ...
  Connection conn=null;

  // set connection properties
  Properties info = new java.util.Properties();
  info.put ("SetFloatAndDoubleUseBinary","true");

  // connect to database
  Context context = new InitialContext();
  DataSource ds = (DataSource)context.lookup("jdbc/myPool");
  conn = ds.getConnection(info);

and I'm getting the following compile error:

myClass.java:1145: cannot find symbol
symbol  : method getConnection(java.util.Properties)
location: interface javax.sql.DataSource
          conn = ds.getConnection(info);
                   ^

Anyone know how I can properly set SetFloatAndDoubleUseBinary here?

UPDATE 1

Changing to:

  import java.sql.*;
  import java.util.Properties;
  import javax.sql.DataSource;
  import javax.naming.Context;
  import javax.naming.InitialContext;
  import javax.naming.NamingException;
  import oracle.jdbc.OracleTypes;
  import oracle.jdbc.*;       // NEW
  import oracle.jdbc.pool.*;  // NEW
  ...
  Connection conn=null;

  // set connection properties
  Properties info = new java.util.Properties();
  info.put ("SetFloatAndDoubleUseBinary","true");

  // connect to database
  Context context = new InitialContext();
  DataSource ds = (DataSource)context.lookup("jdbc/myPool");
  ((OracleDataSource)ds).setConnectionProperties(info); // NEW
  conn = ds.getConnection();                    // NEW

gives the following run time error:

stack trace: java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.DataSource40 cannot be cast to oracle.jdbc.pool.OracleDataSource

It seems to be an Oracle implementation of DataSource. Did you try to cast the DataSource retrieved on jndi to OracleDataSource ? Ex: ((OracleDataSource)ds).setConnectionProperties(info); then conn = ds.getConnection() should return a connection with your properties correctly set. — Alex
– Alex, Commented Aug 27, 2012 at 20:24
Thanks Alex, well it removed the compile error, but I'm not sure the option was set correctly due to run-time error. See UPDATE 1 above. — ggkmath
– ggkmath, Commented Aug 27, 2012 at 20:54

jFrenetic · Accepted Answer · 2012-08-28 20:31:43Z

3

The DataSource is usually configured on the application server. For example, in Glassfish you could set this property in the admin console like this:

enter image description here

and then just call

ds.getConnection() from your client code.

Edit:

If you want to get access to the implementation class of javax.sql.DataSource, you should use DataSource#unwrap method rather than simple cast.

For example:

DataSource ds = (DataSource) ctx.lookup("jdbc/MyPool");
OracleDataSource oracleDS = ds.unwrap(OracleDataSource.class)

Make sure, you have your jdbc driver jar on classpath.

But then your code will be non-portable, if you wish to switch to another database vendor in the future.

Edit 2:
Also, for Glassfish, refer to the Configuring Specific JDBC Connection Pool Features of Oracle Administration Guide.

edited Aug 28, 2012 at 20:31

answered Aug 27, 2012 at 20:41

jFrenetic

5,5726 gold badges45 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ggkmath Over a year ago

I did what is shown here but it doesn't appear the true setting stuck because I'm getting an overflow error whenever I try to setDouble with a number larger than Oracle NUMBER datatype can support (1e127). If the variable is true there shouldn't be any conversion to NUMBER.

jFrenetic Over a year ago

@ggkmath these are not complete settings. You should at least provide connection URL, user and password. And the database vendor in your case should be Oracle apparently. Make sure that you're able to successfully ping created connection pool.

ggkmath Over a year ago

Well, maybe I'm confused. I already had a perfectly working JDBC Connection Pool created in admin console Resources/JDBC/JDBC Connection Pool/myPool, where I've entered all the necessary parameters in the Additional Parameters tab, including user name, pwd, Oracle vendor, etc. So I just added the SetFloatAndDoubleUseBinary switch as another property and restarted the domain, and observed the overflow error when writing large numbers to the database (meaning the switch isn't set to true).

jFrenetic Over a year ago

@ggkmath The property should be set the way you described in your second comment to this answer (i.e. in the Additional Parameters of Connection Pool, along with the other connection settings, such as user, password etc.). You must not create an additional pool with just one property. I'm not sure why it didn't work. I'll try this later. As for your update, see my edit.

ggkmath Over a year ago

Thanks jFrenetic. I also tried adding -Doracle.jdbc.SetFloatAndDoubleUseBinary=true as an option in the Configurations/server-config/JVM Settings/JVM Options screen. That's where the java heap size is set along with a lot of other things. But this also did not seem to have any effect.

|

biziclop · Accepted Answer · 2012-08-27 20:29:48Z

2

This form of getConnection() is specific to OracleDataSource and doesn't exist in the more generic DataSource interface.

The solution is simple, replace this line:

DataSource ds = (DataSource)context.lookup("jdbc/myPool");

with this:

OracleDataSource ds = (OracleDataSource)context.lookup("jdbc/myPool");

Of course this means that from now on your application will only work with Oracle databases. Depending on what you need this may or may not be a good idea.

answered Aug 27, 2012 at 20:29

biziclop

50k12 gold badges80 silver badges108 bronze badges

6 Comments

ggkmath Over a year ago

Thanks biziclop, Should the parameter info, still be implemented like conn = ds.getConnection(info);?

ggkmath Over a year ago

I'm getting a compile warning: warning: [deprecation] getConnection(java.util.Properties) in oracle.jdbc.pool.OracleDataSource has been deprecated. Is there another way to set info?

ggkmath Over a year ago

Ignoring the warning and running the changes also gives the same error as the other two solutions above:

stack trace: java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.DataSource40 cannot be cast to oracle.jdbc.pool.OracleDataSource

biziclop Over a year ago

@ggkmath Well, it means then that the documentation you were reading isn't correct for the scenario you're trying to apply it to.

ggkmath Over a year ago

My GlassFish JDBC Connection Pool has a setting for DataSourceName with a value of OracleDataSource. Also, the Wrap JDBC Object option is enabled. Maybe that's related to the error (?).

|

leonbloy · Accepted Answer · 2012-08-27 20:31:34Z

0

In addition to Alex's comment, I'd try

 conn = ds.getConnection();
 conn.setClientInfo("SetFloatAndDoubleUseBinary","true");

answered Aug 27, 2012 at 20:31

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

Collectives™ on Stack Overflow

How can I set a connection property?

3 Answers 3

8 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related