Mysql and Perl Module

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

Replies are listed 'Best First'.
Re: Mysql and Perl Module by tospo (Hermit) on Jun 14, 2012 at 08:27 UTC
First of all: take the whole setting up out of the subroutine and into the main body of that script. That includes the DBI->connect stuff and preparing a statement handle. This will make your script much faster because you don't keep reconnecting for every single row you want to insert. Same for the preparation of the statement handle. Prepare it once and pass the handle around (into the subroutine). The next thing you should look up is how to use placeholders in your statement (questionmarks in the statement that are bound to values in the execute method. Then there is the question of what it means if you have less than the full set of values in one row of your csv file. I guess those are "NULL" in your database, so you justneed to ensure that those values are "undef", which will insert them as NULLs.	[reply]
Re^2: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 08:41 UTC
Hello Tospo, the problem is it should be an module we can use for all of our customer scripts. The point is, that we need a module where I can only give a few arguments to it. Is it better to make another sub for the connection ?	[reply]
Re^3: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 09:26 UTC
See this example program Re: Open multiple file handles? You can easily create a module with a function like this sub IntoDbImportCSV { my( $dbiconn, # dbi connection string $colnames, # column names $csvfile, # path to csv file $csvargs, # Text::CSV->new options hash ) = @_; my $dbh = DBI->connect( $dbiconn, undef, undef, { RaiseError => 1, PrintError => 1, }, ); open my($infh), '<', $csvfile ... my $csv_in = ... $dbh->begin_work; my $sth = $dbh->prepare($sql); while ( my $row = $csv_in->getline( $infh ) ) { $sth->execute( @{ $row } ); } $dbh->commit; $dbh->disconnect; } IntoDbImportCSV( 'dbi:SQLite:dbname=temp.test.sqlite', '/path/to/foo.cvs', ['ro', 'sham', 'bo' ], { quote_char => '"', sep_char => ',', allow_loose_escapes => 1, empty_is_undef => 1, binary => 1, auto_diag => 1, }, ); [download]	[reply] [d/l]
Re^3: Mysql and Perl Module by tospo (Hermit) on Jun 14, 2012 at 09:29 UTC
The ideal situation for that scenario is that your module takes some sort of config file with the DSN for the databse connection so that you can ship it with a tailor-made config file for each of your customers. Look at something like Config::General to handle such a config. Alternatively, you could make the DSN an argument that needs to be supplied by the user when running this script. If you have a lot of stuff to do with that database then it would also be worth exploring something like DBIx::Class to handle your database in a much more abstract way.	[reply]
Re: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 08:58 UTC
This is the idiom or recipe `my @Columns = qw/ ro sham bo /; my $sql = sprintf "insert into (%s) values (%s) on duplicate key updat +e ", join( ',', map { $dbh->quote_identifier($_) } @Columns ), join( ',', ( '?' ) x @Columns ); my $sth = $dbh->prepare($sql); while ( my $row = $csv_in->getline( $infh ) ) { $sth->execute( @{ $row } ); }` [download] Where $csv_in is a Text::CSV object and $infh is a filehandle of your csv file You could also read the file using using DBD::AnyData or DBD::CSV	[reply] [d/l]
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
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
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
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
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
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 15, 2012 at 08:24 UTC
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 15, 2012 at 06:42 UTC
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
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
Re^4: Mysql and Perl Module by endymion (Acolyte) on Jun 21, 2012 at 14:08 UTC
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
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]
Re: Mysql and Perl Module by tinita (Parson) on Jun 14, 2012 at 08:43 UTC
crosspost: http://www.perl-community.de/bat/poard/thread/17482 (german)	[reply]
Re^2: Mysql and Perl Module by endymion (Acolyte) on Jun 14, 2012 at 08:54 UTC
Sorry for the cross post.	[reply]
Re^3: Mysql and Perl Module by Anonymous Monk on Jun 14, 2012 at 09:04 UTC
Cross-posting is allowed , but you should post a link/url. To quote davido on cross-posting Please make sure when you cross-post that you identify such, so that people here (or there) don't end up working on a problem that is solved. Identifying cross-posting will promote collaborative progress too. Getting a bunch of people doing research for you in parallel without knowing about each others' progress defeats the collaboration aspect of public forums.	[reply]