#! /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]

# 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]

:) 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.

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

The Modern Perl book covers all this too, but see also Tutorials: Simple Module Tutorial , Variable Scoping in Perl: the basics, Coping with Scoping , Mini-Tutorial: Perl's Memory Management

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

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

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]

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

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

$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]

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]