Re^2: Mysql and Perl Module

Replies are listed 'Best First'.
Re^3: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 12:25 UTC
:D Yup, if you follow Re^3: Mysql and Perl Module, you'll see ... for `my $csv_in =`, you'll have to fill in the missing $csvargs bits	[reply] [d/l]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 13:13 UTC
Thank you so much fpr your help here at first ! Here is my testscript: `#! /usr/bin/perl use lib "/data14/progs/perl_moduls"; use Vars; %v = new Vars; use EdifactSplit; use PK_id; use PK_prot; use File::Basename; use Error; use Switch; use DBI; use edi_mysql; (my module) use IO::Handle; use test2db; (module from topeq for testing) open IN, "< /data14/docs/konvdocs/ebookkto.txt.old"; while (<IN>) { chomp; @val = split(/\:/); @ins = ("tab1","tab2"); &insert_tab ("testdb","testtab", "@ins","@val"); } close IN;` [download] And here the module how it looks like at the moment (very confusing ) # 1 INSERT INTO TABLE # 2. SELECT FROM TABLE sub insert_tab { $db = @_[0]; $table = @_[1]; @TAB = split (/ /,@_[2]); @VALUES = split (/ /,@_[3]); @VAL = (); $csv_in = "/data14/docs/konvdocs/ebookkto.txt.old"; $csvfile = "/data14/docs/konvdocs/ebookkto.txt.old"; open ($infh), '<', $csvfile; #------------------------------old script--------------- #foreach $i (@VALUES) #{ #push (@VAL,"\'".$i."\'"); #} #$fieldlist = join (",", @TAB); #$fieldvalues = join (",", @VAL); $con = "DBI:mysql:$db"; $user = "root"; $passwort = "mypass"; $dbh = DBI->connect("$con","$user","$passwort") \|\| die "DB con +nection not made: $DBI_errstr"; $sql = sprintf "insert into (%s) values (%s) on duplicate key +update", join(',', ,map { $dbh->quote_identifier($_) } @TAB ),join ( +',', ('?') x @TAB); #$sql = qq{ insert into $table ($fieldlist) values ($fieldvalu +es) }; ## is working but only inserting data, no update $sth = $dbh->prepare($sql); while ($row = $csv_in->getline( $infh ) ) { $sth->execute( @{ $row } ); } #$sth->execute(); #$sth->finish(); $dbh->disconnect(); } return 1; [download]	[reply] [d/l] [select]
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 13:29 UTC
:) Aww, you didn't take my message to heart :( update: well on the first part you actually appear to be trying on the the second part I'll start with some notes, please consider them When you add comments to your code, might as well make them real quotes (valid perl syntax), use the #, everything after is ignored It might not matter for your machine but `use Vars;` won't work on case insensitive filesystems because perl comes with vars `$ perl -le " use Vars; print for keys %INC; " warnings.pm warnings/register.pm Vars.pm strict.pm` [download] For some module naming guidelines try Namespace for local/internal modules? and consider MyApp::Vars or My_Corp::Vars or Local::Vars I appreciate the fact that you might not be able to change this for some reason I'll be back in a few minutes with some code, but in the meantime, you might as well check out the sections on argument passing in perlintro and the free Modern Perl book, a loose description of how experienced and effective Perl 5 programmers work....You can learn this too.	[reply] [d/l] [select]
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 14:11 UTC
Here we go, csvtest.pl #!/usr/bin/perl -- use strict; use warnings; use edi_mysql; use Carp::Always; Main( @ARGV ); exit( 0 ); sub Main { my @forDbi = ( 'dbi:SQLite:dbname=temp.test.sqlite', 'username', 'password', ); my $csvargsHashref = { quote_char => '"', sep_char => ":", # no more split /\:/ allow_loose_escapes => 1, empty_is_undef => 1, binary => 1, auto_diag => 1, }; edi_mysql::insert_tab( \@forDbi, [ 'tablename', 'tab1', # column name 'tab2', # column name ], '/data14/docs/konvdocs/ebookkto.txt.old', $csvargsHashref, ); } [download] instead of \@forDbi you can change edi_mysql::insert_tab so it takes a $dbh instead And here is edi_mysql.pm package edi_mysql; use strict; use warnings; use Text::CSV; use DBI; use vars qw/ $VERSION /; $VERSION = 0.01; sub insert_tab { use Data::Dump; dd\@_; my( $forDbi, $tableCol, $csvfile, $csvargs ) = @_; my( $tablename, @columns ) = @{ $tableCol }; open my($infh), '<', $csvfile or die "Can't open '<', $csvfile : $ +!"; my $csv_in = my $csv = Text::CSV->new( $csvargs ) or die "Cannot use CSV: ".Text::CSV->error_diag (); my( $dbiconn, $user, $pass ) = @{ $forDbi }; my $dbh = DBI->connect( $dbiconn, $user, $pass, { RaiseError => 1, PrintError => 1, }, ); $dbh->begin_work; my $sth = $dbh->prepare( sprintf "insert into %s (%s) values (%s) on duplicate key upda +te", $dbh->quote_identifier( $tablename ), join( ',', map { $dbh->quote_identifier($_) } @columns ), join( ',', ('?') x @columns ) ); while ( my $row = $csv_in->getline( $infh ) ) { $sth->execute( @{ $row } ); } $dbh->commit; $dbh->disconnect; } 1; ## without return keyword [download] I've tested it with sqlite, it works	[reply] [d/l] [select]
Re^6: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 14:13 UTC
Re^7: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 14:21 UTC
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 13:35 UTC
Yeah I know. I have to learn much about perl in the future. In our company we are working without use strict and warnings in our scripts. I hope, we can change this in the future.	[reply]
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 13:45 UTC
I hope, we can change this in the future. Here is a secret tip, develop your stuff with `use strict; use warnings;` and when you're confident its working as designed, remove it for the company :)	[reply] [d/l]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 12:59 UTC
But I open it already with open($infh), '<', $csvfile; I can't not bring your first script with the second script together. Sorry for the silly questions, I'm a real gringo on Perl an Mysql.	[reply]
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 13:02 UTC
But I open it already with open($infh), '<', $csvfile; I can't not bring your first script with the second script together. Sorry for the silly questions, I'm a real gringo on Perl an Mysql. Post your attempt at marriage, i'll take a look	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 14:17 UTC
Thank you so much! I'll test the script later on. My time runned out for today :-(.	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 15, 2012 at 08:24 UTC
You are right, when I'm deleting the "on duplicate key" command, then it works ... WAHHHH, I'm again at the beginning. I'll try trace. Thanks for your help again.	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 15, 2012 at 06:42 UTC
Hello again ! I'm testing at the moment, but I get an error message. Maybe my system is missing some module ? DBD::myql::insert_tab('ARRAY(0x938eb9c)', 'ARRAY(0x9384cd8)', '/data14 +/docs/konvdocs/ebookkto.txt.old', 'HASH(0x93 main::Main() called at ./csvtest.pl line 10 DBD::mysql::st execute failed: You have an error in your SQL syntax; c +heck the manual that corresponds to your MySQL servemysql.pm line 45 edi_mysql::insert_tab('ARRAY(0x938eb9c)', 'ARRAY(0x9384cd8)', +'/data14/docs/konvdocs/ebookkto.txt.old', 'HASH(0x93 main::Main() called at ./csvtest.pl line 10 Issuing rollback() due to DESTROY without explicit disconnect() of DBD +::mysql::db handle dbname=testdb at /usr/lib/perl5/e eval {...} called at /usr/lib/perl5/edi_mysql.pm line 45 [download]	[reply] [d/l]
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 15, 2012 at 07:02 UTC
Hello again ! I'm testing at the moment, but I get an error message. Maybe my system is missing some module ? Nope, definitely not missing any module I would guess its this part "on duplicate key update" that is causing the error, you can get more info by raising DBI trace level ( see trace in DBI docs) I had to remove that bit because sqlite3 doesn't support it Earlier versions of mysql did not support that either	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 15, 2012 at 09:01 UTC
So, at the end I'll try an "else" path for update.	[reply]
Re^5: Mysql and Perl Module by FloydATC (Deacon) on Jun 15, 2012 at 10:22 UTC
You could also consider using REPLACE INTO instead of writing your own logic to switch between INSERT INTO and UPDATE. -- Time flies when you don't know what you're doing	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 19, 2012 at 06:59 UTC
Hello Monks, now I know why the "on duplicate key update" command is not really working. I have to give the information what to update. For example `$sth = $dbh->prepare( sprintf "insert into %s (%s) values (%s) on duplicate key upda +te $columns[0]=values($columns[0]),$columns[1]=values($columns[1])", $dbh->quote_identifier( $tablename` [download] Do you have any idea to do this command automatically, I tried this `printf "insert into %s (%s) values (%s) on duplicate key update %s=val +ues(%s),%s=values(%s)", $dbh->quote_identifier( $tablename ), join( ',', map { $dbh->quote_identifier($_) } @columns ), join( ',', ('?') x @columns ), ????` [download]	[reply] [d/l] [select]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 21, 2012 at 14:08 UTC
Hello PerlMonks, now I'm ready with my module, and want to make it a little bit comfortable. Do you have any idea to get the column names before the execute? I want to get them in an array for using in the insert script. Thanks for your ideas.	[reply]