How can I get a list of results when sqlite returns more than one row?

th开发者_运维知识库is is how my table looks:

key | driver  | machine     | result
-----------------------------------
1   | 1234    | abc_machine | pass
2   | 1234    | xyz_machine | fail

when a user selects '1234' from driver and all from machine things get a little messy. (user makes selection from a gui)

when i do:

$getConfig = `sqlite3 abc.db "SELECT machine FROM $table_name WHERE driver='$drvrSel'"`;

it gives me abc_machine xyz_machine.

I tried separating them using split(/ /, $getConfig), but does not work.

Thank you.

I am sorry if i asked this question in a wrong place. I tried finding right place but couldn't find it.

#!/usr/bin/perl

use DBI;
use strict;

my $dbargs = {AutoCommit => 0, PrintError => 1};

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","","",$dbargs);
my $sth = $dbh->prepare("SELECT machine FROM test WHERE driver = 1234");
$sth->execute();

my @data;

while (@data = $sth->fetchrow_array()) {
    print $data[0] . "\n";
}

$sth->finish;
$dbh->disconnect;

Instead of calling the SQLite program, you should use DBI.

By the way:

perl -MData::Dumper -e '$_="abc_machine xyz_machine";print Dumper split / /;'

$VAR1 = 'abc_machine';
$VAR2 = 'xyz_machine';

So, check the data contained in $getConfig again.

There might be a hidden tab or newline character in there. Look at the ascii value of the empty character between the strings and use that in your split call.

Actually sqlite3 supports two command-line options that might be useful to you: -list (the separator is '|') and -csv for csv file. See the man page for sqlite3 for details. You can then split on the separator or use Text::CSV_XS to separate the individual fields

Following Rashids comment below, I had clearly misunderstood your question. My mistake. Here is one way to do this:

my $result = `sqlite3 dbname "query"`;
$result =~ s/\n/ /gs;

ok guys i found it...

\W matches any non-“word” character

`@setConfig = split(/\W/, $getConfig);`

this separates it nicely into:

$setConfig[0] = abc_machine $setConfig[1] = xyz_machine

thanks everyone.