Обсуждение: binding values to sql statement in DBI perl
Hi, I am having trouble with binding values to SQL statements using dbi perl. The scenerio is as follows : I have a scheduling database that holds the listings of classes and there schedules for a university. I am trying to query the database to display the listings of classes , sections for a particular semester that the user enters. I am able to get the results with the actual value but then when I use the variable which holds the values of the semester the user entered and try querying , it displays an error message saying "unbound place holder " I am not sure of how to deal with this , I have tried different combinations but in vain. Any input on this will be appreciated. Deepblues
On Wed, Apr 06, 2005 at 11:42:55AM -0500, Deepblues wrote: > > I am having trouble with binding values to SQL statements using dbi perl. > > The scenerio is as follows : > > I have a scheduling database that holds the listings of classes and > there schedules for a university. I am trying to query the database to > display the listings of classes , sections for a particular semester > that the user enters. > I am able to get the results with the actual value but then when I use > the variable which holds the values of the semester the user entered > and try querying , it displays an error message saying > > "unbound place holder " Could you post a small but complete program that exhibits the problem? Without seeing your code we can only guess what might be wrong. Also, please post the complete, exact text of the error message (copied and pasted, not typed manually). I suspect the real error is something like the following: DBD::Pg::st execute failed: execute called with an unbound placeholder at ./foo line 14. If that's the case, then you might have called $sth->execute() with no arguments when you should have called it with arguments, e.g., $sth->execute($variable). But that's just a guess; without seeing your code we can't be sure. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I am having trouble with binding values to SQL statements using dbi perl. ... > and try querying , it displays an error message saying > > "unbound place holder " This means that one of the placeholders you are using is not being "filled in" when you try and execute the query. Send us the exact code and SQL you are using and we can better help you. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200504062339 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCVKvfvJuQZxSWSsgRAt5KAKD1/gq5xy0euinQ7pamEdVtI/VyiACeMVoj WTSgr3+cl+TDBj7Gn4dQZiw= =MJp0 -----END PGP SIGNATURE-----
Here is the code I'm trying to execute:
I'm accepting user input for the semester and gathering the data for that particular semester.
print " Enter the semester:\n";
$f_semester = <STDIN>;
print " Enter the semester:\n";
$f_semester = <STDIN>;
# collecting rows of section,section_loc table to insert into interface table
my $sth = $dbh->prepare('SELECT course_id, course_name,section.section_id,day,time,enroll_capacity,room_capacity,bldg,instructor_id,instructor_name,semester,notes FROM section,section_location where section.section_id = section_location.section_id AND section.semester=?'
)
or die "couldn;t prepare statement:".$dbh->errstr;
$sth->bind_param(1, $f_semester);
my @data;
$sth->execute($f_semester);
while(@data = $sth->fetchrow_array())
{
my $insert_csv = "INSERT INTO interface_write(course_id,course_name,credit_hours,section_id,days,time,cap_enroll,room_cap,bldg,instructor_id,instructor_name,semester,notes)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
$dbh->do($insert_csv,undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$data[7],$data[8],$data[9],$data[10],$data[1
1],$data[12]);
}
$sth->finish;
}
I first tried it without passing the variable to $sth->execute;
my $sth = $dbh->prepare('SELECT course_id, course_name,section.section_id,day,time,enroll_capacity,room_capacity,bldg,instructor_id,instructor_name,semester,notes FROM section,section_location where section.section_id = section_location.section_id AND section.semester=?'
)
or die "couldn;t prepare statement:".$dbh->errstr;
$sth->bind_param(1, $f_semester);
my @data;
$sth->execute($f_semester);
while(@data = $sth->fetchrow_array())
{
my $insert_csv = "INSERT INTO interface_write(course_id,course_name,credit_hours,section_id,days,time,cap_enroll,room_cap,bldg,instructor_id,instructor_name,semester,notes)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
$dbh->do($insert_csv,undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$data[7],$data[8],$data[9],$data[10],$data[1
1],$data[12]);
}
$sth->finish;
}
I first tried it without passing the variable to $sth->execute;
It doesn;t display any error but it seems like it is not entering the while loop.
I am new to postgres and dbi perl , and i'm tryinng to figure this one out. Is there any module that has to be included to use the bind_param . I already included
I am new to postgres and dbi perl , and i'm tryinng to figure this one out. Is there any module that has to be included to use the bind_param . I already included
use DBI qw(:sql_types);
I also need to include the semester in single quotes to search in the table, will bind_param enclose the text in quotes or how does it work.
_
I also need to include the semester in single quotes to search in the table, will bind_param enclose the text in quotes or how does it work.
_
Any help on this would be greatly appreciated.
Thanks and regards
Thanks and regards
Deepblues
On Apr 6, 2005 7:28 PM, Michael Fuhr <mike@fuhr.org> wrote:
> On Wed, Apr 06, 2005 at 11:42:55AM -0500, Deepblues wrote:
> >
> > I am having trouble with binding values to SQL statements using dbi perl.
> >
> > The scenerio is as follows :
> >
> > I have a scheduling database that holds the listings of classes and
> > there schedules for a university. I am trying to query the database to
> > display the listings of classes , sections for a particular semester
> > that the user enters.
> > I am able to get the results with the actual value but then when I use
> > the variable which holds the values of the semester the user entered
> > and try querying , it displays an error message saying
> >
> > "unbound place holder "
>
> Could you post a small but complete program that exhibits the
> problem? Without seeing your code we can only guess what might be
> wrong. Also, please post the complete, exact text of the error
> message (copied and pasted, not typed manually).
>
> I suspect the real error is something like the following:
>
> DBD::Pg::st execute failed: execute called with an unbound placeholder at ./foo line 14.
>
> If that's the case, then you might have called $sth->execute() with
> no arguments when you should have called it with arguments, e.g.,
> $sth->execute($variable). But that's just a guess; without seeing
> your code we can't be sure.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
--
Deepti Mandava
Graduate Teaching Assistant,
The University of Kansas
Lawrence, KS 66045-7546
On Apr 6, 2005 7:28 PM, Michael Fuhr <mike@fuhr.org> wrote:
> On Wed, Apr 06, 2005 at 11:42:55AM -0500, Deepblues wrote:
> >
> > I am having trouble with binding values to SQL statements using dbi perl.
> >
> > The scenerio is as follows :
> >
> > I have a scheduling database that holds the listings of classes and
> > there schedules for a university. I am trying to query the database to
> > display the listings of classes , sections for a particular semester
> > that the user enters.
> > I am able to get the results with the actual value but then when I use
> > the variable which holds the values of the semester the user entered
> > and try querying , it displays an error message saying
> >
> > "unbound place holder "
>
> Could you post a small but complete program that exhibits the
> problem? Without seeing your code we can only guess what might be
> wrong. Also, please post the complete, exact text of the error
> message (copied and pasted, not typed manually).
>
> I suspect the real error is something like the following:
>
> DBD::Pg::st execute failed: execute called with an unbound placeholder at ./foo line 14.
>
> If that's the case, then you might have called $sth->execute() with
> no arguments when you should have called it with arguments, e.g.,
> $sth->execute($variable). But that's just a guess; without seeing
> your code we can't be sure.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
--
Deepti Mandava
Graduate Teaching Assistant,
The University of Kansas
Lawrence, KS 66045-7546
On Thu, Apr 07, 2005 at 11:26:59AM -0500, Deepblues wrote:
>
> print " Enter the semester:\n";
> $f_semester = <STDIN>;
You should probably chomp $f_semester after reading it to remove
the line ending if the data doesn't have line endings. Otherwise
$f_semester will have a value like "Spring 2005\n", which won't
match "Spring 2005".
> my $sth = $dbh->prepare('SELECT course_id,
>
course_name,section.section_id,day,time,enroll_capacity,room_capacity,bldg,instructor_id,instructor_name,semester,notes
> FROM section,section_location where section.section_id =
> section_location.section_id AND section.semester=?'
> )
>
> or die "couldn;t prepare statement:".$dbh->errstr;
>
> $sth->bind_param(1, $f_semester);
> my @data;
> $sth->execute($f_semester);
If you use bind_param() then you don't need to pass an argument to
execute().
You didn't post the exact text of the error message or where it's
happening. Is it here? Do you know that because the error included
a line number, or are you guessing?
> while(@data = $sth->fetchrow_array())
> {
>
> my $insert_csv = "INSERT INTO
>
interface_write(course_id,course_name,credit_hours,section_id,days,time,cap_enroll,room_cap,bldg,instructor_id,instructor_name,semester,notes)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
>
$dbh->do($insert_csv,undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$data[7],$data[8],$data[9],$data[10],$data[1
> 1],$data[12]);
> }
Check the positions of the values you're inserting -- they don't
match the selected data. For example, the third column is credit_hours,
but according to the SELECT statement $data[2] is section_id.
> I also need to include the semester in single quotes to search in the table,
> will bind_param enclose the text in quotes or how does it work.
If you use placeholders then you don't need to quote values -- that
will be done automatically.
I created some tables and populated them with test data but couldn't
reproduce the problem using your code (after adding chomp and
correcting the positions of the INSERT data). I'd suggest posting a
simple but complete example that demonstrates the problem -- that is,
SQL statements to create and populate a test table, and a complete
Perl program that queries that table. Keep it minimal: use the
smallest table, the least amount of data, and the smallest program
that together demonstrate the problem. The less there is to look at,
the easier it is to spot what's wrong.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/