More PERL/CSV woes...

[Arion23]

Hi all,

I previously had a question regarding PERL interfacing with a CSV datasource via the DBI/DBD-CSV modules.

Since then I've been knocking some code together and am very pleased with the results, but now I've been stumped with another problem: some of the field names in the datasource have spaces in them like "Marine or Freshwater"

So far this hasn't been a problem, referencing such fields like $row->{'Marine or Freshwater'} returns the correct value, but I'd like to be able to structure the queries to search based on values in these fields.

For example, I have the following code in my script:
my($query) = "SELECT * FROM $table_name"; which works well.

I'd like to change the select statement to something like:
SELECT * FROM $table_name WHERE Marine or Freshwater = 'Freshwater'
But this produces a parsing error from the DBD module.

Unfortunately, I have no control over the databsource design - so changing field names is not an option...

Any ideas?

 

[sleuth]

I'm not absolutly clear on what module your using but I can say I've never used it, but If you are tyring to call a scalar name with spaces in it you could do something like this,

Instead of:
$table_name WHERE Marine or Freshwater

$Exact_table_name='table_name WHERE Marine or Freshwater ';
# your table is now:
$$Exact_table

I've had cases where a hash name had a random number after table_ so I had to do just that to retrieve it's data.

Hope I wasn't really off,

Tony

 

[Arion23]

Thanks for the reply Tony, although it wasn't really what I was after.

The table name is not problem, it's a field within the table thats causing me problems. The field is "Marine or Freshwater", with possible values of Freshwater, Marine or Both.

So I'm trying to:

SELECT * FROM tbl_name WHERE "Marine or Freshwater" = 'Marine'

But the DBD-CSV module in perl doesn't seem to be able to handle this... Oh well, I'll keep on digging.

Thanks again.

 

[tsdragon]

I don't know if this will work or not, but it's worth a try.

$fieldname = "Marine or Freshwater";
$fieldvalue = "Marine";
$sth = $dbh->prepare(qq[select * from tbl_name where ? = ?]);
$sth->execute($fieldname, $fieldvalue);

The execute will substitute the values of the variables for the ?'s in the query, and will quote them properly automatically. I've never tried using it to insert a field name, but it might work. Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[kempis555]

I know in MS SQL you can enclose funky column names with [] characters. Perhaps there's something similar to this notation in there?

-K

 

[tsdragon]

In MySQL you can use apostrophes around field names with strange characters. Have you tried that?
Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.