Solved

Perl and databases

Posted on 2007-11-26
10
187 Views
Last Modified: 2010-03-05
I have a script that an expert here helped me write and I am trying to get it now to create the TABLE in the database, any suggestions?

I am stuck on that part, the writing.

I have the script here - http://elan.champlain.edu/~rcoughlin32001/cgi-bin/table.cgi

I placed in all of my connection information, etc. I want to write this to the DB:

            >> print(" CREATE TABLE $table( $tableFields );");

Thanks!
Ryan
#!/usr/bin/perl
use CGI qw(:standard);
use DBI;
print "Content-type:text/html \n\n";
 
 
# Ryan Coughlin
# Server Side Scripting
# Breaks code down in to subs
# sub header - print out top HTML code (body tags)
# sub footer - ends HTML page
# sub first  - enter table name and number of fields
# sub second - enter details on each field
# sub three  - prints out SQL create table code
 
# connection details for connection to database
 
$database = "assignments";
$username = "web320";
$password = "web320pass";
 
# make connection string
 
$dsn      = "DBI:mysql:$database:localhost";
 
# make actual connection
 
$dbh=DBI->connect($dsn,$username,$password); # if a password is not required renove $password
 
 
 
 
my $step = param('step') || "START"; # what step are you on
 
if( $step eq "Create Table")
{
      my $table   = param('tableName')   || "TEST";    # table name
      my $fields  = param('fieldCount')  || 0;         # number of fields
      third($table,$fields);
}
elsif( $step eq "Submit")
{
      my $table   = param('tableName');                # table name
      my $fields  = param('fieldCount') || 0;          # number of fields
 
      second("table.cgi","post",$table, $fields);
}
else
{
      first("table.cgi","post");
}
footer();
 
##logic ends here. Only subroutines follow
 
sub header{
my ($pageTitle,$headCode)=@_;
 
print <<HEADER;
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>$pageTitle</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
$headCode
</head>
 
<body>
HEADER
 
}
 
sub footer{
print <<FOOTER;
</body>
</html>
FOOTER
 
}
 
 
# first step type in table name and number of fields
sub first{
my ($action,$method)=@_;
 
print <<FIRST;
<h2>Building and Using a Database</h2>
<h2>Step 1: Name of Table and Number of Fields</h2>
<form id="form1" method="$method" action="$action">
  <p><b>Table Name:</b>
    <input name="tableName" type="text" size="10" maxlength="10" />
 
  </p>
  <p><b>Number of Fields</b>:
    <input name="fieldCount" type="text" size="10" maxlength="10" />
  </p>
  <p>
    <input type="submit" name="step" value="Submit" />
  </p>
 
</form>
 
<p>&nbsp;</p>
FIRST
 
}
 
# type in field characteristics
sub second{
# print out outer HTML form/table, etc.
my ($action,$method,$table,$fields) = @_;
 
print <<HTML;
 
<form id="form1" method="$method" action="$action">
      <input type="hidden" name="tableName" value="$table" />
      <input type="hidden" name="fieldCount" value="$fields" />
      <table width="60%" border="2" cellspacing="0" cellpadding="0">
          <caption>$table</caption>
            <tr>
                  <td><b>FIELD NAME</b></td>
                  <td><b>FIELD TYPE</b></td>
                  <td><b>FIELD LENGTH</b></td>
            </tr>
HTML
 
# print out the number of fields specified in step 1 us basic for loop statement
for ($counter=1; $counter <= $fields; ++$counter){
      print <<HTML1;
            <tr>
                  <td><input name="fieldname$counter" type="text" size="30" maxlength="30" /></td>
                  <td>
                        <select name="fieldtype$counter">
                              <option value="char">char</option>
                              <option value="date">date</option>
                              <option value="float">float</option>
                              <option value="int">int</option>
                              <option value="text">text</option>
                              <option value="varchar">varchar</option>
                        </select>
                  </td>
                  <td><input name="fieldlength$counter" type="text"  size="5" maxlength="5" /></td>
            </tr>
       
HTML1
 
}
print <<HTML3;
      </table>
 
      <p><input type="submit" name="step" value="Create Table" /></p>
</form>
 
HTML3
 
}
 
