DBI:CSV join ... flashback to 2002

Question about joining using DBI:CSV. I must be making a stupid
mistake somewhere.

Earlier posts (2002) state that there were problems using aliases in
joins. I've just installed the modules on a windows machine and am
having the same problems.
DBI (v1.58)
SQL-Statement(v1.15)
Text-CSV_XS(v.3)
DBD-CSV(v.22)
DBD-File(v.35)

Running a simple query with no aliases works.

When I run the same query with an alias i get an error message
(below). However, the error message is followed by the correct query
results.

$sth = $dbh->prepare("Select b.subject from table_b.csv as b");
$sth = $dbh->prepare("Select a.subject from table_a.csv as a");
>>SQL ERROR: Table 'CSV' referenced but not found in FROM list!


I then tried a simple join (using where a.subject=b.subject). I get
the error referenced in previous posts.

$sth = $dbh->prepare("Select a.subject from table_a.csv as a,
table_b.csv as b where a.subject=b.subject");
>>DBD::CSV::st execute failed: Can't call method "col_names" on unblessed reference at c:\perl\site\lib\SQL\Statement.pm line 610, line 1.
[for Statement "Select a.subject from table_a.csv as a, table_b.csv as
b where a.subject=b.subject"] at temp.pl line 14


I then tried using the join syntax instead of the WHERE statement as
suggested in the previous post, but i get an error message

$sth = $dbh->prepare("Select * from table_a.csv natural join
table_b.csv");
>> SQL ERROR: Couldn't parse the explicit JOIN!

table_a.csv
-----------------
chromosome,snp,subject,xx
1,rs1203,102,A
1,rs1203,1025,A
1,rs1203,1034,A
1,rs1203,1078,A

table_b.csv
-----------------
subject
102
1025
1034


If anyone has any suggestions, please let me know. I would greatly
appreciate it.

cheers,
david

Re: DBI:CSV join ... flashback to 2002

The problem seems to be the dot in the table names. The dot have special
meanings in SQL for table names and is not just an allowed character (for
example it is used as scheme separator 'scheme.tablename'). Just try to rename
Your CSV-files by cutting of the extension '.csv'. May be all the problems will
be gone then.

Best regrads

Robert

----

ballard.d@gmail.com schrieb:
> Question about joining using DBI:CSV. I must be making a stupid
> mistake somewhere.
>
> Earlier posts (2002) state that there were problems using aliases in
> joins. I've just installed the modules on a windows machine and am
> having the same problems.
> DBI (v1.58)
> SQL-Statement(v1.15)
> Text-CSV_XS(v.3)
> DBD-CSV(v.22)
> DBD-File(v.35)
>
> Running a simple query with no aliases works.
>
> When I run the same query with an alias i get an error message
> (below). However, the error message is followed by the correct query
> results.
>
> $sth = $dbh->prepare("Select b.subject from table_b.csv as b");
> $sth = $dbh->prepare("Select a.subject from table_a.csv as a");
>>> SQL ERROR: Table 'CSV' referenced but not found in FROM list!
>
>
> I then tried a simple join (using where a.subject=b.subject). I get
> the error referenced in previous posts.
>
> $sth = $dbh->prepare("Select a.subject from table_a.csv as a,
> table_b.csv as b where a.subject=b.subject");
>>> DBD::CSV::st execute failed: Can't call method "col_names" on unblessed reference at c:\perl\site\lib\SQL\Statement.pm line 610, line 1.
> [for Statement "Select a.subject from table_a.csv as a, table_b.csv as
> b where a.subject=b.subject"] at temp.pl line 14
>
>
> I then tried using the join syntax instead of the WHERE statement as
> suggested in the previous post, but i get an error message
>
> $sth = $dbh->prepare("Select * from table_a.csv natural join
> table_b.csv");
>>> SQL ERROR: Couldn't parse the explicit JOIN!
>
> table_a.csv
> -----------------
> chromosome,snp,subject,xx
> 1,rs1203,102,A
> 1,rs1203,1025,A
> 1,rs1203,1034,A
> 1,rs1203,1078,A
>
> table_b.csv
> -----------------
> subject
> 102
> 1025
> 1034
>
>
> If anyone has any suggestions, please let me know. I would greatly
> appreciate it.
>
> cheers,
> david
>
>

RE: DBI:CSV join ... flashback to 2002

Another way to remove the dot in the query is as follows.

$dbh->{'csv_tables'}->{table_a} =3D { 'file' =3D> table_a.csv };
$dbh->{'csv_tables'}->{table_b} =3D { 'file' =3D> table_b.csv };

$sth =3D $dbh->prepare("Select a.subject from table_a as a, table_b as b
where a.subject=3Db.subject");

I haven't verified it will resolve your issue, but if it works for you,
you don't have to rename all your files.
=20

Good luck,

Curtis


-----Original Message-----
From: Robert Roggenbuck [mailto:rroggenb@mathematik.Uni-Osnabrueck.DE]=20
Sent: Monday, July 16, 2007 8:25 AM
To: ballard.d@gmail.com
Cc: dbi-users@perl.org
Subject: Re: DBI:CSV join ... flashback to 2002

The problem seems to be the dot in the table names. The dot have special
meanings in SQL for table names and is not just an allowed character
(for example it is used as scheme separator 'scheme.tablename'). Just
try to rename Your CSV-files by cutting of the extension '.csv'. May be
all the problems will be gone then.

Best regrads

Robert

----

ballard.d@gmail.com schrieb:
> Question about joining using DBI:CSV. I must be making a stupid=20
> mistake somewhere.
>=20
> Earlier posts (2002) state that there were problems using aliases in=20
> joins. I've just installed the modules on a windows machine and am=20
> having the same problems.
> DBI (v1.58)
> SQL-Statement(v1.15)
> Text-CSV_XS(v.3)
> DBD-CSV(v.22)
> DBD-File(v.35)
>=20
> Running a simple query with no aliases works.
>=20
> When I run the same query with an alias i get an error message=20
> (below). However, the error message is followed by the correct query=20
> results.
>=20
> $sth =3D $dbh->prepare("Select b.subject from table_b.csv as b"); $sth =
=3D

> $dbh->prepare("Select a.subject from table_a.csv as a");
>>> SQL ERROR: Table 'CSV' referenced but not found in FROM list!
>=20
>=20
> I then tried a simple join (using where a.subject=3Db.subject). I get=20
> the error referenced in previous posts.
>=20
> $sth =3D $dbh->prepare("Select a.subject from table_a.csv as a,=20
> table_b.csv as b where a.subject=3Db.subject");
>>> DBD::CSV::st execute failed: Can't call method "col_names" on
unblessed reference at c:\perl\site\lib\SQL\Statement.pm line 610,
line 1.
> [for Statement "Select a.subject from table_a.csv as a, table_b.csv as

> b where a.subject=3Db.subject"] at temp.pl line 14
>=20
>=20
> I then tried using the join syntax instead of the WHERE statement as=20
> suggested in the previous post, but i get an error message
>=20
> $sth =3D $dbh->prepare("Select * from table_a.csv natural join=20
> table_b.csv");
>>> SQL ERROR: Couldn't parse the explicit JOIN!
>=20
> table_a.csv
> -----------------
> chromosome,snp,subject,xx
> 1,rs1203,102,A
> 1,rs1203,1025,A
> 1,rs1203,1034,A
> 1,rs1203,1078,A
>=20
> table_b.csv
> -----------------
> subject
> 102
> 1025
> 1034
>=20
>=20
> If anyone has any suggestions, please let me know. I would greatly=20
> appreciate it.
>=20
> cheers,
> david
>=20
>=20