Thread: 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

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

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?

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

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.