# prints out SQL code - will print all fields and characteristics
sub third{
my ($table,$fieldCount) = @_;
 
my $tableFields = "";
for($i=1; $i<$fieldCount; ++$i)
{
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . "(" . param("fieldlength$i") . "), ";
}
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . " (" . param("fieldlength$i") . ")";
 
# print out
print(" CREATE TABLE $table( $tableFields );");
}

Open in new window

0
Comment
  • 6
  • 4
10 Comments
 
LVL 3

Expert Comment

by:BioI
ID: 20349610
if you change the line:
print(" CREATE TABLE $table( $tableFields );");
into:
$dbh->do("CREATE TABLE $table( $tableFields )");
that should work...
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349623
Is there a way to add a message, saying it as was added and still print out that line to show what it added?
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349684
It doesnt want to seem to insert the fields, but adds the table name.
#!/usr/bin/perl
use CGI qw(:standard);
use DBI;
print "Content-type:text/html \n\n";
 
 
# Ryan Coughlin
# Server Side Scripting
# Breaks code down in to subs
# sub header - print out top HTML code (body tags)
# sub footer - ends HTML page
# sub first  - enter table name and number of fields
# sub second - enter details on each field
# sub three  - prints out SQL create table code
 
# connection details for connection to database
 
$database = "assignments";
$username = "xxx";
$password = "xxx";
 
# make connection string
 
$dsn      = "DBI:mysql:$database:localhost";
 
# make actual connection
 
$dbh=DBI->connect($dsn,$username,$password); # if a password is not required renove $password
 
 
 
 
my $step = param('step') || "START"; # what step are you on
 
if( $step eq "Create Table")
{
      my $table   = param('tableName')   || "TEST";    # table name
      my $fields  = param('fieldCount')  || 0;         # number of fields
      third($table,$fields);
}
elsif( $step eq "Submit")
{
      my $table   = param('tableName');                # table name
      my $fields  = param('fieldCount') || 0;          # number of fields
 
      second("table.cgi","post",$table, $fields);
}
else
{
      first("table.cgi","post");
}
footer();
 
##logic ends here. Only subroutines follow
 
sub header{
my ($pageTitle,$headCode)=@_;
 
print <<HEADER;
<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>$pageTitle</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
$headCode
</head>
 
<body>
HEADER
 
}
 
sub footer{
print <<FOOTER;
</body>
</html>
FOOTER
 
}
 
 
# first step type in table name and number of fields
sub first{
my ($action,$method)=@_;
 
print <<FIRST;
<h2>Building and Using a Database</h2>
<h2>Step 1: Name of Table and Number of Fields</h2>
<form id="form1" method="$method" action="$action">
  <p><b>Table Name:</b>
    <input name="tableName" type="text" size="10" maxlength="10" />
 
  </p>
  <p><b>Number of Fields</b>:
    <input name="fieldCount" type="text" size="10" maxlength="10" />
  </p>
  <p>
    <input type="submit" name="step" value="Submit" />
  </p>
 
</form>
 
<p>&nbsp;</p>
FIRST
 
}
 
