Solved

Perl and databases

Posted on 2007-11-26
10
191 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

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!

Question has a verified solution.

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

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
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…
Six Sigma Control Plans

623 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