http://qs321.pair.com?node_id=729812


in reply to Perl DBI + Access bind issues

I appreciate the suggestions to just rename the columns, but that's not really an option. The reason I'm dealing with this craptastic design is because I'm currently in the middle of re-writing it. Believe me, the new database has sensible names.

In the meantime, this Access database is 'production' and I can't alter it. I'm trying to write some routines to pull all the data out of this database so I can load it effectively into the new one. I need to be able to programatically suck all the data out and load the new database at the drop of a hat. My comfort-zone is *nix, so I'm already dealing with a bout of hives being in Windows to get to the data. I was hoping some of you that spend more time in Windows could tell me to just set the 'do what I want' flag or something and it'd work. (Yes, wishful thinking. :) )

So, let me get the only workable hack straight (psuedo code): 

$db->do("select ?,? from ? where ? = ?", '[is Active?]' ,'[is Deleted?
+]', '[is Deleted?]', 1);

[download]
Is that right?

--
I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.

Replies are listed 'Best First'.
Re^2: Perl DBI + Access bind issues
by pfaut (Priest) on Dec 12, 2008 at 02:01 UTC

    Being it is Access, you could copy the database file and rename the columns in the copy. This way you get quick access to the production data without altering the production database.

    Can you export the tables to CSV and import to your new database from there? I've found this is the fastest way to perform ad-hoc queries against an Access database at work. I dump all of the tables to CSV and import them into PostgreSQL. If your target database can't import from CSV it would be simple to write such a utility in perl.

    90% of every Perl application is already written. ⇒
    dragonchild
[reply]

      I just had another idea. You can't alter the tables but can you create a query? You can rename the columns in the query. SELECT [Active?] as active, .... Then select from the query instead of the table in your perl code.

      90% of every Perl application is already written. ⇒
      dragonchild
[reply]
[d/l]

      I can export each table, but I haven't found a way to automate it. It's pretty tedious to select every table one at a time and export.

      Is there an Access trick I'm missing?

      --
      I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.
[reply]

        Yes. You can script Access using Visual Basic. I wrote a function that exports all of the tables I'm interested in but I don't remember the calls to do it. I can look it up when I get to work tomorrow morning.

        90% of every Perl application is already written. ⇒
        dragonchild
[reply]
Re^2: Perl DBI + Access bind issues
by mr_mischief (Monsignor) on Dec 12, 2008 at 03:21 UTC
    I'm unfamiliar with what the square brackets accomplish for Access, but that's the gist of it. You don't want do though, because you don't get a statement handle back from that. You need to either use prepare, execute, and one of the fetch... methods like fetchall_arrayref or you need to use one of the selectall... methods such as selectall_hashref.

    For example: 

    my $sth = $dbh->prepare( "select ?,? from ? where ? = ?" );
$sth->execute( '[is Active?]' ,'[is Deleted?]', '[is Deleted?]', 1 );
my $array_ref = $sth->fetchall_arrayref;

    [download]
[reply]
[d/l]
[select]

      Right. Sorry. I know it's prepare/execute, not do. I was just giving the jist of the solution. I use DBI all the time for real databases. :)

      The square brackets are a type of quoting that Access uses because they allow freaking spaces and question marks in column and table names.

      --
      I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.
[reply]

In Section Seekers of Perl Wisdom