Error in Java when attempting to insert data in database

The attached code throws the errors below when I run it in Eclipse, I want to add a line to a single table database, any suggestions please?

errors -

Exception in thread "main" java.lang.NullPointerException
      at database.Insert.insertRow(Insert.java:60)
      at database.Insert.<init>(Insert.java:36)
      at database.Insert.main(Insert.java:28)
package database;

import java.sql.*;

public class Insert {
	
	static Connection link;
	static Statement statement;
	static ResultSet results;
	
	public static void main(String[] args){
		
		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
		}
		catch(ClassNotFoundException e){
			System.out.println("Unable to load driver");
			System.exit(1);
		}
		catch(SQLException e){
			System.out.println("Cannot connect to the database");
			System.exit(1);
		}
	
		new Insert();
		
	} //End main
	
	public Insert()
	{
		try
		{
			insertRow(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("SQL Error Code: " + e.getErrorCode());
			e.printStackTrace();
			System.exit(1);
		}
	
	}
	
	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException
	  {
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", " 
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";


   
	       
	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       { 
	    	  System.out.println("Failed to insert the following data: " 
	    			         + ID   + "," 
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }
	
	
	
	
	

	  }
}

Open in new window

t38Asked:
Who is Participating?
 
for_yanCommented:
Did you add link.close(); after result = ...  ?
0
 
CEHJCommented:
>>int result = statement.executeUpdate(insert);

The Statement is not initialized:

statement = link.createStatement();

Open in new window

0
 
for_yanCommented:
I suggest that uyou print exactly the same string as you insert
before update operation and then try to do insert of the same for the SQL command prompt
Do you have aacses to SQL command prompt?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
for_yanCommented:
Yes, in this case CEHJ is right this issue is becuase uou need to create statement,
nevertheless what I wrote above is a very good pracitice
when debugging DB activity
0
 
CEHJCommented:
You should probably use a PreparedStatement - it will save you all the bother of quoting and will be less error-prone
0
 
t38Author Commented:
thanks for the info, where exactly should i insert statement = link.createStatement(); ?
0
 
for_yanCommented:


Just after you create connection:

link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");

 statement = link.createStatement();

0
 
for_yanCommented:
Everyone is recommending PreparedStatement, and I do use them especially when
a lot of similar updates need to be done, and PreparedStatemnet should be faster in this case.

However, in my personal observations I very rarely noticed increase of efficiency
comparing PreparedStatement with regular updates. My guess is that I'm working exclusively with Oracle
and Oracle does this caching for similar SQL by itself anyway, that's why I don't see
time efficiency of PreparedStatement

One reason I don't use PreparedStatement all the time is exactly what I wrote above - as far as I know,
PreapredStatemnt does not have a simple method to print prepared SQL string
before you execute it. And this is a very important thing, especially if you work
without setparate testing environemnt and need to do your
debugging on real or almost real database.
In my opinion, the best thing is just make up this insert string
like you do it, and then cut and paste contents of the parenthseies exactly the same
string to System.out.println() - and pruint it out.
System.out.println(..) should of course  go before the actual update line
(otherwise you'll not see it when there is an error).
Actually when I first write the program in the first run I only  leave System.out...
and comment out actual update line - that will save you
possibility to do siomething weird with your database while debugging the initial
version of your code (quite reall possibility in many cases - sya, if you forget
the clause in the update statement)
 


0
 
t38Author Commented:
Thanks, it's still not populating the database though.  I'm not getting an error, when I press Run in Eclipse it compiles but does nothing?
0
 
for_yanCommented:

I sggest that you follow what I wrote above - use the same insertString to print right before
the update operation - you'll see if you get to that point and how the insert line looks
0
 
for_yanCommented:

Does nothing - ?
Does it print what it should print when it failed to update?
and no exception?
0
 
t38Author Commented:
sorry for_yan i don't follow what you mean, i'm relatively new to eclipse, could you get me some more detail please?
0
 
for_yanCommented:

You say that db is not updated fter the run.
But when you run you should see someconsole output or error message - do you see it?
0
 
for_yanCommented:

This is with samll mod that I gsuggested - run this one and lets see if we get printout

package database;

import java.sql.*;

public class Insert {
	
	static Connection link;
	static Statement statement;
	static ResultSet results;
	
	public static void main(String[] args){
		
		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
		}
		catch(ClassNotFoundException e){
			System.out.println("Unable to load driver");
			System.exit(1);
		}
		catch(SQLException e){
			System.out.println("Cannot connect to the database");
			System.exit(1);
		}
	
		new Insert();
		
	} //End main
	
	public Insert()
	{
		try
		{
			insertRow(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("SQL Error Code: " + e.getErrorCode());
			e.printStackTrace();
			System.exit(1);
		}
	
	}
	
	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException
	  {
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", " 
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);
   
	       
	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       { 
	    	  System.out.println("Failed to insert the following data: " 
	    			         + ID   + "," 
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }
	
	
	
	
	

	  }
}

Open in new window

0
 
for_yanCommented:


In the above code I just added
System.out.println(insert);
   
             befiore this:

             int result = statement.executeUpdate(insert);

So we should see, if we reach that place , and if we do, what
kind of insert tsring we hacve
0
 
t38Author Commented:
no, that's the problem it literally does nothing, it seems to compile properly i.e. no errors but when i check the database nothing has been added!
0
 
for_yanCommented:
Sorry , i copied your orioginal, so you need this creatConnection to add again
0
 
for_yanCommented:
OK, so add

System.out.println(insert);

as I suggested above and run again and let us know what you see
0
 
t38Author Commented:
here's the error with the 'new' code -

INSERT INTO BookShelf VALUES (11, 'BookTest', 'Peter Andre','Publish', 2000,20.0)
Exception in thread "main" java.lang.NullPointerException
      at database.Insert.insertRow(Insert.java:60)
      at database.Insert.<init>(Insert.java:36)
      at database.Insert.main(Insert.java:28)
0
 
t38Author Commented:
sorry, didn't see your entry, hold on a sec and i'll run it again..
0
 
t38Author Commented:
this is the output to the console - -

INSERT INTO BookShelf VALUES (11, 'BookTest', 'Peter Andre','Publish', 2000,20.0)
0
 
for_yanCommented:
Even after you added the

System.out.println(insert) ;

line, as I wrote above - ?
0
 
for_yanCommented:
Please, post the whole code again.
Maybe yyiou still don't have createStatement() line?
0
 
t38Author Commented:
yes, here's the code copied from eclipse
package database;

import java.sql.*;

public class Insert {
	
	static Connection link;
	static Statement statement;
	static ResultSet results;
	
	public static void main(String[] args){
		
		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
			statement = link.createStatement();
		}
		catch(ClassNotFoundException e){
			System.out.println("Unable to load driver");
			System.exit(1);
		}
		catch(SQLException e){
			System.out.println("Cannot connect to the database");
			System.exit(1);
		}
	
		new Insert();
		
	} //End main
	
	public Insert()
	{
		try
		{
			insertRow(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("SQL Error Code: " + e.getErrorCode());
			e.printStackTrace();
			System.exit(1);
		}
	
	}
	
	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException
	  {
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", " 
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);
   
	       
	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       { 
	    	  System.out.println("Failed to insert the following data: " 
	    			         + ID   + "," 
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }
	
	
	
	
	

	  }
}

Open in new window

0
 
for_yanCommented:
Actually printout is OK, but it looks like ststemnent is still not created
0
 
t38Author Commented:
so what should i do?
0
 
for_yanCommented:
Remove static from the beginning
(it probably would not help, but its kind of more normal)

      Connection link;
       Statement statement;
       ResultSet results;


and then
add
  System.out.println("statn: " + statement);
after this:

link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
                  statement = link.createStatement();


and also after
Syatem.out.println(insert);

so we'll check if ataement is not null in either case








0
 
for_yanCommented:
maybe it cannot connect to the databse?

You may also print
System.out.pruintln("linl: " + link);

in the first part after you create connection (agfter ..getConnection)
0
 
t38Author Commented:
ok thanks again, i won't be able to try this for about 15 minutes but will report back shortly..
0
 
for_yanCommented:

I suggest that you try first this much less convoluted code whicvh should do the same:
(save your variant somewhere and  then run this one)


package database;

import java.sql.*;

public class Insert {

	static Connection link;
	static Statement statement;
	static ResultSet results;

	public static void main(String[] args){

         new Insert();
    }

      public Insert() {



		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
			statement = link.createStatement();
int ID=11;

String Title =  "BookTest";

String Author =  "Peter Andre";
String Publisher =  "Publish";
double Year = 2000;
double Cost = 20.0;

		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", "
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);


	       int result = statement.executeUpdate(insert);

  System.out.println("iserted " + result + " records");

    link.close();

		}catch(Exception ex){
     System.out.println("Error " + ex.toString();
     ex.printStackTrace();

    }
System.exit(0);
}

}

Open in new window

0
 
for_yanCommented:
Sorry, this is better:

package database;

import java.sql.*;

public class Insert {

	 Connection link;
	 Statement statement;
	 ResultSet results;

	public static void main(String[] args){

         new Insert();
    }

      public Insert() {



		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
			statement = link.createStatement();
int ID=11;

String Title =  "BookTest";

String Author =  "Peter Andre";
String Publisher =  "Publish";
double Year = 2000;
double Cost = 20.0;

		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", "
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);


	       int result = statement.executeUpdate(insert);

  System.out.println("iserted " + result + " records");

    link.close();

		}catch(Exception ex){
     System.out.println("Error " + ex.toString());
     ex.printStackTrace();

    }
System.exit(0);
}

}

