Java/Access database

Thank you for replying.

I have visited many web-sites on Java database programming, though I still cannot connect to the database's overal file Books. And search the tables in the method I described in my orginal message. I have pasted some of the code here, to date I have been able to accept the command line in the main method and seperate the input into differet strings variables, username, bookTitle, and authorName. Now I wish to pass these to the add method, then search and add to the databases as previously described.

I would appreciate your guidance with the add method.


public class databaseAccess{

Connection conn;

void connect(){
try {
DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
}catch(Exception e) {
System.err.println("Error while connecting:");
System.err.println(e.getMessage());
System.exit(1);
}
}

void disconnect(){
try {
conn.close();
}catch(Exception e) {
System.err.println("Error while disconnecting");
System.err.println(e.getMessage());
System.exit(1);
}             //end exception
}

void add(username, bookTitle, authorName){

}

public static void main(String args[]){

//Enter a command such as kkj  add  Fishing guide  John Simpson

//past values to add(username, bookTitle, authorName) save in database

}      

}

I assume you're always getting the "Error while connecting" message.

This is because the driver you are trying to use:

>> DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());

is for MySQL, not Access.  For Access, you need to get a different driver (It should be something more like "com.ms.jdbc.odbc.JdbcOdbcDriver").  See http://servlet.java.sun.com/products/jdbc/drivers.

Thank you for replying.

I will certianly look to change the drivermanager.

My orginal question was regarding the add method, could you possibly offer any guidance in writting this method?

Or should I have posted my questions to the database experts?

Thanks

try
{
   Connection connection = DriverManager.getConnection(DATABASE_URL,
                                                                                 DATABASE_USER,
                                                                                 DATABASE_PASSWORD);
   Statement stmt = connection.createStatement();

   StringBuffer sqlInsert = new StringBuffer("INSERT INTO mybooks title, author VALUES('");
   sqlInsert.append(titleToInsert);
   sqlInsert.append("', '");
   sqlInsert.append(authorToInsert);
   sqlInsert.append("'");

   int rowsInserted = stmt.executeUpdate(sqlInsert.toString());
}
catch (SQLException sqle)
{
   System.err.println("SQLException occurred: " + sqle.toString());
}

That's the kind of thing you're looking for ;-)

Sorry, just to make it more applicable to your original question ;-) :

   StringBuffer sqlInsert = new StringBuffer("INSERT INTO bookinfo Username, Title, Auther VALUES('");
   sqlInsert.append(usernameToInsert);
   sqlInsert.append("', '");
   .
   .

Thank you for replying.

The add method I was trying to write is a little different to the code you sent. The database has two tables, username and bookInfo as below and these have there own fields. In the add method, I was hoping when passed the values for example  ggh  add  Fishing Guide  John Simpson. The add method would first query the username table to confirm if the user has an existing account and if they have an account just add the books details to the bookInfo table. And if the username could not be found in the first table, this new username would be added to the first table and the books details also added to the bookInfo table. It's just a basic database structure. Would you be able to demonstrate the add method.

thank you

    username         bookInfo

    Username         Username      Book       Author

To do a query of the database, you'll need:

ResultSet rs = stmt.executeQuery("SELECT * FROM username WHERE Username='" + userToCheck + "'");

if (rs.next())  // The result set contains at least one matching entry, ie. user has an account.
{
    StringBuffer sqlInsert = new StringBuffer("INSERT INTO bookinfo Title, Auther VALUES('");
    sqlInsert.append(titleToInsert);
    sqlInsert.append("', '");
    sqlInsert.append(authorToInsert);
    sqlInsert.append("'");
    stmt.executeUpdate(sqlInsert.toString());
}
else  // No match found
{
    // Put the appropriate SQL insert statements here, 1 for adding to the username table and one for bookinfo
}

That shows the two main ways you send SQL to the server.  executeQuery() returns a ResultSet, executeUpdate() returns the number of records changed/inserted/deleted etc.  This value can be ignored if you want.

