MySql to sqlite in Java

I have my database in MySql which resides in a machine in the network. I need to convert this db in MySql to sqlite and generate a .db file using java code.

I have the following credentials of MySql db using which I can connect to it.

Hostname - 192.168.0.11
Port: 3306
Username:abc
Password: abc
Database: abc

How can I accomplish this conversion?

Any help in this regard which would lead me to the correct solution would be well appreciated, surely.
Sreejith22Asked:
Who is Participating?
 
for_yanConnect With a Mentor Commented:
Ok, so after you go to any linux or unix system or go to windows system with CYGWIN
in the path and run

mysql-to-sqllite.sh mydb.sql

it will report some errors (don't pay attention)

and will produce file mydb.sql.sql

This is the contents of this file:


begin;
--
-- Table structure for table "tbl_passion_attributes"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "tbl_passion_attributes" (
  "id" integer primary key autoincrement,
  "tbl_passion_attribute_id" bigint(20) DEFAULT '0',
  "code" varchar(10) NOT NULL,
  "name" varchar(100) NOT NULL,
  "keyword" text NOT NULL,
  "tbl_passion_list_id" bigint(20) NOT NULL,
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "tbl_passion_attributes"
--

INSERT INTO "tbl_passion_attributes" VALUES (1,0,'01.A00','Habitat','',1);
INSERT INTO "tbl_passion_attributes" VALUES (2,1,'01.A01','Forest Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (3,1,'01.A03','Desert Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (4,1,'01.A04','Sea Habitat','',0);

--
-- Table structure for table "tbl_passion_lists"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "tbl_passion_lists" (
  "id" integer primary key autoincrement,
  "created" datetime DEFAULT NULL,
  "modified" datetime DEFAULT NULL,
  "code" varchar(10) NOT NULL,
  "description" text NOT NULL,
  "passion" varchar(100) NOT NULL,
  "tbl_passion_list_id" bigint(20) NOT NULL DEFAULT '0',
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "tbl_passion_lists"
--

INSERT INTO "tbl_passion_lists" VALUES (1,NULL,NULL,'01.00.00','','Animals, Pets, Wild Life',0);
INSERT INTO "tbl_passion_lists" VALUES (2,NULL,NULL,'01.01.00','','Domesticated Animals & Pets',1);
INSERT INTO "tbl_passion_lists" VALUES (3,NULL,NULL,'01.01.04','The dog may have been the first animal to be domesticated, and has been the most widely kept working, hunting, and companion animal in human history','Dogs, Pet',2);

--
-- Table structure for table "tbl_profession_lists"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "tbl_profession_lists" (
  "id" integer primary key autoincrement,
  "created" datetime DEFAULT NULL,
  "modified" datetime DEFAULT NULL,
  "code" varchar(10) NOT NULL,
  "description" text NOT NULL,
  "profession" varchar(100) NOT NULL,
  "tbl_profession_list_id" bigint(20) NOT NULL DEFAULT '0',
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "tbl_profession_lists"
--

INSERT INTO "tbl_profession_lists" VALUES (1,NULL,NULL,'11-0000 ','','Management Occupations ',0);
INSERT INTO "tbl_profession_lists" VALUES (2,NULL,NULL,'11-1011.00','','Chief Executives',1);
INSERT INTO "tbl_profession_lists" VALUES (3,NULL,NULL,'11-3031.00','','Financial Managers',1);


--
-- Table structure for table "tbl_profession_attributes"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "tbl_profession_attributes" (
  "id" integer primary key autoincrement,
  "tbl_profession_attribute_id" bigint(20) DEFAULT '0',
  "code" varchar(10) NOT NULL,
  "name" varchar(100) NOT NULL,
  "keyword" text NOT NULL,
  "tbl_profession_list_id" bigint(20) NOT NULL,
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "tbl_profession_attributes"
--

INSERT INTO "tbl_profession_attributes" VALUES (1,0,'15-A00','System Analysis Tools','',6);
INSERT INTO "tbl_profession_attributes" VALUES (146,145,'15-D01','Architects','',0);
INSERT INTO "tbl_profession_attributes" VALUES (145,0,'15-D00','System Architecture','',6);

--
-- Table structure for table "tbl_passion_attribute_mappings"
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "tbl_passion_attribute_mappings" (
  "id" integer primary key autoincrement,
  "passion_codes" varchar(12) NOT NULL,
  "passion_attributes_codes" text NOT NULL,
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "tbl_passion_attribute_mappings"
--

INSERT INTO "tbl_passion_attribute_mappings" VALUES (2,'01.01.04','01.B01,01.D02,01.E03,01.E04,01.F01,01.F02,01.F03');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (3,'02.02.07','02.A01,02.A02,02.A04,02.B01,02.B02,02.B03,02.B04,02.B05,02.B06,02.C02,02.C04,02.D01,02.D02,02.D03,02.E01');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (4,'02.03.07','02.A06,02.A07,02.A08,02.A09,02.B01,02.B02,02.B06,02.C01,02.C02,02.D01,02.D02,02.D03');

commit;

Open in new window



Then you run this Java program below, which will read
mydbl.sql.sql and produce new database in the default folder
named; database is named  testNew3.db - for the code below
(if you run it second time - rename the database to say testNew4.db, etc).

The program requires SQLLite driver.
Download jar from here:

http://files.zentus.com/sqlitejdbc/sqlitejdbc-v056.jar

and make sure it is in the path.

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.substring(0, currentCreate.length()-3);
                   //  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:testNew3.db");
        Statement stat = conn.createStatement();
              for(String ss: creates){

                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){

        }


    }

    public static void main(String[] args) {

        new   ReadMySqlDump();
    }


}

Open in new window

0
 
for_yanCommented:
check these converter tools:
http://www.sqlite.org/cvstrac/wiki?p=ConverterTools

in particualr looka at mysql2sqllite tool, whcih poecess MySql dump into form suitable for sqllite:
https://gist.github.com/943776
0
 
for_yanCommented:
look at soe recommendations here, still mysqldump wit subseqnet mysl2sqllite is probably the way
http://stackoverflow.com/questions/1665061/transfer-mysql-to-sqlite
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Sreejith22Author Commented:
Already saw the first link here before posting - http://www.experts-exchange.com/Apple/Programming/iOS/Q_27181259.html

Was searching for any implementations already done in EE
0
 
for_yanCommented:
Was searching for any implementations already done in EE ?

what you mean by that?
0
 
for_yanCommented:
there cannot be any "implementations in EE" as MySQL is serious database and you defimnetely don't want to write a program to dump it - it will never be as general as the one written by MySQL themsleves
You may write sometimes program in java to re-write say one table from one databse to another but converting the whole database in general way - mkaes no sense to do it other than using existing tools
0
 
Sreejith22Author Commented:
I was looking for any such tool/api merging to java code
0
 
for_yanCommented:
The main question is do you need to copy one or two specific tables?
then you can write osme program to do it.
If you are looking to ciopy the whole databse - it does noty look like there is such toll and the normal way is to use mysqldump
0
 
Sreejith22Author Commented:
Please find attached mysqldump that I need to convert to sqlite db. Using the script attached here, I tried to convert the mysql dump contained in the file to sqlite. No luck, no clues so far with java code.

I have this script and db file in the same directory of my local drive. I know how to open a file using java and read it. But this current task of reading the dumps and generating the corresponding sqlite db seems too hard for me.

Any help with java code snippet using which I can generate the required sqlite db from the attached dumps is much appreciated for sure.
mydb.sql
mysql-to-sqlite.sh
0
 
for_yanCommented:
why are you talking about java - this has nothing to do with any java
and I don't think you need any java for that
0
 
Sreejith22Author Commented:
I need to have this script executed from java code and generate the sqlite file from java code.
0
 
for_yanCommented:
are you currently using sqllite form java ?
0
 
for_yanCommented:
are you currently using sqllite at all?
0
 
Sreejith22Author Commented:
>>are you currently using sqllite form java ?

I have to use sqlite from java

>> are you currently using sqllite at all?

With this, I will start.
0
 
Sreejith22Author Commented:
I get an empty file mydb.sql.db when I run '/home/anees/mysql-to-sqlite.sh' mydb.sql . There is no content inside mydb.sql.db

anees@anees-desktop:~$ cd /home/anees
anees@anees-desktop:~$ '/home/anees/mysql-to-sqlite.sh' mydb.sql 
Error: near line 8: near ")": syntax error
Error: near line 22: no such table: tbl_passion_attributes
Error: near line 23: no such table: tbl_passion_attributes
Error: near line 24: no such table: tbl_passion_attributes
Error: near line 25: no such table: tbl_passion_attributes
Error: near line 33: near ")": syntax error
Error: near line 48: no such table: tbl_passion_lists
Error: near line 49: no such table: tbl_passion_lists
Error: near line 50: no such table: tbl_passion_lists
Error: near line 58: near ")": syntax error
Error: near line 73: no such table: tbl_profession_lists
Error: near line 74: no such table: tbl_profession_lists
Error: near line 75: no such table: tbl_profession_lists
Error: near line 84: near ")": syntax error
Error: near line 98: no such table: tbl_profession_attributes
Error: near line 99: no such table: tbl_profession_attributes
Error: near line 100: no such table: tbl_profession_attributes
Error: near line 108: near ")": syntax error
Error: near line 119: no such table: tbl_passion_attribute_mappings
Error: near line 120: no such table: tbl_passion_attribute_mappings
Error: near line 121: no such table: tbl_passion_attribute_mappings
Conversion completed without error. Output file: mydb.sql.db
anees@anees-desktop:~$ 

Open in new window

0
 
for_yanCommented:
Read attentively what I wrote above - you don't need file mydb.sql.db, it is empty - it is not necessary
You need the file mydb.sql.sql
and this is the file the contents of which I posted above , and my java program reads
file mydb.sql.sql - check - that one is not empty - this is the file which conatins the sql
transferred in the format which SQLlit understands (I also do some final editing in my java program)
0
 
Sreejith22Author Commented:
sorry if Im getting trivial.

>>You need the file mydb.sql.sql

I do not have such a file created when I execute '/home/anees/mysql-to-sqlite.sh' mydb.sql .
Instead I have this file  created - mydb.sql.db
0
 
for_yanCommented:
I don't know - I have such file.
Go to folder /home/anees, copy over there mydb.sql
and run there
mysql-to-sqlite.sh mydb.sql

tehn you'll see error mesaage and you'll see total of 5 files in this folder

mysql-to-sqlite.sh
mydb.sql
mydb.sql.err - with error messages - the same as you saw
mydb.sql.db - empty
mydb.sql.sql - with changed SQL

On what system you are executing ?
0
 
Sreejith22Author Commented:
I am from an Ubuntu machine. Here are the steps I did:

1) I have placed the script file inside /home/anees
2)  I have placed mydb.sql in /home/anees
3) I opened terminal and executed '/home/anees/mysql-to-sqlite.sh' mydb.sql
4) Now I have mydb.sql.db created newly and nothing else.
0
 
for_yanCommented:
It is impossible - I eexcute teh same script I got from you and have this file.

go to the same folder - put everyhting there and go to that folser as a defauilt folder - so ebvereything should be togetehr in one folder

perhpas it writes something to teh same palce where script sits and something to default folder
0
 
for_yanCommented:
did you see the message :
"There were errors during conversion. Please review mydb.sql.err and mydb.sql.sql for details"
0
 
Sreejith22Author Commented:
no.

Conversion completed without error. Output file: mydb.sql.db

Please review the output I posted above.
0
 
Sreejith22Author Commented:
are you from Ubuntu?

I have placed the script file in the same folder in which I have mydb.sql and execute the script from this folder.
0
 
for_yanCommented:
But it shows the same errors I am seeing
0
 
Sreejith22Author Commented:
are you following exactly the same steps I did?
0
 
for_yanCommented:
OK, make a copy of that script file
Then use any editor - open the mysql-to-sqlite.sh file
go close to the bottom of it and
remove commands

rm $1.sql
rm $1.err

and then execute again

Then comapre mysql.sql.sql file with the one I posted above
0
 
for_yanCommented:
I think your system lacks sqllite3 command.

Anyway try to od waht I mentioned in the previous post
0
 
Sreejith22Author Commented:
>>Then comapre mysql.sql.sql file with the one I posted above

Let me see first, I am having the file mysql.sql.sql generated :)
0
 