Open in new window

0
 
t38Author Commented:
ok here's what prints to the console -

linl: sun.jdbc.odbc.JdbcOdbcConnection@de6f34
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713
INSERT INTO BookShelf VALUES (11, 'BookTest', 'Peter Andre','Publish', 2000,20.0)
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713

i've also attached the revised code to ensure i've put it in properly
linl: sun.jdbc.odbc.JdbcOdbcConnection@de6f34
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713
INSERT INTO BookShelf VALUES (11, 'BookTest', 'Peter Andre','Publish', 2000,20.0)
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713

Open in new window

0
 
t38Author Commented:
sorry here;s the code
package database;

import java.sql.*;

public class Insert {
	
	static Connection link;
	static Statement statement;
	static ResultSet results;
	
	public static void main(String[] args){
		
		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
			statement = link.createStatement();
			
			System.out.println("linl: " + link);


			
			 System.out.println("statn: " + statement);

		}
		catch(ClassNotFoundException e){
			System.out.println("Unable to load driver");
			System.exit(1);
		}
		catch(SQLException e){
			System.out.println("Cannot connect to the database");
			System.exit(1);
		}
	
		new Insert();
		
	} //End main
	
	public Insert()
	{
		try
		{
			
			insertRow(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
			
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("SQL Error Code: " + e.getErrorCode());
			e.printStackTrace();
			System.exit(1);
		}
	
	}
	
	
	
	
	
	
	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException
	  {
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", " 
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);
  
  System.out.println("statn: " + statement);

   
	       
	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       { 
	    	  System.out.println("Failed to insert the following data: " 
	    			         + ID   + "," 
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }
	
	
	
	
	

	  }
}

