Results 1 to 4 of 4
  1. #1
    Dcalladi is offline Member
    Join Date
    Oct 2011
    Posts
    27
    Rep Power
    0

    Default SQL Prepared Statement with date

    Hi everyone
    im trying to use a bit of SQL to update some field by using the date in the where clause.

    i current have as my code :

    Java Code:
            public transactionUploader(Double profit, Double change) {
            java.util.Date utilDate = new Date();
            java.sql.Date date = new java.sql.Date(utilDate.getTime());
            try {
                DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                Connection conn = DriverManager.getConnection("jdbc:mysql://samp.inf.brad.ac.uk/dcalladi", "dcalladi", "Dannys21");
                pstmt = conn.prepareStatement("UPDATE Cash_Flows SET Profit = Profit + ?, "
                        + "Waste = + Waste ?, Change = Change + ?, Transactions = Transactions + 1 WHERE Date = ?");
                pstmt.setDouble(1, 56.09);
                pstmt.setDouble(2, 22.70);
                pstmt.setDouble(3, 1.00);
                pstmt.setDate(4, date);
                pstmt.executeUpdate();
                pstmt.close();
                conn.close();
            } catch (Exception ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        }
    this should to me work as it has the date in the right format adding all the data in the right format but i i getting an error

    SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ Waste = + Waste 22.7, Change = Change + 1.0, Transactions = Transactions + 1 W' at line 1

    seems to be curtting the statement short as last time is also included the "WHERE" part of the statement

    Thanks in advance

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    6

    Default Re: SQL Prepared Statement with date

    Are you sure the syntax of your SQL is valid? "Waste = + Waste ?," seems a bit odd.

  3. #3
    javin.paul is offline Member
    Join Date
    Oct 2011
    Posts
    35
    Rep Power
    0

    Default Re: SQL Prepared Statement with date

    try running that SQL generated by code in mysql directly , it will help you to find and resolve errors related to sql syntax quickly. once you have working sql than code that in java.

  4. #4
    Dcalladi is offline Member
    Join Date
    Oct 2011
    Posts
    27
    Rep Power
    0

    Default Re: SQL Prepared Statement with date

    i manage to get it working i seperate each colunm into its own update command and found out which one wasn't working
    which i then found out that it was Change so for future reference to anyone Change is a keywork in SQL so you can not use it in a SQL command
    so i solved my own problem by swapping Change to ChangeGiven

    Thanks Guys

Similar Threads

  1. Jdbc Prepared Statement execute()
    By nitishjtm in forum JDBC
    Replies: 37
    Last Post: 12-16-2010, 08:43 AM
  2. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  3. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 09:49 AM
  4. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM

Tags for this Thread

Posting Permissions

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