Sreejith22Author Commented:
ok, I have all the files now after I removed the commands. Now let me check with java code
0
 
Sreejith22Author Commented:
OK. I placed mydb.sql.sql in the path in which src folder resides and I get testNew3.db created after executing java program,  with just a letter 'S' in it. I opened the file with both text editor and sqlite browser.
0
 
for_yanCommented:
What is letter "S" ?

I don't think it should be text file

I attach whet I'm getting  - testNew3.pdf should be renamend to testNew3.db (EE doesnot allow db extension)

Try if you can open it with sqlite browser
testNew3.pdf
0
 
Sreejith22Author Commented:
your db is perfect. I can see the data. But not from mine. PFA

I can see the Create and Inserts getting printed in console.
testNew3.pdf
0
 
for_yanCommented:
This is the output on java program.
Do you have the bottom part ,where it says

INSERT INTO ...
n:1
INSERT INTO ...
n:1
...

If not - compare your input file mydb.sql.sql
with my file which I posted above ID: 37759545



CREATE TABLE "tbl_passion_attributes" (
 "id" integer primary key autoincrement,
 "tbl_passion_attribute_id" bigint(20) DEFAULT '0',
 "code" varchar(10) NOT NULL,
 "name" varchar(100) NOT NULL,
 "keyword" text NOT NULL,
 "tbl_passion_list_id" bigint(20) NOT NULL );

