Exception while reading an sql file and doing database insertion in Java

Question

From an sql file, I am trying to read the sql queries and create a mysqlite db using the following java code:

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;

public class ReadMySqlDump {

static String sep = System.getProperty("line.separator");

public  ReadMySqlDump(){
    try{
        String s = "";
        String buff="";
        ArrayList<String> creates = new ArrayList<String> ();
           ArrayList<String> inserts = new ArrayList<String> ();

        boolean createStart = false;
        String currentCreate = "";

        BufferedReader br = new BufferedReader(new FileReader("mydb.sql.sql"));
        while((buff=br.readLine()) != null){
            buff = buff.trim();
             if(buff.length() == 0)continue;
            if(buff.startsWith("/*"))continue;
             if(buff.startsWith("--"))continue;
            if(createStart && buff.startsWith(");")){
             //   System.out.println("before: " + currentCreate);
                currentCreate = currentCreate.trim();
                if(currentCreate.endsWith(","))currentCreate = currentCreate.substring(0, currentCreate.length()-1);

             //currentCreate = currentCreate.substring(0, currentCreate.length()-2);
               //  System.out.println("after: " + currentCreate);
                    currentCreate += " " + buff + sep;
                createStart = false;
                creates.add(currentCreate);
                currentCreate = "";
                continue;
            }
            if(createStart){
                currentCreate += " " + buff +sep;
                continue;
            }
            if(!createStart && buff.startsWith("CREATE")){
                createStart = true;
                currentCreate += buff + sep;
                continue;
            }


            if(buff.startsWith("INSERT")) {
                inserts.add(buff);
                continue;

            }



        }
        br.close();

        for(String ss: creates){
            System.out.println(ss);
        }

        System.out.println("");
          System.out.println("");

                for(String ss: inserts){
            System.out.println(ss);
        }


         Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:testNew5.db");
    Statement stat = conn.createStatement();
          for(String ss: creates){

              System.out.println("SQL command: " + ss);

            stat.executeUpdate(ss);
        }

      //  ResultSet rs = stat.executeQuery("select * from tbl_passion_attributes");
        //  while(rs.next()){

         // }

        for (String ss : inserts){

                      System.out.println("ss: " + ss);
            int n =  stat.executeUpdate(ss);

                      System.out.println("n: " + n);
        }
             conn.close();



    } catch(Exception ex){

        ex.printStackTrace();

    }


}

public static void main(String[] args) {

    new   ReadMySqlDump();
}


}

The create statements work perfect and the tables are created; but when the insert statements begin, I get exception with the first INSERT statement itself. Following is the exception:

ss: INSERT INTO acos (created, modified, id, parent_id, model, foreign_key, alias, lft,  rght) VALUES
java.sql.SQLException: near "VALUES": syntax error
at org.sqlite.DB.throwex(DB.java:288)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:114)
at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
at ReadMySqlDump.<init>(ReadMySqlDump.java:94)
at ReadMySqlDump.main(ReadMySqlDump.java:113)

Follwoing is the content of mydb.sql.sql

begin;
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 30, 2013 at 11:55 AM
-- Server version: 5.5.24-log
-- PHP Version: 5.3.13



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: abc
--

-- --------------------------------------------------------

--
-- Table structure for table acos
--

CREATE TABLE IF NOT EXISTS acos (
created datetime NOT NULL,
modified datetime NOT NULL,
id integer NOT NULL ,
parent_id integer DEFAULT NULL,
model varchar(255) DEFAULT NULL,
foreign_key integer DEFAULT NULL,
alias varchar(255) DEFAULT NULL,
lft integer DEFAULT NULL,
rght integer DEFAULT NULL,
);

--
-- Dumping data for table acos
--

INSERT INTO acos (created, modified, id, parent_id, model, foreign_key, alias, lft,  rght) VALUES
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 1, NULL, NULL, NULL, 'controllers', 1, 1124),
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 2, 1, NULL, NULL, 'Pages', 2, 15);

-- --------------------------------------------------------

--
-- Table structure for table aros
--

CREATE TABLE IF NOT EXISTS aros (
created datetime NOT NULL,
modified datetime NOT NULL,
id integer NOT NULL ,
parent_id integer DEFAULT NULL,
model varchar(255) DEFAULT NULL,
foreign_key integer DEFAULT NULL,
alias varchar(255) DEFAULT NULL,
lft integer DEFAULT NULL,
rght integer DEFAULT NULL
);

--
-- Dumping data for table aros
--

INSERT INTO aros (created, modified, id, parent_id, model, foreign_key, alias, lft,  rght) VALUES
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 1, NULL, 'Group', 1, NULL, 1, 12),
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 2, NULL, 'Group', 2, NULL, 13, 18);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
commit;

I use the jdbc jar from here

Would appreciate if someone could help resolve this.

does this insert statements run well when you run on your DB — asifsid88
– asifsid88, Commented Feb 6, 2013 at 6:13

Cyrille Ka · Accepted Answer · 2013-02-06 06:15:49Z

1

The INSERT statement sent to the server seems to be incomplete, truncated after VALUES whereas it should contain the data provided by the two following lines in the file.

You are not getting the INSERT statements properly from the file. For CREATE, it is done allright: you are managing a currentCreate flag and concatenate the lines of the statements. You are not doing that for INSERT:

if(buff.startsWith("INSERT")) {
    inserts.add(buff);
    continue;
}

Here, you just take the first line (buff is the result of br.readLine()), and the rest is ignored.

The solution is to do the same thing for INSERT as you do for CREATE: when you see an INSERT, concatenate the subsequent lines until you see the statement is complete, that is the line ends with );.

answered Feb 6, 2013 at 6:15

Cyrille Ka

15.6k5 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1400538 · Accepted Answer · 2013-02-06 09:15:37Z

OK, resolved this myself by including the following in the code:

if(!insertStart && (buff.startsWith("INSERT") && buff.endsWith("VALUES"))) {
                insertStart = true;
                insertMain = buff;
                continue;

            }
            if(insertStart && (buff.endsWith("),"))){
                buff = buff.substring(0, buff.length()-1);
                insertQuery = insertMain+" " + buff+";";
                inserts.add(insertQuery);
                continue;
            }
            if(insertStart && (buff.endsWith(");"))){
                insertQuery = insertMain+" " + buff+";";
                inserts.add(insertQuery);
                insertStart = false;
                insertMain = "";
                continue;
            }

PS: SQLite doesn't accept the INSERT statement in the form I have shown in the question. It will only accept ONE set of values for each INSERT statement. So I had to convert this multi-line INSERT into single INSERT statements

Collectives™ on Stack Overflow

Exception while reading an sql file and doing database insertion in Java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related