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

Question

Hello,

this 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.

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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

Answer 5

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.