Solved

MySql to sqlite in Java

Posted on 2012-03-22
51
650 Views
Last Modified: 2012-03-26
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.
0
Comment
Question by:Sreejith22
  • 27
  • 24
51 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37755899
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37755905
look at soe recommendations here, still mysqldump wit subseqnet mysl2sqllite is probably the way
http://stackoverflow.com/questions/1665061/transfer-mysql-to-sqlite
0
 

Author Comment

by:Sreejith22
ID: 37755917
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37755925
Was searching for any implementations already done in EE ?

what you mean by that?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37755927
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
 

Author Comment

by:Sreejith22
ID: 37755940
I was looking for any such tool/api merging to java code
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37755965
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
 

Author Comment

by:Sreejith22
ID: 37758485
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37758504
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
 

Author Comment

by:Sreejith22
ID: 37758535
I need to have this script executed from java code and generate the sqlite file from java code.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37758560
are you currently using sqllite form java ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37758586
are you currently using sqllite at all?
0
 

Author Comment

by:Sreejith22
ID: 37758615
>>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
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 37759545
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
 

Author Comment

by:Sreejith22
ID: 37764348
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764373
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
 

Author Comment

by:Sreejith22
ID: 37764417
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764424
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
 

Author Comment

by:Sreejith22
ID: 37764448
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764452
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764457
did you see the message :
"There were errors during conversion. Please review mydb.sql.err and mydb.sql.sql for details"
0
 

Author Comment

by:Sreejith22
ID: 37764463
no.

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

Please review the output I posted above.
0
 

Author Comment

by:Sreejith22
ID: 37764467
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764474
But it shows the same errors I am seeing
0
 

Author Comment

by:Sreejith22
ID: 37764482
are you following exactly the same steps I did?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:for_yan
ID: 37764483
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764485
I think your system lacks sqllite3 command.

Anyway try to od waht I mentioned in the previous post
0
 

Author Comment

by:Sreejith22
ID: 37764489
>>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
 

Author Comment

by:Sreejith22
ID: 37764493
ok, I have all the files now after I removed the commands. Now let me check with java code
0
 

Author Comment

by:Sreejith22
ID: 37764562
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764596
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
 

Author Comment

by:Sreejith22
ID: 37764612
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764656
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
 

Author Comment

by:Sreejith22
ID: 37764714
stat.executeUpdate(ss);

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

Open in new window


Not sure why this happens
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37764726
post your mydb.sql.sql file
0
 

Author Comment

by:Sreejith22
ID: 37764734
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37764817
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764847
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
 

Author Comment

by:Sreejith22
ID: 37764862
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764875
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764880
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37764885
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
 

Author Comment

by:Sreejith22
ID: 37765026
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
 

Author Comment

by:Sreejith22
ID: 37765029
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37765057
- where do you get this change_passwords_log
There is no such table
0
 

Author Comment

by:Sreejith22
ID: 37765063
it is inside the main dump file which I have.
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 500 total points
ID: 37765076
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
 

Author Comment

by:Sreejith22
ID: 37765110
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
 

Author Comment

by:Sreejith22
ID: 37765120
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
 

Author Comment

by:Sreejith22
ID: 37765250
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37768503
post  your .sql.sql file
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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 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…

743 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

13 Experts available now in Live!

Get 1:1 Help Now