Solved

Perl and databases

Posted on 2007-11-26
10
185 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


($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
Comment Utility
I dont see it add the table and I dont see any errors. Any suggestions?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Expert Comment

by:BioI
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

11 Experts available now in Live!

Get 1:1 Help Now