perl to sql page

Sorry if this is a no brainer to every one but I just cant get it to work !
I have 2 pages that search and insert data to my mysql database the only problem is that the insert script does not work infact it seems to do nothing !
I have used the same passwords etc on both forms snd the search form works I will include a section of the insert form in this post to see if anyone has any ideas
Thanks a lot
newbie under pressure.



#!/usr/bin/perl

# ---> insert.cgi <---
# CGI to insert data into the SQL database
use CGI;
use DBI;
$q = new CGI;

# Definitions - You need to change these if you use a different db
$db = "inventory";
$table = "machines";
$user = "root";
$password = "iamnotanumber";
# End of definitions

HTML code for page etc

# Build and execute the SQL statement
$SQLstatement = "insert into $table (alias,ip,canonical,owner,misc,ostype)
values (\"$alias\",\"$ip\",\"$canonical\",\"$owner\"
\"$misc\",\"$ostype\"";
dbh->do($SQLstatement);

# print "$name added to database<br><br>";
# print $q->end_html;
}

The syntax for your INSERT wrong which is why it doesn't work. I don't how much you read up on using DBI but if you look at the 'do()' function you'll see that you cannot just put scalars in wherever you want which in essence, is what you are trying to do. You must hold the place of the variable which is to be inserted with a '?'. then you pass the the scalars which will be used as arguments to the 'do()' function.

So it should look like this:


If you are doing this INSERT inside any kind of loop, I would recommend using the 'prepare/execute' method rather that the 'do()' method for reasons of efficiency.

Hope that helps.

Looks like you might have forgotten a closing parenthesis:

$SQLstatement = "insert into $table (alias,ip,canonical,owner,misc,ostype)
values (\"$alias\",\"$ip\",\"$canonical\",\"$owner\"
\"$misc\",\"$ostype\")";

cheers randymorphis ive tried that it fails to load at all with it in ??
Also to dsb I dont know a lot about 'do()'function but does this make sense (didnt include this in my previous post)sorry to be a pain




sub printform {

print $q->header;
print $q->start_html(-title=>'Network Machines Database',
-BGCOLOR=>'black',
-TEXT=>'white');
print "<CENTER><H1>Network Machines Database</H1></CENTER><HR>";
print $q->startform;
print "Alias: ", $q->textfield(-name => alias,
-size => 16), "<BR>";
print "IP address : ", $q->textfield(-name => ip
-size => 16), "<BR>";
print "Canonical name: ", $q->textfield(-name => canonical,
-size => 16), "<BR>";
print "Owner: ", $q->textfield(-name => owner,
-size => 16), "<BR>";
print "Misc: ", $q->textfield(-name => misc,
-size => 16), "<BR>";
print "OS type: ", $q->textfield(-name => ostype,
-size => 16), "<BR>";
print $q->submit;
print $q->endform;
print $q->end_html;
}

sub results {

($alias, $ip, $canonical, $owner, $misc, $ostype);

$alias = $q->param('alias');
$ip = $q->param('ip');
$canonical = $q->param('canonical');
$owner = $q->param('owner');
$misc = $q->param('misc');
$ostype = $q->param('ostype');

print $q->header;
print $q->start_html(-title=>'Database Results',
-BGCOLOR=>'black',
-TEXT=>'white');

# Tell the script that we will be using
# a MySQL database
($drh, $dbh);
$drh = DBI->install_driver( 'mysql' );

# Establish a connection with the database
# $dbh = $drh->connect($db, $user, $password);

# A simple check to see if we connected
if (!$dbh) {
print "Cannot connect: $DBI::errstr<BR>";
print $q->end_html;
die;
}

# Build and execute the SQL statement
$SQLstatement = "insert into $table (alias,ip,canonical,owner,misc,ostype)
values (\"$alias\",\"$ip\",\"$canonical\",\"$owner\"
\"$misc\",\"$ostype\"";
dbh->do($SQLstatement);

# print "$name added to database<br><br>";
# print $q->end_html;
}

Take a look at my last post. Note the syntax I used for the statement. Variables cannot be interpolated into SQL queries like the way you have in your statement:


You must use placeholders when you are creating SQL statements that will hold variables like so:
[code]
$dbh->do( "INSERT INTO tablename VALUES( ?, ?, ?, ? )", undef, $val1,$val2,$val3,$val4 ); # ?'s hold spot for scalars - scalar args to do fill in the blanks held by ?'s


[Edited by dsb on 03-02-2001 at 10:01 AM]

You have _have_ to use place holders, but you should never just put it right in the middle of the code. Here's another method you can use that will work fine:

Although I don't usually create a variable to hold the sql statement itself, I find that to just take up extra room and memory (so you'd just put the sql statement in the do() sub).