How did you make sure that your XPath expression actually matches nodes?

Consider making DBI die on errors:

my $dbh = DBI->connect($dsn,$username,$password, {RaiseError => 1, Pri
+ntError => 0 })
    or die "cannotconnect to database : $DBI::errstr";
[download]

Also, Basic debugging checklist.

Included your mentioned line "{RaiseError => 1, PrintError => 0 })".. execution was successful..without any error..but still data not inserted..

i think he as defined mentioned node in xml file.. And corion is right "how can you make sure that your XPath expression actually matches nodes?".. Corion is there any way to check that..even i want to know.??

foreach my $row($xp->findnodes('/main/MAIN')){
    print "I matched a node.\n";
};
[download]

I think you forgot: $dbh->commit

Ok, I think the error comes because you have forgot to define the columns ar your MySQL syntax:

my $sth = $dbh->prepare(qq{INSERT INTO `tutpoint_tbl` (`column-1`,`col
+umn-2`,`column-3`) VALUES (?,?,?)});
[download]

Change "column-1,column-2,column-3" with your column names, and also at the execute part:

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

I think it should work now. It would be good to load also the warnings it will help you with decoding errors:

use warnings;
[download]

Update:

I am sorry I did not explain why I reccomend using the following syntax. We use backticks (`) when you to insert a name that might be reserved for enclosing identifiers such as table and column names. MySQL 9.3 Reserved Words, ofcourse if you are not using reserved word you do not need to use backticks but I have preference to use them to help better when I am decoding my code and understand the process step by step.

The double quotes (") we use them because you are executing a MySQL command through strings $string.

Finalyzing, since you are using strings for denoting $username, $password etc. when you loging to mysql, I would use it also for naming the table e.g. `$tutpoint_tbl` and in any point that the name can be changed.

By doing so, you have a more generic code and having a configutation file you can easilly modify the names. In any other case if you want to use another table you will have to change the name on the INSERT function. Well it does not seem so importand but imagine a greater picture where you have UPDATE statements also that are using the same table name and for some reason you decided to change the table and dbase name you have to go and change the values manually in several points when you can just change one.

I hope my explanation makes sense and it is not confusing, in any case please do not hesitate to ask me if you do not understand what I am saying.

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.

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.

Finally done with parsing and insertion.. Thanks a lot to all the people involved in this discussion.. Specially to erix and thanos1983. Learned lots of things other then my question. :) backticks, debugging, qotes ......

To: ashishg0310,

We are glad that we where able to assist even to the minimum possible degree.

It is really nice that there is a community like this one, where people can ask questions and learn so many things.

I would propose to post an answer to your question with the correct syntax that worked for you, it might assist also someone else in the future.