Entering a new Data Base entry

[EmbeddedHelp]

Hi all,

(Firstly please note I am a beginner programmer!).

I have the following code where I am trying to print the data base entries, then the user press the space bar to add another entry, followed by the data base contents being printed again.

Any pointers in the right direction would be great!

import java.sql.*;
import java.util.Scanner;

public class Q1
{
	public static void main(String[] args)
	{
		Connection connection = null;
		Statement statement = null;
		ResultSet results = null;
		
		Scanner keyboard = new Scanner(System.in);

		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			connection = DriverManager.getConnection(
								"jdbc:odbc:Finances","","");
		}
		catch(ClassNotFoundException cnfEx)
		{
			System.out.println("* Unable to load driver! *");
			System.exit(1);
		}
		catch(SQLException sqlEx)
		{
			System.out.println(
						"* Cannot connect to database! *");
			System.exit(1);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch(SQLException sqlEx)
			{
				System.out.println("* Unable to disconnect! *");
			}
		}
		
		processResults();
		System.out.print("Old Data.");
		System.out.print("\n\nPress 'Enter' to add an entry.");
		keyboard.nextLine();

		String insert = "INSERT INTO Accounts"
			+ "VALUES(112233,'SMITH',"
			+ "'John James',752.85)";
		int result = statement.executeUpdate(insert);
		processResults();
		System.out.print("New Data.");
	}
	
	public static void processResults()
	{
		Connection connection = null;
		Statement statement = null;
		ResultSet results = null;
	try
	{
		statement = connection.createStatement();
		results = statement.executeQuery(
								"SELECT * FROM Accounts");
	}
	catch(SQLException sqlEx)
	{
		System.out.println("* Cannot execute query! *");
		System.exit(1);
	}

	try
	{
		while (results.next())
		{
			System.out.println();
			System.out.println("Account no. "
								+ results.getInt(1));
			System.out.println("Account holder:  "
								+ results.getString(3)
								+ " " + results.getString(2));
			System.out.printf("Balance: %.2f %n%n",
										results.getFloat(4));
		}
	}
	catch(SQLException sqlEx)
	{
		System.out.println("* Error retrieving data! *");
		System.exit(1);
	}
	}
}

Much appreciated :)

[javaAddict]

First of all. You open the connection and immediately you close. The finally always gets executed. So when you open it at the beginning of your program you go and close it.

At the method processResults you declare another Connection object. It is not the same as the one in the main method. You initialize to null and you try to use it.

Here is a suggestion. Use different methods. Make it simple The following code may not be very efficient but it's simple. Remove the connection from the main and have 2 methods:

