aspose file tools*
The moose likes JDBC and the fly likes commit() and rollback() problem! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "commit() and rollback() problem!" Watch "commit() and rollback() problem!" New topic
Author

commit() and rollback() problem!

ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
Hello All,
I am still having problems with my code.
I want to be able to insert data from a file into a database but when i spell one of the column names wrong (on pupose), it inserts the data that is correct then thows the exception, I dont want this as I want the exception to be thown first. If all the names are correct then go ahead and insert.
I ahve tried the commit() and rollback() methods but I am still getting the same responce.
I dont know what I am doing wrong so could someone please help me?
Thanks Ben
here is my code:


I have tried print out before the preparredStatement and the executeUpdate statmnet and this is the output:
prepare
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
prepare
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
prepare
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
one
two
prepare
one
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Sever] Invalid column name 'Postcod'.
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24187
    
  34

So what you're saying is that even though you get the SQLException and rollback() is called, if you go and look at the database, you find that the updates have been executed anyway?


[Jess in Action][AskingGoodQuestions]
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Just a thought...
I noticed you are using the jdbc-odbc bridge driver to connect to a SQLServer. Perhaps it does not support transactions. Although I would think that if that was the case, the calls to setAutoCommit()/commit()/rollback() would generate exceptions but maybe not.
In any case, you should consider using the SQLServer JDBC driver. It should give you more features, boost performance over the jdbc-odbc bridge driver, and possibly solve your problem here with transaction management.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
so What changes should I make?
Could you give me an example?
Thanks
Ben
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Originally posted by ben riches:
so What changes should I make?
Could you give me an example?

I'm assuming you are asking how to change the driver you are using:
1. Download the SQLServer JDBC driver from Microsoft and install it.
2. Make sure the jar file(s) are included in your CLASSPATH.
3. Change your url appropriately. I believe your new url would be something like:
4. Change your Driver class:
That should be all you need to change, but double check the docs that come with the JDBC driver because I actually don't work with SQLServer so I can't guarantee the accuracy of my statements.
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
hello,
I have downloaded the driver you told me to.
What does the part //[dbserver_ip]:[port] do does this stay the same all the time?
Also how do I connect to my database? Do I have to name my datasouce name as I did before like below?

Also I get an exception saying:
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
Thanks Ben
Thanks Ben
[ March 09, 2004: Message edited by: ben riches ]
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Substitute [dbserver_ip] with the actual ip address of your SQLServer. Substitute [port] with the actual port on the database server that you connect to (I believe the default for SQLServer is 1433).
Using the SQLServer JDBC driver eliminates the need for you to create an ODBC DSN.
The ClassNotFoundException means that the SQLServer JDBC driver jar is not in your CLASSPATH. After you install the drivers make sure that mssqlserver.jar is specified in your CLASSPATH. For example, make sure that you have "<install_dir>\lib\mssqlserver.jar" (substitute <install_dir> with the actual installation directory) in your CLASSPATH. There might be other jar files in the lib directory that you need to include too.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: commit() and rollback() problem!