Re: Java vs SQL Server float datatype limits

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



isyourfriend@xxxxxxxxx wrote:

On Jun 28, 11:52 am, Joe Weinstein <joeNOS...@xxxxxxx> wrote:

isyourfri...@xxxxxxxxx wrote:

SQL Server float limits are : - 1.79E+308 to -2.23E-308, 0 and
2.23E-308 to 1.79E+308
However Java double has a much wider range : 4.94065645841247E-324
-4.94065645841247E-324

There is no "apparent" problem on using java limit values but if the
table ischecked for data purity it will of course produce errors. BCP
also complains when using native format.

Is there a setting to force the driver to limit the values for SQL
Server to those supported on SQL 92 ?

Many thanks,

-Noel (DBA)

No, and the driver doesn't know you're trying to stuff a Java
double into a float column. Java does have a float too, which
corresponds correctly to the DBMS column. It should be up to
the application author to match data to target column. However,
I ran this code, and it seems to work:

System.out.println("Driver version is " + c.getMetaData().getDriverVersion() );
System.out.println("DBMS version is " + c.getMetaData().getDatabaseProductVersion() );

Statement s = c.createStatement();
try{s.executeUpdate("drop procedure joe_proc");}catch(Exception e) {}
s.executeUpdate("create table #foo(joe float)");
PreparedStatement p = c.prepareStatement("insert #foo values(?)");
double dd = Double.MAX_VALUE / 2;
double ddd = Double.MIN_VALUE * 2;

p.setDouble(1, dd);
p.execute();
p.setDouble(1, ddd);
p.execute();

ResultSet r = s.executeQuery("select * from #foo");
while (r.next()) System.out.println("sent " + dd + " return " + r.getDouble(1) );

I get:
Driver version is 1.1.1320.0
DBMS version is 8.00.818
sent 8.988465674311579E307 return 8.988465674311579E307
sent 1.0E-323 return 1.0E-323

Joe Weinstein at BEA Systems


Joe, as you can see 1.0E-323 is *out of range* for the float definiton
on sql server
which by the way float = float(53) = double presicion

It is read and written correctly from the query engine but BCP chokes
and DBCC CHECKTABLE('yourtablename') WITH DATA_PURITY complains!!!

So I would have imagined that an SQL Server driver either handle the
case or it doesn't but that some things work and some others don't is
not nice ;)

Thanks for your reply.

-Noel

Hi, sure. I'm not clear on how the JDBC driver is involved. It seems to work
with that code, so if you can show me an alteration of that code that shows
a problem, that would be good. Regarding BCP or DBCC, those are DBMS issues.
It seems the driver is faithfully correctly transferring those values, and
the DBMS is returning them without corruption in this case. What case do you
want the driver to handle?
Joe

.

Relevant Pages

  • Re: Dont do DDL with PreparedStatement with Oracle!
    ... The driver got the expected ... response packet from the DBMS, ... in fact OCI mark the DDL for re-parsing, ... no-op on subsequent execution. ...
    (comp.lang.java.databases)
  • Re: Kinda urgent Connection error messages
    ... driver perspective, these are all unexpected problems the driver is suffering ... I guarantee that the driver is making the socket the same way ... Do you hold JDBC connections open indefinitely, ... Maybe the DBMS has an idle connection timeout, ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Kinda urgent Connection error messages
    ... I believe the DBMS is in trouble. ... finding out that the socket connection it sues to the DBMS is dieing. ... > Unable to load SQL Server Data Driver driver. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Kinda urgent Connection error messages
    ... I believe the DBMS is in trouble. ... finding out that the socket connection it sues to the DBMS is dieing. ... > Unable to load SQL Server Data Driver driver. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • cant write a correct schema.ini for a text connection
    ... ODBC Text Setup) ... After defining all fields and press ok I get the error message: ... MS Text Driver Version 4.00.6304.00 ... Col4=YLDG Float ...
    (microsoft.public.data.odbc)