Java problem inserting a record into Access DB

[dav3]

Alright folks I am in need of a lil guidance/assistance here. I have a program which reads in a txt file. This txt file contains lines of the form

January 3, 2007, 85.8

Now each line of the txt file is to be read into my java program and then inserted (from the program into an Access Database). I am not exactly sure of where the problem lies. I know it has something to do with how I am parsing the file, or how I am passing my arguments to my insertRecord().

Ok enough chatter, here is the code I need help with.

This is the function reading the file obviously.

public static void readFile(String fileName)
{
    JDBC dbInstance = new JDBC();
    String line;
    try
    {
        BufferedReader in = new BufferedReader(new FileReader(fileName));
        while((line = in.readLine())!= null)
        {
            StringTokenizer tokens = new StringTokenizer(line, ",");
 
                String month = tokens.nextToken();
                String day = tokens.nextToken();
                String year = tokens.nextToken();
                String close = tokens.nextToken();
                double aDouble = Double.parseDouble(close);
                String date = month+" "+day;
                System.out.println(date+","+year+","+aDouble);
 
            dbInstance.insertRecord(date,year,aDouble);
                if(line == null)
                break;
        //    System.out.println(line);
            }
        in.close();
    }catch(Exception e)
    {
        System.out.println("error loading file or something. " + e.getMessage());
    }
}
 

Here is the function inserting the records

       public void insertRecord(String Date, String Year, double Close )
       {
           String dateAndYear = Date+","+Year;
           String data = "("+"'"+dateAndYear+"'"+","+Close+");";
           String sqlRecord = "insert into Apple values " + data;
           System.out.println ("record = " + sqlRecord);
           try
           {
              db_statement.executeUpdate (sqlRecord);
              System.out.println ("-= record inserted =-");
              commitChanges();
           } catch (Exception excep) {
              System.out.println ("Unable to insert record: n" + excep);
              System.exit(0);
           }
        }
 

Lastly here is my createTable function, just in case anyone was interested:)

  public void createTable(String stockName)
       {
           String custTable = "CREATE TABLE "+ stockName +
           "([Date] TEXT(50) NOT NULL, Close NUMBER NOT NULL)";
           try 
           {
              db_statement.executeUpdate(custTable);
              System.out.println ("-= table created =-");
           } catch (Exception excep) {
              System.out.println ("Unable to create table: n" + excep);
              System.exit(0);
           }
        }
 

I have been able to insert records if i hardcode them into my program directly (obviously I don't want to do this for thousands of records). I suppose my output may also be helpful for the brave soul who undertakes the role of my hero. It should also be noted that I am not a very experienced programmer so please "dumb" down any help you may have for the sake of my sanity:)


Output:
-= database driver loaded =-
-= connected to database to DB =- stocks
-= statement created =-
-= table created =-
January 3,2007,83.8
record = insert into Apple values ('January 3,2007',83.8);
Unable to insert record: njava.lang.NullPointerException

[dav3]

ok I am pretty sure everything is ok in my code, as far as I can tell anyway. Its this nullPointerExceptin that is causing me to lose hair at a rapid rate!

[r035198x]

ok I am pretty sure everything is ok in my code, as far as I can tell anyway. Its this nullPointerExceptin that is causing me to lose hair at a rapid rate!

1.) Don't use StringTokenizer, use the String.split method instead.
2.) In your catch block, don't print the Exception's toString, call execption.printStackTrace() instead. That way you get full information about the exception.
3.) What does your commitChanges() method do?

[dav3]

1.) Don't use StringTokenizer, use the String.split method instead.
2.) In your catch block, don't print the Exception's toString, call execption.printStackTrace() instead. That way you get full information about the exception.
3.) What does your commitChanges() method do?

ty for your reply, I will try your recommendations tonight. My commitchanges() method just commits the changes to the database. Without it the records won't save to the database.

[r035198x]

ty for your reply, I will try your recommendations tonight. My commitchanges() method just commits the changes to the database. Without it the records won't save to the database.

By default connections are set to autoCommit true so your autoCommit method is probably redundant. There are only two suspects for that nullpointer and one of them is that autoCommit method of yours.

[dav3]

By default connections are set to autoCommit true so your autoCommit method is probably redundant. There are only two suspects for that nullpointer and one of them is that autoCommit method of yours.

I removed the commitchanges function and am still getting this pesky error about nullpointerexceptions. I tried leaving my code as is just commenting out commitchanges.

[r035198x]

I removed the commitchanges function and am still getting this pesky error about nullpointerexceptions. I tried leaving my code as is just commenting out commitchanges.

Did you use exception.printStackTrace() to see more about the exception?

[dav3]

it will not allow me to use Exception.printStackTrace(); perhaps im calling it incorrectly?

catch(Exception e)
    {
        System.out.println("error loading file or something. " + e.printStackTrace());
    }
 

EDIT: ah was adding it to the wrong catch:)