# type in field characteristics
sub second{
# print out outer HTML form/table, etc.
my ($action,$method,$table,$fields) = @_;
 
print <<HTML;
 
<form id="form1" method="$method" action="$action">
      <input type="hidden" name="tableName" value="$table" />
      <input type="hidden" name="fieldCount" value="$fields" />
      <table width="60%" border="2" cellspacing="0" cellpadding="0">
          <caption>$table</caption>
            <tr>
                  <td><b>FIELD NAME</b></td>
                  <td><b>FIELD TYPE</b></td>
                  <td><b>FIELD LENGTH</b></td>
            </tr>
HTML
 
# print out the number of fields specified in step 1 us basic for loop statement
for ($counter=1; $counter <= $fields; ++$counter){
      print <<HTML1;
            <tr>
                  <td><input name="fieldname$counter" type="text" size="30" maxlength="30" /></td>
                  <td>
                        <select name="fieldtype$counter">
                              <option value="char">char</option>
                              <option value="date">date</option>
                              <option value="float">float</option>
                              <option value="int">int</option>
                              <option value="text">text</option>
                              <option value="varchar">varchar</option>
                        </select>
                  </td>
                  <td><input name="fieldlength$counter" type="text"  size="5" maxlength="5" /></td>
            </tr>
       
HTML1
 
}
print <<HTML3;
      </table>
 
      <p><input type="submit" name="step" value="Create Table" /></p>
</form>
 
HTML3
 
}
 
# prints out SQL code - will print all fields and characteristics
sub third{
my ($table,$fieldCount) = @_;
 
my $tableFields = "";
for($i=1; $i<$fieldCount; ++$i)
{
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . "(" . param("fieldlength$i") . "), ";
}
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . " (" . param("fieldlength$i") . ")";
 
# print out
$dbh->do("CREATE TABLE $table( $tableFields )");
}

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 3

Expert Comment

by:BioI
ID: 20349710


($dbh->do("CREATE TABLE $table( $tableFields )") and print "table $table succesfully created\n") or die "SQL Error: $dbh->errstr() \n";

(last part to make sure that you catch errors)
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349719
I dont see it add the table and I dont see any errors. Any suggestions?
0
 
LVL 3

Expert Comment

by:BioI
ID: 20349787
just print the create statement again like your initial code.
must be an error in your sql code that you first have to solve. Did this sql-code work if you copy-pasted it into a mysql command prompt?
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349821
You're right on that, hmm.

EX - table varchar(40) - correct, It seems code is printing too much, any suggestions?

http://elan.champlain.edu/~rcoughlin32001/cgi-bin/table.cgi

>> CREATE TABLE e e char (5) ;

I feel its duplicating the TABLE NAME see how it has "e e"
If it had one it would be correct code
# prints out SQL code - will print all fields and characteristics
sub third{
my ($table,$fieldCount) = @_;
 
my $tableFields = "";
for($i=1; $i<$fieldCount; ++$i)
{
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . "(" . param("fieldlength$i") . "), ";
}
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . " (" . param("fieldlength$i") . ")";
 
# print out
print(" CREATE TABLE $table $tableFields ;");
}

Open in new window

0
 
LVL 3

Accepted Solution

by:
BioI earned 500 total points
ID: 20349845
another option for debugging: first store everything in a variable:

$create = "CREATE TABLE $table( $tableFields )";
print "create => $create\n";
my $sql = $dbh->prepare("$create");
$slq->execute || die $dbh->errstr();

PS don't forget to drop the old table before trying to create a new one. Otherwise you will get some errors:
you can prevent this by adding in front of your create statement:
my $drop = "drop table if exist $table";
$dbh->do("$drop");
0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349849
Correct syntax.
# prints out SQL code - will print all fields and characteristics
sub third{
my ($table,$fieldCount) = @_;
 
my $tableFields = "";
for($i=1; $i<$fieldCount; ++$i)
{
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . "(" . param("fieldlength$i") . "), ";
}
$tableFields .= param("fieldname$i") . " " . param("fieldtype$i") . "(" . param("fieldlength$i") . ")";
 
# print out
print(" CREATE TABLE $table( $tableFields );");
}

Open in new window

0
 
LVL 1

Author Comment

by:catonthecouchproductions
ID: 20349864
I got it working, thank you man! if you have a second could you help me some more on here

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_22982260.html

Thanks for the help! Appreciate it.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question