Re: Mysql and Perl Module

Replies are listed 'Best First'.
Re^2: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 11:23 UTC
Hello, one questins again. The $infh is only the path to my file? For example $infh = "/data1/konv/perl/test.csv";	[reply]
Re^3: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 12:17 UTC
Hello, one questins again. The $infh is only the path to my file? For example $infh = "/data1/konv/perl/test.csv"; No, I said and $infh is a filehandle of your csv file I gave an example in Re^3: Mysql and Perl Module, and Text::CSV shows an example in synopsis ($fh)	[reply]
Re^2: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 12:13 UTC
Now I found my problem. I get an error with the "getline" command. "Can't call method "getline" on an undefined value" D	[reply]
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
Re^5: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 14:11 UTC
Re^6: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 14:13 UTC
Some notes below your chosen depth have not been shown here
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
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
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
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
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]
Re^2: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 09:21 UTC
Hello, I don't understand the meaning of %s ? Is this a hash ? I'm getting my information from an array. My problem is I don't know how many columns I'm getting.	[reply]
Re^3: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 09:33 UTC
Hello, I don't understand the meaning of %s ? Is this a hash ? No, its a string, a format string If you use `perl -MO=B::Deparse,-p myfile.pl` to see how perl parses my code, you can see how that format string is the first argument to the sprintf function I'm getting my information from an array. My problem is I don't know how many columns I'm getting. You don't need to know, the array/code I posted knows that information, and does the right thing -- I linked a runnable example ( Re: Open multiple file handles? ) a few minutes ago in Re^3: Mysql and Perl Module	[reply]
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 10:36 UTC
Thank you Monk :-) I'm trying at the moment to adapt your example on my requirements. This is very helpful.	[reply]
Re^3: Mysql and Perl Module by tospo (Hermit) on Jun 14, 2012 at 09:35 UTC
in sprintf, %s is a placeholder for a string, as in: `$a = "world"; sprintf "hello %s", $a; # results in "hello world"` [download]	[reply] [d/l]