Results 1 to 14 of 14
  1. #1
    Unnel is offline Member
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    23
    Rep Power
    0

    Default Problem inserting into database

    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

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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)?

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default

    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.

  4. #4
    Unnel is offline Member
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    23
    Rep Power
    0

    Default Thanks

    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

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default

    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.

  6. #6
    Unnel is offline Member
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    23
    Rep Power
    0

    Default

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

    along with the same SQL error message...

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

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

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default

    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.

  9. #9
    Unnel is offline Member
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    23
    Rep Power
    0

    Default

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

  10. #10
    Unnel is offline Member
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    23
    Rep Power
    0

    Default

    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...

  11. #11
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Well, what's the problem?

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

    Java Code:
    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();

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,006
    Rep Power
    20

    Default

    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.

  13. #13
    Andri_JT is offline Member
    Join Date
    Jan 2011
    Posts
    10
    Rep Power
    0

    Default

    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.

  14. #14
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by Andri_JT View Post
    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.

Similar Threads

  1. data is not inserting into database
    By gb.rashu in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 08-21-2010, 03:05 PM
  2. inserting date in oracle database
    By jackjosh_in in forum Advanced Java
    Replies: 6
    Last Post: 05-23-2010, 06:38 PM
  3. inserting values from jtable into database
    By sandeepsai17 in forum New To Java
    Replies: 1
    Last Post: 06-29-2009, 07:31 PM
  4. Replies: 1
    Last Post: 12-30-2008, 04:39 PM
  5. Inserting file in to database
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-07-2008, 08:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •