?
Solved

Exception while doing database insertion in Java

Posted on 2013-02-05
5
Medium Priority
?
396 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 P
Amitkumar P earned 400 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 36

Accepted Solution

by:
mccarl earned 1600 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 36

Expert Comment

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

A solution for Fortify Path Manipulation.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

850 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