Page 1 of 2 12 LastLast
Results 1 to 20 of 22
  1. #1
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default 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
    Java Code:
    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
    Java Code:
        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

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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
    Java Code:
    VALUES (?)
    to
    Java Code:
    ?,
    And, just for the sake of complete correctness, remove the extra space after
    Java Code:
    'Wembley'

  3. #3
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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
    Java Code:
    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

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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.

  5. #5
    serjant's Avatar
    serjant is offline Senior Member
    Join Date
    Jun 2008
    Location
    Ukraine,Zaporozhye
    Posts
    487
    Rep Power
    6

    Default

    The SQL command for insert has the following syntax:

    Java Code:
    INSERT INTO table(column) VALUES (<value according to the type of the column>)
    if you want to insert something from the another table:
    Java Code:
    INSERT INTO table(column) VALUES AS SELECT <what you need> FROM another_table WHERE ....
    Last edited by serjant; 09-27-2008 at 11:28 AM.

  6. #6
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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
    Java Code:
    	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?

  7. #7
    serjant's Avatar
    serjant is offline Senior Member
    Join Date
    Jun 2008
    Location
    Ukraine,Zaporozhye
    Posts
    487
    Rep Power
    6

    Default

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

  8. #8
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    Java Code:
    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()
    Java Code:
    ps.setString(1, update2[i].getTime());
    The last name is in my objects getLastname() and my evetype is held in the variable eveType2.

  9. #9
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Well, for one, you're going to want to change this
    Java Code:
    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
    Java Code:
    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
    Java Code:
    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.

  10. #10
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    Sorry, forgot to mention that i had changed it before to
    Java Code:
    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

  11. #11
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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.

  12. #12
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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
    Java Code:
    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

  13. #13
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

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

  14. #14
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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 ?

  15. #15
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

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

  16. #16
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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 ?

  17. #17
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    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?

    Java Code:
    ps.setDouble(1, Double.parseDouble(update2[i].getTime()));

  18. #18
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Quote Originally Posted by nick2price View Post
    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.

  19. #19
    nick2price is offline Member
    Join Date
    May 2008
    Posts
    27
    Rep Power
    0

    Default

    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
    Java Code:
    "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
    Java Code:
    "WHERE Event_Name='"eveType2"' ";
    But its asking me for loads of semicolumns on this line. Whats the correct syntax for this?

  20. #20
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

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

Page 1 of 2 12 LastLast

Similar Threads

  1. Question mark colon operator question
    By orchid in forum Advanced Java
    Replies: 9
    Last Post: 12-19-2010, 08:49 AM
  2. Replies: 0
    Last Post: 04-01-2008, 10:17 AM
  3. Replies: 0
    Last Post: 09-28-2007, 12:56 PM
  4. Statement or Prepared Statement ?
    By paty in forum JDBC
    Replies: 3
    Last Post: 08-01-2007, 04:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •