Solved

Perl and databases

Posted on 2007-11-26
10
186 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now