Open in new window

0
 
for_yanCommented:
All this output looks ok, and there is no error - are you sure it does not insert in database?
0
 
for_yanCommented:
Id it still did not insert, it is strange.
If indeed it still didn't insert, then
I'd still suggest that you try the code which
I sent you while you were in the break - the second variant
0
 
t38Author Commented:
ok so your suggested code worked, what's the difference that's making it function??
0
 
t38Author Commented:
apart from the obvious answer that you know what you're doing of course..!!  i'd really like to know why my version won't work though.
0
 
for_yanCommented:
Are you sure that your code which made that good output wihout any errors ddid not insert into databse?

it didn't peint any exceptio, it didn't print your outpout if(ressult == 0) how could it not work?
0
 
CEHJCommented:
You need to close the Statement and Connection
0
 
for_yanCommented:
It is suffiecient to close connection, but it is nit somethinfg which would explain any issue
0
 
for_yanCommented:
Please, check - when you executed last time your variant it should have been worked

Change the book name and execvute it again - thesre was no error - it should have worked
0
 
CEHJCommented:
>>it didn't peint any exceptio, it didn't print your outpout if(ressult == 0) how could it not work?

by not closing the Connection
0
 
for_yanCommented:

Yest it is cgood practce to do it, but normally it works for me even I forget to close connection
0
 
for_yanCommented:
Well, myabe Orcale is different - yes it is yuseful to close connections
0
 
t38Author Commented:
my code is definitely updating the database...
0
 
for_yanCommented:
Great!
0
 
t38Author Commented:
no sorry, that should have had a NOT in it, it has been a long day, it's not updating using my code..  this is very frustrating
0
 
for_yanCommented:
Add the close, and change Book title, so that it would be easy to check.
It should update
0
 
CEHJCommented:
This should be the general pattern (and using a PreparedStatement so that an author name like O'Leary won't make the app fall over)
import java.sql.*;


