1

im trying to use the below piece of code to insert in temp table and select it...when I run it, I get a NullPointer exception...Not sure what is the problem..the same runs well with mysql driver..Im using jdbc in both the cases

package mysql.first;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MSSqlAccess {
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private int dmlresultSet = 0;
    private ResultSet ddlresultSet = null;
    /**
     * @param args
     */

    public static void main(String[] args) throws Exception {
         MSSqlAccess dao = new MSSqlAccess();
            //dao.readDataBase();
         dao.createtemptable();
      }

    public void createtemptable() throws Exception{

          try
          {
              // This will load the MySQL driver, each DB has its own driver
              Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
              // Setup the connection with the DB

              String db_connect_string = "jdbc:sqlserver://localhost";
              String db_userid = "Test";
              String db_password ="test";

              Connection connect = DriverManager.getConnection(db_connect_string
                     ,db_userid, db_password);

              connect.setAutoCommit(false);

              // Statements allow to issue SQL queries to the database
              statement = connect.createStatement();
              // Result set get the result of the SQL query

              String sql = "CREATE TABLE #REGISTRATION " +
                      "(id INTEGER not NULL, " +
                      " first VARCHAR(255), " + 
                      " last VARCHAR(255) )"; 

              dmlresultSet = statement
                  .executeUpdate(sql);

              System.out.println("Temp table created return code = " + dmlresultSet);

              insertRows(1,"FNAME1","LNAME1");
              selectRows();
              insertRows(2,"FNAME2","LNAME2");
              selectRows();
              updateRows();
              selectRows();
              insertRows(3,"FNAME3","LNAME3");

          }
          catch (Exception e){
              System.out.println("Error = " + e);
                throw e;  
          } finally{
              close();
          }

      }

      private void insertRows(int id, String first, String last) throws SQLException {
          System.out.println(id);
          System.out.println(first);
          System.out.println(last);
          preparedStatement = connect
                  .prepareStatement("insert into  REGISTRATION values (?, ?, ?)");

          preparedStatement.setString(2, first);
          preparedStatement.setString(3, last);
          preparedStatement.setInt(1, id);
          preparedStatement.executeUpdate();

      }

      private void selectRows() throws SQLException {
          preparedStatement = connect
                  .prepareStatement("SELECT id, first, last from REGISTRATION");
          ddlresultSet = preparedStatement.executeQuery();
          writeResultSet(ddlresultSet);

      }

      private void updateRows() throws SQLException {
          preparedStatement = connect
                  .prepareStatement("Update REGISTRATION set first = 'RENAMED' WHERE ID = 1");
          dmlresultSet = preparedStatement.executeUpdate();
      }

      private void writeResultSet(ResultSet resultSet) throws SQLException {
          System.out.println("                                                        ");
          System.out.println("*****           Printing the Rows                  *****");
            // ResultSet is initially before the first data set
            while (resultSet.next()) {
              // It is possible to get the columns via name
              // also possible to get the columns via the column number
              // which starts at 1
              // e.g. resultSet.getSTring(2);
              int id = resultSet.getInt("id");
              String first = resultSet.getString("first");
              String last = resultSet.getString("last");
              System.out.println("ID: " + id);
              System.out.println("First: " + first);
              System.out.println("Last: " + last);

            }
          }

  // You need to close the resultSet
    private void close() {
    try {
      if (ddlresultSet != null) {
        ddlresultSet.close();
      }

      if (statement != null) {
        statement.close();
      }

      if (connect != null) {
        connect.close();
      }
    } catch (Exception e) {

    }
  }

}

2
  • Where do you get the NPE? Commented Jul 16, 2013 at 21:00
  • In insertRows.prepareStatement() Commented Jul 16, 2013 at 21:02

1 Answer 1

7

In "insertRows", you call a method on your class variable "connect" which is never initialized. Instead of:

Connection connect = DriverManager.getConnection(db_connect_string ,db_userid, db_password);

do:

this.connect = DriverManager.getConnection(db_connect_string ,db_userid, db_password);

That should do the trick.

Sign up to request clarification or add additional context in comments.

3 Comments

Connection connect = DriverManager.getConnection(db_connect_string ,db_userid, db_password);
You're instantiating a local variable, not your class variable.
@user1050619 If this answer helped you resolve your issue, please indicate so by upvoting (because it was useful) and accepting (because it was the most useful). Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.