Create database from perl script help

bobekdj has asked for the wisdom of the Perl Monks concerning the following question:

Hey everyone, quick question, can anyone help me turn this code into something I can use in the DBI class to create a database if a button on a form is clicked. I have managed to make the button work, but am having trouble combining these two codes, although, there may be another one i should use instead of "do"

This is the code i want to run off a form button:

    

CREATE DATABASE cag_repeat; 
 
DROP TABLE cag_repeat.CAG_MAIN; 
 
CREATE TABLE cag_repeat.CAG_MAIN 
 
( 
 
   ID          INT(10)       NOT NULL auto_increment, 
 
   SEQUENCE_NAME  VARCHAR(100)  NOT NULL, 
 
   SEQUENCE        TEXT          NOT NULL, 
 
   NOREPEATS      VARCHAR(100)  NOT NULL, 
   LOCATION       VARCHAR(100)  NOT NULL, 
   WARNINGLVL     VARCHAR(20)   NOT NULL, 
   PRIMARY KEY (ID) 
 
);
[download]

And this is what i believe could work, but i dont know the syntax to make it work:
(using this do command to actually run the code above, instead of using it like it is below to insert data into the database)

    

############################# Insert Into Database ###################
+########## 
 
my $dbh = DBI->connect($dsn, $user, $password) or die "Unable to conne
+ct: $DBI::errstr\n"; 
 
my $row = $dbh->do("INSERT INTO cag_repeat.CAG_MAIN 
(SEQUENCE_NAME,SEQUENCE,NOREPEATS,LOCATION,WARNINGLVL) 
VALUES('$seqname','$sequence','$len','$amino_acid_number','$riskeval')
+;"); 
 
$dbh->disconnect(); 
 
######################################################################
+##########
[download]

Thank you for your help

Comment on Create database from perl script help Select or Download Code

Replies are listed 'Best First'.
Re: Create database from perl script help by Tanktalus (Canon) on Dec 15, 2008 at 21:46 UTC
Two points: USE PLACEHOLDERS. Do not EVER do anything like `VALUES('$seqname','$sequence','$len','$amino_acid_number','$riskeval')`. Instead, do `VALUES(?,?,?,?,?)`, and pass in `$seqname, $sequence, $len, $amino_acid_number, $riskeval` as extra parameters. (You'll be able to go from here to faster things later, but one step at a time...) Most DBD drivers don't allow multiple statements. Try calling do three times: one for your create db, one for your drop table, and a third for your create table. Hope that helps	[reply] [d/l] [select]
Re: Create database from perl script help by hangon (Deacon) on Dec 15, 2008 at 23:34 UTC
Is this what you're looking for? `$dbh->do( qq(CREATE DATABASE cag_repeat) ); $dbh->do( qq(DROP TABLE cag_repeat.CAG_MAIN) ); $dbh->do( qq(CREATE TABLE cag_repeat.CAG_MAIN ( ID INT(10) NOT NULL auto_increment, SEQUENCE_NAME VARCHAR(100) NOT NULL, SEQUENCE TEXT NOT NULL, NOREPEATS VARCHAR(100) NOT NULL, LOCATION VARCHAR(100) NOT NULL, WARNINGLVL VARCHAR(20) NOT NULL, PRIMARY KEY (ID) ) ) );` [download]	[reply] [d/l]
Re: Create database from perl script help by kdj (Friar) on Dec 15, 2008 at 21:56 UTC
What kind of database are you using? This sounds an awful lot like a job for a Stored Procedure.	[reply]
Re: Create database from perl script help by mje (Curate) on Dec 15, 2008 at 21:59 UTC
We do not know what DBD you are using. Assuming the create database works the CAG_MAIN table cannot exist. As another monk said, use placeholders. I do not see anything else wrong with your do method call to insert, what is the error you get?	[reply]
Re^2: Create database from perl script help by Anonymous Monk on Dec 15, 2008 at 22:34 UTC
No, inserting the data into the database works fine, which i have stated. It is a mySQL database (sorry for not including the) what i want to do is use a command to run the first script, which creates a database, and formatted table, the second code was an example of a do command, and has NOTHING TO DO WITH WHAT I WANT TO DO	[reply]