[r035198x]

it will not allow me to use Exception.printStackTrace(); perhaps im calling it incorrectly?

catch(Exception e)
    {
        System.out.println("error loading file or something. " + e.printStackTrace());
    }
 

EDIT: ah was adding it to the wrong catch:)

Have you found out the reason for the nullpointer then?

[dav3]

well it gave me 3 things to look at, and i cannot find a problem with any of the 3 statements.

Unable to insert record: njava.lang.NullPointerException
java.lang.NullPointerException
at JDBC.insertRecord(JDBC.java:69)
at gui_and_Main.readFile(gui_and_Main.java:30)
at gui_and_Main.main(gui_and_Main.java:57)

the lines in question are:

 db_statement.executeUpdate (sqlRecord);

dbInstance.insertRecord(date,aDouble);

      readFile("apple.txt");

This is all I am given, and cannot "conncet" the dots so to speak.

[r035198x]

well it gave me 3 things to look at, and i cannot find a problem with any of the 3 statements.

Unable to insert record: njava.lang.NullPointerException
java.lang.NullPointerException
at JDBC.insertRecord(JDBC.java:69)
at gui_and_Main.readFile(gui_and_Main.java:30)
at gui_and_Main.main(gui_and_Main.java:57)

the lines in question are:

 db_statement.executeUpdate (sqlRecord);

dbInstance.insertRecord(date,aDouble);

      readFile("apple.txt");

This is all I am given, and cannot "conncet" the dots so to speak.

Now put a println before

db_statement.executeUpdate (sqlRecord);

to check which variable is null at that point.

Probably db_statement itself is null at that point.

P.S Welcome to println debugging. The poor man's debugger.

[dav3]

ok inserted a line to see if either value was null, and they are both there. Could the problem be the whitespace? and if so, how do you get rid of it? I looked into it last night but could not get anything to work.

[r035198x]

ok inserted a line to see if either value was null, and they are both there. Could the problem be the whitespace? and if so, how do you get rid of it? I looked into it last night but could not get anything to work.

So you mean none of the values there is null? Come on there is a null reference there that's being dereferenced.

[dav3]

-= database driver loaded =-
-= connected to database to DB =- stocks
-= statement created =-
record = INSERT INTO Apple VALUES ('03-Jan-07' , 83.8);
test line 03-Jan-0783.8
Unable to insert record: njava.lang.NullPointerException
java.lang.NullPointerException
at JDBC.insertRecord(JDBC.java:70)
at gui_and_Main.readFile(gui_and_Main.java:30)
at gui_and_Main.main(gui_and_Main.java:57)


theres the output. Here is my current insertRecord();

 public void insertRecord(String Date, String Close )
       {
         //  String dateAndYear = Date+","+Year;
           String data = "("+"'"+Date+"'"+" , "+Close+");";
           String sqlRecord = "INSERT INTO Apple VALUES " + data;
           System.out.println ("record = " + sqlRecord);
           try
           {
               System.out.println("test line " + Date + Close);
              db_statement.executeUpdate (sqlRecord);
              System.out.println ("-= record inserted =-");
            commitChanges();
           } catch (Exception excep) {
              System.out.println ("Unable to insert record: n" + excep);
              excep.printStackTrace();
              System.exit(0);
           }
        }
 

[r035198x]

And where did you print the db_statement variable itself to check if it's null or not?

[dav3]

Not sure I understand now...

is that not what lines 5 and 6 of my previous post are doing?

[r035198x]

Not sure I understand now...

is that not what lines 5 and 6 of my previous post are doing?

Add this line and see what happens

System.out.println(db_statement); //add this line
db_statement.executeUpdate(sqlRecord);

[dav3]

ok it says its null, but the statement hasnt been created yet? (no?)


Why the hell is it null?!


*confused*

[r035198x]

ok it says its null, but the statement hasnt been created yet? (no?)


Why the hell is it null?!


*confused*

Where did you initialize the db_statement variable?
It often helps to dry run the code and check to make sure that your flow ensures that all variables are properly initialized when they are needed.

[dav3]

but why will insertRecord(); work perfectly if i pass it say

dbInstance.insertRecord("01-11-07","89.7");

Ok through trial and error i stumbled upon a solution. I just need to move my db.Instance.loadDriver(); etc.... functions up to the readfile();

I think I see the reason for this, thank you for your help ro35198x. This is only step 4 of a MASSIVE project so rest assured I will be back:)

Thanks again.

[r035198x]

but why will insertRecord(); work perfectly if i pass it say

dbInstance.insertRecord("01-11-07","89.7");

Ok through trial and error i stumbled upon a solution. I just need to move my db.Instance.loadDriver(); etc.... functions up to the readfile();

I think I see the reason for this, thank you for your help ro35198x. This is only step 4 of a MASSIVE project so rest assured I will be back:)

Thanks again.

It's r035198x.