CREATE TABLE "tbl_passion_lists" (
 "id" integer primary key autoincrement,
 "created" datetime DEFAULT NULL,
 "modified" datetime DEFAULT NULL,
 "code" varchar(10) NOT NULL,
 "description" text NOT NULL,
 "passion" varchar(100) NOT NULL,
 "tbl_passion_list_id" bigint(20) NOT NULL DEFAULT '0' );

CREATE TABLE "tbl_profession_lists" (
 "id" integer primary key autoincrement,
 "created" datetime DEFAULT NULL,
 "modified" datetime DEFAULT NULL,
 "code" varchar(10) NOT NULL,
 "description" text NOT NULL,
 "profession" varchar(100) NOT NULL,
 "tbl_profession_list_id" bigint(20) NOT NULL DEFAULT '0' );

CREATE TABLE "tbl_profession_attributes" (
 "id" integer primary key autoincrement,
 "tbl_profession_attribute_id" bigint(20) DEFAULT '0',
 "code" varchar(10) NOT NULL,
 "name" varchar(100) NOT NULL,
 "keyword" text NOT NULL,
 "tbl_profession_list_id" bigint(20) NOT NULL );

CREATE TABLE "tbl_passion_attribute_mappings" (
 "id" integer primary key autoincrement,
 "passion_codes" varchar(12) NOT NULL,
 "passion_attributes_codes" text NOT NULL );



