Solved

Writing to database

Posted on 2011-09-28
20
268 Views
Last Modified: 2012-05-12
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
Comment
Question by:Mickeys
  • 13
  • 7
20 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36719199

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
 
LVL 47

Expert Comment

by:for_yan
ID: 36719207
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
 

Author Comment

by:Mickeys
ID: 36719290
I run mysql

2011-01-11
0
 

Author Comment

by:Mickeys
ID: 36719304
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36719315

then you should use this with Orcale:

to_date('2011-01-11','YYYY-MM-DD')
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36719325
 val = mStmt.executeUpdate("INSERT INTO flight(flFligtNo, flDDate, flName) VALUES('"+flightNo +",to_date('" +date +"','YYYY-MM-DD'),'" +planeName +"' "); 

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36719334
Sorry, I forgot that you are on MySQL (there is simpulatneously anotythe question about Oraclele)- it may require different date format
0
 

Author Comment

by:Mickeys
ID: 36719378
yes it is.

I tried google but so far no luck
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 36719407
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
 

Author Comment

by:Mickeys
ID: 36719418
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 47

Expert Comment

by:for_yan
ID: 36719421


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
 
LVL 47

Expert Comment

by:for_yan
ID: 36719432
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
 

Author Comment

by:Mickeys
ID: 36719434
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36719458
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
 

Author Comment

by:Mickeys
ID: 36812931
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36812943

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
 
LVL 47

Expert Comment

by:for_yan
ID: 36812951


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
 
LVL 47

Expert Comment

by:for_yan
ID: 36812956


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
 

Author Closing Comment

by:Mickeys
ID: 36812983
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36812991
Great, that  it worked.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Java 1603 Error 2 42
solarwind tftp server 2 32
backtracking recursion  code 19 41
Java DateChooser? 2 13
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now