Re: DBI prepare syntax
That was a rookie mistake :). Double quotes works, thanks Beginner!
Beginner [EMAIL PROTECTED] wrote: On 8 Nov 2007 at 8:59, Mike wrote:
Hi,
#Update override_exclude set th_flag=N to indicate that thesaurus entries
#have been added to sierra2_thesaurus.xml
use DBI;
my $dbh = DBI-connect(dbi:mysql:endeca_tracking,$user,$password);
my $sth = $dbh-prepare('update override_exclude set th_flag=N
where pid=$pid') or die Couldn't prepare statement .$dbh-errstr;
$sth-execute;
I'm no guru but shouldn't you be double quoting the prepare
statement:
my $sth = $dbh-prepare(update override_exclude set th_flag='N'
where pid=$pid)
or better still
my $sth = $dbh-prepare('update override_exclude set th_flag=N
where pid=?)
$sth-execute($pid);
HTH,
Dp.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: DBI prepare syntax
Mike wrote:
Hi,
I have a variable setup as $pid and i've tried to call this in the prepare part of the DBI module but i'm get an Unknown column $pid syntax error. Probably $pid was passed as a string instead of a variable inside the prepare (''). Anyone has an idea of how i can fix this? Thanks.
DBD::mysql::st execute failed: Unknown column '$pid' in 'where clause' at
createThesaurus.pl line 33,
---
while ( NEW ) {
next if $. == 1; # exclude header
chomp;
my ($pid,$thesaurus_from,$thesaurus_to,$thesaurus_type) = split /\|/;
#Update override_exclude set th_flag=N to indicate that thesaurus entries
#have been added to sierra2_thesaurus.xml
use DBI;
my $dbh = DBI-connect(dbi:mysql:endeca_tracking,$user,$password);
my $sth = $dbh-prepare('update override_exclude set th_flag=N where pid=$pid') or
die Couldn't prepare statement .$dbh-errstr;
The prepare statement here is inside single quotes, inside of which Perl
doesn't interpolate variables (it does inside double quotes). But DBI
has a mechanism called placeholders that allow you to not worry about
quoting and interpolation of variables, documented under the section
Placeholders and Bind Values in the DBI documentation.
You can change your prepare statement to:
$dbh-prepare('update override_exclude set th_flag=? where pid=?')
(the question marks denote the placeholder)
$sth-execute;
And then change your execute to:
$sth-execute( N, $pid );
to pass along the list of values meant to take the place of your
placeholders. This is generally safer than passing variables directly
to your prepare statement. For instance, if $pid held a nasty string
that terminated the original statement and then followed up with
something destructive, such as 5; truncate table_foo;, bad things
could happen. With placeholders you don't have to worry about that, as
the database driver fixes that up by quoting it properly (not to say you
shouldn't still validate input to a database!)
Hope that helps a bit,
-- Douglas
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: DBI prepare syntax
On 8 Nov 2007 at 8:59, Mike wrote:
Hi,
#Update override_exclude set th_flag=N to indicate that thesaurus entries
#have been added to sierra2_thesaurus.xml
use DBI;
my $dbh = DBI-connect(dbi:mysql:endeca_tracking,$user,$password);
my $sth = $dbh-prepare('update override_exclude set th_flag=N
where pid=$pid') or die Couldn't prepare statement .$dbh-errstr;
$sth-execute;
I'm no guru but shouldn't you be double quoting the prepare
statement:
my $sth = $dbh-prepare(update override_exclude set th_flag='N'
where pid=$pid)
or better still
my $sth = $dbh-prepare('update override_exclude set th_flag=N
where pid=?)
$sth-execute($pid);
HTH,
Dp.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: DBI prepare syntax
Perl is Awsome! Thanks for explaining that basic concept to me Douglas!
Douglas Hunter [EMAIL PROTECTED] wrote: Mike wrote:
Hi,
I have a variable setup as $pid and i've tried to call this in the prepare
part of the DBI module but i'm get an Unknown column $pid syntax error.
Probably $pid was passed as a string instead of a variable inside the prepare
(''). Anyone has an idea of how i can fix this? Thanks.
DBD::mysql::st execute failed: Unknown column '$pid' in 'where clause' at
createThesaurus.pl line 33,
---
while ( ) {
next if $. == 1; # exclude header
chomp;
my ($pid,$thesaurus_from,$thesaurus_to,$thesaurus_type) = split /\|/;
#Update override_exclude set th_flag=N to indicate that thesaurus entries
#have been added to sierra2_thesaurus.xml
use DBI;
my $dbh = DBI-connect(dbi:mysql:endeca_tracking,$user,$password);
my $sth = $dbh-prepare('update override_exclude set th_flag=N
where pid=$pid') or die Couldn't prepare statement .$dbh-errstr;
The prepare statement here is inside single quotes, inside of which Perl
doesn't interpolate variables (it does inside double quotes). But DBI
has a mechanism called placeholders that allow you to not worry about
quoting and interpolation of variables, documented under the section
Placeholders and Bind Values in the DBI documentation.
You can change your prepare statement to:
$dbh-prepare('update override_exclude set th_flag=? where pid=?')
(the question marks denote the placeholder)
$sth-execute;
And then change your execute to:
$sth-execute( N, $pid );
to pass along the list of values meant to take the place of your
placeholders. This is generally safer than passing variables directly
to your prepare statement. For instance, if $pid held a nasty string
that terminated the original statement and then followed up with
something destructive, such as 5; truncate table_foo;, bad things
could happen. With placeholders you don't have to worry about that, as
the database driver fixes that up by quoting it properly (not to say you
shouldn't still validate input to a database!)
Hope that helps a bit,
-- Douglas
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
