• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Writing to database

I have colums
Varchar , Date, varchar

I run this:

 public boolean registerFlight(String flightNo, String date, String planeName) {

        int val = 0;
            try {
                  val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +"," +date +"," +planeName +"'");



I get this:


com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)


Do you see the error because I cant spot it.
0
Mickeys
Asked:
Mickeys
  • 13
  • 7
1 Solution
 
for_yanCommented:

Plane name is most probably string, so you need tyo add single quotes like that (at least you don't have single qouote befpre it in the snippet above )

 val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +"," +date +",'" +planeName +"' "); 

Open in new window

0
 
for_yanCommented:
The format of the date is a sepatare question - printourt how your date looks and I can check if it goes wit oracle or you need to do to_date(...)
0
 
MickeysAuthor Commented:
I run mysql

2011-01-11
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MickeysAuthor Commented:
running the above code makes it:


com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'Hera'' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

So you are probably right about the date. :-/
0
 
for_yanCommented:

then you should use this with Orcale:

to_date('2011-01-11','YYYY-MM-DD')
0
 
for_yanCommented:
 val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +",to_date('" +date +"','YYYY-MM-DD'),'" +planeName +"' "); 

Open in new window

0
 
for_yanCommented:
Sorry, I forgot that you are on MySQL (there is simpulatneously anotythe question about Oraclele)- it may require different date format
0
 
MickeysAuthor Commented:
yes it is.

I tried google but so far no luck
0
 
for_yanCommented:
It looks like YYYY-MM-DD is defaul formatof the date for MySQL, so it should understand your date  in this way:

 val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +",'" + date +"','" +planeName +"' "); 

Open in new window


0
 
MickeysAuthor Commented:
I dont know if I am on the right track but I wrote this but it doesnt work correctlly yet. So I first need to fix this method

public void StringToDate ()
         {
        try {  String str_date="2011-01-01";
        DateFormat formatter ;
        Date date ;
         formatter = new SimpleDateFormat("yyyy-MMM-dd");
         date = (Date)formatter.parse(str_date);  
        System.out.println("Today is " +date );
         } catch (ParseException e)
         {System.out.println("Exception :"+e);  }  
        
        }




Exception :java.text.ParseException: Unparseable date: "2011-01-01"
0
 
for_yanCommented:


I read here:
http://www.tizag.com/mysqlTutorial/mysql-date.php

the forllowing:

The default way to store a date in MySQL is with the type DATE. Below is the proper format of a DATE.

    YYYY-MM-DD
    Date Range: 1000-01-01 to 9999-12-31
0
 
for_yanCommented:
But this is another story - this is about SimpleDateFormat

I think you have one extra "M" - you should have

      formatter = new SimpleDateFormat("yyyy-MM-dd");

let me try
0
 
MickeysAuthor Commented:
Hmmm I found the error. One M to much in MMM

Now I get this

Today is Sat Jan 01 00:00:00 CET 2011


So I guess this wont work
0
 
for_yanCommented:
This worked for me :

     SimpleDateFormat   formatter = new SimpleDateFormat("yyyy-MM-dd");

        java.util.Date ddm = formatter.parse("2011-01-11", new ParsePosition(0));

Open in new window

0
 
MickeysAuthor Commented:
well works yes but it dont gets to the correct date. The output is this:

Today is Sat Jan 01 00:00:00 CET 2011

when the output should be 2011-01-01

So I guess this is the wrong method.

So I think I need something else.

The big question is HOW can I make this to_date('2011-01-11','YYYY-MM-DD')  work with mysql?
0
 
for_yanCommented:

This will give you the output which you want, but I don't understand what is your goal
You had str_date in the first place in this format?
public void StringToDate ()
         {
        try {  String str_date="2011-01-01";
        DateFormat formatter ;
        Date date ;
         formatter = new SimpleDateFormat("yyyy-MMM-dd");
         date = (Date)formatter.parse(str_date);  
        System.out.println("Today is " + formatter.format(date) );
         } catch (ParseException e)
         {System.out.println("Exception :"+e);  }  
        
        }

Open in new window

0
 
for_yanCommented:


And why do you need " how to_date('2011-01-11','YYYY-MM-DD')  work with mysql?"

Did it work this way without error ?


 val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +",'" + str_date +"','" +planeName +"' ");

where str_date = "2011-01-01" ?

Did you try it this way?

0
 
for_yanCommented:


When you use this operator:
     System.out.println("Today is " +date );

and date is instance of java.util.Date it will use the toString() method of Date
and it prints the same date but with hours, day of the week, etc.

If you want to print it in  different format you need to use SimpleDateFormat to format it the way you want

But if you want to print it the same wy you had it formatted originally,
then there is no reason to parse it first to a Date and the fomrat it again to the  same string
0
 
MickeysAuthor Commented:
Needed a little tweek.

Replaced - with / in my date string and then this

val = mStmt.executeUpdate("INSERT INTO flight(flFlightNo, flDDate, flName) VALUES('"+flightNo +"',STR_TO_DATE('" +date +"','%Y/%m/%d'),'" +planeName +"' "+")");
0
 
for_yanCommented:
Great, that  it worked.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 13
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now