|
|
Welcome to the Java Forums.
You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community, you will:
- have access to post topics
- communicate privately with other members (PM)
- not see advertisements between posts
- have the possibility to earn one of our surprises if you are an active member
- access many other special features that will be introduced later.
Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact us.
|
|

09-26-2008, 05:25 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
|
JDBC statement question
I have a table called tblMeet. It has a primary key called ID which is an autoNumber and a meetName field set to 'Wembley'. Then i have another table called tblEvent. It also has an pimary key ID field set as an autoNumber, it has a eventName field, as it has a field called Meet_ID which is the foriegn key to the ID field in my tblMeet. Now in my java program, i want to try and set this foriegn key. I have got a working query in my database, like so
CODEINSERT INTO tblEvent ( Event_Name, Meet_ID )
SELECT '100M Run', [ID]
FROM tblMeet
WHERE Meet_Name='Wembley';
But now i need to do this in my java program.
I am attempting somthing like this but it is totally wrong
try
{
con = DatabaseUtils.connect(DRIVER, URL);
String sql = "INSERT INTO tblEvent (Event_Name, Meet_ID) VALUES (?) " +
"SELECT eveType2, " +
"ID FROM tblMeet " +
"WHERE Meet_Name='Wembley' ";
ps = con.prepareStatement(sql);
ps.setString(1, eveType2);
ps.executeUpdate();
}
Any advice appreciated
|
|

09-26-2008, 07:45 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
You've designated two columns to insert and provide only one value,
Then perform another query after it, without a semicolon.
If you were to look critically at your queries you should be able to see the difference yourself.
Change
to
And, just for the sake of complete correctness, remove the extra space after
|
|

09-26-2008, 07:55 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
Sorry, just getting a bit confused. I need to insert two values into the table. 1 value is a passed String (eveType2), the other is gotten from another table (Meet_ID). This is where i am at
public void eventData(String eveType)
{
String eveType2 = eveType;
PreparedStatement ps = null;
try
{
con = DatabaseUtils.connect(DRIVER, URL);
String sql = "INSERT INTO tblEvent (Event_Name, Meet_ID) ?, " +
"SELECT ID FROM tblMeet " +
"WHERE Meet_Name='Wembley'";
ps = con.prepareStatement(sql);
ps.setString(1, eveType2);
ps.executeUpdate();
}
catch(Exception e)
{
System.out.println(e);
DatabaseUtils.rollback(con);
e.printStackTrace();
}
finally
{
DatabaseUtils.close(ps);
DatabaseUtils.close(con);
}
}
Now i am only kinda setting one value arnt i? And i shouldnt really use a preparedstatement for this?
Any advice would be so good, been stuck on this for days going over the same things.
cheers
|
|

09-27-2008, 09:06 AM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
|
Sorry, the "?," should come after the select keyword.
Come on, I mean really, you showed a supposedly "working" SQL statement in your first post, but are unable to "recreate" that SQL statement as a String literal in a Java Program.
I don't mean to sound mean here, but this ain't rocket science.
|
|

09-27-2008, 01:26 PM
|
 |
Senior Member
|
|
Join Date: Jun 2008
Location: Ukraine,Zaporozhye
Posts: 356
|
|
The SQL command for insert has the following syntax:
INSERT INTO table(column) VALUES (<value according to the type of the column>)
if you want to insert something from the another table:
INSERT INTO table(column) VALUES AS SELECT <what you need> FROM another_table WHERE ....
Last edited by serjant : 09-27-2008 at 01:28 PM.
|
|

09-29-2008, 01:02 AM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
I have changed it slightly now. For this table, multiple rows are going to be updated and the values are got from an object i have created. This is what i have now
try
{
con = DatabaseUtils.connect(DRIVER, URL);
String sql = "INSERT INTO tblResult (Competitor_ID, Event_ID, results) " +
"SELECT c.ID, e.ID, ? " +
"FROM tblCompetitor as c, tblEvent as e " +
"WHERE c.Last_Name = ? and e.Event_Name = ?";
ps = con.prepareStatement(sql);
for(int i = 0; i < update2.length; i++)
{
ps.setString(1, update2[i].getTime());
ps.setString(2, update2[i].getLastname());
ps.setString(3, eveType2);
}
ps.executeUpdate();
}
The problem i am having is that no data is getting inserted into this table. I am not sure if i have to setString in order of the column names or in order of my ? Can anyone see any mistakes?
|
|

09-29-2008, 04:35 AM
|
 |
Senior Member
|
|
Join Date: Jun 2008
Location: Ukraine,Zaporozhye
Posts: 356
|
|
|
Have you read my previous post?You have the problem with sql statement
|
|

09-29-2008, 12:33 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
INSERT INTO table(column) VALUES AS SELECT <what you need> FROM another_table WHERE ....
I am using the above, isnt my sql right? The ? symbolise the values i dont know and have to get, which is what i am trying to do. This will update multiple rows, my result is held in my object getTime()
ps.setString(1, update2[i].getTime());
The last name is in my objects getLastname() and my evetype is held in the variable eveType2.
|
|