It's really up to you to create the appropriate SQL statements now ;-)

Don't forget to close() the statements and connections when you've finished with them.

Thank you for replying.

I found a reference to connecting to a MS database in a Java text. Though when I ran the program the following error was displayed.

Error while connecting:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specfied.

My database was overal saved as music.mdb

Connection conn;

void connect(){
try {
System.setProperty("jdbc.drivers","sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbc:odbc:music";
conn = DriverManager.getConnection(database);
}catch(Exception e) {
System.err.println("Error while connecting:");
System.err.println(e.getMessage());
System.exit(1);
}             //end exception
}

I think it's a problem with the URL you're supplying on the following line:

>> String database = "jdbc:odbc:music";

This should be more like:

String database = "jdbc:odbc://music"

I use MySQL, and my database URLs look like this:  jdbc:mysql://localhost/databasename

This might mean that you also need the "localhost" bit, but I'm not sure.

I haven't used Access, but I'll have a look round to see what else I can find ;-)

After further research ;-)

I think you need to set up a DSN to the database, then the database URL should be:

jdbc:odbc:dsnname

(I knew there was something extra required for Access, I just couldn't remember what it was ;-))

Thank you for replying.

I changed the code to include the dsnname, though the same error message is being displayed. I have copied the database to the same directory where the Java file is and the command Javac, Java. And also copied the music.mdb file to the c: directory just to make sure the file is being found. I should also note I am using MS Access 97.

Do you know of any other expert, who might know the answer.

Thank you

Connection conn;

void connect(){
try {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String database = "jdbc:odbc:dsnname";
Class.forName(driver);
conn = DriverManager.getConnection(database);
}catch(Exception e) {
System.err.println("Error while connecting:");
System.err.println(e.getMessage());
System.exit(1);
}

}

Hi again.  I'm not giving up with this one yet ;-)

The dsnname is something that you set up within windows that provides a named link to the Access database (as I recall, it's called ODBC settings or something).

When you create the DSN name for the database, this is what goes on the URL.  In this case, if your DSN name is "music", your database string should be:

String database = "jdbc:odbc:music";

What errors/exceptions are currently being reported?

Hello,

I changed the line to:

String database = "jdbc:odbc:music";

The error is the same:

Error while connecting:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specfied.

There may be two problems here.

The first one to sort out is ensuring the driver is available to the JVM.  Can you confirm that your classpath includes the jar file that contains the sun.jdbc.odbc.JdbcOdbcDriver class?  The "no default driver specified" seems to imply that the JdbcOdbcDriver is not being found.  Although from the research I've done, the DSN part seems to be the most frequent cause of this problem.

Just so I know where we are:

Have you used the ODBC management tool to set up the DSN name?  What did you need to enter?
(Start-->Programs-->Administrative Tools-->Data Source(ODBC))

Does the database require a username and password (I'm guessing that it doesn't)?

What jar file are you using for the JDBC/ODBC driver?

Thank you for replying.

I could not find no reference to sun.jdbc.odbc.JdbcOdbcDriver on the entire hard drive.

Can I download this, and where should it be placed.

Thanks

The code is as follows:

void connect(){
try {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String database = "jdbc:odbc:dsnname";
Class.forName(driver);
conn = DriverManager.getConnection(database);
}catch(Exception e) {
System.err.println("Error while connecting:");
System.err.println(e.getMessage());
System.exit(1);
}

}

Hmm.  It appears that sun.jdbc.odbc.JdbcOdbcDriver is actually in the standard Java installation, it's just not documented :-(

That brings us back to the ODBC setup.  Have a look at this, it may help :-)

http://www.mundayweb.co.uk/neil/tutorials/jdbc-odbc-tut.html

I would recommend modifying your code to catch the different exceptions (ClassNotFoundException and SQLException) - see the example code in the above link.  By catching the generic "Exception", it is less clear which line actually caused the problem.

Sorry for not replying eailer, I followed the web-site's instructions and now the connection error has now gone. Thanks:)

I now have an error with the code here: the error says

StringBuffer sqlInsert = new StringBuffer("INSERT INTO artist user,
                                                           (^) (unclosed string literal
     
                                            artist, title VALUES("'");
                                                                           (^) (expected ')'

Here is all the code, guidance would be much appreciated

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM tableUsername WHERE user='" + variUsername + "'");

if(rs.next()){
StringBuffer sqlInsert = new StringBuffer("INSERT INTO artist user,
                                                       artist, title VALUES("'");
sqlInsert.append(variUsername);
sqlInsert.append("', '");
sqlInsert.append(artist);
sqlInsert.append("', '");
sqlInsert.append(title);
sqlInsert.append("'");

stmt.executeUpdate(sqlInsert.toString());
}else{

}

You're missing the ")" at the end of the statement ;-)

The last sqlInsert.append line should read:

sqlInsert.append("')");

Thank you for replying.

The same two errors are being displayed pointing to the same positions.

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM tableUsername WHERE user='" + variUsername + "'");

if(rs.next()){
StringBuffer sqlInsert = new StringBuffer("INSERT INTO artist user,   artist, title VALUES('"));

sqlInsert.append(variUsername);
sqlInsert.append("', '");
sqlInsert.append(artist);
sqlInsert.append("', '");
sqlInsert.append(title);
sqlInsert.append("')");

stmt.executeUpdate(sqlInsert.toString());
}else{

}

I would have expected a different error here.

StringBuffer sqlInsert = new StringBuffer("INSERT INTO artist user,   artist, title VALUES('"));

should be:

StringBuffer sqlInsert = new StringBuffer("INSERT INTO artist user,   artist, title VALUES('");

Thank you for all your advice.

I posted second Java question early last week, this was regarding a hashmap. For some reason I never got a reply, or advice in the last week. And wondered if you could advise? I found this exercise in a java text, and it looked really interesting. The program addes objects, to a hashmap and then tries to add links between the objects. I have achieved this, though the program also includes an option to remove objects, and as I understand this would break the links. I wondered if you could look over my removeObject method, and offer any gudiance over the Iterator.

Many thanks
Antoni

    HashMap map = new HashMap();

    void addObject(Object x){
        boolean boolAns = containsObject(x);

        if(boolAns != true){
            graph.put(x, new HashSet());
        }
    }

    boolean removeObject(Object x){
        boolean boolAns = containsObject(x);

        if(boolAns == true){

            Set s = (Set)map.get(x);
            boolAns = map.containsKey(s);

            if(boolAns == true){
                Set keys = map.keySet();
                Iterator keyIter = keys.iterator();

                while(keyIter.hasNext()){
                        //Remove x keys
                }

                map.remove(x);
            }
            else{
                map.remove(x);
            }
        }

        return boolAns;
    }

    void addObjectEdge(Object x, Object y){
        boolean boolAns = map.containsKey(x);

        if(boolAns == false){
            addObject(x);
        }

        boolAns = map.containsKey(y);

        if(boolAns == false){
            addObject(y);
        }

       ((Set)map.get(x)).add(y);
       ((Set)map.get(y)).add(x);
    }

Do you have a link to your other question?

In the mean time, first, some general advice.

When testing a boolean, you don't really need to compare against true and false.  So if you really want to have a local variable storing a boolean value, instead of:

   if (boolAns == true)

just use:

  if (boolAns)

and instead of:

   if (boolAns == false)

use:

   if (!boolAns)

Having said that, you can probably reduce the code you've shown above because you don't really need to store the variable locally (in this case):

    public void addObject(Object x){
        if(containsObject(x)){
            graph.put(x, new HashSet());
        }
    }

    public boolean removeObject(Object x){
        boolean boolAns = containsObject(x);

        if(boolAns == true){

            Set s = (Set)map.get(x);
            boolAns = map.containsKey(s);

            if(boolAns == true){
                Set keys = map.keySet();
                Iterator keyIter = keys.iterator();

                while(keyIter.hasNext()){
                        //Remove x keys
                }

                map.remove(x);
            }
            else{
                map.remove(x);
            }
        }

        return boolAns;
    }

    public void addObjectEdge(Object x, Object y){
        if(!map.containsKey(x)){
            addObject(x);
        }

        if(!map.containsKey(y)){
            addObject(y);
        }

       ((Set)map.get(x)).add(y);
       ((Set)map.get(y)).add(x);
    }

As far as the content of the iterator loop goes, I'm not sure what "graph" is, but judging by the fact that you use .put() on it in the add method, I would suggest that you could replace the iterator with a call to .clear().  This assumes it's another HashMap (or similar).

Thank you for replying:

The link is:

https://www.experts-exchange.com/questions/20803677/Java-Hashmap.html#9785944

There is one hashmap called map, sorry I made a mistake when typing the code:) The object are added to the map, and then we can add lines between two objects. The addObjectEdges method, takes two objects (x) and (y) and adds the position of y to x and the position of x to y.

By using these two lines:
       ((Set)map.get(x)).add(y);
       ((Set)map.get(y)).add(x);

I think though when I remove an object (x) from the map, and if that object is part of a link, then all the keysets with a reference to x should be removed before the object x is removed. Just a thought.

I cannot workout how to remove the link references to x if they are needed?

Many thanks  

I wondered if you could offer any guidance in removing the correct setKeys.

Regards

Hello,

jimmack would have any time this weekend to offer any guidance?

Hi Antoni,

  Sorry about that, I must have deleted the last e-mail I had that referred to this question.  I'll take another look later tonight (probably in about 3 hours or so).

Hi Antoni, I'm back ;-)

I've just deleted a long list of questions that I was going to ask ;-)  I decided that it might be better to do this the other way round :-)

OK.  Here is what I think you have, followed by some questions.

You have a HashMap called "map".
You have the following methods that operate on the "map" object:
   addObject()
   removeObject()
   addObjectEdge()

The objects that are added and removed from "map" are also HashMaps (is this right?)

At this point, I start to get a bit lost.  I'm not clear on how you determine how edges are defined.

Does the code compile at the moment?

If it does:
   What does the containsObject() method do?

If it doesn't:
   In the addObject() method, you probably need to change "containsObject(x)"  to "map.containsKey(x)".


Tell me if the following description is right...

If we have some objects - o1, o2 and o3

map is initially empty (-> means "contains a reference to").

adding o1 gives us:

    map
      -> o1

adding o2 gives us:

    map
      -> o1
      -> o2

adding o3 gives us:

    map
      -> o1
      -> o2
      -> o3

Then... if we call addObjectEdge(o1, o2) we should get:

    map
      -> o1
            -> o3
      -> o2
      -> o3
            -> o1

So at this stage, if you call removeObject(o1), you are concerned that you might end up with:

    map
      -> o2
      -> o3
            -> o1

(ie. the o1 reference is "dangling").  What you actually want is:

    map
      -> o2
      -> o3

Hows that? ;-)

Thank you for replying.

There are two methods, containsObject and containsEdge which return a boolean answer. These appear to be working.

The program is quite difficult to explain, if I we had 4 objects, put them in the map and used a hashset to store the object's position.

The addObjectEdge adds to the hashset of each object, with the position of the link object.

So for object (1) the hashset would contain the position of object 1 and object 2 if we linked these and the hashset of object 2 would contain the position of object 2 and object 1. If I linked object 2 to three and three to four. The hashsets would look like, I think.

 (1)              (2)                 (3)               (4)
{1,2}         {2,1,3}           {3,2,4}           {4,3}

If I removed object 2, I think I would need to remove any reference of object 2 from all the hashsets, and after remove the value of object 2. I think the hashsets would then look:

 (1)            (3)               (4)
{1}          {3,,4}            {4,3}

The problem I am having is, working out how to loop through all the hashsets and remove the correct hashset values, in the above example I would remove the hashset links to object 2.

    boolean removeObject(Object x){
        boolean boolAns = containsObject(x);        //checks if object x exists

        if(boolAns == true){

            Set s = (Set)map.get(x);                      //this part should check if there is an edge
            boolAns = map.containsKey(s);             //exists, not sure if correct

            if(boolAns == true){
                Set keys = map.keySet();
                Iterator keyIter = keys.iterator();    

                while(keyIter.hasNext()){

                }                                                    //remove all links to object x from hashsets

                map.remove(x);                              //remove object x value
            }
            else{
                map.remove(x);
            }
        }

        return boolAns;
    }             //end removeObject

You're right.  It's not simple ;-)

