Insert into MSSQL

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow monks,
I am a newbie attempting to insert data into a MSSQL Database. I am using the DBI and the DBD::ODBC modules. My inteface design could be wrong. I am using page with text boxes to gather information. I then submit that page to my perl script to interpret and process to the database. How can i use placeholders within my insert statement if my data sources origin is from a post? This is my NOVICE attempt so far. I realise it is wrong, but it might demonstrate my intentions

--------------------------------------------------------
#!/perl/bin/perl.exe  

use strict; 

use CGI;

use DBI;
use DBD::ODBC;

my ( $data_source, $database) = qw( server database user              
+                           password );

$data_source = "DIGGER";
$database = "My Company";


my $conn_string = "driver={SQL Server};Server=$data_source;           
+                  Database=$database";

my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die              
+                $DBI::errstr;

my $query = CGI->new();

print "Content-type: text/html\n\n";
print "<html><body>\n";

print "ADD NEW FILM\n";

if ($query->param('FilmName')) {
        my $FilmName = $query->param('FilmName');
        chomp $FilmName;
}

if ($query->param('Price')) {
        my $Price = $query->param('Price');
        chomp $Price;
}


my $sth = $dbh->do(
         "INSERT INTO tblFilm (Name, Price) VALUES                    
+   ('$FilmName', '$Price')") || print "Can't                    prepa
+re statement: $DBI::errstr";

print "</body></html>\n";

$dbh->disconnect;
------------------------------------------------------
[download]

I am wanting to use placeholders (?) to achieve better design. Is there a better design method? When i run this script the variables are not entered into the database.

Thankyou guru's,

edit (broquaint): added formatting

Comment on Insert into MSSQL Download Code

Replies are listed 'Best First'.
Re: Insert into MSSQL by ysth (Canon) on Jul 11, 2004 at 11:09 UTC
You have some scoping issues. In your if blocks, you declare $FilmName and $Price with my. That makes each scoped to that block only. When you refer to $FilmName and $Price later, they will be different global varibles. Because you haven't declared them and are using strict, your script will die with a message like: `Global symbol "$FilmName" requires explicit package name at FILE line +LINE` [download] I'm guessing you haven't checked your error log. (Update: or this isn't actually very close to the actual code you are using.) I also notice here: `my $sth = $dbh->do( "INSERT INTO tblFilm (Name, Price) VALUES ('$FilmName', '$Price')") \|\| + print "Can't prepare statement: $DBI::errstr";` [download] that you probably want to use "or" instead of "\|\|". Since \|\| has higher precedence than =, the way it is will assign the return value of do() to $sth, or assign 1 (the return value of print) if do() returned false. With or instead of \|\|, $sth will be left undefined if the do() fails.	[reply] [d/l] [select]
Re: Insert into MSSQL by freddo411 (Chaplain) on Jul 11, 2004 at 16:14 UTC
First off, you make your code more readable if you don't mix output in with the query processing / database writing part. Put the HTML elements at the end of the script, or better yet use HTML::Template or another templating module. Seperate out the Database operations and the query processing operations. Second, read up on DBI recipes for answers to using placeholders. good luck ------------------------------------- Nothing is too wonderful to be true -- Michael Faraday	[reply]
Re: Insert into MSSQL by runrig (Abbot) on Jul 11, 2004 at 18:17 UTC
After you fix the other issues already mentioned, you'll want to read the DBI docs on 'do', 'prepare', and 'execute'. Because 'do' does not return a statement handle, 'prepare' does. Then you can 'execute' it with arguments. Something like: `my $sth = $dbh->prepare('insert into tblfilm (name, price) values (?,?)'; $sth->execute($FilmName, $Price); #Update: I forgot, 'do' also accepts placeholder arguments like this.. $dbh->do('insert into tblfilm (name, price) values (?,?)', undef, $FilmName, $Price);` [download] Warning: I am not doing any error checking, but you might want to look at the RaiseError attribute for the connect statement, and the 'Transactions' section of the DBI docs for idiomatic error handling.	[reply] [d/l]