• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

JDBC:ODBC problem

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
Grizzle
Asked:
Grizzle
  • 7
  • 5
  • 2
  • +4
1 Solution
 
objectsCommented:
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
 
rjackmanCommented:
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
 
ibroCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
shyamkumarreddyCommented:
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
 
shahnazaliCommented:

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

bye.
0
 
TimYatesCommented:
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
 
GrizzleAuthor Commented:
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
 
shyamkumarreddyCommented:
Hi Grizzle

Use CallableStatement for calling stored procedure

Have a look at java documentation and example available in javasoft

Shyam
0
 
TimYatesCommented:
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
 
GrizzleAuthor Commented:
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
 
TimYatesCommented:
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
 
GrizzleAuthor Commented:
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
 
GrizzleAuthor Commented:
Tim, just checked port 1433 on Rasserver, we are using this (default) and it is open.
0
 
TimYatesCommented:
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
 
GrizzleAuthor Commented:
ahhh...  
will have to wait till Monday now to try this.  
thanks
:)
0
 
GrizzleAuthor Commented:
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
 
GrizzleAuthor Commented:
Sorry I took so long to award you the points, kind of forgot about it.

0
 
TimYatesCommented:
Hee hee, no worries :-)

Glad I was of some help :-)

Tim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now