Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

MySQL insert via DBI

by AlexTape (Monk)
on Sep 19, 2011 at 15:14 UTC ( [id://926759]=perlquestion: print w/replies, xml ) Need Help??

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

dear monks,
#!"C:\perl\bin\perl.exe" -w use strict; use DBI(); use Data::Dumper; my $database = "****"; my $hostname = "****"; my $port = "3306"; my $user = "****"; my $password = "****"; my $table = "****"; # connect my $dbh = DBI->connect("DBI:mysql:database=$database;host=$hostname" +, "$user", "$password", {'RaiseError' => 1}); my $scalar = ''; open( my $fh, "+>:scalar", \$scalar ); $dbh->trace( 1, $fh ); $dbh->do("INSERT INTO $table(version,filename,release_notes,rec_leve +l,os_type) VALUES(1, 2, 3, 4, 5)"); # retrieve my $sth = $dbh->prepare("SELECT * FROM $table"); $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) { print Dumper $ref; # output OK => gives a hash of one existing lin +e } $sth->finish(); $dbh->disconnect(); print Dumper $scalar;
i try to import data to a mysql table. the $dbh->do("..") should do this but it wonīt. even if i dump $ref i get a line of the table, for this i suppose that there is a connection available.
show me the bug please...

perhaps there is a better way to do this?
EDIT
This IS a dump from the catched SQL line via this script. for that the connection is done. but the insert wonīt work.
EDIT
$VAR1 = { 'ID' => '2', 'date_compl_green' => undef, 'rec_level' => '1', 'feature_set' => undef, 'date_rec_green' => undef, 'size' => undef, 'os_type' => '1', 'remark' => undef, 'version' => '1.0', 'MD5_checksum' => undef, 'release_notes' => 'note', 'date_rec_yellow' => undef, 'date_compl_red' => undef, 'filename' => 'file', 'date_comp_yellow' => undef, 'date_rec_red' => undef };
$perlig =~ s/pec/cep/g if 'errors expected';

Replies are listed 'Best First'.
Re: MySQL insert via DBI
by graff (Chancellor) on Sep 19, 2011 at 18:01 UTC
    Have you tried using the "mysql" client utility to connect to the database (i.e. without using perl)? This would allow you to:
    • confirm that the username/password are correct
    • confirm that the table exists
    • confirm the names and data types of the columns in the table
    • see whether the insert statement you used in your perl script actually works when you run it at the 'mysql>' command line
    • see whether your perl script succeeded with its "insert" statement (and is only failing on the "select" statement)
      yes i tried. login correct. table exist. for the data, look at the dump => itīs a table line out of the DB. the scriptline actually works:
      mysql>insert into $table (version,filename,release_notes,rec_level,os_ +type) values ("1.0","1.txt","1.txt",3,4)\g
      there is no succeeded message given from perl. only the dump comes out of it.

      my $sth = $dbh->prepare("INSERT INTO $table(version,filename,release +_notes,rec_level,os_type) VALUES(?,?,?,?,?)"); $sth->execute( "test","test","test",1,1);
      $VAR1 = ' DBI::db=HASH(0x35bffc4) trace level set to 0x0/1 (DBI @ 0 +x0/0) in D BI 1.616-ithread (pid 2144) <- prepare(\'INSERT INTO table_1(version,filename,release _notes,rec_level,os_type) VALUES(?,?,?,?,?)\')= ( DBI::st=HASH(0x35ed6 +ec) ) [1 i tems] at sql.pl line 21 <- execute(\'test\', \'test\', ...)= ( 1 ) [1 items] at sql.pl lin +e 22 <- prepare(\'SELECT * FROM nl_firmware_recommendation\')= ( DBI::s +t=HASH(0x3 5d7bcc) ) [1 items] at sql.pl line 24 <- execute= ( 2 ) [1 items] at sql.pl line 25 !! ERROR: 2000 CLEARED by call to fetch method <- fetchrow_hashref= ( HASH(0x35f117c)16keys ) [1 items] row1 at s +ql.pl line 26 !! ERROR: 2000 CLEARED by call to fetch method !! ERROR: 2000 CLEARED by call to fetch method <- fetchrow_hashref= ( undef ) [1 items] row2 at sql.pl line 26 <- finish= ( 1 ) [1 items] at sql.pl line 29 <- disconnect= ( 1 ) [1 items] at sql.pl line 30 ';
      this worked. but why $dbh->do is not able to do this?
      and whats about:     !! ERROR: 2000 CLEARED by call to fetch method?
      $perlig =~ s/pec/cep/g if 'errors expected';
Re: MySQL insert via DBI
by moritz (Cardinal) on Sep 19, 2011 at 15:24 UTC

    Please show us the schema for $table, it's hard to tell what's going on without knowing that.

    It's also a good idea to enable tracing and then run the script, see the TRACING section in DBI.

      hereīs the trace:
      DBI::db=HASH(0x348da4c) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI +1.616-ithread (pid 3144) <- do(\'INSERT INTO table_1(version,filename,release_notes,rec_level,o +s_type) VALUES(1, 2, 3, 4, 5)\')= ( undef ) [1 items] at sql.pl line +21 <- prepare(\'SELECT * FROM table_1\')= ( DBI::st=HASH(0x34bb204) ) + [1 items] at sql.pl line 23 <- execute= ( 1 ) [1 items] at sql.pl line 24 !! ERROR: 2000 CLEARED by call to fetch method <- fetchrow_hashref= ( HASH(0x34bea74)16keys ) [1 items] row1 at s +ql.pl line 25 !! ERROR: 2000 CLEARED by call to fetch method <- fetchrow_hashref= ( undef ) [1 items] row1 at sql.pl line 25 <- finish= ( 1 ) [1 items] at sql.pl line 28 <- disconnect= ( 1 ) [1 items] at sql.pl line 29
      perhaps this is the problem?
      table_1(version,filename,release_notes,rec_level,os_type) VALUES(1, 2, 3, 4, 5)\')= ( undef ) [1 items] at sql.pl line 21
      VALUES = (undef) ?!
      $perlig =~ s/pec/cep/g if 'errors expected';

        Are you using a fork or threads somewhere in your code (in the part that you don't show us)?

        Also please provide the schema for the table, so that we can actually run the script and reproduce your problem.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://926759]
Approved by ChuckularOne
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2024-03-28 11:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found