INSERT INTO "tbl_passion_attributes" VALUES (1,0,'01.A00','Habitat','',1);
INSERT INTO "tbl_passion_attributes" VALUES (2,1,'01.A01','Forest Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (3,1,'01.A03','Desert Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (4,1,'01.A04','Sea Habitat','',0);
INSERT INTO "tbl_passion_lists" VALUES (1,NULL,NULL,'01.00.00','','Animals, Pets, Wild Life',0);
INSERT INTO "tbl_passion_lists" VALUES (2,NULL,NULL,'01.01.00','','Domesticated Animals & Pets',1);
INSERT INTO "tbl_passion_lists" VALUES (3,NULL,NULL,'01.01.04','The dog may have been the first animal to be domesticated, and has been the most widely kept working, hunting, and companion animal in human history','Dogs, Pet',2);
INSERT INTO "tbl_profession_lists" VALUES (1,NULL,NULL,'11-0000 ','','Management Occupations ',0);
INSERT INTO "tbl_profession_lists" VALUES (2,NULL,NULL,'11-1011.00','','Chief Executives',1);
INSERT INTO "tbl_profession_lists" VALUES (3,NULL,NULL,'11-3031.00','','Financial Managers',1);
INSERT INTO "tbl_profession_attributes" VALUES (1,0,'15-A00','System Analysis Tools','',6);
INSERT INTO "tbl_profession_attributes" VALUES (146,145,'15-D01','Architects','',0);
INSERT INTO "tbl_profession_attributes" VALUES (145,0,'15-D00','System Architecture','',6);
INSERT INTO "tbl_passion_attribute_mappings" VALUES (2,'01.01.04','01.B01,01.D02,01.E03,01.E04,01.F01,01.F02,01.F03');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (3,'02.02.07','02.A01,02.A02,02.A04,02.B01,02.B02,02.B03,02.B04,02.B05,02.B06,02.C02,02.C04,02.D01,02.D02,02.D03,02.E01');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (4,'02.03.07','02.A06,02.A07,02.A08,02.A09,02.B01,02.B02,02.B06,02.C01,02.C02,02.D01,02.D02,02.D03');
ss: INSERT INTO "tbl_passion_attributes" VALUES (1,0,'01.A00','Habitat','',1);
n: 1
ss: INSERT INTO "tbl_passion_attributes" VALUES (2,1,'01.A01','Forest Habitat','',0);
n: 1
ss: INSERT INTO "tbl_passion_attributes" VALUES (3,1,'01.A03','Desert Habitat','',0);
n: 1
ss: INSERT INTO "tbl_passion_attributes" VALUES (4,1,'01.A04','Sea Habitat','',0);
n: 1
ss: INSERT INTO "tbl_passion_lists" VALUES (1,NULL,NULL,'01.00.00','','Animals, Pets, Wild Life',0);
n: 1
ss: INSERT INTO "tbl_passion_lists" VALUES (2,NULL,NULL,'01.01.00','','Domesticated Animals & Pets',1);
n: 1
ss: INSERT INTO "tbl_passion_lists" VALUES (3,NULL,NULL,'01.01.04','The dog may have been the first animal to be domesticated, and has been the most widely kept working, hunting, and companion animal in human history','Dogs, Pet',2);
n: 1
ss: INSERT INTO "tbl_profession_lists" VALUES (1,NULL,NULL,'11-0000 ','','Management Occupations ',0);
n: 1
ss: INSERT INTO "tbl_profession_lists" VALUES (2,NULL,NULL,'11-1011.00','','Chief Executives',1);
n: 1
ss: INSERT INTO "tbl_profession_lists" VALUES (3,NULL,NULL,'11-3031.00','','Financial Managers',1);
n: 1
ss: INSERT INTO "tbl_profession_attributes" VALUES (1,0,'15-A00','System Analysis Tools','',6);
n: 1
ss: INSERT INTO "tbl_profession_attributes" VALUES (146,145,'15-D01','Architects','',0);
n: 1
ss: INSERT INTO "tbl_profession_attributes" VALUES (145,0,'15-D00','System Architecture','',6);
n: 1
ss: INSERT INTO "tbl_passion_attribute_mappings" VALUES (2,'01.01.04','01.B01,01.D02,01.E03,01.E04,01.F01,01.F02,01.F03');
n: 1
ss: INSERT INTO "tbl_passion_attribute_mappings" VALUES (3,'02.02.07','02.A01,02.A02,02.A04,02.B01,02.B02,02.B03,02.B04,02.B05,02.B06,02.C02,02.C04,02.D01,02.D02,02.D03,02.E01');
n: 1
ss: INSERT INTO "tbl_passion_attribute_mappings" VALUES (4,'02.03.07','02.A06,02.A07,02.A08,02.A09,02.B01,02.B02,02.B06,02.C01,02.C02,02.D01,02.D02,02.D03');
n: 1

Open in new window

0
 
Sreejith22Author Commented:
stat.executeUpdate(ss);

Inside exceptionjava.sql.SQLException: near "NUL": syntax error

Open in new window


Not sure why this happens
0
 
for_yanCommented:
post your mydb.sql.sql file
0
 
Sreejith22Author Commented:
0
 
for_yanCommented:
This is the file I careted form your .sql.sql file which you just posted  and my code
whih I copied from my post  ID: 37759545
and compiled and just executed on another coomputer
As you see it works without problem.

Must work for you also.

Are you using my code exactly without any changes?

are you sure you downloaded and have have this .jar

http://files.zentus.com/sqlitejdbc/sqlitejdbc-v056.jar

in your classpath?
testNew3.pdf
0
 
for_yanCommented:
Use this code (exactly)
and it should report exception stack trace if it does not work.
Post the whole trace if you see the error

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.substring(0, currentCreate.length()-3);
                   //  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){

                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

0
 
Sreejith22Author Commented:
CREATE TABLE "tbl_passion_attributes" (
 "id" integer primary key autoincrement,
 "tbl_passion_attribute_id" bigint(20) DEFAULT '0',
 "code" varchar(10) NOT NULL,
 "name" varchar(100) NOT NULL,
 "keyword" text NOT NULL,
 "tbl_passion_list_id" bigint(20) NOT NUL );

CREATE TABLE "tbl_passion_lists" (
 "id" integer primary key autoincrement,
 "created" datetime DEFAULT NULL,
 "modified" datetime DEFAULT NULL,
 "code" varchar(10) NOT NULL,
 "description" text NOT NULL,
 "passion" varchar(100) NOT NULL,
 "tbl_passion_list_id" bigint(20) NOT NULL DEFAULT '0 );

CREATE TABLE "tbl_profession_lists" (
 "id" integer primary key autoincrement,
 "created" datetime DEFAULT NULL,
 "modified" datetime DEFAULT NULL,
 "code" varchar(10) NOT NULL,
 "description" text NOT NULL,
 "profession" varchar(100) NOT NULL,
 "tbl_profession_list_id" bigint(20) NOT NULL DEFAULT '0 );

CREATE TABLE "tbl_profession_attributes" (
 "id" integer primary key autoincrement,
 "tbl_profession_attribute_id" bigint(20) DEFAULT '0',
 "code" varchar(10) NOT NULL,
 "name" varchar(100) NOT NULL,
 "keyword" text NOT NULL,
 "tbl_profession_list_id" bigint(20) NOT NUL );

CREATE TABLE "tbl_passion_attribute_mappings" (
 "id" integer primary key autoincrement,
 "passion_codes" varchar(12) NOT NULL,
 "passion_attributes_codes" text NOT NUL );



INSERT INTO "tbl_passion_attributes" VALUES (1,0,'01.A00','Habitat','',1);
INSERT INTO "tbl_passion_attributes" VALUES (2,1,'01.A01','Forest Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (3,1,'01.A03','Desert Habitat','',0);
INSERT INTO "tbl_passion_attributes" VALUES (4,1,'01.A04','Sea Habitat','',0);
INSERT INTO "tbl_passion_lists" VALUES (1,NULL,NULL,'01.00.00','','Animals, Pets, Wild Life',0);
INSERT INTO "tbl_passion_lists" VALUES (2,NULL,NULL,'01.01.00','','Domesticated Animals & Pets',1);
INSERT INTO "tbl_passion_lists" VALUES (3,NULL,NULL,'01.01.04','The dog may have been the first animal to be domesticated, and has been the most widely kept working, hunting, and companion animal in human history','Dogs, Pet',2);
INSERT INTO "tbl_profession_lists" VALUES (1,NULL,NULL,'11-0000 ','','Management Occupations ',0);
INSERT INTO "tbl_profession_lists" VALUES (2,NULL,NULL,'11-1011.00','','Chief Executives',1);
INSERT INTO "tbl_profession_lists" VALUES (3,NULL,NULL,'11-3031.00','','Financial Managers',1);
INSERT INTO "tbl_profession_attributes" VALUES (1,0,'15-A00','System Analysis Tools','',6);
INSERT INTO "tbl_profession_attributes" VALUES (146,145,'15-D01','Architects','',0);
INSERT INTO "tbl_profession_attributes" VALUES (145,0,'15-D00','System Architecture','',6);
INSERT INTO "tbl_passion_attribute_mappings" VALUES (2,'01.01.04','01.B01,01.D02,01.E03,01.E04,01.F01,01.F02,01.F03');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (3,'02.02.07','02.A01,02.A02,02.A04,02.B01,02.B02,02.B03,02.B04,02.B05,02.B06,02.C02,02.C04,02.D01,02.D02,02.D03,02.E01');
INSERT INTO "tbl_passion_attribute_mappings" VALUES (4,'02.03.07','02.A06,02.A07,02.A08,02.A09,02.B01,02.B02,02.B06,02.C01,02.C02,02.D01,02.D02,02.D03');
java.sql.SQLException: near "NUL": 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:77)
	at ReadMySqlDump.main(ReadMySqlDump.java:107)

Open in new window


please check the line  "passion_attributes_codes" text NOT NUL ); - dont know how it comes!
0
 
for_yanCommented:
Use this code (exactly) and post the output:

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.substring(0, currentCreate.length()-3);
                   //  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

0
 
for_yanCommented:
I dn't know how you can have "NOT NUL" when I'm having "NOT NULL" with the same input and the same code
0
 
for_yanCommented:
OK, try this code:

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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.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

0
 
Sreejith22Author Commented:
SQL command: CREATE TABLE "change_password_logs" (
 "id" integer primary key autoincrement,
 "created" datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 "modified" datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 "user_id" bigint(20) NOT NULL DEFAULT '0',
 "current_password" varchar(100) NOT NULL DEFAULT '',
 "status" varchar(100) NOT NULL DEFAULT ' );

java.sql.SQLException: unrecognized token: "' );
"
	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:80)
	at ReadMySqlDump.main(ReadMySqlDump.java:110)

Open in new window

0
 
Sreejith22Author Commented:
how did you fix the previous issue -  "NOT NUL"

If the new issue I have posted is something similar, I will try resolving it.
0
 
for_yanCommented:
- where do you get this change_passwords_log
There is no such table
0
 
Sreejith22Author Commented:
it is inside the main dump file which I have.
0
 
for_yanConnect With a Mentor Commented:
I don't know if your other dump follows different format.

try this:

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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

0
 
Sreejith22Author Commented:
again. I am sorry for this. I keep on getting these when I apply this to main dump

SQL command: CREATE TABLE "tbl_appss" (
 "id" integer primary key autoincrement,
 "apps_content_code" varchar(10) NOT NULL,
 "apps_name" varchar(100) CHARACTER SET utf8 NOT NULL,
 "apps_producer_code" varchar(8) CHARACTER SET utf8 NOT NULL,
 "apps_url_source" text CHARACTER SET utf8 NOT NULL,
 "apps_desc" text CHARACTER SET utf8 NOT NULL,
 "apps_paid_app" integer NOT NULL,
 "apps_charge_basis" varchar(4) CHARACTER SET utf8 NOT NULL,
 "apps_street_price" varchar(5) CHARACTER SET utf8 NOT NULL,
 "apps_price_currency" varchar(3) CHARACTER SET utf8 NOT NULL,
 "apps_license_period" datetime NOT NULL,
 "apps_discount_type" integer NOT NULL,
 "apps_discount" varchar(5) CHARACTER SET utf8 NOT NULL,
 "apps_download_size" integer NOT NULL,
 "apps_rating" integer NOT NULL );

Open in new window

0
 
Sreejith22Author Commented:
java.sql.SQLException: near "CHARACTER": 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:83)
	at ReadMySqlDump.main(ReadMySqlDump.java:113)

Open in new window

0
 
Sreejith22Author Commented:
I removed CHARACTER SET utf8  from the entire .sql.sql file and it worked. So, I presume a removal code for CHARACTER SET utf8 has to be included to make this work.

Also,

Main dump insert query:
INSERT INTO "tbl_appwidgets" VALUES (2,'AGM00AB003','FXCM Mobile TSII','H','http://www.freewarelovers.com/android/download/temp/1297271311_FXCM_Mobile_TSII_1.0.6.apk','FXCM Mobile Trading Station II (FXCM Mobile TSII) is an app that allows you to trade Forex anytime anywhere. FXCM Mobile TSII gives Standard, Micro* & Practice account holders the ability to keep track of your account, place trades, close open positions, watch breaking market news, and real-time charts.

*Micro clients will be charged $0.10 per lot per trade.',0,'','','','0000-00-00 00:00:00',0,'',3,2);

Open in new window


From the java code, part of this query is clipped off which results in exception.

ss: INSERT INTO "tbl_appwidgets" VALUES (2,'AGM00AB003','FXCM Mobile TSII','H','http://www.freewarelovers.com/android/download/temp/1297271311_FXCM_Mobile_TSII_1.0.6.apk','FXCM Mobile Trading Station II (FXCM Mobile TSII) is an app that allows you to trade Forex anytime anywhere. FXCM Mobile TSII gives Standard, Micro* & Practice account holders the ability to keep track of your account, place trades, close open positions, watch breaking market news, and real-time charts.
java.sql.SQLException: unrecognized token: "'FXCM Mobile Trading Station II (FXCM Mobile TSII) is an app that allows you to trade Forex anytime anywhere. FXCM Mobile TSII gives Standard, Micro* & Practice account holders the ability to keep track of your account, place trades, close open positions, watch breaking market news, and real-time charts."
	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:97)
	at ReadMySqlDump.main(ReadMySqlDump.java:116)

Open in new window

0
 
for_yanCommented:
post  your .sql.sql file
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.