Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Perl and databases

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
catonthecouchproductions
Asked:
catonthecouchproductions
  • 6
  • 4
1 Solution
 
BioICommented:
if you change the line:
print(" CREATE TABLE $table( $tableFields );");
into:
$dbh->do("CREATE TABLE $table( $tableFields )");
that should work...
0
 
catonthecouchproductionsAuthor Commented:
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
 
catonthecouchproductionsAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BioICommented:


($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
 
catonthecouchproductionsAuthor Commented:
I dont see it add the table and I dont see any errors. Any suggestions?
0
 
BioICommented:
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
 
catonthecouchproductionsAuthor Commented:
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
 
BioICommented:
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
 
catonthecouchproductionsAuthor Commented:
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
 
catonthecouchproductionsAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now