Solved

Exception while doing database insertion in Java

Posted on 2013-02-05
5
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 36

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 36

Expert Comment

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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

695 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