[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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));";
0
AntoniRyszard656
Asked:
AntoniRyszard656
  • 9
  • 7
1 Solution
 
CEHJCommented:
String delete = "DELETE FROM books WHERE username = ? AND bookAuthor = ?";

Use a PreparedStatement
0
 
AntoniRyszard656Author Commented:
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?




 
0
 
CEHJCommented:
It'd remove the whole row
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AntoniRyszard656Author Commented:
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.

0
 
CEHJCommented:
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);
      }
 
}
0
 
sciuriwareCommented:
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!
0
 
AntoniRyszard656Author Commented:
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
0
 
CEHJCommented:
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);
      }
      
}
0
 
AntoniRyszard656Author Commented:
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){

}
}
0
 
CEHJCommented:
You need to make sure the all resources are closed in your code. toString() is not necessary. Why are you creating tables btw?
0
 
AntoniRyszard656Author Commented:
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
0
 
CEHJCommented:
In your code example there is no code at all that deallocates resources. You create a Statement that never gets closed.
0
 
AntoniRyszard656Author Commented:
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
0
 
CEHJCommented:
>>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.
0
 
AntoniRyszard656Author Commented:
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
0
 
CEHJCommented:
>>Though with this method I am still getting the error ...

I mentioned the reason for that already:


>>Your function needs to pass the parameters to the statement. At the moment it is not doing so. Rather than concatenate Strings, you can use a PreparedStatement

final String DELETE_STATEMENT = "DELETE FROM Book WHERE user = ? AND author = ? AND booktitle = ?";
PreparedStatement ps = conn.prepareStatement(DELETE_STATEMENT);
ps.setString(1, user1);
ps.setString(2, author1);
ps.setString(3, booktitle1);
ps.executeUpdate();


0
 
CEHJCommented:
8-)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now