programatically.
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");
conn = ((DataSource)ds).getConnection(info);
http://stackoverflow.com/questions/12148976/how-can-i-set-a-connection-property
Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Ez az
üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut ętre privilégié. Si vous n'ętes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert ŕ l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement ętre sujets ŕ la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
Date: Tue, 28 Aug 2012 16:10:47 +0000
From: modjklist_at_comcast.net
To: users_at_glassfish.java.net
Subject: Re: (resend) how to set Oracle connection property in GlassFish admin web console?
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?