CODEWITHSUNDEEP
mysqlsqlperl
Sune Hansen
Sune Hansen

Reputation: 13

split a string in Perl into a database

I use the code:

$data_read=$port_obj->read($buffer);
print "$data_read\n";

The output look like this:

T=3420499518  A=914 B=97 C=49 D=436 E=428 F=863 G=34 H=771 I=214 J=493 K=165

I want to put it in this:

$T,$A,$B,$C,$D,$E,$F,$G,$H,$I,$J,$K

So I can write it to a MySQL database. How do I do it ?

My Database handler :

$query = "INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K)
          VALUES ($T,$A,$B,$C,$D,$E,$F,$G,$H,$I,$J,$K)";

$query_handle = $connect->prepare($query);

$query_handle->execute();

Upvotes: 1

Views: 312

Answers (4)

w.k
w.k

Reputation: 8376

I like it this way:

use strict;
use warnings;

my $data_read = "T=3420499518  A=914 B=97 C=49 D=436 E=428 F=863 G=34 H=771 I=214 J=493 K=165";
my %data = split(/\s+|=/, $data_read);

my @cols = keys %data;
my $cols = join(', ', @cols);
my $placeholders = join(', ', ('?')x scalar @cols );

my $query = "INSERT INTO table ($cols) VALUES ($placeholders)";
    # execute like this:
# $dbh->do($query, undef, @data{@cols});

print "$query\n";

Upvotes: 0

Dave Sherohman
Dave Sherohman

Reputation: 46187

Extending on comments to Jonathan Leffler's answer, perldoc keys tells us that:

The keys of a hash are returned in an apparently random order. The actual random order is subject to change in future versions of Perl, but it is guaranteed to be the same order as either the values or each function produces (given that the hash has not been modified).

(emphasis mine) This means we can use a hash to collect all the field names and their associated values without needing to explicitly enumerate them in the code:

#!/usr/bin/env perl

use 5.010;
use strict;
use warnings;

my $in = 'T=3420499518  A=914 B=97 C=49 D=436 E=428 F=863 G=34 H=771 I=214 J=493 K=165';

my %data = $in =~ /([A-Z])=([0-9]+)/g;

my $query = 'INSERT INTO myhouse (' . join(',', keys %data)
          . ') VALUES (' . join(',', values %data) . ')';

say $query;

(Note that, as TLP mentioned in a comment on the original question, this is only safe because the regex which fills %data guarantees us that the keys will all be single uppercase letters and the values will consist solely of English-language digits. If there's any possibility that malicious input could result in uncontrolled keys or values in %data, then you need to hardcode the field names and use placeholders for the values unless you want a visit from little Bobby Tables.)

Upvotes: 2

Borodin
Borodin

Reputation: 126722

You should store the data values in a hash, use placeholders in the prepared SQL statement, and pass a hash slice to the execute method.

The code looks like this

my $data_read = 'T=3420499518  A=914 B=97 C=49 D=436 E=428 F=863 G=34 H=771 I=214 J=493 K=165';

my %data = $data_read =~/([A-Z0-9]+)/g;

my $query_handle = $connect->prepare(<<SQL);
INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
SQL

$query_handle->execute(@data{ qw/ T A B C D E F G H I J K / });

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

I'm not sure that putting them into the individually named variables is the best choice, but the customer is always right:

use strict;
use warnings;

my $data_read = "T=3420499518  A=914 B=97 C=49 D=436 E=428 F=863 G=34 H=771 I=214 J=493 K=165";

my $data = $data_read;
$data =~ s/[A-KT]=//g;

print "$data\n";

my($T,$A,$B,$C,$D,$E,$F,$G,$H,$I,$J,$K) = split /\s+/, $data;

my $query = "INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K) VALUES\n" .
            "($T,$A,$B,$C,$D,$E,$F,$G,$H,$I,$J,$K)";

print "$query\n";

print "\nAlternative:\n";
my(@list) = split /\s+/, $data;
print "@list\n";

my $query2 = "INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K)\nVALUES(" .
             join(",", @list) . ")";

print "$query2\n";

When this snippet is run, it produces:

3420499518  914 97 49 436 428 863 34 771 214 493 165
INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K) VALUES
(3420499518,914,97,49,436,428,863,34,771,214,493,165)

Alternative:
3420499518 914 97 49 436 428 863 34 771 214 493 165
INSERT INTO myhouse (T,A,B,C,D,E,F,G,H,I,J,K)
VALUES(3420499518,914,97,49,436,428,863,34,771,214,493,165)

The second half of the script shows a simpler alternative, using an array to hold the values read in.

Clearly, you can check that values were assigned to all the variables and other such error checking if you wish.

Upvotes: 1

Related Questions