09-29-2008, 12:48 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
Well, for one, you're going to want to change this
ps = con.prepareStatement(sql);
for(int i = 0; i < update2.length; i++) {
ps.setString(1, update2[i].getTime());
ps.setString(2, update2[i].getLastname());
ps.setString(3, eveType2);
}
ps.executeUpdate();
to either this
ps = con.prepareStatement(sql);
for(int i = 0; i < update2.length; i++) {
ps.setString(1, update2[i].getTime());
ps.setString(2, update2[i].getLastname());
ps.setString(3, eveType2);
ps.addBatch()
}
ps.executeBatch();
or this
ps = con.prepareStatement(sql);
for(int i = 0; i < update2.length; i++) {
ps.setString(1, update2[i].getTime());
ps.setString(2, update2[i].getLastname());
ps.setString(3, eveType2);
ps.executeUpdate();
}
Or you will only be executing the last item in the array, rather than all of them. The first variant will be a bit quicker.
|
|

09-29-2008, 12:55 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
Sorry, forgot to mention that i had changed it before to
try
{
con = DatabaseUtils.connect(DRIVER, URL);
String sql = "INSERT INTO tblResult (Competitor_ID, Event_ID, results) " +
"SELECT c.ID, e.ID, ? " +
"FROM tblCompetitor AS c, tblEvent AS e " +
"WHERE c.Last_Name = ? and e.Event_Name = ?";
ps = con.prepareStatement(sql);
for(int i = 0; i < update2.length; i++)
{
ps.setString(1, update2[i].getTime());
ps.setString(2, update2[i].getLastname());
ps.setString(3, eveType2);
ps.addBatch();
}
int[] upCounts1 = ps.executeBatch();
con.commit();
}
All of my tables are being updated apart from this one. Is my SQL right? Do i have everything in the right order? I have been checking it for so long now and i cant see anything wrong.
cheers
|
|

09-29-2008, 01:00 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
|
Well, try it out manually.
Edit: And once it works, manually, post that SQL statement exactly as you used it.
And, I have also seen your threads at Sun, you could have at least mentioned that you were also getting help there, and update this thread as you got information from there.
|
|

09-29-2008, 01:08 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
DuffoMo on the sun forum was really helpfull. The problem is the code he showed me inserted data through the database and not through java. Also, he used things like WHERE c.name = 'foo'. I am not going to know the names which is why in my code i have to use the ? and i am trying to loop my object where the names are held.
I have used this sql in my database
INSERT INTO tblResults ( Competitor_ID, Event_ID, result )
SELECT c.ID, e.ID, 10.5
FROM tblCompetitor AS c, tblEvent AS e
WHERE c.First_Name='foo' And e.Event_Name='200M Run';
And it inserts the correct row into the table. So i presume that means there is no problem with my relationships. But as i say, in my java i have to use ? for the variables i dont know.
Any help would be greatly appreciated and from now on i will be sure to post on just one site.
cheers
|
|

09-29-2008, 01:23 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
|
The first one is a number. Use setDouble on the first parameter rather than setString.
|
|

09-29-2008, 01:32 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
|
I actually have it set to text for convinience. What would be good to know is the order i use setString(). Should i do it in order of my columns (Competitor_ID, Event_ID, results) or in order of my ?
|
|

09-29-2008, 01:43 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
|
The first ? (as you read the statement) is 1, the second ? is two.
But, if the statement that you posted above works, then use setDouble (at least give it a try).
|
|

09-29-2008, 01:55 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
|
if i use setDouble i get back the error that it cannot be applied to String. I was thinking that my error might be to not having enough quotations around my variables. I have another statement that where i use WHERE on a variable, i do it like ' " + eveType2 + " ', but i cannot get it to work for this statement with the ?
|
|

09-29-2008, 02:18 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
Right, because it can't be applied to String, it needs to be a Double or a double. Now, knowing that (which the name of the method should have been enough to let you at least guess that) why didn't you read the API docs for Double and check to see if there was a way to create a (D/d)ouble out of a String?
ps.setDouble(1, Double.parseDouble(update2[i].getTime()));
|
|

09-29-2008, 02:19 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
Originally Posted by nick2price
I have another statement that where i use WHERE on a variable, i do it like ' " + eveType2 + " '
Then you should change that one to a PreparedStatement, too.
|
|

09-29-2008, 03:02 PM
|
|
Member
|
|
Join Date: May 2008
Posts: 21
|
|
Just one last question on this topic then i think i should be able to work it out. On my WHERE statement, i do somthing like
"WHERE Event_Name='100M Run' ";
Where 100M Run is the String to search for. But what do i do if the Event_Name is held in an object called eveType2? I have tried
"WHERE Event_Name='"eveType2"' ";
But its asking me for loads of semicolumns on this line. Whats the correct syntax for this?
|
|

09-29-2008, 03:09 PM
|
|
Senior Member
|
|
Join Date: Jun 2008
Posts: 551
|
|
|
What is eveType2? Is it a String? Something else?
If it is a String, than the simple setString method will work.
|
|
| Thread Tools |
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|