Solved

Perl and databases

Posted on 2007-11-26
10
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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

Technology Partners: 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!

Question has a verified solution.

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

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
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…
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…

752 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