public class Insert {
    Connection link;
    PreparedStatement ps;
    ResultSet results;

    public void insert(int ID, String Title, String Author, String Publisher, int Year, double Cost) {
	try {
	    //Step 1
	    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	    //Step 2
	    link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");
	    doRow(ID, Title, Author, Publisher, Year, Cost);
	} catch (ClassNotFoundException e) {
	    e.printStackTrace();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	finally {
	    try {
		ps.close();
	    }
	    catch(SQLException e) {
		/* ignore */
	    }
	    try {
		link.close();
	    }
	    catch(SQLException e) {
		/* ignore */
	    }
	}
    }

    public void doRow(int ID, String Title, String Author,
	    String Publisher, int Year, double Cost) throws SQLException {
	String insert = "INSERT INTO Bookshelf VALUES(?, ?, ?, ?, ?, ?)";
	ps = link.prepareStatement(insert);
	ps.setInt(1, ID);
	ps.setString(2, Title);
	ps.setString(3, Author);
	ps.setString(4, Publisher);
	ps.setInt(5, Year);
	ps.setDouble(6, Cost);

	int result = ps.executeUpdate(insert);

	if (result == 0) {
	    System.out.println("Failed to insert the following data: " + ID +
		    "," + Title + "," + Author + "," + Publisher + "," + Year +
		    "," + Cost);
	}
    }

    public static void main(String[] args) {	
	Insert i = new Insert();
	i.insert(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
    }
}

Open in new window

0
 
t38Author Commented:
ok, i've added link.close(); but even before i did so i re-ran the code and it produced an error - if copied and pasted it some many times i don't know if i've made a mistake or not but it looks ok, here's the error -

linl: sun.jdbc.odbc.JdbcOdbcConnection@de6f34
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713
INSERT INTO BookShelf VALUES (11, 'BookTest', 'Peter Andre','Publish', 2000,20.0)
statn: sun.jdbc.odbc.JdbcOdbcStatement@e09713
Message: General error
SQL State: S1000
SQL Error Code: 0
java.sql.SQLException: General error
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
      at database.Insert.insertRow(Insert.java:74)
      at database.Insert.<init>(Insert.java:45)
      at database.Insert.main(Insert.java:36)
package database;

import java.sql.*;

public class Insert {
	
	static Connection link;
	static Statement statement;
	static ResultSet results;
	
	public static void main(String[] args){
		
		try{
			//Step 1
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			//Step 2
			link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
			statement = link.createStatement();
			
			System.out.println("linl: " + link);


			
			 System.out.println("statn: " + statement);

		}
		catch(ClassNotFoundException e){
			System.out.println("Unable to load driver");
			System.exit(1);
		}
		catch(SQLException e){
			System.out.println("Cannot connect to the database");
			System.exit(1);
		}
	
		new Insert();
		
	} //End main
	
	public Insert()
	{
		try
		{
			
			insertRow(11, "BookTest", "Peter Andre", "Publish", 2000, 20.0);
			
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("SQL Error Code: " + e.getErrorCode());
			e.printStackTrace();
			System.exit(1);
		}
	
	}
		
	
	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException
	  {
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", " 
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);
  
  System.out.println("statn: " + statement);

   
	       
	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       { 
	    	  System.out.println("Failed to insert the following data: " 
	    			         + ID   + "," 
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }
	
	link.close();
	
	
	

	  }
}

Open in new window

0
 
for_yanCommented:

Well, I suggest that you run several times,
till there is some consistent result.

If you don't chaneg anyhting it should run the same.
If you risrt time ran - there was not error, and second time there was error -
it is something wrong with the hardawre perhaps.

Try to run several times as it is - and if it behacves differently
then you have some hardware  issue or something
0
 
t38Author Commented:
actually i think that error was because i was trying to repopulate a row already in use -

here#s what i'm entering now -

insertRow(15, "TestOne", "Peter Andre", "Publish", 2000, 20.0);

OK, it's working now!  i think it was the link.close that did it.

thank you so much for all your help and patience....
0
 
for_yanCommented:
Great!

Perhpas there is some uniquer constraint somehwre in the table
0
 
for_yanCommented:
Well, CEHJ deserves credit in this case.
0
 
objectsCommented:
you should be using a PreparedStatement

let me know if you need help doing that
0
 
CEHJCommented:
>>Well, CEHJ deserves credit in this case.

Thanks for that for yan - next time maybe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.