Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^2: Not able to insert data in mysql

by erix (Vicar)
on May 09, 2014 at 07:20 UTC ( #1085553=note: print w/ replies, xml ) Need Help??


in reply to Re: Not able to insert data in mysql
in thread Not able to insert data in mysql

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)


Comment on Re^2: Not able to insert data in mysql
Replies are listed 'Best First'.
Re^3: Not able to insert data in mysql
by thanos1983 (Friar) on May 09, 2014 at 10:39 UTC

    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;

    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.

    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')

    error output:

    #1136 - Column count doesn't match value count at row 1
    ERROR 1136 (21S01): Column count doesn't match value count at row 1

    And also at the MySQL (terminal):

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

    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;

    After:

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

    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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1085553]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (15)
As of 2015-07-07 19:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (93 votes), past polls