Solved

JDBC:ODBC  problem

Posted on 2001-07-31
18
534 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now