?
Solved

SQL/Java

Posted on 2003-12-03
17
Medium Priority
?
297 Views
Last Modified: 2010-03-31
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
Comment
Question by:AntoniRyszard656
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 9865608
String delete = "DELETE FROM books WHERE username = ? AND bookAuthor = ?";

Use a PreparedStatement
0
 

Author Comment

by:AntoniRyszard656
ID: 9867481
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9867687
It'd remove the whole row
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:AntoniRyszard656
ID: 9869887
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870750
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
 
LVL 24

Expert Comment

by:sciuriware
ID: 9873432
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
 

Author Comment

by:AntoniRyszard656
ID: 9891868
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9892111
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
 

Author Comment

by:AntoniRyszard656
ID: 10011561
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10011601
You need to make sure the all resources are closed in your code. toString() is not necessary. Why are you creating tables btw?
0
 

Author Comment

by:AntoniRyszard656
ID: 10011705
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10011738
In your code example there is no code at all that deallocates resources. You create a Statement that never gets closed.
0
 

Author Comment

by:AntoniRyszard656
ID: 10012597
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10012637
>>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
 

Author Comment

by:AntoniRyszard656
ID: 10037067
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
 
LVL 86

Accepted Solution

by:
CEHJ earned 260 total points
ID: 10037147
>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 10076653
8-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question