Hi Shalini,
Can you comment about trying to set "SetFloatAndDoubleUseBinary" as done in the following?
As a third method, I entered the SetFloatAndDoubleUseBinary=true setting 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. If I add
-Doracle.jdbc.SetFloatAndDoubleUseBinary=true
as a new property, save the screen, then restart the domain and run my app again, it still fails. By "fails" I mean I get the error
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: number precision too large
when I try to write a large number to a database via Java and JDBC connection pool using
cs.setDouble(1, myDoubleVariable);
where myDoubleVariable=1e200D for example. Since this is larger than Oracle NUMBER datatype limits (1e127), the error is generated because standard JDBC setDouble converts double in Java to Oracle NUMBER datatype. The switch is supposed to prevent this conversion. What could I be doing wrong? If I set myDoubleVariable<1e127, everything works fine.
----- Original Message -----
From: "Shalini" <shalini.muthukrishnan_at_oracle.com>
To: users_at_glassfish.java.net
Cc: "Anissa Lam" <anissa.lam_at_oracle.com>
Sent: Tuesday, August 28, 2012 11:27:29 PM
Subject: Re: (resend) how to set Oracle connection property in GlassFish admin web console?
Hi,
There is a setConnectionProperties(java.util.Properties) that can take in this property "SetFloatAndDoubleUseBinary". In GlassFish, however, only key value pairs are supported currently in additional properties section. There exists an RFE :
http://java.net/jira/browse/GLASSFISH-18394 to be enable to parse a Properties object passed as a value.
HTH
Shalini.
On Tuesday 28 August 2012 10:38 PM, Anissa Lam wrote:
Thats how you set the property for a connection pool in the console. Maybe this property is not recognized ?
Let me ping the developers of the JDBC pool.
thanks
Anissa.
On 8/28/12 9:10 AM, modjklist_at_comcast.net wrote:
Anyone have any ideas how to set this property?
From: modjklist_at_comcast.net
To: users_at_glassfish.java.net
Sent: Monday, August 27, 2012 6:22:23 PM
Subject: Re: (resend) how to set Oracle connection property in GlassFish admin web console?
I also tried the following:
1. Log in
2. Click on JDBC under Resources
3. Click on JDBC Resources (this is the new step)
4. Click on my pool name 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. Restart the domain.
However, the results are exactly the same as noted below.
----- Original Message -----
From: modjklist_at_comcast.net
To: users_at_glassfish.java.net
Sent: Monday, August 27, 2012 5:30:02 PM
Subject: (resend) how to set Oracle connection property in GlassFish admin web console?
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?