Solved

JDBC:ODBC  problem

Posted on 2001-07-31
18
548 Views
Last Modified: 2008-02-01
My java program eventually falls over, though I have debugged to hilt.   I suspect the Jdbc:Odbc bridge.
I am using Jdbc:OdbcDriver to connect to SQL Server 7. My database connect method is as follows:

   public void connectDBase() {
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }catch(Exception e) {
      System.out.println("failed to load driver");
    }
    con = null;
    stmt = null;
    try{
      con = DriverManager.getConnection(URLace,"","");
      stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    }catch(Exception e) {
      System.out.println("problems connecting to  database");
    }
  }


In summary, on connection I am selecting a ResultSet rs, updating fields then updating the row using:
        rs.updateString("Orig", orig);
        rs.updateString("Term", num);
        rs.updateRow();

This runs correctly for some 8,500 records, then I get a memory violation error.  

Am I correct in thinking that the problem probably is the jdbc:odbc bridge and a possible leakage back to the JVM?   Is there a better driver that I can use?  I have read that SQL Server has a JDBC driver but I don't know where/how to find it, ie. how to rewrite
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
to direct to the SQL driver (if it exists).
   Any assistance would be greatly appreciated.  Thanks
0
Comment
Question by:Grizzle
  • 7
  • 5
  • 2
  • +4
18 Comments
 
LVL 92

Expert Comment

by:objects
ID: 6336887
It does sounds like a possible driver problem.
Try another driver, NetDirect has a trial version of theirs available for download at http://www.j-netdirect.com/.
Give it a go and see what happens.
0
 
LVL 3

Expert Comment

by:rjackman
ID: 6336892
hi
instead of having the
System.out.println("ur message");
in the catch block try with
following to get exact exception
try
{

}
catch(Exception tt)
{
  tt.printStackTrace();
}
as u said ur driver works fine for 8500 records
afterwhich u are getting error
what is ur query statement
r u doing any batch processing job
it is always better to use the type3/type 4 drivers
Cheers
RJ
0
 
LVL 3

Expert Comment

by:ibro
ID: 6337020
Hi Grizzle,
 I'm using WebLogic Type4 JDBC driver to connect to SQL Server. So far I didn't experinece any problems. It's not free, but you can download evaluation version from http://www.bea.com/products/weblogic/drivers.shtml

 By the way I will suggest you to use stored procedures in SQL Server to make large updates in the database. It will work faster and you will decrease the network traffic.
 hope this helps
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6337098
Hi Grizzle

Do SQLServer doesn't provide u with any JDBC Driver. Rather than using the sun.jdbc.driver.JdbcOdbcDriver.
Please check for documentation

Shyam
0
 
LVL 1

Expert Comment

by:shahnazali
ID: 6337398

R u closing connections using close() method or not ....
check it out.

bye.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 6337897
I think the drivers you want to use are over on http://www.freetds.org

they are opensource, and free, and documentation is available on the site...

Hope this helps :)

Tim.
0
 
LVL 1

Author Comment

by:Grizzle
ID: 6338976
Thank you all for replies.  

I have downloaded driver from freetds, thanks Tim, needed a freebie ;)   Have looked at documentation there, but still do not know where to put the downloaded files.  I am running NT 4.0 sp6a.   Basic stuff, I know....

Also, any advice on how to make Java call on an SQLServer stored procedure?
thanks.  
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6339702
Hi Grizzle

Use CallableStatement for calling stored procedure

Have a look at java documentation and example available in javasoft

Shyam
0
 
LVL 35

Expert Comment

by:TimYates
ID: 6339837
You should have either a JAR or a ZIP from the freetds download.

You have to set the classpath to point to the file(s) you downloaded...(ie: if you are using JBuilder, add them into the "Required Libraries" bit...  if you are using DOS commands, add them into the -classpath bit of your build/execute script)

I think all this is in the FAQ on the FreeTDS site...

Good Luck again!!

Tim.
0
 
LVL 1

Author Comment

by:Grizzle
ID: 6343704
Thanks Tim for comments. Yes, I am using JBuilder4. After I wrote to the list I settled down and set the freetds jar file to the classpath.  My setup follows the documentation...however, I can't get the connection.  Is the driver in place but is not connecting to the database? - see the NullPointerException below.  

Your comments on whether I am interpreting the exception log correctly would be appreciated.   Know this is difficult without seeing the code, but thought it was too much to post all here.   The DriverManager.getConnection("jdbc:freetds:sqlserver://server/ACE")is as per documentation.
   
I am the first here to use Java to access MSSQLServer database.  Someone is checking on database security issues, though I have access with the odbc:jdbc driver.  

Here is part of the exception log generated:
JDBC DriverManager initialized

registerDriver:
driver[className=com.internetcds.jdbc.tds.Driver,com.internetcds.jdbc.tds.Dr
iver@feec445d]

DriverManager.getConnection("jdbc:freetds:sqlserver://server/ACE")
    trying
driver[className=com.internetcds.jdbc.tds.Driver,com.internetcds.jdbc.tds.Dr
iver@feec445d]

java.sql.SQLExceptionException- java.lang.NullPointerException
     at
