Problem with insert using DBI

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

Can anybody tell me why this insert might not be working.
The first code is from the cgi script the second is from a module.

if ($param{submit} && $param{day} >0 && $param{day} <=  $leapdays{$tmp
+mon}&& $param{month} > 0 && $param{month} <= 12 && $param{year
} > 1920 ) {
        $id++;
        #$mysql->Insert("events",$id,$param{day},$param{month},$param{
+year},$param{subject},$param{description},$username);
        $mysql->Insert("events",$param{day},$param{month},$param{year}
+,$param{subject},$param{description},NULL);
        print "Record Inserterd";
[download]

sub Insert {
        my $self = shift;
        my $table = shift;
        my @fields;
        my @values;
        my $show_field;
        foreach (@_) {
                my ($field_name,$value) = split(/\|/,$_);
                if (!$value) {
                        $show_field = 1;
                        $_ = &clean_text($_);
                        push (@values,$_);
                } else {
                        $value = &clean_text($value);
                        push(@fields,$field_name);
                        push(@values,$value);
                }
        }


        my $value = join("\',\'",@values);
        $value = "\'$value\'";

        if ($show_field != 1) {
                my $field = join(",",@fields);
                $field = "($field)";
        }


        my $mysql = "INSERT INTO $table $field VALUES ($value)";

        #return $mysql;
        my $sth = $self->_execute($mysql);

        #my $sth = $self->[0]->prepare($mysql) or die "Prep Error";
        #my $rv = $sth->execute or die "Exec Error";

        #return $self;
}
[download]

Comment on Problem with insert using DBI Select or Download Code

Replies are listed 'Best First'.
Re: It won't insert by Juerd (Abbot) on Mar 20, 2002 at 07:28 UTC
Can anybody tell me why this insert might not be working. Try printing $DBI::errstr. Use RaiseError in DBI if possible. Use placeholders, and don't interpolate values. We don't have your _execute, so we can't possibly have a clue about what's going on. IMHO, DBIx::Abstract is a better solution for abstracting SQL. U28geW91IGNhbiBhbGwgcm90MTMgY W5kIHBhY2soKS4gQnV0IGRvIHlvdS ByZWNvZ25pc2UgQmFzZTY0IHdoZW4 geW91IHNlZSBpdD8gIC0tIEp1ZXJk	[reply]
Re: It won't insert by dws (Chancellor) on Mar 20, 2002 at 07:45 UTC
In addition to Juerd's excellent advice, `-w` and `use strict;` are your friends, particularly with the code from the module, which has at least one variable lifecycle problem.	[reply] [d/l] [select]
Re: Re: It won't insert by dragonchild (Archbishop) on Mar 20, 2002 at 14:44 UTC
What variable lifecycle problem did you see? I looked 3 times and didn't find any. blinkblink ------ We are the carpenters and bricklayers of the Information Age. Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.	[reply]
Re: Re: Re: It won't insert by dws (Chancellor) on Mar 20, 2002 at 18:10 UTC
What variable lifecycle problem did you see? I looked 3 times and didn't find any. Look very carefully at the lifecycle(s) of `$field`. If you don't see the problem, consider this: `$foo = 1; if ( 1 ) { my $foo = 2; } print "$foo\n";` [download] Looking at this, what would you expect it to print? I doubt this has anything to do with the problem you originally reported, but it is one more red flag among many raised by the code fragment you posted.	[reply] [d/l] [select]
Re4: It won't insert by dragonchild (Archbishop) on Mar 20, 2002 at 18:34 UTC
Re: It won't insert by seattlejohn (Deacon) on Mar 20, 2002 at 07:54 UTC
It seems like you may have an error in your SQL statement. I believe the proper syntax is either: `INSERT INTO table VALUES (value1, value2, ...)` if you want to list the values in order, or: `INSERT INTO table SET column1=value1, column2=value2, ...` if you want to specify the columns individually. General suggestions for debugging DB code: print your SQL statement so you can visually verify it; include $DBI::errstr in your "or die"; tweak your SQL statements at the DB console until they behave the way you suggest. Good luck. Update: As VSarkiss points out below, listing the column names explicitly is always a good idea. Using `SET` in `INSERT` may be behavior that's specific to MySQL, so the general-purpose format that rdfield spells out is probably a better choice.	[reply] [d/l] [select]
Re: Re: It won't insert by rdfield (Priest) on Mar 20, 2002 at 10:25 UTC
`INSERT INTO table SET column1=value1, column2=value2, ...` depends on the RDBMS: for instance, Oracle's Insert statement is: `INSERT INTO table (column1,column2,...) VALUES (value1,value2,...)` [download] rdfield	[reply] [d/l] [select]
Re: Re: It won't insert by VSarkiss (Monsignor) on Mar 20, 2002 at 17:04 UTC
Sorry, but this is very poor advice. It's always better to list the columns in an insert statement rather than depending on column order in the table. It costs you nothing (other than typing ;-), clarifies your intent, and protects you against future changes to the table. The syntax has been standard since at least SQL-89, so any self-respecting RDBMS will support it. I think your other example is confusing UPDATE statements with INSERT statements. It's `UPDATE table SET col = val, ...`There is no SET clause in an INSERT statement in any dialect of SQL I know.	[reply] [d/l]