Yeah, your suggestion might avoid a problem when the total number of table columns differs from number of values inserted.

But it's not inherently necessary:

http://sqlfiddle.com/#!2/c6d7e/2/0 ( using MySQL 5.5.32, but works unaltered on postgres)

(BTW, what a nice litte tool sqlfiddle is)

To: erix,

Nice!!! I had no clue that MySQL syntax can be applied like this.

To be honest out of curriocity I created a small working example:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $username = "username";
my $password = "password";
my $tut_id = "10";
my $tut_title = "title";
my $tut_author = "author";
my $dsn = "dbi:mysql:thanos:127.0.0.1:3306";
my $dbh = DBI->connect($dsn,$username,$password) or die "cannot
connect to database : $DBI::errstr";

my $sth = $dbh->prepare(qq{INSERT INTO `test` VALUES(?,?,?)});

$sth->execute("$tut_id","$tut_title","$tut_author") || die $DBI::errst
+r;

$dbh->disconnect;
[download]

And I am getting this error:

DBD::mysql::st execute failed: Column count doesn't match value count 
+at row 1 at db.pl line 18.
[download]

The moment that I am placing the columns e.g. (`column-1`,`column-2`,`column-3`) the code executes withought any erorrs.

I tried the same syntax on phpMyadmin:

INSERT INTO `test` VALUES ('10','title','author')
[download]

error output:

#1136 - Column count doesn't match value count at row 1
[download]

ERROR 1136 (21S01): Column count doesn't match value count at row 1
[download]

And also at the MySQL (terminal):

ERROR 1136 (21S01): Column count doesn't match value count at row 1
[download]

At this point maybe my version is different.

Well anyway, back to the question. The error should be coming of the avoidance of the quotes on the strings:

Before:

$sth->execute($tut_id,$tut_title,$tut_author) || die $DBI::errstr;
[download]

After:

$sth->execute("$tut_id","$tut_title","$tut_author") || die $DBI::errst
+r;
[download]

Wow so many things that we learn in this forum, I had also no clue about (sqlfiddle). Thanks for sharing. :D

Update:

I think I know the reason that my code withought specifying the columns can not work

From 13.2.5 INSERT Syntax text taken:

If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values.

So I assume this syntax can work on SQL but not on MySQL that I am using.

But it was nice that you pointed out, someone who is using SQL will be interested on this.

Thanks a lot for your suggestion of backticks and putting table name in variable. Definitely i am going to use it for my further programs.. As per your suggestion i modified my program but still data is not getting inserted..and execution of program is successful.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use XML::XPath;
use XML::XPath::XMLParser;

my $username = "user";
my $password = "userabca";
my $dsn = "dbi:mysql:books:127.0.0.1";
my $dbh = DBI->connect($dsn,$username,$password,{RaiseError =>
1,PrintError => 0}) or die "cannot connect to database :
$DBI::errstr";

my $xp = XML::XPath->new(filename=>'test.xml');

my $sth = $dbh->prepare(qq{INSERT INTO
'tutpoint_tbl'('tut_id','tut_title','tut_author') VALUES(?,?,?)});

foreach my $row($xp->findnodes('/main/MAIN')){

        print "node matched";

        my $tut_id = $row->find('tut_id')->int;
        my $tut_title=$row->find('tut_title')->string_value;
        my $tut_author=$row->find('tut_author')->string_value;

        $sth->execute("$tut_id","$tut_title","$tut_author") || die $DB
+I::errstr;

}
$dbh->disconnect;
[download]

Stop doing too many things at the same time.

First make sure the SQL works, and *only then* start messing with XML parsing. Show the results, input, output -- not just the program.

Change the program to just "hard-code" some values into the table. Only when that has become reliable make a program that parses the xml (without any database). Only when the two parts separately are both reliable merge them into your final program.

Also, please show both input and output of programs. Nobody can know what you are doing wrong without that.

To erix: Tried INSERT statement with some hard coded values..n data got inserted.... It worked.. now tym to check xml parsing,,,

To erix: Tried INSERT statement with some hard coded values..n data got inserted.... It worked.. now tym for xml parsing,,,

Like erix suggested, try inserting some test records and if that works then add in the XML code. For example

my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM tutpoint_tbl')
+; 
print "$count records in table\n";

# insert test records
my $sql_ins = 'INSERT INTO tutpoint_tbl VALUES(?,?,?)';
my $sth = $dbh->prepare($sql_ins);
for my $id (1..5){
  my $result = $sth->execute($id,'test insert title','author');
  print "$result records inserted\n"
}

$count = $dbh->selectrow_array('SELECT COUNT(*) FROM tutpoint_tbl'); 
print "$count records in table\n";

# delete test records
my $sql_del = 'DELETE FROM tutpoint_tbl WHERE tut_title = ?';
$sth = $dbh->prepare($sql_del);
my $result = $sth->execute('test insert title');
print "$result records deleted\n";

$count = $dbh->selectrow_array('SELECT COUNT(*) FROM tutpoint_tbl'); 
print "$count records in table\n";
[download]

poj

Ok, let's take it from the beginning because there are a few things worth mentioning here.

Fist of all for me at least, it is not clear if you are using SQL or MySQL Database.

As erix really nicely point out, maybe your SQL code was able to operate correctly without my proposed modifications that apply to MySQL syntax.

I am not a user of SQL so I am not really familiar with the syntax, and I do not have an operating system with SQL so unfortunately I can not help you a lot in case that you are not using MySQL.

I can give you a few advices that I believe that can assist you in general when you debug you database syntax.

In most cases when I get an error I take my code and I execute on MySQL terminal, or on phpMyAdmin for testing purposes.

I am following this prosedure until I found the correct syntax, so when I will call the function through my Perl script I know that it will do what I am expecting it to do.

I assume that SQL has similar tools to check the syntax of your code.

I noticed also that your updated code:

my $sth = $dbh->prepare(qq{INSERT INTO 'tutpoint_tbl'('tut_id','tut_ti
+tle','tut_author') VALUES(?,?,?)});
[download]

Contains (') single quotes, which on MySQL syntax is not correct, I do not know about SQL.

On my previous post I tried to "describe" why I proposed to use (`) backticks and (") double quotes on your MySQL, be carefull maybe the same syntax not apply on SQL. Maybe my explanation was not well defined so, I found this really simple tutorial quotes in Perl that explains with examples the use of single and double quotes. In all programing languages you will use them very often so it would be beneficial to understand how to use them correctly and when to use which.

Finalizing, as everyone correctly proposed I think your code is not even reaching the database syntax, probably the error is somewhere higher at your code.

XML is a bit tricky you need to find a way to debug your code step by step.

Hope this long answer will give you a good start and help you to continue.

To Tanos: FYI am using MySQL database. To debug my code i just wrote simple insert statement in my perl file to check whether values were getting inserted or not.. I removed dat '' (single code) from my insert statement any my values got inserted. Which means there was no problem in connectivity to database. The problem is with xml parsing.