Solved

Exception while doing database insertion in Java

Posted on 2013-02-05
5
382 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 20

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Creating and Managing Databases with phpMyAdmin in cPanel.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
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:

760 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

24 Experts available now in Live!

Get 1:1 Help Now