#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30

    Java date into SQL DB creating errors..


    Hi guys,

    i've googled around and managed to transform my dates into MySQL dates, however I seem to be getting SQL errors when running my SP .... Weird thing is, if I run the SP manually it runs fine I can't work it out


    My error logs are showing the SQL error of:
    /signup-1POST 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 ''stu','Q49K2heWlQE8+Bz5A5jnHQ==','stu','stu','1986-01-03','0','stu','stu',''' at line 1

    and my SP is basically:

    SQL Code:
     
    DELIMITER $$
     
    DROP PROCEDURE IF EXISTS `uphoto`.`qryCreateUser` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `qryCreateUser`(
    IN _username VARCHAR( 30 ) ,
    _password VARCHAR( 50 ) ,
    _forename VARCHAR( 40 ) ,
    _surname VARCHAR( 40 ) ,
    _dob DATE,
    _confcode VARCHAR( 40 ) ,
    _email VARCHAR( 40 ) ,
    _location VARCHAR( 40 ) ,
    _occupation VARCHAR( 40 )
    )
    INSERT INTO `Users` ( `UserName` , `Pass` , `Forename` , `Surname` , `dob` , `confCode` , `email` , `location` , `occupation`
     
    )
    VALUES (
    _username, _password, _forename, _surname, _dob, _confcode, _email, _location, _occupation
    ) $$
     
    DELIMITER ;


    The code I am using is my DAO method..

    JAVA Code:
     
     
    /Set Date..
     
    DateFormat df = new SimpleDateFormat("mm/dd/yyyy");
     
        //Get Date objects..
        java.sql.Date dobSQLDate = null;
     
        java.util.Date dob = null;
     
     dob = df.parse(req.getParameter("dob"));
            dobSQLDate = new java.sql.Date(dob.getTime());
     
    //Call DAO:
     
    CreateUserCheck = UserDao.createUser(username, password, forename,
                                                   surname, dobSQLDate, confCode, email,
                                                   location,   occupation);
     
    //MySQLUsers DAO:
     
    pstmt.setString(1, username.toString()); // here the values are passed in as params to the method (Create User)
          pstmt.setString(2, password.toString());
          pstmt.setString(3, forename.toString());
          pstmt.setString(4, surname.toString());
          pstmt.setDate(5, new Date(dob.getTime()));
          pstmt.setString(6, confcode.toString());
          pstmt.setString(7, email.toString());
          pstmt.setString(8, location.toString());
          pstmt.setString(9, occupation.toString());



    The date is in 01/01/1899 format - strangly also, is the date I am entering is 04/03/1986 but the 04 is becoming 01?

    any ideas?

    Cheers
  2. #2
  3. Feelin' Groovy
    Devshed Supreme Being (6500+ posts)

    Join Date
    Aug 2001
    Location
    Chicago, IL
    Posts
    10,131
    Rep Power
    5058
    You're parsing a string to a java.util.Date, then using that date to create a java.sql.Date, which you later use to create a java.util.Date to set as a parameter??? Why all the extra steps? And it looks from your variable name ("pstmt") that you might be using a PreparedStatement. Why not just use a CallableStatement for the stored procedure you created?

    The date is in 01/01/1899 format
    The "format" in MySQL isn't relevant to Java. Just make sure your parsing format handles the string you give to it.

    ~
    Last edited by Yawmark; August 5th, 2007 at 04:13 PM.
    Yawmark
    class Sig{public static void main(String...args){\u0066or(int
    \u0020$:"v\"ʲ\"vΤ\"".to\u0043h\u0061rArray()
    )System./*goto/*$/%\u0126//^\u002A\u002Fout.print((char)(($>>
    +(~'"'&'#'))+('<'>>('\\'/'.')/\u002Array.const(~1)\*\u002F)));}}
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    407
    Rep Power
    30
    Originally Posted by Yawmark
    You're parsing a string to a java.util.Date, then using that date to create a java.sql.Date, which you later use to create a java.util.Date to set as a parameter??? Why all the extra steps? And it looks from your variable name ("pstmt") that you might be using a PreparedStatement. Why not just use a CallableStatement for the stored procedure you created?


    The "format" in MySQL isn't relevant to Java. Just make sure your parsing format handles the string you give to it.

    ~
    Not sure why I used Pstmt, read about it being better for SQLInjection somewhere...

    Ok, so i changed my code a bit I now have:
    JAVA Code:
    dobSQLDate = new Date(Long.parseLong(req.getParameter("dob")));


    (NEW DATE IS A JAVA.SQL.DATE Object) But this is now not parsing correcting and parses as 1970-01-01, I followed up further on google, but still cna't find anything.
  6. #4
  7. Feelin' Groovy
    Devshed Supreme Being (6500+ posts)

    Join Date
    Aug 2001
    Location
    Chicago, IL
    Posts
    10,131
    Rep Power
    5058
    Not sure why I used Pstmt, read about it being better for SQLInjection somewhere.
    You'd use a PreparedStatement if you were building the SQL in the data access class. You'd use a CallableStatement if you were calling a stored procedure on the database. Use the right tool for the job.

    But this is now not parsing correcting and parses as 1970-01-01
    It *is* parsing correctly. You're just expecting it to do something it's not intended to do. What is the format of the "dob" parameter value? If it's not the number of milliseconds since Jan 1, 1970 (which I rather doubt, unless you're doing some JavaScript munging of the data before sending the request) and actually looks like something someone would enter as a birthdate (which is what I would expect) you should consider parsing the value with java.text.SimpleDateFormat.

    ~

    Comments on this post

    • Nemi agrees
    Yawmark
    class Sig{public static void main(String...args){\u0066or(int
    \u0020$:"v\"ʲ\"vΤ\"".to\u0043h\u0061rArray()
    )System./*goto/*$/%\u0126//^\u002A\u002Fout.print((char)(($>>
    +(~'"'&'#'))+('<'>>('\\'/'.')/\u002Array.const(~1)\*\u002F)));}}

IMN logo majestic logo threadwatch logo seochat tools logo