aspose file tools*
The moose likes JDBC and the fly likes commit problem - always happens even if set to false!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "commit problem - always happens even if set to false!!" Watch "commit problem - always happens even if set to false!!" New topic
Author

commit problem - always happens even if set to false!!

Lukas Alamar
Ranch Hand

Joined: Mar 17, 2004
Posts: 68
Hi all, I have a problem doing the following:
1-get a connection object
2-set its auto commit to false
3-get a PreparedStatement from the Connection object
4-last I call ps.executeUpdate();

I DO NOT call commit() on my Connection object, so I expect the program not to do any insert/updates, but... surprise! it does
my PreparedStatement obj writes to the db...

Can anyone tell me how this can be??

Thanks a lot
[ September 13, 2004: Message edited by: Lukas Alamar ]

SCJP 1.4
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61648
    
  67

Are you rolling the transaction back? If not, I believe that the connection object may be performing a commit when it closes if it detects an open transaction. Perhaps someone more in the know on this issue could comment?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
David C. Meyers
Greenhorn

Joined: May 20, 2004
Posts: 14
What database are you using? All of the databases that I have used with the exception of Oracle auto commit. I would suggest forcing a roll back if you do not want to commit.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I very much doubt that you'd be seeing the behaviour that Bear suggests. The natural thing to do (if anything - I would suggest nothing) would be for the driver software to rollback under those circumstances.

The more likely explanation is that the database you're using (my guess would be the ubiquitous MySQL) doesn't support transactions by default (or in your version). I take it you're sure that the data is committed and that you aren't just looking at uncommitted data somehow (possible either within the same session/connection or using READ_UNCOMMITTED transaction isolation)?

All major RDBMS (Oracle, Sybase, MS SQL Server, DB2, Ingres) support transactions by default in my experience. It's only noddy ones like Access and MySQL that don't.

Hope that helps.

Jules
Lukas Alamar
Ranch Hand

Joined: Mar 17, 2004
Posts: 68
Hi, thanks for your reply, I got it fixed with con.rollback().

By the way :roll: ,
have you ever had problems using a PreparedStatement object's setNull()??
I do the following

and get this error:
java.sql.SQLException: Could not execute sql command - Original message: null

If I try to execute the same query on my db client...it works.

I have to set that value to null and the corresponding db field is of type CHAR and is nullable.

Maybe some of you had the same problem and can help.

Thanks.
[ September 16, 2004: Message edited by: Lukas Alamar ]
Jyothi Lature
Ranch Hand

Joined: Oct 31, 2001
Posts: 60
Hi,

Try giving java.sql.NULL i.e.,

ps.setNull(1, java.sql.NULL);


Jyothi<br />Sun Certified Java Programmer<br />Brainbench Certified for Javascript
Jyothi Lature
Ranch Hand

Joined: Oct 31, 2001
Posts: 60
Sorry, i meant java.sql.Types.NULL
Lukas Alamar
Ranch Hand

Joined: Mar 17, 2004
Posts: 68
no way... it still doesnt work...


i cant figure it out...

any ideas??
Lukas Alamar
Ranch Hand

Joined: Mar 17, 2004
Posts: 68
I've been trying everything:

ps.setString(10, null);
ps.setNull(10, Types.CHAR);
ps.setString(10, Types.NULL);

I'always getting
java.sql.SQLException: Could not execute sql command - Original message: null
on this line:

ps.executeUpdate();

Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
What is the sql you are trying to do an update for? Print that out. The error may not be related to the parameter settings.
Lukas Alamar
Ranch Hand

Joined: Mar 17, 2004
Posts: 68
Hi, I do print it out and execute it, it works fine this way, it just doesn't work at runtime.
Pradyut Bhattacharya
Ranch Hand

Joined: Aug 12, 2004
Posts: 63
try using ps.setString(10, "") instead
thnks


Pradyut
http://pradyut.tk/
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: commit problem - always happens even if set to false!!