Link to home
Start Free TrialLog in
Avatar of AntoniRyszard656
AntoniRyszard656

asked on

SQL/Java

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));";
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

String delete = "DELETE FROM books WHERE username = ? AND bookAuthor = ?";

Use a PreparedStatement
Avatar of AntoniRyszard656
AntoniRyszard656

ASKER

Thank you for replying.

The table book has three fields username, bookauthor and booktitle

If I use the delete line as your explained would this also remove the booktitle value in a record if the username and bookauthor were found?




 
It'd remove the whole row
Thank you for replying.

I would like to ask, in one of the databases methods I take a single string containing several words, then take the first 2 characters of each word and store these in a Stringbuffer.

The reason being I can not be sure how many words the string will contain. And there I think I could not store the 2 characters directly into  a string[]

I would now like to store the stringbuffer values and store these in a string[]. I have been trying this, though I could not workout how to loop through a stringbuffer. Any advice would be appreciated.

If you just want the first two characters of each word in an array, you could do this sort of thing:

String source = "Alpha Beta Gamma Delta Epsilon";
String[] firstTwos = source.split(" ");
for(int i = 0;i < firstTwos.length;i++) {
      if (firstTwos[i].length() > 2) {
            firstTwos[i] = firstTwos[i].substring(0, 3);
      }
 
}
SQL: don't create the table every time;
use "SHOW TABLES;" to see if it is already there.
Every unnecessary error message will obscure serious messages.
;JOOP!
Hello,

I saw in a Java text an Queue data structure, which adds and removed objects.

The text declared the Queue as   Queue q = new Queue();

Though I cannot find the Queue class in the jdk1.3

Could anyone suggest the import statement?

Thank you
It was probably a custom class. They're very easy to make, using a List of some sort:

public class Queue extends LinkedList {

//FIFO queue

      public Object dequeue() {
            if (size() < 1)
                  throw new ArrayIndexOutOfBoundsException("Queue empty!");
            return removeFirst();
      }

      public void enqueue(Object o) {
            add(o);
      }
      
}
Thank you for replying.

I have written an add method already which adds to a access database, and this method works. Eventually I intend to use the mysql software rather than MS Access. For some reason the remove method here is not working, I have no errors but the rows are not being deleted. Just wondered if anyone could see the error.

Regards


Connection conn;
static String Book = "booktable";

void remove(String userName, String bookAuthor, String titleName){
try{
Statement stmt = conn.createStatement();
String sql = "CREATE TABLE " + Book + " (" + "user VARCHAR(10), author VARCHAR(50), booktitle CHARVAR(50));";

String delete = "DELETE FROM Book WHERE user = userName AND author = bookAuthor AND booktitle = titleName";
stmt.executeUpdate(delete.toString());

}catch(Exception e){

}
}
You need to make sure the all resources are closed in your code. toString() is not necessary. Why are you creating tables btw?
Thank you for replying.

I have checked the code many times, and cannot find any errors and the method does not give any errors when running.

I am planned to link the Java file to mysql, and in the example I have they created a temp table in each of the methods and used gobel string Book. I used the create table function in the add method and this works.

Could you offer any further guidance with the remove.

Regards
In your code example there is no code at all that deallocates resources. You create a Statement that never gets closed.
Thank you for replying.

I have written a disconnect function to be used in the main method, after this method is complete.

I decided to change the exception to type sql, the error now shown is, [Microsoft][ODBC Microsoft Access Driver] Too few prameters Expected 3.

I donot fully understand the Create table function, in the add method I used it to store the values before using the method stmt.executeUpdate(sqlInsert.toString());

Would it be possible to copy all the Access database values into the create table (string table) and delete the values then use the stmt.executeUpdate() function to update the Access database? Could you possible demonstrate this?

Many regards
>>Too few prameters Expected 3.

Your function needs to pass the parameters to the statement. At the moment it is not doing so.

>>Would it be possible to copy ...

Please say in detail what your ultimate objectives are. I don't know what's going on with this temporary table stuff.
Thank you for replying.

My apologies for not replyng eariler, I decided to remove the create table line, and I also checked the three values were being passed correctly to the method, this workes fine. I have a access database with a table called Book, this table includes the fields user, author and booktitle. I wrote an add method which work's perfectly and adds to the table Book.

Though with this method I am still getting the error [Microsoft][ODBC Microsoft Access Driver] Too few prameters Expected 3.

void remove(String user1, String author1, String booktitle1){
try{
System.out.println(user1 + " " +author1 + " " + booktitle1);

Statement stmt = conn.createStatement();
String delete = "DELETE FROM Book WHERE user = user1 AND author = author1 AND booktitle = booktitle1";
stmt.executeUpdate(delete);

}catch(SQLException e){
System.err.println(e.getMessage());
}

disconnect();

}             //end remove
ASKER CERTIFIED SOLUTION
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
8-)