Have you tried emailing the author or DBD::XBase? He would know what it supports, and would be the one to fix any problems.
One solution to consider is renaming or copying the files with names that it can't handle to ones that it can.
Another solution is to use the low-level XBase access modules. Your original code was just reading the data. It doesn't need any of the power of SQL and DBI. You wouldn't have to worry about quoting identifiers in that case.
| [reply] [Watch: Dir/Any] |
| [reply] [Watch: Dir/Any] |
Let's look at how the XBase database(s) work internally to store data first.
In a given XBase database, each database table is stored in a file of the corresponding name. For example, the table USER whould have been stored as USER.dbf.
When you construct an SQL to query data inside USER, you would do something like this:
$dbname = "USER";
$sql = "select USERNAME from $dbname";
And this should work fine.
Now what happens if you have spaces in the filenames, say, "User Names Database.dbf" on windows, which is valid, the SQL query becomes:
$dbname = "User Names Database";
$sql = "select USERNAME from $dbname";
And of cause it will not work, because the SQL string becomes
select USERNAME from User Names Database
which is an invalid SQL statement.
I haven't tried this, but to resolve the long name issue on Windows boxes, you could patch the database names by adding single quotes around it:
$dbname = "User Names Database";
$tblname = "'" . $dbname . "'";
# or $tblname = '"' . $dbname . '"';
$sql = "select USERNAME from $tblname";
And this should work because the new SQL string becomes
select USERNAME from 'User Names Database'
I think this might resolve the long filename issue on Windows machines.
Update:
Ok, I tried my solution, but it didn't work on my Sybase database. It's more of a database implementation issue. **blush**
Try it anyway to see if it works or not. Otherwise the quick solution is to change spaces in the filenames to underscores.
| [reply] [Watch: Dir/Any] [d/l] [select] |
LOL :) I *tried* to warn you :) Thats what the previous thread covered. Thanks for investigating the problem. Its nice to know I am not hallucinating :)
Unfortunately I am trying to read tables generated by another application that still uses them so I cannot rename them. So thus far the best solution appears to be copying to a temp file and then accessing. The low-level driver has the same problem with long filenames. I haven't had a chance to try and nail down the problem in the code.
I have also contemplated using Win32::ODBC or Win32::DBIODBC and using the native VFP drivers. I have also not had an opportunity to try to implement this.
Again Thanks for the attempt, I appreciate it.
| [reply] [Watch: Dir/Any] |
The issue here has nothing to do with the length of the filename, it has to do with the spaces in it. The issue is delimited identifiers, also called quoted identifiers. It is unrelated to quoting of *values* which is the concern of placeholders and $dbh->quote(). It has to do with allowing non-standard names for tables and columns inside delimiters. Spaces in table names are nonstandard. The SQL standard specifies double-quote " as the delimiter but MySQL uses backticks as well as double-quote, Access uses square brackets, some RDBMSs use single quotes.
DBD::XBase does not support delimited identifiers at all. This is usually not a problem because it also doesn't check to see if the table name is standard SQL, but it won't know how to parse table names with spaces in them.
But since a table name is just a file name in DBD::XBase, why not just write a script to rename the files replacing spaces with underscores, run it on *.dbf, and away you go.
Or, if you really don't want to rename your files, you could patch XBase.pm with this single line at linenum 43 of version 2.40:
$filename =~ s/_/ /g;
Replacing the underscore with any character you'd like and just use that character instead of a space in your SQL e.g. "SELECT * FROM foo_bar_baz.dbf" would find file "foo bar baz.dbf". | [reply] [Watch: Dir/Any] |