Allow DataDirect to return the value what is inserted on NUMBER(x,y) column for Oracle Database

Status
Not open for further replies.
D

datec

Guest
1. The column declared for NUMBER (x,y) is for decimal point numbers. However many people use this column as Integer holder. For example, Let's create a table something like follows. create table Employee ( ID VARCHAR2 (4 BYTE) NOT NULL, First_Name VARCHAR2(20, BYTE), Last_Name VARCHAR2 (20, BYTE), Salary Number(8,2) ) Then insert values as follows. insert into Employee (ID, First_name, Last_Name, Salary) values ('01','David', 'Park', 10000) 2. Oracle JDBC (ojdbc5.jar, ojdbc6.jar) behavior. conn = DriverManager.getConnection (" .... oracle connection url ", "id","password"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery ("SELECT Salary from Employee where ID='01'"); Long lSalary = Long.parseLong (rs.getString("Salary")); Oracle JDBC (rs.getString("Salary") returns 10000, thus Long.parseLong() method works fine. Oracle JDBC returns what is inserted without formatting for the Number format on Number column. 3. DataDirect JDBC behavior refer to the source code above .... Long.parseLong(rs.getString("Salary")); raises NumberFormatException. Because DataDirect JDBC returns 10000.00 after formatting for the Salary column format : Number (8,2) 4. Problem In common sense, the value on Salary (Number column) should return with decimal point number like 10000.00. However many JDBC applications already made by using Long.parseLong() method on Number column, and there were no exceptions from their application because they insert integer value on decimal point column. If one try to replace Oracle JDBC driver by DataDirect JDBC driver, NumberFormatException raises, and application stops running. Thus one can not replace Oracle JDBC driver without modifying source code. Most of the time, it is very hard to modify source code for existing running application. 5. Enhancement It would be very nice to make option to act like Oracle JDBC driver, so that the customer does not need to change source code to use DataDirect JDBC Driver. For Example, make connection URL option something like NumberColumnWorkaround=true for Oracle Database, then implement getString() on Number column to return what is in the column (Same format as inserted) If inserted value is 10000, then return 10000. If inserted value is 10000.00 then return 10000.00 6. Effect More people can use DataDirect JDBC without changing their existing source code which can give customer more values.

Continue reading...
 
Status
Not open for further replies.
Top