where ID = ?... how can i get the ID?!
i have this method used to update my contacts:publicvoid updateContact(ContactPanel updatePanel){
try{
preparedStatement = connection.prepareStatement("update " + updatePanel.getName().toUpperCase().charAt(0) +" set NAME = ?, NUMBER = ?, EMAIL = ? where ID = ?");
preparedStatement.setString(1, updatePanel.getName());
preparedStatement.setString(2, updatePanel.getNumber());
preparedStatement.setString(3, updatePanel.getEMail());
//preparedStatement.setInt(4, updatePanel.getID());
preparedStatement.executeUpdate();
}catch(SQLException ex){
ex.printStackTrace();
}
}
my problem is that i have no clue how to get the ID of each contact, because the ID column was created as an identity column
this is how i create each table: create table A
(
"ID" INTEGER generated always as identity,
"NAME" VARCHAR(50),
"NUMBER" VARCHAR(50),
"EMAIL" VARCHAR(50)
)
[1445 byte] By [
Alex1989a] at [2007-11-27 6:50:22]
# 1
Are you generating it or is it being generated for you somewhere?
You are just going to have to query the table for a person with certain conditions.
For example
SELECT ID FROM A WHERE NAME='junior' AND NUMBER='00000';
and hope that you get one.
Another thing you might want to do is create your own unique identifier to make sure you do get one hit to find that person. Something like an SSN would work jsut fine.
Make sure you make any identity column as NOT NULL and UNIQUE too
# 2
> Are you generating it or is it being generated for
> you somewhere?
its being generated automatically each time a contact is added to the database "ID" INTEGER generated always as identity
> You are just going to have to query the table for a
> person with certain conditions.
>
> For example
>
> SELECT ID FROM A WHERE NAME='junior' AND
> NUMBER='00000';
>
> and hope that you get one.
im a little confused here. how can i "hope" to get one? surely if the contact has a set ID then there shouldnt be any hoping?
> Another thing you might want to do is create your own
> unique identifier to make sure you do get one hit to
> find that person. Something like an SSN would work
> jsut fine.
what is an SSN?
> Make sure you make any identity column as NOT NULL
> and UNIQUE too
so theres no method that allows u to get the value of an identity?
# 3
> i have this method used to update my contacts:
You don't update the ID field.
At some point someone is telling you what to update - they had to pick something. At that point you should be retaining the ID that they picked. You use that in a where clause to specify what row to update. You can update everything else.
# 4
> You don't update the ID field.
yea but i need some way to get it.
> At some point someone is telling you what to update -
> they had to pick something. At that point you should
> be retaining the ID that they picked. You use that in
> a where clause to specify what row to update. You
> can update everything else.
im a bit lost here haha! my brain aint meant for programming :P
# 5
> yea but i need some way to get it.Why haven't you already got it?You've populated a list of "contacts" and the user is asking you to update one of them. Why didn't you retrieve the IDs when you retrieved the list of contacts so that you could use it later?
# 6
i tried getting the ID like this: preparedStatement = connection.prepareStatement("select ID from " + updatePanel.getName().toUpperCase().charAt(0) + " where NAME = ?, NUMBER = ?, EMAIL = ?");
preparedStatement.setString(1, updatePanel.getName());
preparedStatement.setString(2, updatePanel.getNumber());
preparedStatement.setString(3, updatePanel.getEMail());
preparedStatement.executeUpdate();
but i get this exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 32.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
at JavaPhonebook.ContactIO.updateContact(ContactIO.java:103)
at JavaPhonebook.ContactPanel.actionPerformed(ContactPanel.java:281)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:177)
at java.awt.Dialog$1.run(Dialog.java:1039)
at java.awt.Dialog$3.run(Dialog.java:1091)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Dialog.show(Dialog.java:1089)
at java.awt.Component.show(Component.java:1419)
at java.awt.Component.setVisible(Component.java:1372)
at java.awt.Window.setVisible(Window.java:801)
at java.awt.Dialog.setVisible(Dialog.java:979)
at JavaPhonebook.ContactPanel.buildEditDialog(ContactPanel.java:229)
at JavaPhonebook.ContactPanel.actionPerformed(ContactPanel.java:273)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "," at line 1, column 32.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
... 60 more
# 7
Ok. The error is because that's not valid SQL.
The deeper question is why you think you need to write a new query to get the information. You have ALREADY got a list of contacts. Why not modify that query to get the ID as well as the contact details.
If you hang on to that ID you can then use it for doing the update later on.
# 8
i mdified my loadContacts method to also get each contacts ID:public void loadContacts(JPanel[] loadPanelArray){
for(i = 65; i <= 90; i++){
try{
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
resultSet = statement.executeQuery("select ID, NAME, NUMBER, EMAIL from " + String.valueOf((char)(i)) + " order by NAME");
while(resultSet.next()){
loadID = resultSet.getInt("ID");
loadName = resultSet.getString("NAME");
loadNumber = resultSet.getString("NUMBER");
loadEMail = resultSet.getString("EMAIL");
loadPanelArray[i - 65].add(new ContactPanel(loadName, loadNumber, loadEMail));
}
}catch(SQLException ex){
ex.printStackTrace();
}
}
}
is that what u were talking about?
# 9
> is that what u were talking about?
Yes. Good.
Right, so now you have acquired the ID into your variable "loadID" but the problem now is that you don't use it anywhere.
Your contact panel knows about the name, number, and email. If you told it about the ID as well, you'd have access to the ID associated with the other three fields anywhere that you were accessing them in the ContactPanel wouldn't you?
# 10
i made another constructor, so now i have public ContactPanel(String contactName, String contactNumber, String contactEMail)
and public ContactPanel(int contactID, String contactName, String contactNumber, String contactEMail)
i also made a getID() method. but my updateContact method still doesnt seem to work: public void updateContact(ContactPanel updatePanel){
try{
preparedStatement = connection.prepareStatement("update " + updatePanel.getName().toUpperCase().charAt(0) + " set NAME = ?, NUMBER = ?, EMAIL = ? where ID = ?");
preparedStatement.setString(1, updatePanel.getName());
preparedStatement.setString(2, updatePanel.getNumber());
preparedStatement.setString(3, updatePanel.getEMail());
preparedStatement.setInt(4, updatePanel.getID());
preparedStatement.executeUpdate();
}catch(SQLException ex){
ex.printStackTrace();
}
}
i get not errors, the contact just doesnt get updated :(
# 11
Then probably you've supplied the wrong ID (there are a few other possibilities but that's the most likely). Do some debugging. Things to consider:
What ID are you trying to update? How do you know?
Where is the ID being stored? How do you know?
What is the ID when you obtain it? How do you know?
This process is debugging. Most programming is debugging. You have the code, you can put in code to print out the values of variables at various points in your program. You have information to hand. I don't. Time for you to do some work, right?
First step in debugging: find out where it starts to go wrong.
# 12
> but my updateContact method still doesnt seem to> work: Are you using MS Access as the database?
# 13
> What ID are you trying to update? How do you know?
> Where is the ID being stored? How do you know?
> What is the ID when you obtain it? How do you know?
i did a little debugging and it seems that the IDs that i get are correct System.out.println(updatePanel.getID());
so now i have no clue what is wrong haha!
> Are you using MS Access as the database?
nope, using derby
Message was edited by:
Alex1989
# 14
I ask six questions. You provided one answer.
# 15
> I ask six questions. You provided one answer.i answered all the questions
# 16
> I answered all the questions
Actually you didn't really answer any of them. What you said was:
>> i did a little debugging and it seems
>> that the IDs that i get are correct
Did I ask you if the ID you were getting was correct?
1. What ID are you trying to update? Not answered
2. How do you know? Not answered
3. Where is the ID being stored? Not answered
4. How do you know? Not answered
5. What is the ID when you obtain it? Not answered
6. How do you know? Not answered
>> System.out.println(updatePanel.getID());
The code you cite could have been placed anywhere in your code - it doesn't answer any of the above questions. It suggests that the answer to question 3 might be in the update panel object, but it certainly doesn't prove it.
The reason I asked you those six questions was that it's very likely that you're trying to do an update using a null or otherwise incorrect value. If you can answer those questions with definite actual answers it will be because you have debugged that particular path to failure. There are others, but they are less likely and I'm not that interested in helping if you're not going to help yourself.
# 17
I kind of agree with dcminter and sort of hesitate to reply.... but I will anyway...
I think whatever you're doing is probably wrong. If I interpret your code correctly then you have 26 tables, named "A" through "Z". Table "A" stores all contacts whose names start with "A" and so on. Is that really what you're doing? Why on god's green earth would you do that? That's going to lead to so many bugs it's not even funny (well, not funny for you anyway).
What happens in your thing if "John Smith" updates his name to "Mr. John Smith". Now he's no longer in table J, now he's in table M. Can rows in different tables have the same ID, because if they can then you're really screwed. What if John Smith has ID=10 in table J, and Mary Mack has ID=10 in table M. Then when John Smith changes his name to Mr. John Smith it will overwrite Mary Mack in table M, and leave the old copy of John Smith in table J. That might be why you don't see any update, are you sure you're looking in the right table? Put them all in one table, it will save you a lot of grief.
Also, you might just need to commit your update. Either call connection.commit(), or call connection.setAutocommit(true) after you open the connection.
Also, you're not closing your PreparedStatement when you're done with it, which is bad. After your "catch", add a finally block and close the PreparedStatement. Also, the PreparedStatement should be declared in that method, not a class member as it appears to be now.
Next, like dcminter, add some System.out.println to your code. Print out the SQL query you're about to run. Print out the parameters. Then run your program. If it didn't work then open up your database tool, copy and paste the query from the println, fill in the parameters from the println, and see what happens when you run it. Or, you can learn to use a debugger, which will save you countless hours in your programming lifetime.
Jemiah
# 18
i have 26 tables because of my loadContacts method: public void loadContacts(JPanel[] loadPanelArray){
for(i = 65; i <= 90; i++){
try{
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
resultSet = statement.executeQuery("select ID, NAME, NUMBER, EMAIL from " + String.valueOf((char)(i)) + " order by NAME");
while(resultSet.next()){
loadID = resultSet.getInt("ID");
loadName = resultSet.getString("NAME");
loadNumber = resultSet.getString("NUMBER");
loadEMail = resultSet.getString("EMAIL");
loadPanelArray[i - 65].add(new ContactPanel(loadID, loadName, loadNumber, loadEMail));
}
}catch(SQLException ex){
ex.printStackTrace();
}
}
}
maybe then u could help me figure out a way to load the contacts into their proper tabs with them being saved into 1 table only
# 19
> i have 26 tables because of my loadContacts methodNo, you have that backwards. Surely you didn't write that code and then say "Oh, that means I have to have 26 tables."
# 20
> No, you have that backwards. Surely you didn't write> that code and then say "Oh, that means I have to have> 26 tables."does it matter? obviously i wrote the code after i made the tables
# 21
> > No, you have that backwards. Surely you didn't write
> > that code and then say "Oh, that means I have to have
> > 26 tables."
>
> does it matter?
Definitely. If someone else created that schema and you are stuck with it now then that is the way it is.
On the other hand if you are creating that schema right now, then the first question that comes to my mind is why?
# 22
> Definitely. If someone else created that schema and
> you are stuck with it now then that is the way it
> is.
>
> On the other hand if you are creating that schema
> right now, then the first question that comes to my
> mind is why?
whats a schema?
# 23
> whats a schema?Roughly, the design of your database tables. Basically having one table for every letter of the alphabet is very poor. There's no good reason to design a DB like that, so you'd do well to reconsider this approach.
# 24
so i should use only 1 table? how will i load the contacts into their proper places?
# 25
Probably. Just have a where clause that selects the appropriate subset of elements. If as appears to be the case here you're trying to get all the elements where the name starts with a particular letter, you could use the like operator:
select * from foo where name like 'A%';
Extend as appropriate if you need to make it caseless, or to match on some other criterion. Having a table for every condition you want to meet is rather missing the point of having a database in the first place.
PS Your question suggests that you may be trying to load ALL elements, then assign them to their appropriate entries in the GUI - in which case you probably shouldn't be doing the manipulation at the database level at all - process the rows as you add them to determine which view component they'll be added to.
# 26
In fact, looking at your code again, that's exactly what you're trying to do... that's nuts!
Pseudo code:
select all records from DB
for each record
determine first letter
construct wrapper
determine panel model corresponding to first letter
add wrapper to model
# 27
so what do u guys rate i should do?