I'm under the impression that:

        boolean boolAns = containsObject(x);        //checks if object x exists

should actually be:

        boolean boolAns = map.containsValue(x);        //checks if object x exists in map

Also, I'm not sure that the objects contain self-references, eg.

 (1)              (2)                 (3)               (4)
{1,2}         {2,1,3}           {3,2,4}           {4,3}


should read:

 (1)              (2)                 (3)               (4)
{2}             {1,3}              {2,4}             {3}

If this is the case, then when you have the object that you want to delete (let's call it currentObject), eg. (2), you could get the keys for this object, eg. {1, 3}.  Iterate through these keys, retrieving the objects and for each one and call removeObject(currentObject).  When that is complete, you can safely remove the currentObject from map.

Thank you for replying.

The containsObject uses the line  boolean boolAns = map.containsKey(x); it just checks if object x exists and return true or false.

In the addObject method, I was able to add the object to the map and store its positon in a hashset and in the addObjectEdge method get the hashset value of x and y and add y to x and x to y.

I also found this piece of code on the net which, I think gets the hashset value of x, but I am not sure how to seperate the hashset value held in s.

            Set s = (Set)map.get(x);
             

    void addObject(Object x){
        boolean boolAns = containsNode(x);

        if(boolAns != true){
            map.put(x, new HashSet());
        }
    }

    void addObjectEdge(Object x, Object y){
        boolean boolAns = map.containsKey(x);

        if(boolAns == false){
            addObject(x);
        }

        boolAns = map.containsKey(y);

        if(boolAns == false){
            addObject(y);
        }

       ((Set)map.get(x)).add(y);
       ((Set)map.get(y)).add(x);
    }

Hello,

I have the removeObject method now working, I was just missing the line keys.remove(x); in the while loop, this seems to work.

Many thanks for your advice.

No problem.  How do you want to accept the answers?

I would suggest accepting my answer to this question that I posted at 11/16/2003 05:55PM GMT (the hours and timezone may be different, depending on where you are based, but the date and the :55 should give it away ;-))

