Note: I somehow managed to link the original posting of the below question onto an unrelated thread. Please reply to this thread instead of the earlier one.
I can connect fine to an Oracle 11.2 database using JDBC driver, a GlassFish connection pool, 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();
...
// set IN Parameters for stored procedure
cs.setDouble(1, my_double_variable);
...
But now I need to set the connection property SetFloatAndDoubleUseBinary to true. See page 4-16 here
http://docs.oracle.com/cd/E14072_01/java.112/e10589.pdf
I'm attempting to do this using GlassFish admin web console. The steps I use are:
1. Log in
2. Click on JDBC under Resources
3. Click on JDBC Connection Pools
4. Click on my pool of interest, myPool
5. Click the Additional Properties tab
6. Click Add Property
7. Enter SetFloatAndDoubleUseBinary in the Name column, and true in the Value column.
8. Click Save. It reports New Values Saved Successfully.
9. I restart the domain I am going to use.
Then I test the connection by passing a small number (e.g. 1234) for my_double_variable into an Oracle table column defined as BINARY_DOUBLE, which works fine.
Then I set my_double_variable to a large number such as 1e200 and when I try to write this value in the column defined for BINARY_DOUBLE, I get an error:
java.sql.SQLException: Internal Error: Overflow Exception trying to bind 1E200
The overflow exception I think it's referring to is that Oracle NUMBER datatype can only go up to 1e127, whereas Java double or Oracle BINARY_DOUBLE can go up to 1e308. According to the Oracle documentation, SetFloatAndDoubleUseBinary is default equal to false, which causes the Java double variable to be converted to a NUMBER. However, by setting SetFloatAndDoubleUseBinary to true, this conversion to NUMBER datatype is avoided:
"The SetFloatAndDoubleUseBinary connection property when set to true causes
the JDBC standard APIs, setFloat(int, float), setDouble(int, double),
and all the variations, to send internal binary bits instead of NUMBER bits."
So from this simple experiment I conclude that I have not set SetFloatAndDoubleUseBinary correctly in GlassFish, or perhaps I need to reboot something related to Glassfish (besides restarting the domain) to make this change stick (?). Any ideas what I'm doing wrong?