Solved

Exception while doing database insertion in Java

Posted on 2013-02-05
5
383 Views
Last Modified: 2013-02-06
From an sql file, I am trying to 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();
    }


}

Open in new window


The create statements work perfect; 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)

Open in new window


I do not know what causes this. The above code is an executable java file.Any help in resolving the issue, such that, I can do insertion to the tables that have been created, would be well appreciated with points.

The jdbc jar for the code above, can be downloaded from here


Also, I have attached the sql file mydb.sql.sql I use. Since experts-exchange does not allow sql attachments, I have renamed that to mydb.sql.pdf. Please rename the same to mydb.sql.sql for using with above code.
mydb.sql.pdf
0
Comment
Question by:Sreejith22
  • 2
  • 2
5 Comments
 
LVL 21

Assisted Solution

by:Amitkumar Panchal
Amitkumar Panchal earned 100 total points
ID: 38858263
Looking at the code, it seems that Insert statement is not generated properly. Check the value of inserts by printing it on console. It should show INSERT INTO acos (created, modified, id, parent_id, model, foreign_key, alias, lft, rght) VALUES, not the values. You need to add some logic so that insert statements recognize properly (similar to that you did for create statement (if(createStart && buff.startsWith(");")){))
0
 
LVL 35

Accepted Solution

by:
mccarl earned 400 total points
ID: 38858287
There are TWO issues here...

The first is that your INSERT statements are multi-line just like your CREATE statements, so in theory you should add logic like you did with your CREATE statement, to collect the entire INSERT statement from the multiple line before inserting it into your list.

*HOWEVER* the second problem you have is that SQLite doesn't accept the INSERT statement in this form. It will only accept ONE set of values for each INSERT statement. So you would need to convert this multi-line INSERT ...
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', 3, 2, NULL, NULL, 'display', 3, 4),
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 4, 2, NULL, NULL, 'add', 5, 6),
('0000-00-00 00:00:00', '0000-00-00 00:00:00', 5, 2, NULL, NULL, 'edit', 7, 8);

Open in new window

... into these separate INSERT statements ...
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', 3, 2, NULL, NULL, 'display', 3, 4);
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', 4, 2, NULL, NULL, 'add', 5, 6);
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', 5, 2, NULL, NULL, 'edit', 7, 8);

Open in new window

So I would suggest the logic go something like...
- Flag the start of an INSERT statement and capture the line up to and including the VALUES word.
- Then for each line of values (this can be determined by the opening/closing paranthesis and the comma/semi-colon at the end of line) prepend the capture INSERT .... VALUES from above to the actual line of values and put this into your "inserts" list.

If the above is unclear, let me know and I can help further!
0
 

Author Comment

by:Sreejith22
ID: 38858289
hmm. this code was not written by me. so, need to first study it then and solve!!
0
 

Author Comment

by:Sreejith22
ID: 38858564
Thanks, resolved it with the following code as you suggested:

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;
                }

Open in new window

0
 
LVL 35

Expert Comment

by:mccarl
ID: 38859277
;) I'm happy to have helped!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Basic Java Case or If-Else statement... 3 43
problems with mysql ODBC Connector and vbscript 4 28
updating the date data 12 25
expectj telnet failing 5 26
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now