Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 7 of 7

Thread: MySQL Syntax Error

  1. #1
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default MySQL Syntax Error

    I am trying to pull a .csv file from the web that contains some stock data. I am then trying to write this data to a MySql database. Half of the data is being input into the preparedStatement, but I then when I get to 'open' it will only accept a single character. I tried substituting the String for just plain text but it still will only accept a single character. This is my code:

    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import java.net.MalformedURLException;
    import java.net.URL;
    import java.net.URLConnection;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.StringTokenizer;
     
     
    public class YahooStock {
     
    	private String ticker;
     
    	public YahooStock(String inTicker) {
    		ticker = inTicker;
    	}
     
    	private String retrieveQuote() {
    		StringBuffer buf = new StringBuffer();
    		try {
    			URL page = new URL("http://quote.yahoo.com/d/quotes.csv?s=" + ticker + "&f=sl1d1t1c1ohgv&e=.csv");
    			String line;
    			URLConnection conn = page.openConnection();
    			conn.connect();
    			InputStreamReader in = new InputStreamReader(conn.getInputStream());
    			BufferedReader data = new BufferedReader(in);
    			while ((line = data.readLine()) != null) {
    				buf.append(line + "\n");
    			}
    		} catch (MalformedURLException urle) {
    			System.out.println("Bad URL: " + urle.toString());
    		} catch (IOException ioe) {
    			System.out.println("Error: " + ioe.toString());
    		}
    		return buf.toString();
    	}
     
    	private void storeQuote(String data) {
    		StringTokenizer tokens = new StringTokenizer(data, ",");
    		String[] fields = new String[9];
    		for (int i = 0; i < fields.length; i++) {
    			fields[i] = stripQuotes(tokens.nextToken());
    		}
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    			Connection conn = DriverManager.getConnection("jdbc:mysql://XXXX.X.X.XX:3306/QuoteData", "xxx", "xxx");
    			PreparedStatement prep2 = conn.prepareStatement("INSERT INTO yahoo(ticker, price, quoteDate, change, open, high, low, volume) VALUES(?,?,?,?,?,?,?,?)");
    			prep2.setString(1, fields[0]);
    			prep2.setString(2, fields[1]);
    			prep2.setString(3, fields[2]);
    			prep2.setString(4, fields[3]);
    			prep2.setString(5, fields[4]);
    			prep2.setString(6, fields[5]);
    			prep2.setString(7, fields[6]);
    			prep2.setString(8, fields[7]);
    			prep2.executeUpdate();
    			conn.close();
    		} catch (SQLException sqle) {
    			System.out.println("SQL Error: " + sqle.toString());
    		} catch (ClassNotFoundException cnfe) {
    			System.out.println("Error: " + cnfe.toString());
    		}
    	}
     
    	private String stripQuotes(String nextToken) {
    		StringBuffer output = new StringBuffer();
    		for (int i = 0; i < nextToken.length(); i++) {
    			if (nextToken.charAt(i) != '\"') {
    				output.append(nextToken.charAt(i));
    			}
    		}
    		return output.toString();
    	}
     
    	public static void main(String[] args) {
    		if (args.length != 1) {
    			System.out.println("Usage: java YahooStock tickerSymbol");
    			System.exit(1);
    		}
    		YahooStock ys = new YahooStock(args[0]);
    		String data = ys.retrieveQuote();
    		ys.storeQuote(data);
    	}
     
    }

    The csv file that im pulling contains this:

    "AMZN",188.39,"11/29/2011","4:00pm",-5.76,195.13,195.50,187.30,6565622

    And the error I'm getting is:

    SQL Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: 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 'change, open, high, low, volume) VALUES('AMZN','188.39','11/29/2011','4:00pm','-' at line 1



  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: MySQL Syntax Error

    What is the database schema. Are all of the columns string values?

  3. #3
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax Error

    Yes they are all set to:
    Type: VARCHAR
    Collation: utf8_general
    and I set the to a length of 60.

    I tried changing one to INT and then using Integer.parseInt(fields[4]) but that didn't seem to help either.
    I've attached an screen shot of phpMyAdmin
    Picture 1.jpg

  4. #4
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax Error

    Interestingly I have noticed that its the number of characters that seems to be causing the truncation. The command only seems to be able to handle 27 characters. Is this a parameter that is set somewhere?

  5. #5
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax Error

    Has anybody else ever had a problem like this? I've searched high and low but can't find an answer...

  6. #6
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: MySQL Syntax Error

    Add a println statement in there to define what exactly is the SQL update query that is being used - the exception shows there is an error which doesn't seem obvious to me. And I don't have a clue what you mean in your original post by 'open' and 'accept single character'

  7. #7
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: MySQL Syntax Error

    Sorry I have just read back the original post, it was late!!! I tried debuging the code or about 4 hours last night with no success other than working out that I could only write a maximum of 54 bytes before the error occured. Switched off the computer... came back today and it all works beautifully!!!

    Thanks anyway copeg.

Similar Threads

  1. Syntax error with dispose()
    By jagnat in forum AWT / Java Swing
    Replies: 1
    Last Post: October 14th, 2011, 11:00 AM
  2. getobject error retrieving data from mysql
    By zeberrun in forum Other Programming Languages
    Replies: 1
    Last Post: September 19th, 2011, 05:27 AM
  3. do while syntax error problem
    By derekxec in forum What's Wrong With My Code?
    Replies: 5
    Last Post: July 1st, 2011, 06:30 PM
  4. Syntax error on token ";", @ expected after this token
    By MagicMojo in forum What's Wrong With My Code?
    Replies: 1
    Last Post: March 16th, 2011, 07:48 AM
  5. syntax question
    By surfbumb in forum What's Wrong With My Code?
    Replies: 2
    Last Post: March 9th, 2011, 04:01 PM