processResults() {

Connection connection = null;
Statement statement = null;
ResultSet results = null;

try {

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  connection = DriverManager.getConnection("jdbc:odbc:Finances","","");
  statement = connection.createStatement();
	results = statement.executeQuery("SELECT * FROM Accounts");
  
  
  while (results.next()) {
			System.out.println();
			System.out.println("Account no. " + results.getInt(1));
			System.out.println("Account holder:  " + results.getString(3) + " " + results.getString(2));
			System.out.printf("Balance: %.2f %n%n", results.getFloat(4));
	}
	
} catch(ClassNotFoundException cnfEx) {
  System.out.println("* Unable to load driver! *");
  System.out.println(cnfEx.getMessage();
  System.exit(1);
} catch(SQLException sqlEx) {
  System.out.println("* Cannot connect to database! *");
  System.out.println(sqlEx.getMessage();
  System.exit(2);
} finally {
 try {
  if (results!=null) results.close();
  if (statement!=null) statement.close();
  if (connection!=null) connection.close();
 } catch(SQLException sqlEx) {
  System.out.println("* Unable to disconnect! *");
  System.out.println(sqlEx.getMessage();
 }
}

}

insert() {

Connection connection = null;
Statement statement = null;

try {

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  connection = DriverManager.getConnection("jdbc:odbc:Finances","","");
  statement = connection.createStatement();
  
  int result = statement.executeUpdate("INSERT INTO Accounts VALUES(112233,'SMITH', 'John James',752.85)");
	
} catch(ClassNotFoundException cnfEx) {
  System.out.println("* Unable to load driver! *");
  System.out.println(cnfEx.getMessage();
  System.exit(1);
} catch(SQLException sqlEx) {
  System.out.println("* Cannot connect to database! *");
  System.out.println(sqlEx.getMessage();
  System.exit(2);
} finally {
 try {
  if (statement!=null) statement.close();
  if (connection!=null) connection.close();
 } catch(SQLException sqlEx) {
  System.out.println("* Unable to disconnect! *");
  System.out.println(sqlEx.getMessage();
 }
}

}

Then in the main, Simply call those methods. Next time don't forget to post the errors that you get as well

[EmbeddedHelp]

Thank you for the advice!

I have edited the code to look like this:

import java.sql.*;
import java.util.Scanner;

public class Q1
{
	public static void main(String[] args)
	{
		processResults(); 
		System.out.print("Old Data.");
		
		System.out.println("\n\nPress enter to continue...");
		Scanner keyboard = new Scanner(System.in);
		keyboard.nextLine();

		insert();
		
		processResults(); 
		System.out.print("New Data.");	
		
	}
		public static void processResults() 
		{
				 
				Connection connection = null;
				Statement statement = null;
				ResultSet results = null;
			
				try {
				 
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  connection = DriverManager.getConnection("jdbc:odbc:Finances","","");
				  statement = connection.createStatement();
					results = statement.executeQuery("SELECT * FROM Accounts");
				 
			 
				while (results.next()) {
							System.out.println();
					System.out.println("Account no. " + results.getInt(1));
							System.out.println("Account holder:  " + results.getString(3) + " " + results.getString(2));
							System.out.printf("Balance: %.2f %n%n", results.getFloat(4));
				}
				 
				} catch(ClassNotFoundException cnfEx) {
				  System.out.println("* Unable to load driver! *");
				  System.out.println(cnfEx.getMessage());
				  System.exit(1);
				} catch(SQLException sqlEx) {
				  System.out.println("* Cannot connect to database! *");
			    System.out.println(sqlEx.getMessage());
			    System.exit(2);
				} finally {
				try {
				  if (results!=null) results.close();
				  if (statement!=null) statement.close();
				  if (connection!=null) connection.close();
				 } catch(SQLException sqlEx) {
				  System.out.println("* Unable to disconnect! *");
				  System.out.println(sqlEx.getMessage());
				 }
				}
		}
		
		public static void insert() {
				 
				Connection connection = null;
				Statement statement = null;
				 
				try {
				 
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  connection = DriverManager.getConnection("jdbc:odbc:Finances","","");
			      statement = connection.createStatement();
				 
				  int result = statement.executeUpdate("INSERT INTO Accounts VALUES(112233,'SMITH', 'John James',752.85)");
				 
			} catch(ClassNotFoundException cnfEx) {
			  System.out.println("* Unable to load driver! *");
				  System.out.println(cnfEx.getMessage());
			  System.exit(1);
				} catch(SQLException sqlEx) {
				  System.out.println("* Cannot connect to database! *");
			  System.out.println(sqlEx.getMessage());
				  System.exit(2);
				} finally {
				 try {
			  if (statement!=null) statement.close();
				  if (connection!=null) connection.close();
				 } catch(SQLException sqlEx) {
			  System.out.println("* Unable to disconnect! *");
			  System.out.println(sqlEx.getMessage());
				 }
			}
			 
			}


	}

However the output reads as follows:
"Account no. 123456
Account holder: James Michael Black
Balance: 123.45

Account no. 234567
Account holder: Peter White
Balance: 35.60

Account no. 333333
Account holder: Sally Jones
Balance: 5000.00

Account no. 112233
Account holder: John James SMITH
Balance: 752.85

Old Data.

Press enter to continue...

* Cannot connect to database! *
General error"

As you can see it generates the new entry as soon as you run the program, not after the enter key is hit. Also at the end instead of displaying all the values it gives an error?

Any pointers would be much appreciated on how to run the main section :)

[javaAddict]

As you can see the first method runs. But the insert method doesn't/ Maybe you should add some debug messages, like at the beginning and end of each method:

public static void processResults() {
  System.out.println("Entering processResults");
......
.....

  System.out.println("Exiting processResults");
}

Also replace the System.exit code with return statements.
And it would be a good thing inside each catch to add this:

catch(SQLException sqlEx) {
  sqlEx.printStackTrace();
  ......
}