Problem with DBI:CSV

------=_Part_23858_9479199.1175985778592
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I want to use a given csv file as an Database. My problem ist the
content, if I delete any "." (dot) or "-" everythings work as i would
estimate. But in my case only:

DBD::CSV::st fetchrow_array failed: Attempt to fetch row from a Non-
SELECT statement [for Statement "SELECT * FROM info where
IP=192.168.1.44"] at X:\Scripting\Perl\DBD-CSV\test1\test.pl line 13.

appears.
How could I solve this?

###content of my test.pl###
use DBI;
# $dbh=Database handle
# $sth=Statement handle
#
$dbh = DBI->connect("DBI:CSV:f_dir=X:\\Scripting\\Perl\\DBD-CSV\
\test1")
or die "Cannot connect: " . $DBI::errstr;

$dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\;});
$dbh->{'csv_tables'}->{'info'} = { 'file' => 'test_csv_tabelle'};

$sth = $dbh->prepare("SELECT * FROM info where IP=192.168.1.44");
$sth->execute($baz);
while ( @row = $sth->fetchrow_array ) {
print "@row\n";
}

$sth->finish();
$dbh->disconnect();
###END###
###content of my test_csv_tabelle###
Date;Time;PC_Name;SN;MAC;IP;User;Switch;SW_IP;Port
1;2;3;4;5;6;7;8;9;10
20070104;12:21;PC013;KXXXXXP;
000000000007;192.168.1.44;CK;S130;192.168.1.1;Fa0/17;
###END###

------=_Part_23858_9479199.1175985778592--

Re: Problem with DBI:CSV

You can solve this with DBD::CSV the same way you'd solve it with any rea=
sonably compliant SQL implementation - by putting delimiters around ident=
ifiers that are not valid SQL identifiers. DBD::CSV uses the double quot=
e character as an identifier delimiter, so this will work:

my $sql =3D qq{ SELECT * FROM info WHERE ip =3D "192.168.1.44" };

Also it looks like you are connecting twice - the first connection in you=
r script will be ignored.

--
Jeff

SELCT

> -----Original Message-----
> From: Christian Krauße [mailto:christian.krausse@gmail.com]
> Sent: Saturday, April 7, 2007 10:42 PM
> To: dbi-users@perl.org
> Subject: Problem with DBI:CSV
>
> I want to use a given csv file as an Database. My problem ist the
> content, if I delete any "." (dot) or "-" everythings work as i would
> estimate. But in my case only:
>
> DBD::CSV::st fetchrow_array failed: Attempt to fetch row from a Non-
> SELECT statement [for Statement "SELECT * FROM info where
> IP=3D192.168.1.44"] at X:\Scripting\Perl\DBD-CSV\test1\test.pl line 13.=

>
> appears.
> How could I solve this?
>
> ###content of my test.pl###
> use DBI;
> # $dbh=3DDatabase handle
> # $sth=3DStatement handle
> #
> $dbh =3D DBI->connect("DBI:CSV:f_dir=3DX:\\Scripting\\Perl\\DBD-CSV=
\
> \test1")
> or die "Cannot connect: " . $DBI::errstr;
>
> $dbh =3D DBI->connect(qq{DBI:CSV:csv_sep_char=3D\\;});
> $dbh->{'csv_tables'}->{'info'} =3D { 'file' =3D> 'test_csv_tabelle'=
};
>
> $sth =3D $dbh->prepare("SELECT * FROM info where IP=3D192.168.1.44"=
);
> $sth->execute($baz);
> while ( @row =3D $sth->fetchrow_array ) {
> print "@row\n";
> }
>
> $sth->finish();
> $dbh->disconnect();
> ###END###
> ###content of my test_csv_tabelle###
> Date;Time;PC_Name;SN;MAC;IP;User;Switch;SW_IP;Port
> 1;2;3;4;5;6;7;8;9;10
> 20070104;12:21;PC013;KXXXXXP;
> 000000000007;192.168.1.44;CK;S130;192.168.1.1;Fa0/17;
> ###END###
>

Re: Problem with DBI:CSV

> You can solve this with DBD::CSV the same way you'd solve it with any r=
easonably compliant SQL implementation - by putting delimiters around ide=
ntifiers that are not valid SQL identifiers. DBD::CSV uses the double qu=
ote character as an identifier delimiter, so this will work:
>
> my $sql =3D qq{ SELECT * FROM info WHERE ip =3D "192.168.1.44" };


Er, except I mistakenly thought you were talking about a column name (an =
identifier) rather than a value. Values are delimited by single quote ch=
aracters, not double quote characters, so the correct answer is this:

my $sql =3D qq{ SELECT * FROM info WHERE ip =3D '192.168.1.44' };

To use delimited identifiers would be something like this:

my $sql =3D qq{ SELECT * FROM "c:/foo/bar.csv" WHERE ip=3D'192.168.1.4=
4' };

--
Jeff
>
> Also it looks like you are connecting twice - the first connection in y=
our script will be ignored.
>
> --
> Jeff
>
> SELCT
>
> > -----Original Message-----
> > From: Christian Krauße [mailto:christian.krausse@gmail.com]
> > Sent: Saturday, April 7, 2007 10:42 PM
> > To: dbi-users@perl.org
> > Subject: Problem with DBI:CSV
> >
> > I want to use a given csv file as an Database. My problem ist the
> > content, if I delete any "." (dot) or "-" everythings work as i would=

> > estimate. But in my case only:
> >
> > DBD::CSV::st fetchrow_array failed: Attempt to fetch row from a Non-
> > SELECT statement [for Statement "SELECT * FROM info where
> > IP=3D192.168.1.44"] at X:\Scripting\Perl\DBD-CSV\test1\test.pl line 1=
3.
> >
> > appears.
> > How could I solve this?
> >
> > ###content of my test.pl###
> > use DBI;
> > # $dbh=3DDatabase handle
> > # $sth=3DStatement handle
> > #
> > $dbh =3D DBI->connect("DBI:CSV:f_dir=3DX:\\Scripting\\Perl\\DBD-C=
SV\
> > \test1")
> > or die "Cannot connect: " . $DBI::errstr;
> >
> > $dbh =3D DBI->connect(qq{DBI:CSV:csv_sep_char=3D\\;});
> > $dbh->{'csv_tables'}->{'info'} =3D { 'file' =3D> 'test_csv_tabell=
e'};
> >
> > $sth =3D $dbh->prepare("SELECT * FROM info where IP=3D192.168.1.4=
4");
> > $sth->execute($baz);
> > while ( @row =3D $sth->fetchrow_array ) {
> > print "@row\n";
> > }
> >
> > $sth->finish();
> > $dbh->disconnect();
> > ###END###
> > ###content of my test_csv_tabelle###
> > Date;Time;PC_Name;SN;MAC;IP;User;Switch;SW_IP;Port
> > 1;2;3;4;5;6;7;8;9;10
> > 20070104;12:21;PC013;KXXXXXP;
> > 000000000007;192.168.1.44;CK;S130;192.168.1.1;Fa0/17;
> > ###END###
> >
>
>
>