For the second part, if you would like to accept one of my comments in your other question (preferably the one I posted with the link back to this question), please make sure to post a brief comment on it saying that the question was answered here.

Thanx.

Jim.

Hello,

I wondered if I could ask a question about SQL/Java, or should I start a new question?

Probably best to start a new question.  You could continue here, but you'll have more expert comments on a fresh question ;-)

Thank you for replying.

I would like to write a removeRecords method, which removes all the records from a sigle user who's account includes the bookauthor given. I am know using the create table function.

Statement stmt = conn.createStatement();
String sql = "CREATE TABLE " + books + " (" +
"username VARCHAR(8), bookAuthor VARCHAR(50), bookTitle CHARVAR(50));";

Sorry Antoni, perhaps I didn't hint strongly enough.  This is really a new question (and you need to accept some answers to the one's you currently have open).

;-)

Jim.

Antoni, please accept some answers on your previous questions.

Questioners that have a high proportion of open questions tend to be ignored by experts.

In https://www.experts-exchange.com/questions/20792468/XSL-XML-display-problem.html, I think metalmickey has done more than enough to deserve an accepted answer

And in https://www.experts-exchange.com/questions/20803677/Java-Hashmap.html (and this one, I believe that I've done enough).

Thanx.

Jim.

Thanx.  ;-)