Solved

SQL/Java

Posted on 2003-12-03
17
250 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
  • 9
  • 7
17 Comments
 
LVL 86

Expert Comment

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

Use a PreparedStatement
0
 

Author Comment

by:AntoniRyszard656
Comment Utility
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
Comment Utility
It'd remove the whole row
0
 

Author Comment

by:AntoniRyszard656
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:AntoniRyszard656
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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 65 total points
Comment Utility
>>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
Comment Utility
8-)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now