Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unusual problem

 
David Aguilar
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am sure this has a simple solution, but I can't seem to figure the problem out. Here is some sample code that I am using (specifics removed to protect the guilty):

import java.sql.*;
import javax.swing.JOptionPane;
public class SQLtest
{
public static void main(String args[])
{
ResultSet resultset1 = null;
try
{
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection connection1 = java.sql.DriverManager.getConnection("jdbc:sqlserver://SERVERNAME : PORTNUM;user=USERNAME;password=PASSWORD");
System.out.println("Connected!");
Statement statement1 = connection1.createStatement();
statement1.execute("SET IMPLICIT_TRANSACTIONS ON;");
statement1.execute("update TABLENAME set FIELDX = DUMMYVALUE where FIELDY = VARIABLE1 AND FIELDZ = VARIABLE2;");
resultset1 = statement1.executeQuery("select * from TABLENAME where FIELDY = VARIABLE1;");
JOptionPane.showMessageDialog(null, "Click here to print the ResultSet", "User Interaction Required", JOptionPane.INFORMATION_MESSAGE);
while(resultset1.next() )
{
System.out.println("TABLENAME result = " + resultset1.getObject(2) + " " + resultset1.getObject(3) + " " + resultset1.getObject(7));
}
statement1.execute("COMMIT TRANSACTION;");
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}

The above code works perfectly well as one would expect; after clicking OK in the information pane, the screen prints a list of variables from TABLENAME. The problem is, if I try to use the print statements in the while loop AFTER the line with the COMMIT statement (by shifting the COMMIT statement up before while), it tells me the data set is already closed.

In other words, I cannot use my result set in the code after my COMMIT statement. Has anyone encountered an error like this before? The result set is declared before the try block, so that's not the problem, and it should be public.

Thanks,
D.
[ May 22, 2006: Message edited by: David Aguilar ]
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yep, nothing unusual about it. That code should do exactly that, it's what the JDBC standard requires.

The resultset1 object is generated from the statement1 object and remains a child of that statement; when a new query is executed with that Statement object (for a Statement), or a PreparedStatement object is re-prepared, the JDBC specification requires that any child ResultSet objects be closed.

You can either use a second Statement object, or put your commit between the update and the select.
 
David Aguilar
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Thanks for the help; I will just use variables that I set to the specific element of the result set I want to use later on.

Another issue. When I run the following block of code within a servlet, I get the message:

com.microsoft.sqlserver.jdbc.SQLServerException: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

the code looks like this:

String q = "SET IMPLICIT_TRANSACTIONS ON";
update(q); //This method executes the statement in q
q = "BEGIN TRANSACTION";
update(q);
q = "UPDATE TABLENAME SET VARIABLE = VARIABLE + 1";
update(q);
q = "SELECT VARIABLE FROM TABLENAME";
ResultSet rs1 = getQuery(q); //This method returns a resultset
Unique = getNumber(rs1); //This method manipulates the results and sets a variable
q = "COMMIT TRANSACTION";
update(q);
q = "SET IMPLICIT_TRANSACTIONS OFF";
update(q);

There is both a BEGIN and a SET IMPLICIT_TRANSACTIONS statement before the COMMIT, yet it doesn't seem to be recognized.

Thanks,
D.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David Aguilar:
Hi,

Thanks for the help; I will just use variables that I set to the specific element of the result set I want to use later on.

Another issue. When I run the following block of code within a servlet, I get the message:

com.microsoft.sqlserver.jdbc.SQLServerException: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

the code looks like this:

String q = "SET IMPLICIT_TRANSACTIONS ON";
update(q); //This method executes the statement in q
q = "BEGIN TRANSACTION";
update(q);
q = "UPDATE TABLENAME SET VARIABLE = VARIABLE + 1";
update(q);
q = "SELECT VARIABLE FROM TABLENAME";
ResultSet rs1 = getQuery(q); //This method returns a resultset
Unique = getNumber(rs1); //This method manipulates the results and sets a variable
q = "COMMIT TRANSACTION";
update(q);
q = "SET IMPLICIT_TRANSACTIONS OFF";
update(q);

There is both a BEGIN and a SET IMPLICIT_TRANSACTIONS statement before the COMMIT, yet it doesn't seem to be recognized.

Thanks,
D.


Either something 'extra' is going on in the update(), getQuery(), or getNumber() calls, or the feature is broken (or I'm not understanding something myself).

One totally wild guess is that those functions aren't using the same connection; transactions are tied to the session of the connection.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic