Help!! Simple question about date formating.

Hey all.
This is part of my code:
//Set date
          Date currentDate = new Date();
          SimpleDateFormat fmt = new SimpleDateFormat("dd.MM.yyyy");
          String formatert = fmt.format(currentDate);
          fmt.setLenient(false);
          System.out.println(formatert);
          Date dato = fmt.parse((formatert),new java.text.ParsePosition(0));
          System.out.println(dato);

The case is that I need to send a date from my app to a table in a MSSQL server via JDBC.

The problem is that I get the following message when executing my sql statement:Syntax error converting datetime from character string. I understand that the format of my date has to be the same as in the db and my simpledateformat should fix that. But after I parse the string to get a date the format is all wrong. I thought the setLenient(false) would ensure that my format stays unaltered. What am I doing wrong??




wiggorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CichaCommented:
How you pass the date to sql statement?
0
girionisCommented:
 What database are you using? And in what format does the database expects the date?
0
wiggorAuthor Commented:
Through a insert into <tablename> (columnname) value (variable)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

wiggorAuthor Commented:
MSSQL database, I suspect it expects to get it as a date.
0
girionisCommented:
 Can you use java.sql.Date instead of java.util.Date and tell us what happens? And pelase do not format it. Just create the java.sql.Date object and send it to the database.
0
wiggorAuthor Commented:
I send the Date dato variable in my statement, but I think the problem is that the parsing changes my SimpleDateFormat so that the db doesn't recognize it.
0
wiggorAuthor Commented:
Same error as before.
0
wiggorAuthor Commented:
What is the difference between the Date in java.sql an util?
0
girionisCommented:
 java.sql.Date is more appropriate date object for database interaction. Lets try something else.

  Can you just ignore the following code:

Date currentDate = new Date();
         SimpleDateFormat fmt = new SimpleDateFormat("dd.MM.yyyy");
         String formatert = fmt.format(currentDate);
         fmt.setLenient(false);
         System.out.println(formatert);
         Date dato = fmt.parse((formatert),new java.text.ParsePosition(0));
         System.out.println(dato);

  and just do:

 java.sql.Date date = new java.sql.Date()

  and then try to send this "date" object to the database?
0
CichaCommented:
You can use
PrepareStatment and set values as java objects.The JDBC  
driver will convert the object into rigth form.
Example.

SQL Statment: insert into <tablename> (columnname) ? (variable)
String sqlStat = "insert into <tablename> (columnname) ? (variable)";
Date value = ....;
Connection connection = ....;
PrepareStatment ps = connection.getPrepareStatment(sqlStat);

ps.setDate(1, value);

ps.exequte();
0
wiggorAuthor Commented:
girionis
>and just do:

>java.sql.Date date = new java.sql.Date()

I get a can not resolve symbol in the Date() constructor.
0
girionisCommented:
 Yes you are right. The constructor is different. Try this:

java.sql.Date = new java.sql.Date(System.currentTimeMillis());

  Not that the currentTimeMillis represents the date from the midnight of 1st of January 1970.
0
wiggorAuthor Commented:
Copied it and got cannot resolve symbol in class sql.
0
girionisCommented:
 Can you please post the exact error message?
0
girionisCommented:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis());

  Sorry for before.
0
girionisCommented:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis());

  Sorry for before.
