JDBC class not fully operational

wduty · Jul 27, 2000

I have a class set up which simply inserts into a database. When I call this class repeatedly in a loop it works ok but when I call it once it doesn't perform the insert. Calling a short loop and placing the loop index in the database, I can see that it seems not to be performing the LAST insert. For example if I write for (int i=0;i<4;i++)     {call insert class} I see in the database column containing the i value: 0 1 2 But NOT 3. This means that a single call, which is also the last call, does nothing. Here is the class: import java.util.*; import java.sql.*; public class searchJDBC     {      Connection connobj = null;      Statement stmtobj = null;      public void setConnection(String DBstr)            {           try {           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot

;           connobj = DriverManager.getConnection("jdbc

dbc:"+DBstr);            stmtobj = connobj.createStatement();           }           catch(ClassNotFoundException e) {e.getMessage();}           catch(SQLException e) {System.out.println(e.getMessage());}           }     public void insertSQLstr(String SQLstr)           {           try {            stmtobj.executeUpdate(SQLstr);            }           catch(SQLException e) {System.out.println(e.getMessage());}           } }  There's hardly anything to this and it does work but only under repeated calls. It must be something really simple. Any help appreciated. --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

wduty · Jul 27, 2000

Actually I made a mistake in the above presentation which was an earlier version of the code. Here's the class: import java.util.*; import java.sql.*; public class searchJDBC     {      Connection connobj = null;      Statement stmtobj = null;      public void setConnection(String DBstr)           {          try {           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver&quot

;           connobj = DriverManager.getConnection("jdbc

dbc:"+DBstr);          }           catch(ClassNotFoundException e) {e.getMessage();}           catch(SQLException e) {System.out.println(e.getMessage());}           }      public void insertQuery(String url,String keywords)           {           try {           stmtobj = connobj.createStatement();           String SQLstr = "INSERT INTO keywords (url,keywords) VALUES ('"+url+"','"+keywords+"')";           stmtobj.executeUpdate(SQLstr);           stmtobj.close();          }           catch(SQLException e) {System.out.println(e.getMessage());}      } } --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

carpeliam · Jul 28, 2000

what program are you using for debugging? it would be helful to step through and see what's going on... Liam Morley <a href=mailto:lmorley@wpi.edu>lmorley@wpi.edu</a> <a href=

http://www.wpi.edu/~lmorley/>

] :: imotic :: website :: [</a> "light the deep, and bring silence to the world. 
light the world, and bring depth to the silence.

wduty · Jul 28, 2000

Actually I wasn't using a debugger. Any you might recommend? --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

carpeliam · Jul 28, 2000

depends on what OS you're running on and how much money you're willing to spend. I use the Symantec Visual Cafe IDE, but it has it's share of issues (as just about any program will if you use it enough to discover them all). It's really up to you. With regards to your program... I don't understand how this line would execute as you don't have the url, keywords outside of quotes. String SQLstr = "INSERT INTO keywords (url,keywords) VALUES ('"+url+"','"+keywords+"')"; should be String SQLstr = "INSERT INTO keywords (" + url + "," + keywords + &quot

VALUES ('"+url+"','"+keywords+"')"; I think... unless I'm misunderstanding your program/database structure. Liam Morley <a href=mailto:lmorley@wpi.edu>lmorley@wpi.edu</a> <a href=

http://www.wpi.edu/~lmorley/>

] :: imotic :: website :: [</a> "light the deep, and bring silence to the world. 
light the world, and bring depth to the silence.

wduty · Jul 29, 2000

I'm running windows 98 (it's a local test project for a potential customer that might want some specific searching capabilities for certain URLs). I do have Visual Studio Enterprise 6 which I assume comes with some kind of java debugger although I haven't installed the j++ part of VS. (The thing comes with a pile of CDs). Do you know anything about this? As for the SQL syntax, the values in the first set of parens are the column names which aren't variable (one column is called "url" and the other is called "keywords&quot

. It might look confusing because the variables containing the string literals to be inserted happen to have the same names as the column in the database. There are examples of this syntax at: <A HREF="

http://w3.one.net/~jhoffman/sqltut.htm#Adding_Data"

TARGET="_new">

http://w3.one.net/~jhoffman/sqltut.htm#Adding_Data</A> <A

HREF="

http://www.devguru.com/index.asp?page=/technologies/jetsql/quickref/insert_into.html"

TARGET="_new">

http://www.devguru.com/index.asp?page=/technologies/jetsql/quickref/insert_into.html</A> <A

HREF="

http://nt2.ec.man.ac.uk/aspcourse/material/slides/aspcourse/tsld039.htm"

TARGET="_new">

http://nt2.ec.man.ac.uk/aspcourse/material/slides/aspcourse/tsld039.htm</A> <A

HREF="

http://www.compapp.dcu.ie/databases/f297.html"

TARGET="_new">

http://www.compapp.dcu.ie/databases/f297.html</A>

--Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

calahans · Jul 30, 2000

Hi, Just thinking, but if its insert all but the last record then it could be something to do with SQL Commit.  The subsequent transactions could be forcing the COMMIT on the previous statement. Maybe the fact that you code does not explicitly close connection gracefully (no offence!!!) could be causing ROLLBACK of the SQL update for the last statement ... Cal <a href=mailto: > </a> <a href= > </a>

wduty · Jul 30, 2000

Well, this last suggestion seems to have solved the problem. When I first wrote this class I had a line in the method insertSQLstr() which closed the database immediately after the executeUpdate statement. ... stmtobj.executeUpdate(SQLstr); connobj.close(); ... But I thought that perhaps the close statement was causing the problem somehow so I took it out, but that didn't make any difference. But now that "gracefully" was mentioned I thought maybe I should add a separate method to my searchJDBC class which just closes the connection like this: public void closeConnection()     {     try{         connobj.close();     }     catch(SQLException e) {System.out.println(e.getMessage());}     } This seems to have solved the problem. Now I can make a single insert by invoking the class as follows: searchJDBC insert = new searchJDBC(); insert.setConnection("javatest&quot

; insert.insertSQLstrDBstr("INSERT INTO test (test) VALUES ('testing ok')&quot

; insert.closeConnection(); I'm used to working with ADO so I'm not familiar with the particular idiosyncrasies of java JDBC programming though I'm finding it pretty cool so far. Thanks a lot Cal and Liam for your time and suggestions! I have another question also. I see that there are a lot of useful looking methods in the java2 class specification at sun for the SQL classes. But most of them are marked JDBC2.0 and when I try to call any of these (like resultset.isLast() or resultset.first()) I get a method not found error. I'm using jdk1.2.2 on windows98. Do either of you know what I have to do to use JDBC2.0? --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

carpeliam · Jul 30, 2000

Hmm... as you can see at [ <A HREF="

http://java.sun.com/docs/books/tutorial/jdbc/index.html"

TARGET="_new">

http://java.sun.com/docs/books/tutorial/jdbc/index.html</A>

], JDBC2.0 was released with the JDK 1.2. Might you possibly have another older JDK in your classpath? How long ago did you download the JDK? For more interesting JDBC classes, you might want to take a look at the standard extension classes... The javax.sql package has some more useful stuff. Liam Morley <a href=mailto:lmorley@wpi.edu>lmorley@wpi.edu</a> <a href=

http://www.wpi.edu/~lmorley/>

] :: imotic :: website :: [</a> "light the deep, and bring silence to the world. 
light the world, and bring depth to the silence.

wduty · Jul 30, 2000

I downloaded the jdk1.2.2 about four or five months ago. I also downloaded microsoft's java SDK though that's in another directory entirely so I can't see how that would be affecting it. I'll go back and see what exactly the errors were that I was getting and make sure it's not something else. I'll also take a look at the javax.sql package. Thanks. --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

carpeliam · Jul 30, 2000

try this: echo %PATH% If your path includes both directories, there might be a conflict. You might want to try downloading it again? Depends on the speed of your connection- if you have a fast connection, that might not be a big deal, but if you have a slower connection, I'd definitely check other options out first. Just curious... I haven't dealt with the MS JDK, what does it have that the Sun JDK doesn't have? Liam Morley <a href=mailto:lmorley@wpi.edu>lmorley@wpi.edu</a> <a href=

http://www.wpi.edu/~lmorley/>

] :: imotic :: website :: [</a> "light the deep, and bring silence to the world. 
light the world, and bring depth to the silence.

wduty · Jul 31, 2000

I'll give the echo %PATH% a shot. Otherwise, I'm on a pretty fast DSL so I can redownload the jdk if all else fails. As to the MS java, I didn't really explore it much but the one thing it has is a program called javareg.exe which registers a java class as a COM object. I didn't actually get it to work because although it apparently registered the class ok, I then got a permission error in the calling asp page and I was too busy to work on it further. If I can get to know jsp better it seems like it would be easier to just use that than fiddle around with registering asp components. --Will Duty <a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

JDBC class not fully operational

wduty

Programmer

wduty

Programmer

carpeliam

Programmer

wduty

Programmer

carpeliam

Programmer

wduty

Programmer

calahans

Programmer

wduty

Programmer

carpeliam

Programmer

wduty

Programmer

carpeliam

Programmer

wduty

Programmer

Similar threads

Part and Inventory Search

Sponsor