Create database from perl script help

[bobekdj]

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) 
 
);

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(); 
 
######################################################################
+##########

Thank you for your help

[Tanktalus]

Two points:

1. 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...)
2. 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

[hangon]

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) 
   )
) );

[kdj]

What kind of database are you using? This sounds an awful lot like a job for a Stored Procedure.

[mje]

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?

[Anonymous Monk]

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