DBI using Hashes To Create

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

Hey Monks

Given the Following Code

  my $dpt = $q->param('email_dpt');
  my $from = $q->param('email_dpt_from');
  my $name = $q->param('email_dtp_name');
  my $subject = $q->param('email_dpt_subject');
  my $content = $q->param('email_dpt_content');

  # set send to address based on select drop down
  # first email add is value of 0!!
  my @DEPARTMENTS = qw(
   editor@lsweb.org.uk
   webamster@lsweb.org.uk
   news@lsweb.org.uk
   ls2@lsweb.org.uk
   sport@lsweb.org.uk
   comment@lsweb.org.uk
   extra@lsweb.org.uk
    );
  my $to = $DEPARTMENTS[$dpt];
  # set up vaibles to send to the email template (email)
  my $vars = {
          to    => $to,
          from    => $from,
                name    => $name,
                subject    => $subject,
                content    => $content
             };
               
  # send an email with the details
    $mail_template->process('email/email',
                            $vars,
                            $to,    # to?
                            $from,    # from?
                            $subject)        # subject
                            || die $mail_template->error;
  # add email to the Database

[LINE 102] LSweb::Database::Email->create({
                'to'        => \$to,
                'from'        => \$from,
                'name'        => \$name,
                'subject'    => \$subject,
                'content'    => \$content,
                # id and datetime auto fields
                });
[download]

The Script does send the email but errors and cant add the email to the database, using the Create sql query (indicated as Line 102).

I get the Following Error

Fri Apr 28 23:36:44 2006 error Error executing run mode 'email_dpt': Can't insert new LSweb::Database::Email: Cannot call execute with a reference (SCALAR(0x98d69dc))\n at /home/lsweb/public_html/perllib/LSweb/Application/Email.pm line 102\n at /dev/null line 0\n

Can anyone help with the Create?

Yours

Barry Carlyon barry@barrycarlyon.co.uk

Comment on DBI using Hashes To Create Download Code

Replies are listed 'Best First'.
Re: DBI using Hashes To Create by eric256 (Parson) on Apr 28, 2006 at 22:58 UTC
You probably don't want to send references to all those (thats what the \ is doing) Try removing that and see what happens. ___________ Eric Hodges	[reply]
Re^2: DBI using Hashes To Create by barrycarlyon (Beadle) on Apr 28, 2006 at 23:00 UTC
I should of mentioned that that was first choice, but since that didnt work I added the backslahes and that didnt work Error for no backslahes Fri Apr 28 23:59:48 2006 error Error executing run mode 'email_dpt': Can't insert new LSweb::Database::Email: DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'to, from, subject, content, name)\nVALUES ('webamster@lsweb.org. for Statement "INSERT INTO email (to, from, subject, content, name)\nVALUES (?, ?, ?, ?, ?)\n" at /usr/lib/perl5/site_perl/5.8.6/DBIx/ContextualFetch.pm line 52.\n at /home/lsweb/public_html/perllib/LSweb/Database/Email.pm line 102\n at /dev/null line 0\n Barry Carlyon barry@barrycarlyon.co.uk	[reply]
Re^3: DBI using Hashes To Create by bart (Canon) on Apr 28, 2006 at 23:09 UTC
"from" is a reserved word in SQL. I'm guessing "to" might be too. You should be using less bugprone column names, but you might try just putting backquotes ("`") around those column names. That's a way with Mysql to allow special characters, or reserved words, in column names. See quoting identifiers.	[reply]
Re^3: DBI using Hashes To Create by chromatic (Archbishop) on Apr 28, 2006 at 23:07 UTC
Why did you add the backslashes? The error message suggests that the SQL syntax is invalid. That means that whatever `LSweb::Database::Email` does, either you're not using it right or it's not generating the correct code. Unless we can see it, we can't tell you which it is. Update: Yeah, we also need to see `LSweb::DBI`....	[reply] [d/l] [select]
Re: DBI using Hashes To Create by kwaping (Priest) on Apr 28, 2006 at 23:02 UTC
Update: Well, I learned something new today. `@array = qw(...)` actually works. Nevermind this post... The first issue I noticed was that your `@DEPARTMENTS` array creation was not right. It should read: `my @DEPARTMENTS = (qw( editor@lsweb.org.uk webamster@lsweb.org.uk news@lsweb.org.uk ls2@lsweb.org.uk sport@lsweb.org.uk comment@lsweb.org.uk extra@lsweb.org.uk ));` [download] Also, I can't find any reference to LSweb::Database::Email - is that an in-house custom module? If so, I don't know if your method call is correct or not. Have you tried setting the hash values inside the create statement to regular scalars (ex: `$to`) instead of references (`\$to`)? --- It's all fine and dandy until someone has to look at the code.	[reply] [d/l] [select]
Re^2: DBI using Hashes To Create by jdporter (Paladin) on Apr 28, 2006 at 23:35 UTC
kwaping, you are mistaken. The way he has it works just fine. The outer parens are not necessary. We're building the house of the future together.	[reply]
Re^3: DBI using Hashes To Create by barrycarlyon (Beadle) on Apr 28, 2006 at 23:51 UTC
Especially considering that i got his comparing solution from perl monks.org anyway I forget which node number, but its the previous one that I wrote/started Barry Carlyon barry@barrycarlyon.co.uk	[reply]
Re^2: DBI using Hashes To Create by barrycarlyon (Beadle) on Apr 28, 2006 at 23:04 UTC
`package LSweb::Database::Email; use strict; use base qw(LSweb::DBI); use Apache::Reload; __PACKAGE__->table('email'); __PACKAGE__->columns(All => qw/id from to name subject content datetim +e/); __PACKAGE__->set_sql(all => 'SELECT __ESSENTIAL__ FROM __TABLE__'); 1;` [download] Barry Carlyon barry@barrycarlyon.co.uk	[reply] [d/l]
Re: DBI using Hashes To Create by barrycarlyon (Beadle) on Apr 28, 2006 at 23:48 UTC
Solution Removed Backslahes on the Create Changed the Tbale Column names as used reseved words, Alter the @DEPARTMENTS as shown Barry Carlyon barry@barrycarlyon.co.uk	[reply]
Re^2: DBI using Hashes To Create by Scott7477 (Chaplain) on Apr 29, 2006 at 04:59 UTC
If I followed the thread correctly the code should now look something like this: my $dpt = $q->param('email_dpt'); my $replace_from = $q->param('email_dpt_from'); my $name = $q->param('email_dpt_name'); my $subject = $q->param('email_dpt_subject'); my $content = $q->param('email_dpt_content'); # set send to address based on select drop down # first email add is value of 0!! my @DEPARTMENTS = qw( editor@lsweb.org.uk webamster@lsweb.org.uk news@lsweb.org.uk ls2@lsweb.org.uk sport@lsweb.org.uk comment@lsweb.org.uk extra@lsweb.org.uk ); my $replace_to = $DEPARTMENTS[$dpt]; # set up variables to send to the email template (email) my $vars = { to => $replace_to, from => $replace_from, name => $name, subject => $subject, content => $content }; # send an email with the details $mail_template->process('email/email', $vars, $replace_to, $replace_from, $subject) # subject \|\| die $mail_template->error; # add email to the Database [LINE 102] LSweb::Database::Email->create({ 'to' => $replace_to, 'from' => $replace_from, 'name' => $name, 'subject' => $subject, 'content' => $content, # id and datetime auto fields }); [download]	[reply] [d/l]