0
wiggorAuthor Commented:
Ok so far so good. But there is still some problems. I'll post a code snippet were I try to send the date variable to my db. I get a cannot resolve symbol on the date variable. Date date is a global variable.
public void DbClient()
     {
          username = new String("****");
          password = new String("****");
          databaseURL = new String("jdbc:microsoft:sqlserver://server-2:1433");
          databaseName = new String("NO");

          //Establish connection to db via driver
          try
          {
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //Load driver
          }
          catch (ClassNotFoundException ex)
          {
               System.out.println(ex);
          }
          System.out.println("JDBC driver loaded");

          try
          {
               con = DriverManager.getConnection(databaseURL,username,password); //Oppretter forbindelse
               System.out.println("Connection created");
          }
          catch (java.sql.SQLException ex)
          {
               System.out.println("Error " + ex.getErrorCode() +ex.getMessage());
               System.exit(0);
          }
          //Initialize preparedStatement
          try
          {
               String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                      + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               PreparedStatement ps = con.prepareStatement(sqlStat);
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
          ps.setInt(1,160379);
          ps.setInt(2,0);
          ps.setString(3,"headline");
          ps.setString(4,"bilde");
          ps.setString(5,"intro");
          ps.setString(6,"brodtekst2");
          ps.setString(7,"brodtekst3");
          ps.setString(8,"brodtekst4");
          ps.setString(9,"brodtekst5");
          ps.setString(10,"brodtekst6");
          ps.setString(11,"brodtekst7");
          ps.setString(12,"brodtekst8");
          ps.setString(13,"brodtekst9");
          ps.setString(14,"brodtekst10");
          ps.setString(15,"brodtekst");
          ps.setString(16,"kilde");
          ps.setDate(17,date);
          ps.setString(18,"link");
          ps.setString(19,"fnyheter");
          ps.setInt(20,1);
          ps.execute();
     }
0
girionisCommented:
 Can you post the exact error message?
0
wiggorAuthor Commented:
C:\Documents and Settings\raymond.ZOONONWEB\My Documents\Kode\ArticleApplication\ArticleApplicationBack.java:295: cannot resolve symbol
symbol  : variable date  
location: class ArticleApplicationBack
          ps.setDate(17,date);
                              ^
1 error
0
girionisCommented:
 Can you post a sniplet of code where you declare "date"?
0
wiggorAuthor Commented:
Man I think I have changed to much of my code now, the program compiles but it's hard to check if the date works because I get a nullpointerexeption when running it. If you give me a hand with this I would appreceate it and certanly up the points.
0
wiggorAuthor Commented:
It doesn't like my prepared statement.
0
girionisCommented:
 OK first of all where do you get this nullpointer exception? Can you post a snippet of code?
0
wiggorAuthor Commented:
         //Initialize preparedStatement
          try
          {
               String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                      + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               PreparedStatement ps = con.prepareStatement(sqlStat);
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
          try
          {
          ps.setInt(1,160379);
          ps.setInt(2,0);
          ps.setString(3,"headline");
          ps.setString(4,"bilde");
          ps.setString(5,"intro");
          ps.setString(6,"brodtekst2");
          ps.setString(7,"brodtekst3");
          ps.setString(8,"brodtekst4");
          ps.setString(9,"brodtekst5");
          ps.setString(10,"brodtekst6");
          ps.setString(11,"brodtekst7");
          ps.setString(12,"brodtekst8");
          ps.setString(13,"brodtekst9");
          ps.setString(14,"brodtekst10");
          ps.setString(15,"brodtekst");
          ps.setString(16,"kilde");
          ps.setDate(17,date);
          ps.setString(18,"link");
          ps.setString(19,"fnyheter");
          ps.setInt(20,1);
          ps.execute();
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }

     }

It doesn't like the way I implement the prepared statement I think. I use this sniplet to initialize the db variables with temp values. And then I call functions that read user input and configure the variables.
These functions also generate nullpointerexceptions.
Example:
/**
     *Function that alteres the kilde/source in db
     */
     public void setKilde()
     {
          source = tfSource.getText();
          btSource.setBackground(Color.green);
          try
          {
               ps.setString(16,source);
               ps.execute();

          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
     }
0
girionisCommented:
 Sorry I am lost here. Which one of the specific lines throw the NullPointerException?

  I also assume this line: ps.setDate(17,date); compiles fine now.
0
wiggorAuthor Commented:
Yes everything compiles fine. Sorry, the nullpointer exception gets thrown in this line:
ps.setInt(1,160379);
This is the primary key of the db and is supposed to set itself as a random number in the end produkt. I assignet the value just to test if the prepared statement worked.
0
girionisCommented:
 Can you do a System.out.println("ps: " + ps); just before the ps.setInt(1, 160379); and tell me the output?
0
wiggorAuthor Commented:
ps: null Why, I am connected to the db. I must have the statement all wrong.
0
girionisCommented:
 Well you might not be connected after all. Do you see the "Connection created" message?
0
wiggorAuthor Commented:
Yes, and I have tested the connection before I started using the prepared statement.
0
girionisCommented:
 I can only say that the error is in this block of code:

//Initialize preparedStatement
try
{
  String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                     + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  PreparedStatement ps = con.prepareStatement(sqlStat);
}
catch(SQLException sqle)
{
  System.out.println("SQL problem:" + sqle);
}

  please add another one catch snippet at the end. It should look like:

//Initialize preparedStatement
try
{
  String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                     + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  PreparedStatement ps = con.prepareStatement(sqlStat);
}
catch(SQLException sqle)
{
  System.out.println("SQL problem:" + sqle);
}
catch(Exception e)
{
  System.out.println("Exception occured: " + e);
  e.printStackTrace();
}

  and let me know for the error message.
0
wiggorAuthor Commented:
I get the same error: Nullpointerexception on the same line.
0
girionisCommented:
 Do you have any more "ps" variables defined in your programme? The "ps" variable inside your try statement is only accessible to the try block..
0
wiggorAuthor Commented:
No, it's declared globally but not defined anywhere else, don,t you have to use a try statement when defining it?
0
girionisCommented:
 So you have a global declaration like:

  PreparedStatement ps = null;

  or

  PreparedStatement ps;

  and inside your try statement you have another one. You see, all the variables inside the try statement are only *local* to the try statement, they cannot be used outside of it. Now you are having two identical "ps" variables, but as long as JVM is concerned they are different (despite the same name). So the "ps" outside the try is different to the "ps" inside your try. You initialize the "ps" inside the try but you use your global "ps" outside the try, which of course, your global "ps" is not initialized.

  The problem can be solved if you change the following line:

  PreparedStatement ps = con.prepareStatement(sqlStat);

  to

  ps = con.prepareStatement(sqlStat);

  Try it and tell me the output.
0
wiggorAuthor Commented:
Crap!!, I redeclare the PreparedStatement inside the try statement. I get a bit closer to getting it to work, but there is still a sql error. As I mentioned the ID is automaticly set if you punch the data directly into the db. So the IDENTITY_INPUT is set to off. I can't just leave it out of my ps can I?
0
wiggorAuthor Commented:
Actually I get the IDENTITY_INSERT is set to off on all the variables.
0
girionisCommented:
 No you can't. If it needs it it should be there. What is the error message you are getting this time? Can you post the exact message?
0
girionisCommented:
> As I mentioned the ID is automaticly set if you punch the data directly into the db

  So the ID field is set to Auto? Then you could leave it out yes.
0
wiggorAuthor Commented:
Cannot insert explisit vakue for identity column in table 'avisklipp' when IDENTITY_INSERT is set to OFF.
0
girionisCommented:
 Yes you could leave it out... Please ignore my comment on: 03/06/2003 05:07AM PST I didn't read your post carefuly so I didn't realize it was set to auto. You cannot insert anythign into columns that used as IDENTITY, i.e. to identify data.
0
wiggorAuthor Commented:
The ultimate anticlimax, no error but nothing works. I'm pretty new to JDBC but by running the sniplets I posted above you are supposed to add a new line in the db right??
0
girionisCommented:
 Yes that's what is supposed to happen. Are you sure you are not getting any error messages? Can you post again the new code thsi time that inserts records into the database?
0
wiggorAuthor Commented:
//To connect and initialise the prepared statement I use this.
public void DbClient()
     {
          username = new String("****");
          password = new String("****");
          databaseURL = new String("jdbc:microsoft:sqlserver:server-2");
          databaseName = new String("NO");

          //Establish connection to db via driver
          try
          {
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //Load driver
          }
          catch (ClassNotFoundException ex)
          {
               System.out.println(ex);
          }
          System.out.println("JDBC driver loaded");

          try
          {
               con = DriverManager.getConnection(databaseURL,username,password); //Oppretter forbindelse
               System.out.println("Connection created");
          }
          catch (java.sql.SQLException ex)
          {
               System.out.println("Error " + ex.getErrorCode() +ex.getMessage());
               System.exit(0);
          }
          //Initialize preparedStatement
          try
          {
           sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                 + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
           ps = con.prepareStatement(sqlStat);
           System.out.println(ps);
          }
          catch(SQLException sqle)
          {
           System.out.println("SQL problem:" + sqle);
          }
          catch(Exception e)
          {
           System.out.println("Exception occured: " + e);
           e.printStackTrace();
          }

          try
          {
          ps.setInt(1,0);
          ps.setString(2,"headline");
          ps.setString(3,"bilde");
          ps.setString(4,"intro");
          ps.setString(5,"brodtekst2");
          ps.setString(6,"brodtekst3");
          ps.setString(7,"brodtekst4");
          ps.setString(8,"brodtekst5");
          ps.setString(9,"brodtekst6");
          ps.setString(10,"brodtekst7");
          ps.setString(11,"brodtekst8");
          ps.setString(12,"brodtekst9");
          ps.setString(13,"brodtekst10");
          ps.setString(14,"brodtekst");
          ps.setString(15,"kilde");
          ps.setDate(16,date);
          ps.setString(17,"link");
          ps.setString(18,"fnyheter");
          ps.setInt(19,1);
          System.out.println(sqlStat);
          ps.execute();
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }

     }
And to change the individual entries in the db I use several functions like this:
     *Function that alteres intro/bildetekst in db
     */
     public void setIntro()
     {
          pictureText = taPictureText.getText();
          pictureTextLength = pictureText.length();
          btPictureText.setBackground(Color.green);

          try
          {
               ps.setString(4,pictureText);
               ps.execute();
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
     }
0
girionisCommented:
 It looks fine. What do you get if you do a:

  System.out.println(ps.execute());
0
wiggorAuthor Commented:
I assume you meen System.out.println(ps);
If so I get:
com.microsoft.jdbc.BasePreparedStatement@ce2dd4
0
wiggorAuthor Commented:
I suppose it is the way it is suppose to be but the ps doesn't change from the time it is initiated to after the values in it is set.
0
girionisCommented:
 No I meant System.out.println(ps.execute()); the execute() method returns either true or false.

  Just replace the ps.execute() with the System.out...

 Or you could better also do a System.out.println(ps.executeUpdate()); and tell me the result? It should return a numebr, probably 1 if everything is fine.
0
wiggorAuthor Commented:
Ran both:
System.out.println(ps.execute()); returned false
System.out.println(ps.executeUpdate()); returned 1
I wouldn't say everything is fine though.;)
0
girionisCommented:
 Hmmm. weird. Can you please do a System.out.println(con.getAutoCommit()); and tell me the result?
0
wiggorAuthor Commented:
Sorry, girionis I've had to leave my post for today. I'll try it first thing tomorrow and let u know. Thank you for your patience and help so far. Youre a lifesaver.
0
wiggorAuthor Commented:
>System.out.println(con.getAutoCommit()); returns true
0
girionisCommented:
 Can you set it to false after creating the connection:

  con.setAutoCommit(false);

  and after you do the ps.execute(); add the following line:

  con.commit();

  Can you also try and close the database connections after you've done and tell me if this helps?
0
wiggorAuthor Commented:
Nope, still ain't adding anything.
0
girionisCommented:
 Are you sure you are not getting any error messages? Have you checked the log files?
0
wiggorAuthor Commented:
I'm not getting any errors in my console. I'm not sure how to access db logs, have enterprise installed tough.
0
wiggorAuthor Commented:
Don't know if it is supposed to be like that but the current->SQL server logs doesn't show any activity from me at all, but I've been printing tables all day so that doesn't say much.
0
girionisCommented:
 Ok lets try something else. Can you try sending a JDBC statement using the Statement object and see what happens?

  Just change this:

   sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

  into this:

 sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                + "VALUES (0,'headline' ....)";

  initialize Statement and then do: stmt.executeUpdate(sqlStat);
0
wiggorAuthor Commented:
Allreaddy tryed that, I think I'll have to go back to the drawing board. But I have a concern about the way I have built the app. If it works,SOMETIME IN THE FUTURE, what will happen when I run the functions for changing the individual values. Like the setIntro wich I posted above.
Will I create whole new entry in the db. Thats not what I'm aiming for.
0
girionisCommented:
> Allreaddy tryed that

  Did it work? If it did it means there is a problem with the PreparedStatement code, otherwise there is a database problem. If we can identify where the error is then we can start from there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wiggorAuthor Commented:
No, it did not work I'm afraid. I think I'll have to take a talk to the system guys, I think we have covered every aspect of my code don't you agree?
0
girionisCommented:
 That's what I think as well. Your code compiles fine and it looks fine. Of course it is difficult to say without looking at all of your files and with no error messages generated but if you could get a second opinion from the database guys this would be more helpful.
0
wiggorAuthor Commented:
I will do that, you have been a big help girionis, your persistence and knowledge are impressive. I will post the answer as soon as I find it. But as I mentioned there are a couple of issues that bothers me a bit. With the prepared statement. Won't I get multiple entries in the db, when I run the functions for configuring the individual variables in the ps. As posted above?
And another concern I have is the date issue wich we discussed in the beginning. In the db the date is set by a datetime with the format dd.MM.yyyy. Sure this compatible with the solution you gave me before:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); or do I need to format it?
Some thoughts from the top of your head will be appreciated imensly. Thnx a lot.
0
wiggorAuthor Commented:
Defenitive answer will be posted.
0
girionisCommented:
 Thank you :-)

>  With the prepared statement. Won't I get multiple entries in the db, when I run the functions for configuring the individual variables in the ps. As posted above?

  It depends. Entries in the database will go only after the ps.execute() has been reached and the database commits the changes. This commit is by default set to auto so the database commits the changes automatically after executing each statement (in our case after we do a ps.execute() the database will commit automatically). If you set it to manual (by doing a connection.setAutoCommit(false) then you need to do a connection.commit() explicitly. If changes are not commited in the database then there is nothing there, regardless if you have executed a statement.

>  In the db the date is set by a datetime with the format dd.MM.yyyy. Sure this compatible with the solution you gave me before:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); or do I need to format it?

  No you shouldn't need to format it. java.sql.Date is written in a way that it complies with database standards. I never needed to format any date in order to insert it into SQL server 2000. Besides if this were the problem for the database you should really be getting error messages about the date format.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.