Problem inserting into database

[Unnel]

Good day,

I've developed an application that is reading data from a file and saving it into a database (I'm using MySql). Unfortunately I'm having problems inserting the data. Below is the relevant piece of code (I'm trying to create a table using the name of the file that my application is reading) and the SQL error I get:

Class.forName("com.mysql.jdbc.Driver").newInstance ();
con = DriverManager.getConnection("jdbc:mysql:///mastersdata", "root", "");
if(!con.isClosed())

stat = con.createStatement();
stat.execute("CREATE TABLE " + selectedFile.getName());

Message: Exception: 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 '300160' at line 1

Please note that 300160 is the name of the file my application is reading and which I want to use to name the table I'm trying to create.

Many thanks

[masijade]

Uhm, where is the rest of the "create table" command? And what if the table already exists? What if the table already exists but no longer has the correct definition (due to a change in requirements, or whatever)?

[Tolls]

check the manual that corresponds to your MySQL server version for the right syntax to use

Do as this says as well as listening to what masijade has said.

[Unnel]

Thanks, I'll try out your suggestions. However I was thinking the error might have been with my Java code.
@Masijade, this is what the entire CREATE Statement looks like..

stat.execute("CREATE TABLE " + selectedFile.getName() + "(NAME VARCHAR(20), DOB DATE)");

Thanks

[Tolls]

Create a String with that CREATE statement in it and print it out before executing it.
Looks to me like you're missing a space.

[Unnel]

This is the printout that I get:
CREATE TABLE 300160(NAME VARCHAR(20),DOB DATE);

along with the same SQL error message...

[masijade]

I don't believe "300160" is a valid tablename in mysql. See its documentation.

[Tolls]

That's the more likely bet, though I did look up naming and I didn't immediately see anything saying you can't. Then again, it was only a quick skim of the relevant page.

I thought it was the missing space between the name and the '(', but that's not it.

Changing the table name to '<number>a' works.

[Unnel]

Thanks a lot for your invaluable feedback guys...@Tolls it works NOW thanx to your suggestion!!!Thanx a mil...

[Unnel]

Unfortunately now I'm having a problem with inserting data:

Below are the statements:

stat = con.createStatement();
tableName = selectedFile.getName()+ "a";
String g ="CREATE TABLE " + tableName + "(Date DATE,Bandwidth INT(5), WebsiteVisited TEXT);" ;
stat.execute(g);
Above is the create statement which fortunately creates the table and below is the Insert which does dot work:

for(int i =0; i < temp.length;)
{
unixTime = Float.valueOf(temp[0]);
timeStamp = unixTime * 1000;
d = new Date((long) timeStamp);

try {
stat.execute("INSERT INTO " + tableName + " VALUES (" + d + ", "+temp[4] + ", "+ temp[6]+");");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

So just you know, temp is the String array in which I store the data that is contained in a file that I'm reading. I'm retrieving particular piece of data in each line, thus the loop...So d is the date, temp[4] and temp[6] are some data I am interested in storing in the table I created above..I'm looping through each line of the file I'm reading...

[masijade]

Well, what's the problem?

However, for one, do not cobble together the values portion of that statement. Use PreparedStatement as such

String sql = "INSERT INTO " + tableName + " VALUES (?, ?, ?)");
PreparedStatement ps = conn.prepareStatement(sql);
for (/*some array of values*/) {
  ps.setDate(1, dateVar);
  ps.setInt(2, intVar);
  ps.setString(3, stringVar);
  ps.addBatch();
}
ps.executeBatch();

[Tolls]

Yes, PreparedStatement.
You are almost certainly not getting the SQL you think you are, especially with concatenating that Date in there relying on the toString() method of Date.

[Andri_JT]

is it related to this information?
dbConnect.createStatement(ResultSet.TYPE_SCROLL_IN SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

Excerpt:
1. CONCUR_READ_ONLY:This resultSetConcurrency is the default. The database cannot be updated through the ResultSet.

2. CONCUR_UPDATABLE: This resultSetConcurrency allows changes to the database through the ResultSet. However, an update can be processed through a ResultSet only if the query
meets the following three conditions: it references only one table; does not contain a JOIN or GROUP BY clause; and selects the primary key of the table. Furthermore, if an insert
is attempted, any column not selected by the query must have a default value or accept null as a value.

[masijade]

is it related to this information?
dbConnect.createStatement(ResultSet.TYPE_SCROLL_IN SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

Excerpt:
1. CONCUR_READ_ONLY:This resultSetConcurrency is the default. The database cannot be updated through the ResultSet.

2. CONCUR_UPDATABLE: This resultSetConcurrency allows changes to the database through the ResultSet. However, an update can be processed through a ResultSet only if the query
meets the following three conditions: it references only one table; does not contain a JOIN or GROUP BY clause; and selects the primary key of the table. Furthermore, if an insert
is attempted, any column not selected by the query must have a default value or accept null as a value.

Well, seeing as how he is not doing a select, no.