$hour_insert_sth->execute showing error

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

 
my $hour_master_db = DBI->connect("DBI:mysql:database=$database;host=$
+host;mysql_socket=/opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","root
+","", {'RaiseError' => 1});
my $hour_insert_sth = $hour_master_db->prepare("insert into esme_hourl
+y_master values(?,?,?,?,?,?,?,?,?,now(),'MIS'");
$hour_insert_sth->execute($hr,$date,$system_id,$total_sub_data{$statsd
+ate}{$system_id},$totalacpt_data{$statsdate}{$system_id},$total_rej_d
+ata{$statsdate}{$system_id},$del_data{$statsdate}{$system_id},$undel_
+data{$statsdate}{$system_id},$exp_data{$statsdate}{$system_id}) or di
+e $DBI::errstr;
[download]

This is the code I am using to insert data into mu DB. But its showing error

 DBD::mysql::st execute failed: You have an error in your SQL syntax; 
+check the manual that corresponds to your MySQL server version for th
+e right syntax to use near '' at line 1 at final.pl line 328.
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near '' at line 1 at final.pl line 328.
[download]

But when I print data its showing perfectly like 09,2015-11-03,uni_upe,499887,466649,33238,0,0,0 I think I have to add ' ' to uni_upe which is a string. But how can I?? Or is there any other mistake???

My DB schema

 

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| hour          | int(2)      | NO   | PRI | NULL    |       |
| day           | date        | NO   | PRI | NULL    |       |
| system_id     | varchar(21) | NO   | PRI |         |       |
| submitted     | bigint(20)  | NO   |     | 0       |       |
| accepted      | bigint(20)  | NO   |     | 0       |       |
| rejected      | bigint(20)  | NO   |     | 0       |       |
| delivered     | bigint(20)  | NO   |     | 0       |       |
| undelivered   | bigint(20)  | NO   |     | 0       |       |
| expired       | bigint(20)  | NO   |     | 0       |       |
| creation_date | datetime    | NO   |     | NULL    |       |
| created_by    | varchar(20) | NO   |     |         |       |
+---------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
[download]

Comment on $hour_insert_sth->execute showing error Select or Download Code

Replies are listed 'Best First'.
Re: $hour_insert_sth->execute showing error by poj (Abbot) on Nov 04, 2015 at 08:02 UTC
`$hour_master_db->prepare(" INSERT into esme_hourly_master VALUES (?,?,?,?,?,?,?,?,?,now(),'MIS') ^ add missing ) ");` [download] poj	[reply] [d/l]
Re^2: $hour_insert_sth->execute showing error by 1nickt (Canon) on Nov 04, 2015 at 08:17 UTC
Dammit I looked at that until my eyes were crooked and I couldn't see it! Nice one, poj. The way forward always starts with a minimal test.	[reply]
Re^2: $hour_insert_sth->execute showing error by ravi45722 (Pilgrim) on Nov 04, 2015 at 09:53 UTC
I scrolled twice trice up to my mouse wheel cracks. Still I don't get that one. Thank you	[reply]
Re^3: $hour_insert_sth->execute showing error by poj (Abbot) on Nov 04, 2015 at 10:27 UTC
I expect you would have seen it written like this `my $sql = "insert into esme_hourly_master values (?,?,?,?,?,?,?,?,?,now(),?"; my $hour_insert_sth = $hour_master_db->prepare($sql);` [download] poj	[reply] [d/l]
Re^4: $hour_insert_sth->execute showing error by ravi45722 (Pilgrim) on Nov 04, 2015 at 11:06 UTC
Re: $hour_insert_sth->execute showing error by 1nickt (Canon) on Nov 04, 2015 at 07:16 UTC
Hi ravi45722, Yes, this is a not very informative message from MySQL. You can help by placing `DBI->trace(2);` [download] in your script; this will print out a lot of information including the actual SQL passed to MySQL, which will probably help you spot your problem. See DBI TRACING. Your error is probably not caused by the string value in `$system_id`: your use of placeholders makes quoting the values you are going to pass to the DBI unnecessary. Also note that since you are passing the `RaiseError` attribute you don't need the `or die $DBI::errstr` after your calls to your DBI methods. Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re: $hour_insert_sth->execute showing error by Corion (Patriarch) on Nov 04, 2015 at 07:43 UTC
`You have an error in your SQL syntax;` Have you printed out your SQL and inspected it for errors? Does the SQL work when manually pasted into the MySQL command line interface, with the placeholders replaced by the values?	[reply] [d/l]
Re: $hour_insert_sth->execute showing error by chacham (Prior) on Nov 04, 2015 at 15:06 UTC
insert into esme_hourly_master values(?,?,?,?,?,?,?,?,?,now(),'MIS'" Side comment: Please specify column names (in parens, after the table name.) This will self-document the statement, future-proof against additional (not null) columns, and protect against column order changes.	[reply]
Re: $hour_insert_sth->execute showing error by nikosv (Deacon) on Nov 04, 2015 at 07:24 UTC
change the schema of creation_date to : `creation_date datetime NOT NULL DEFAULT NOW(),` [download] then you don't need to specify it as a '?' parameter or now() in your code,as it's going to be automatically filled for you	[reply] [d/l]