com.internetcds.jdbc.tds.Constructors.newConnection(Constructors.java:305)
     at com.internetcds.jdbc.tds.Driver.connect(Driver.java:257)
     at java.sql.DriverManager.getConnection(DriverManager.java:517)
     at java.sql.DriverManager.getConnection(DriverManager.java:177)
     at TestTDSDriver.ftdsjdbc(TestTDSDriver.java:17)
     at TestTDSDriver.main(TestTDSDriver.java:8)
getConnection failed: java.sql.SQLException
java.sql.SQLException
     at
com.internetcds.jdbc.tds.Constructors.newConnection(Constructors.java:305)
     at com.internetcds.jdbc.tds.Driver.connect(Driver.java:257)
     at java.sql.DriverManager.getConnection(DriverManager.java:517)
     at java.sql.DriverManager.getConnection(DriverManager.java:177)
     at TestTDSDriver.ftdsjdbc(TestTDSDriver.java:17)
     at TestTDSDriver.main(TestTDSDriver.java:8)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 6343865
Is this what you did?

-----------

String URI = "jdbc:freetds:sqlserver://server:1433/ACE" ;
String uid = "mylogin" ;
String pwd = "mypassword" ;

Class.forName("com.internetcds.jdbc.tds.Driver");
Connection connection = DriverManager.getConnection( URI, uid, pwd );

-----------

Setting "server" to your servername, and uid and pwd to your username/password pair for the db?

Dont forget the port number :)

If this is what you did....maybe posting a very small code snippet...

Good Luck!!

Tim.

0
 
LVL 1

Author Comment

by:Grizzle
ID: 6346746
Good point Tim, didn't have the port number.  otherwise did as per yr notes.

Added port number, still got same error.  No password used on our db.

My test code:
import java.io.*;
import java.util.*;
import java.sql.*;

public class TestTDSDriver {
  static void main(String args[]){
    TestTDSDriver test = new TestTDSDriver();
    test.ftds();
  }
  static void ftds () {
    try {
      java.sql.DriverManager.setLogStream(System.out);
      String url = "jdbc:freetds:sqlserver://Rasserver:1433/ACE";
      Class.forName("com.internetcds.jdbc.tds.Driver");

   Connection connection   = DriverManager.getConnection(url, "sa", "");
      Statement st = connection.createStatement();

      //execute  query
      String sql = "SELECT * FROM \"CallRecd\"";
      ResultSet rs = stmt.executeQuery(sql);
      while (rs.next()) {
        //do something        
      }
      st.close();
      connection.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

what do you think of that?  Seems straight forward enough.
0
 
LVL 1

Author Comment

by:Grizzle
ID: 6346929
Tim, just checked port 1433 on Rasserver, we are using this (default) and it is open.
0
 
LVL 35

Accepted Solution

by:
TimYates earned 300 total points
ID: 6347883
Hmmm...try setting a password on your db...  The drivers may require one (I remember seeing something about this once, but can't rememebr where) :)

Don't forget though: (from FreeTDS FAQ):
----------------------------------------
Microsoft supports two security models in three permutations:

Windows NT Authentication Mode (Windows NT Authentication)
Standard Mode (SQL Server Authentication)
Mixed Mode (Windows NT Authentication and SQL Server Authentication)
You need either Standard or Mixed mode.  
"Windows NT Authentication", often called "integrated security", relies on Microsoft's trusted connections and is not supported by FreeTDS. In it, user's network security attributes are established at network login time. When connecting to the database server, SQL Server uses Windows NT facilities to determine the validated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.

FreeTDS supports the traditional database security model, which Microsoft terms "SQL Server Authentication" but is frequently known as "standard security". Username+Password pairs have to be passed to the server explicitly.
------------------------------------

Good luck again....we WILL get this working :)
Tim.
0
 
LVL 1

Author Comment

by:Grizzle
ID: 6350369
ahhh...  
will have to wait till Monday now to try this.  
thanks
:)
0
 
LVL 1

Author Comment

by:Grizzle
ID: 6354846
Tim..

We are using security model - Mixed Mode.
I created another username + password for the dbase, and passed these as
  Connection connection   = DriverManager.getConnection(url, "sb", "sb");
and it failed....with the exact same error message as before, ie. it finds the driver, but gets NullPointerException on Db.

I don't know if this will assist you think further on this matter, but I'm using JBuilder4 Enterprise and when I look at Tools - JDBC Explorer, in the LH window, under Database urls, 2 are listed, Borland and the freetds:sqlserver://Rasserver:1433?ACE
When I click on this I get a window with textboxes for username and password.  When I enter sb (for each one)another window pops up with message re Null Pointer, so it seems some setting is still wrong, prior to running the application.  I don't understand this.

To "keep the show on the road" I have reverted to odbc:jdbc
this time updating Db through stored procedure/CallableStatement, it is running at the moment and okay at 12,000 records.  Do you think this is because each call to the procedure gives a 'begin' and 'end' to each update transaction, whereas ResultSet.updateRow() kept the database in an "unstable" state, with a log that haemorraged at 8,500 records?  I am in new territory here, but I am thinking that maybe there is a way to make my program more stable by defining an end to each transaction.

I still wish to get the Jdbc driver up, so would be interested to hear if you have any other thoughts.  Thanks for your continuing support...

0
 
LVL 1

Author Comment

by:Grizzle
ID: 6943546
Sorry I took so long to award you the points, kind of forgot about it.

0
 
LVL 35

Expert Comment

by:TimYates
ID: 6944354
Hee hee, no worries :-)

Glad I was of some help :-)

Tim.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question