Skip to main content

(resend) how to set Oracle connection property in GlassFish admin web console?

8 replies [Last post]
929 Guest
Offline
Joined: 2012-02-13
Points: 0

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?

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
929 Guest
Offline
Joined: 2012-02-13
Points: 0

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@comcast.net
To: users@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?

929 Guest
Offline
Joined: 2012-02-13
Points: 0

Anyone have any ideas how to set this property?

----- Original Message -----
From: modjklist@comcast.net
To: users@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@comcast.net
To: users@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?

anilam
Offline
Joined: 2005-03-29
Points: 0

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@comcast.net wrote:
> Anyone have any ideas how to set this property?
>

Shalini Guest
Offline
Joined: 2011-04-18
Points: 0

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@comcast.net wrote:
>> Anyone have any ideas how to set this property?
>>

929 Guest
Offline
Joined: 2012-02-13
Points: 0

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"
To: users@glassfish.java.net
Cc: "Anissa Lam"
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@comcast.net wrote:

Anyone have any ideas how to set this property?

From: modjklist@comcast.net
To: users@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@comcast.net
To: users@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?

Shalini Guest
Offline
Joined: 2011-04-18
Points: 0

Please see inline.

On Wednesday 29 August 2012 08:01 PM, modjklist@comcast.net wrote:
> Hi Shalini,
>
> Can you comment about trying to set "SetFloatAndDoubleUseBinary" as done in the following?

|Properties props= new java.util.Properties();
info.put("SetFloatAndDoubleUseBinary", "true");

If such a Properties object is set as a value for|setConnectionProperties, as in,
in the jdbc connection pool additional properties, you add a property by name
"connectionProperties" and value "props".

However, as I mentioned earlier, parsing properties as value is not supported in
GlassFish as of now.

>
> 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"
> To: users@glassfish.java.net
> Cc: "Anissa Lam"
> 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@comcast.net wrote:
>
>
>
> Anyone have any ideas how to set this property?
>
>
> From: modjklist@comcast.net
> To: users@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@comcast.net
> To: users@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?
>
>

929 Guest
Offline
Joined: 2012-02-13
Points: 0

Thank you Shalini, At least now I know it is not covered and I can stop trying to get it to work.

----- Original Message -----
From: "Shalini"
To: users@glassfish.java.net
Cc: "Anissa Lam"
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@comcast.net wrote:

Anyone have any ideas how to set this property?

----- Original Message -----
From: modjklist@comcast.net
To: users@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@comcast.net
To: users@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?

929 Guest
Offline
Joined: 2012-02-13
Points: 0

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: "Anissa Lam"
To: users@glassfish.java.net
Sent: Tuesday, August 28, 2012 10:08:40 AM
Subject: Re: (resend) how to set Oracle connection property in GlassFish admin web console?

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.