SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation SQL exception with prepared statement - please help

    I am getting this error:

    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement.

    From this code:

    Code:
    stmtLogout = connection.prepareStatement(
    	"UPDATE Logs " +
    	"SET Logout_Time = NOW() " +
    	"WHERE Username = ? " +
    	"AND IP_Addr = ? " +
    	"ORDER BY Login_Time DESC " +
    	"LIMIT 1");
    stmtLogout.setString(1, username);
    stmtLogout.setString(2, ip_addr);
    stmtLogout.execute();
    Can someone please help? Thanks!

  2. #2
    SitePoint Wizard silver trophy rushiku's Avatar
    Join Date
    Dec 2003
    Location
    A van down by the river
    Posts
    2,056
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    It would appear that MS Access SQL requires a semicolon at the end of the statement - eg:
    executeQuery( "select foo from bar;" );

  3. #3
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would appear that way, but I had tried putting a semicolon in and it still said the same thing. After researching for a while I determined that Access does not like the ORDER BY and LIMIT clauses in this case. I've gotten ORDER BY to work in several other Access queries, though.

    Can someone please help me create an equivalent SQL statement to the one posted that will work in Access? Thanks!

  4. #4
    SitePoint Addict mmanders's Avatar
    Join Date
    Jul 2006
    Location
    Edinburgh, Scotland
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never used an ORDER BY clause in an UPDATE statement because as far as I know it can't be used - that said, I have never used Access as a backend database so maybe this is a Microsoft SQL quirk. Logically it doesn't make sense in an UPDATE because you specify the criteria of which rows to affect in the WHERE clause - I don't see what effect the ORDER BY would have.

    If you could elaborate on why you are using the ORDER BY clause and what you wish to achieve with it, perhaps I can suggest something to help?

  5. #5
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply. Perhaps this is actually just a feature of MySQL 5.0, because the first example of this page is where I got the idea to structure the query that way:
    http://dev.mysql.com/doc/refman/5.0/en/update.html

    I have a database that stores login and logout information for computer labs. A record is inserted into the database with a group policy login script, and then the record needs to be updated when the user logs out (another group policy logout script is run). The logout should only apply to the last login, which is why I need to limit my results. Would something like the following work?

    Code:
    stmtLogout = connection.prepareStatement(
    	"UPDATE Logs " +
    	"SET Logout_Time = NOW() " +
    	"WHERE ID = (" + 
    	"SELECT ID FROM Logs " + 
    	"WHERE Username = ? " +
    	"AND IP_Addr = ? " +
    	"ORDER BY Login_Time DESC, ID DESC " +
    	"LIMIT 1)");
    stmtLogout.setString(1, username);
    stmtLogout.setString(2, ip_addr);
    stmtLogout.execute();

  6. #6
    SitePoint Addict mmanders's Avatar
    Join Date
    Jul 2006
    Location
    Edinburgh, Scotland
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's more like what I was expecting to see; using a SELECT query within your WHERE clause. I would give that a try.


Bookmarks

Posting Permissions

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