JDBC statement question

[nick2price]

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

[masijade]

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

VALUES (?)

to

?,

And, just for the sake of complete correctness, remove the extra space after

'Wembley'

[nick2price]

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

[masijade]

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.

[serjant]

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 ....

[nick2price]

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?

[serjant]

Have you read my previous post?You have the problem with sql statement

[nick2price]

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.

[masijade]

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.

[nick2price]

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

[masijade]

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.

[nick2price]

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

[masijade]

The first one is a number. Use setDouble on the first parameter rather than setString.

[nick2price]

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 ?

[masijade]

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).

[nick2price]

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 ?

[masijade]

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()));

[masijade]

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.

[nick2price]

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?

[masijade]

What is eveType2? Is it a String? Something else?
If it is a String, than the simple setString method will work.