Page 1 of 1

how to invoke a set of update statements

Posted: Tue Dec 12, 2006 3:05 am
by jasongr
Hi people

I have a file that contains many SQL statements that are generated in runtime.
These are queries in the following syntax:

Code: Select all

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `ID` int(10) unsigned not null,
...
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# comment here

insert into table1 (`ID`, ....) values (1, ...);
insert into table1 (`ID`, ....) values (2, ...);
insert into table1 (`ID`, ....) values (3, ...);
Basically, the queries in the file fall under 4 formats:
1) drop table statements
2) create table statements
3) comments
4) insert statements

The file can be very large.
I need some way of invoking ALL the queries in the file using function mysql_query.

Initially what I did was to split the content of the file by ';'
each splitted part was invoked separately. This worked fine, until a semicolon was found in one of the "insert into" statements and that broke my code:

Code: Select all

insert into table1 (`ID`, `Text`) values (1, 'This string contains ;');
I am looking for a robust way to parse a file that contains SQL statements.
Possibly using regular expressions can achieve a more robust parsing.
If I can invoke all the sql statements in the file without any parsing, that would be perfect

any suggestions?

regards

Posted: Tue Dec 12, 2006 3:16 am
by matthijs
Maybe use the file function, which reads a file line after line and returns an array (for each line). Only problem would be that you should write each statement on one single line.

But maybe it's easier to place the statements in a db table?
Or a php file (in which you place an array of the statements)?

Posted: Tue Dec 12, 2006 3:27 am
by jasongr
I have no control over the content of the file.
As it is produced by a third party.
All I know is that its content must be a set of valid SQL statements.
For example,
the statements may not even be separated by newlines (all the statements will be in one long line in the file)
Some of the statements may be separated by newlines and others by white space...

I need a robust way to separate the statements which is based on the fact that these are valid sql statements

regards

Posted: Tue Dec 12, 2006 3:47 am
by matthijs
In that case it will be - probably very complicated - regex. I can't help you with that I'm afraid.

Posted: Tue Dec 12, 2006 5:41 am
by volka
Or maybe you can adopt some code of phpmyadmin for that matter.

Posted: Tue Dec 12, 2006 7:54 am
by feyd
read through raghavan20's threads in Regex. ;)