ruby:using regex i want to split sql statements

Question

i'm using ruby and sinatra and oci8 and i want to run an sql file which contain multiple sql statements but oci8 just can let me execute one statement

str="INSERT INTO AA(NAME) VALUES ('asasa')"
@conn1 = OCI8.new(DB_USER, DB_PASSWORD, DB_SERVER)
@conn1.exec(str)

for that i have to split all sql statements by using regex but i had a problem in that,i did'nt find a regex which let me do this. i want to split all statments

example of an sql file ;

create table users (name varchar2(20),age int);
begin
insert into users values('amine5',20);
insert into users values('amine5',30);
end;

the file can contain all sql statements

It's probably not a good idea to do this and you should probably just execute the SQL directly without OCI8 for these initial inserts but anyway, a question - will these only be insert statements? — Mark Silverberg, Aug 17 '14 at 00:45
no i'm dealing with all sql statements,i know that's difficult to do this but i don't know any gem which can read an sql fil and execute it or which can execute many sql statements in the same time — sakhri Mohamed Amine, Aug 17 '14 at 15:32

score 0 · Answer 1 · answered Aug 17 '14 at 00:46

Here's a start for you if you are only dealing with insert statements.

string2.0.0-p353 :001 > string =<<-EOS
2.0.0-p353 :002"> insert into users values('amine5','sdfsdf');
2.0.0-p353 :003"> begin
2.0.0-p353 :004"> insert into users values('amine5','sdfsdf');
2.0.0-p353 :005"> insert into users values('amine5','sdfsdf');
2.0.0-p353 :006"> end;
2.0.0-p353 :007"> insert into users values('amine5','sdfsdf');
2.0.0-p353 :008"> EOS
 => "insert into users values('amine5','sdfsdf');\nbegin\ninsert into users values('amine5','sdfsdf');\ninsert into users values('amine5','sdfsdf');\nend;\ninsert into users values('amine5','sdfsdf');\n" 
2.0.0-p353 :012 > string.scan(/insert.+;/i)
 => ["insert into users values('amine5','sdfsdf');", "insert into users values('amine5','sdfsdf');", "insert into users values('amine5','sdfsdf');", "insert into users values('amine5','sdfsdf');"]

Rubular permalink: http://rubular.com/r/Ffrd4swl4z

i'm not only dealing with insert statements,i'm dealing with all sql statements thanks for your response — sakhri Mohamed Amine, Aug 17 '14 at 12:03

ruby:using regex i want to split sql statements

1 Answers1