• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

using regex matcher to match an INSERT statement

I have an insert statement that is of the form

INSERT INTO pubs VALUES (16590,'Header','HEADER1995','1995-00-00',1549,NULL,'tp','NOVEL',NULL,NULL,'$5.95',NULL),(16591,'Headhunter','BKTG16613','1993-00-00',11,'422','pb','NOVEL','0450591123',NULL,'L4.99',NULL),(16592,'Headhunter','BKTG03173','1994-00-00',282,'440','hc','NOVEL','0517598272',NULL,'$23.00',NULL);

However, it's something like a million characters long. It's from a mysql export that I need to import into a different database. My system is failing from stack overflows.

To fix it I want to use a regex Matcher to go through and for every 100 values, insert a new INSERT line. So, if we split it for every one, the result would be to convert this:
  INSERT INTO pubs VALUES (1,2,3),(2,3,4),(4,5,6)
to this:
  INSERT INTO pubs VALUES (1,2,3);
  INSERT INTO pubs VALUES (2,3,4);
  INSERT INTO pubs VALUES (4,5,6)

I don't know how to write a regex that will properly match everything including string values that may contain delimeters. For instance, I have no idea how to match against something like:
  INSERT INTO pubs VALUES ('I (by which I mean we), really like Jill''s face.');

Alternatively, I suppose you could tell me how to get derby to not choke on the original megabyte-long insert statement.
0
HappyEngineer
Asked:
HappyEngineer
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
gkishorejiCommented:
if u r sure that the string inside the insert statements wont contain ),(

you can use it to split the strings

)[ ]*,[ ]*(   --> This will match   ),(
replace it with
); INSERT INTO pubs VALUES (
0
 
Ajay-SinghCommented:
use loop around the insert statement,
a. create PreparedStatement
b. in a loop bind the parameters
c. commit.
0
 
Ajay-SinghCommented:
pls ignore the last comment
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.

 
CEHJCommented:
>>However, it's something like a million characters long.

You mean one single import statement is? You should be able to run the file against the new db given the correct setup of the target db
0
 
hoomanvCommented:
That insert seems like the output of a database dump
Most dump tools support two ways of generating insert statements
i.e. musqldump.exe by default uses --extended-insert but you can disable it to have single insert for each tuple
0
 
objectsCommented:
agree with hoomanv, change the way you export the data
0
 
HappyEngineerAuthor Commented:
I didn't export the data. I don't have control over the way it is exported.
0
 
objectsCommented:
did u try gkishoreji suggestion, looks like you nned to replace all:

),(

with

);
INSERT INTO pubs VALUES (

(not really a java question, you can do the conversion usiong something like awk)
0
 
hoomanvCommented:
0
 
hoomanvCommented:
It seems that ZQL does not accept multi value insert statements
OK for you a pretty handy solution is to go here http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
Paste your statement, and the script will beautify it so each tuple appears in a new line
Now its easy to separate them
0
 
HappyEngineerAuthor Commented:
I split it up at the ),( boundaries by first using the pattern:

    "INSERT INTO ([a-zA-Z0-9_]+) VALUES .*;\n"

then for each match I did:
    String insertStr = rowMatcher.group(0);
    String tableName = rowMatcher.group(1);
    insertStr = insertStr.replaceAll("\\),\\(", ");\nINSERT INTO "+ tableName +" VALUES (");

However, the tool that hoomanv mentioned also looks interesting. Sadly, it's unlikely it would work to upload a 50MB file to it. But, if necessary I probably could have contacted the site to get the code.
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.

Join & Write a Comment

Featured Post

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.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now