FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Validating sql statements





jmraker
I would like a program that checks a file that contains several SQL statements taht create tables and add records to them. (mostly from phpmyadmin)

Code:
CREATE TABLE `blog` (
  `blog_id` int(11) NOT NULL auto_increment,
  `blog_title` varchar(255) NOT NULL default '',
  `blog_content` longtext NOT NULL,
  PRIMARY KEY  (`blog_id`)
) ENGINE=MyISAM;

INSERT INTO `blog` VALUES (null, 'item1', 'item1');
INSERT INTO `blog` VALUES (null, 'item2', 'item2');
INSERT INTO `blog` VALUES (null, 'item3', 'item3');


Is there a easy way to validate or test these types of sql statements to check whether or not it will run without leaving traces that it ran the command? Or didn't run the command.
rvec
create new tables and insert lines without leaving traces?
I'd say that's only possible if you drop them afterwards Confused
jmraker
This is for an installer, so it won't start installing and halfway run into sql errors, I want it to leave the tables alone, like if the table 'blog' already exists, that is caught before the install starts. It has to use the phpmyadmin style of export/import file which is a series of SQL statements.
kv
You can use this sql statement (works only with mysql) to see if the table already exists.

Code:

SHOW TABLES [[FROM dbname] LIKE 'tablename']


Depending on whether a row is returned or not, you can execute your create table statement.

This is of course possible if you are executing sqls inside php. If it is plain sql script, it may be more difficult.

As far table creation goes, you can just try creating table. If it already exists, it throws an error. The insert statement gets executed however.
Hogwarts
jmraker wrote:
This is for an installer, so it won't start installing and halfway run into sql errors, I want it to leave the tables alone, like if the table 'blog' already exists, that is caught before the install starts. It has to use the phpmyadmin style of export/import file which is a series of SQL statements.


Use transactions; they'll allow you to roll-back things if something screws up.
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html
kv
Hogwarts wrote:

Use transactions; they'll allow you to roll-back things if something screws up.
http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html


Only DMLs can be rolled back, not DDLs. If you drop or create a table, it is a permanent action and cannot be rolled back, even if it is done within a transaction. Moreover, when you execute a DDL, the DMLs executed till then and are not committed will be automatically committed.
Hogwarts
Ah, really? Damn.

Yeah, I suppose the best way then would be to check for the existence of tables and that you have the permission to create them.
Related topics
SQL Basics
Executing a .sql file through PHP
Database on Frihost site
SQL
best phpmyadmin database exporting format
importing SQL databases
[Community Project] Easy Simple Content Management System
Is this code safe, or not?
MySQL error
mysql syntax error I can't find why
how to selecting this from a mysql table ?
a site where i can learn mysql syntax
Guestbook problem. PLEASE HELP!
Image Position
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.