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
Solved

Exception while doing database insertion in Java

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
check java version using powershell 13 169
SHA2 certs for IIS AND Java? 2 113
CSV file parsing thru Java 13 34
check mysql insert 12 26
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

840 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