Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Long file names in .dbf databases

by talwyn (Monk)
on Oct 06, 2003 at 18:58 UTC ( [id://297036]=perlquestion: print w/replies, xml ) Need Help??

talwyn has asked for the wisdom of the Perl Monks concerning the following question:

I previously attempted to access some foxpro tables using DBI. However neither DBD::XBASE, or ::XBase seem to support long file names in win32. Please see Longfilenames in DBI?? for the previous thread of conversation about this problem. The problem does not appear to be a quoting issue.

I wonder if anyone has any suggestions on an alternate method of reading these file types.

Replies are listed 'Best First'.
Re: Long file names in .dbf databases
by iburrell (Chaplain) on Oct 06, 2003 at 21:54 UTC
    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.

      THanks. I have e-mailed the author but have recieved no answer thus far. The low-level access ::Xbase module will also not accept longfile names.

      I thought of copying the files.. it seemed so kludgy but perhaps it is the best solution.

Re: Long file names in .dbf databases
by Roger (Parson) on Oct 07, 2003 at 02:02 UTC
    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.

      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.

Re: Long file names in .dbf databases
by jZed (Prior) on Nov 06, 2003 at 18:02 UTC
    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".

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://297036]
Approved by Aristotle
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (5)
As of 2024-03-28 13:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found