[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

How do I connect to multiple tables in JSP?

I have connected successfully to a database table and executed select statments on the table. I now want to do an insert into a different table but i get a SQL error which says: "SQLException: Result set not positioned properly, perhaps you need to call next(). "
How do I do what I am trying to do?
0
VbMonk
Asked:
VbMonk
  • 4
  • 4
2 Solutions
 
TimYatesCommented:
PreparedStatement stmt = null ;
try
{
    stmt = conn.prepareStatement( "UPDATE tablename SET field=? where idfield=?" ) ;
    stmt.setString( 1, "TimYates" ) ;
    stmt.setInt( 2, id ) ;
    int rslt = stmt.executeUpdate() ;
    System.out.println( rslt + " lines updated" ) ;
}
catch( SQLException ex )
{
  ex.printStackTrace() ;
}
finally
{
    try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
}
0
 
VbMonkAuthor Commented:
what does this do? Does it connect to another different table?
0
 
reginabCommented:
this statment assumes a generic connection method, which is reusable then for however many sql call to any number of tables you need to make.  conn, is either out of the api or you can define your own if you require specailization. it would be something like this:
try {
      Class.forName(driver);
           conn = DriverManager.getConnection(connString, userName, password);
        }catch(java.lang.ClassNotFoundException e) {
        }
        catch(java.sql.SQLException e){
        }
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VbMonkAuthor Commented:
I have something like what reginab has outlined but i get the erro above. The PreparedStatement suggested byTimYates is like a template statement which can be executed a number of times when values are supplied. What if I want to execute a different statment from the one I executed before? for example,
Query1 = "insert into table1 (field1, field2) values (1, 2)"; and the second query statement is:
Query2 = "select * from table2 where id=2"
These are different queries. how can they be executed once?
0
 
reginabCommented:
import java.sql.*;
String Query1 = new String;
String Query2= new String;

Query1 = "insert into table1 (field1, field2) values (1, 2)"; and the second query statement is:
Query2 = "select * from table2 where id=2"

try {
   DriverManager.registerDriver(Class.forName("your-driver-class"));
   Connection con = DriverManager.getConnection("your-connect-string");
   Statement stmt = con.createStatement();
   stmt.execute(Query1);
   stmt.execute(Query2);
   stmt.close();
   con.close();
}
catch(ClassNotFoundException ex) {
   System.err.println("Could not load database driver: " + ex.getMessage());
}
catch(SQLException ex) {
   System.err.println("Database error: " + ex.getMessage());
}
0
 
VbMonkAuthor Commented:
I'll give you feedback when i test your code tomorrow reginab
0
 
VbMonkAuthor Commented:
Sorry my query statements need a result table. It doesn't work
0
 
reginabCommented:
well you have to have a result set to hold your results or a bean or something and you have to have a for loop to load it.  I guess I am not sure what it is you are actually trying to do here.  I went back and read your first post and it is confusing to me now, is the problem the result set or the problem query to multiple tables.
0
 
reginabCommented:
here is something you can play around with.
here are some details for using RowSet


http://www.javaworld.com/javaworld/jw-02-2001/jw-0202-cachedrow.html
http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html


here is an example of what to with a returned recordset when you need to work through a set of records.

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_10316891.html?query=java+fill+a+recordset&clearTAFilter=true

 public void executeQuery(String sQuery)
{
//For result returning SQL statements    
          this.theQuery = sQuery;
          ResultRows = new Vector();
          try
          {
            Statement queryStatement = databaseConnection.createStatement();
            ResultSet theResults = queryStatement.executeQuery(theQuery);
            ResultSetMetaData rsmeta = theResults.getMetaData();

            int numColumns = rsmeta.getColumnCount();
            String resultsText = new String("");
            while(theResults.next())
            {
              numRows++;
               Cols = new Vector();
               for(thisCol = 1; thisCol <= numColumns; thisCol++){
                    resultsText = theResults.getString(thisCol).trim();
                    Cols.addElement(resultsText);
               }
               ResultRows.addElement(Cols);
            }
          }
          catch(SQLException sqle)
          {
            System.out.println(sqle.toString());
          }    
     }
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now