Re^2: Not able to insert data in mysql

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]

Comment on Re^2: Not able to insert data in mysql Download Code

Replies are listed 'Best First'.
Re^3: Not able to insert data in mysql by erix (Prior) on May 10, 2014 at 06:05 UTC
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.	[reply]
Re^4: Not able to insert data in mysql by ashishg0310 (Novice) on May 10, 2014 at 07:56 UTC
To erix: Tried INSERT statement with some hard coded values..n data got inserted.... It worked.. now tym to check xml parsing,,,	[reply]
Re^4: Not able to insert data in mysql by ashishg0310 (Novice) on May 10, 2014 at 08:06 UTC
To erix: Tried INSERT statement with some hard coded values..n data got inserted.... It worked.. now tym for xml parsing,,,	[reply]
Re^3: Not able to insert data in mysql by poj (Abbot) on May 10, 2014 at 07:55 UTC
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	[reply] [d/l]
Re^3: Not able to insert data in mysql by thanos1983 (Parson) on May 10, 2014 at 18:04 UTC
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.	[reply] [d/l]
Re^4: Not able to insert data in mysql by Anonymous Monk on May 11, 2014 at 18:59 UTC
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.	[reply]