Thread DBI TRUE - FALSE - UNKNOWN (15 answers)
Opened by Gast at 2009-12-26 11:03

Gast Gast
 2009-01-02 19:02
#129944 #129944
So, jetzt bin ich zufrieden.

Code (perl): (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

#!/usr/bin/perl
use 5.010;
use warnings;
use strict;
use DBI;


my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, csv_null => 1, } ); # csv_null => 0
my $table = 'Table1.csv';
my( $sth, $sql );

sub output {
    my $sql = shift;
    say $sql;
    $sth = $dbh->prepare( $sql );
    $sth->execute();
    while ( my @row = $sth->fetchrow_array ) {
        say DBI::neat_list( \@row )
    }
    say "";
}
 
$dbh->do( "DROP TABLE IF EXISTS $table" );
$dbh->do( "CREATE TABLE $table ( id INT, nachname CHAR(64), vorname CHAR(64) )" );

$sth = $dbh->prepare("INSERT INTO $table( id, nachname, vorname ) VALUES (?,?,?)");

$sth->execute( 1, 'Lohan', 'Lindsay' );
$sth->execute( 2, '', '' );
$sth->execute( 3, undef, undef );


$sql = "SELECT * FROM $table";
output( $sql );
$sql = "SELECT * FROM $table WHERE vorname = 'Lindsay'";
output( $sql );
$sql = "SELECT * FROM $table WHERE vorname <> 'Lindsay'";
output( $sql );

$dbh->disconnect;


Die CSV-Datei schaut immer gleich aus:
Quote
id,nachname,vorname
1,Lohan,Lindsay
2,,
3,,


Ausgabe:
Default ( csv_null = 0 )

Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM Table1.csv
'1', 'Lohan', 'Lindsay'
'2', '', ''
'3', '', ''

SELECT * FROM Table1.csv WHERE vorname = 'Lindsay'
'1', 'Lohan', 'Lindsay'

SELECT * FROM Table1.csv WHERE vorname <> 'Lindsay'
'2', '', ''
'3', '', ''


Mit "csv_null = 1" ( bzw. csv_always_quote = 1, csv_blank_is_undef = 1 )

Code: (dl )
1
2
3
4
5
6
7
8
9
10
SELECT * FROM Table1.csv
'1', 'Lohan', 'Lindsay'
'2', '', ''
'3', undef, undef

SELECT * FROM Table1.csv WHERE vorname = 'Lindsay'
'1', 'Lohan', 'Lindsay'

SELECT * FROM Table1.csv WHERE vorname <> 'Lindsay'
'2', '', ''



Was habe ich daraus gelernt: eine CSV-Datei speichert versteckte Informationen
und

Code (perl): (dl )
1
2
3
4
5
6
7
8
9
10

my $nn;
my $vn = Paul;
my $array_ref = [       [ qw( id nachname vorname ) ],  
                        [ 1, 'Lohan', 'Lindsay' ], 
                        [ 2, undef, 'Karl' ],   # gut
                        [ 3, $nn, $vn ],        # auch gut
                        [ 4, ,'Peter' ],        # nicht gut     
];

$dbh->do( "CREATE TEMP TABLE $table AS import( ? )", {}, $array_ref );

View full thread DBI TRUE - FALSE - UNKNOWN