Re: binding values to sql statement in DBI perl

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/