|
Re: Perl DBI + Access bind issues
by mje (Curate) on Dec 11, 2008 at 22:12 UTC
|
I think you are stuffed ;-) There is no way as it stands that DBD::ODBC can know these are not placeholders - the algorithm to search for placeholders is rather simple. We did introduce http://search.cpan.org/~mjevans/DBD-ODBC-1.17/ODBC.pm#odbc_ignore_named_placeholders for a similar reason and I guess we can do so again. If you want to report this as a problem you cannot find a way around on rt.cpan.org for DBD::ODBC I'll try and look into it for you.
| [reply] |
Re: Perl DBI + Access bind issues
by mr_mischief (Monsignor) on Dec 11, 2008 at 23:34 UTC
|
The best fix would be to name the columns something sensible. There is a dirty hack that might get you by, though.
I know some DBDs allow one to use placeholders for column names and fill those in at the time of the execution. I asked about DBD::ODBC in the ChatterBox and kennethk checked on that possibility. He says it works through DBD::ODBC for Access and he confirmed the trick works through DBD::Oracle, too.
This defeats part of the purpose of using a prepare. The execution engine for the DB won't be able to plan the queries for the most efficient execution this way. It might allow you to use column names that have to be escaped, though.
I'd like to thank kennethk for testing the idea when I asked if it would work. He didn't feel like creating a new table with question marks in the column names just to test, but he already went above and beyond to answer the question. It's worth a shot trying it with your column names if you can't get them renamed since he confirmed the trick in general.
My preference would still be to get the columns renamed.
| [reply] |
Re: Perl DBI + Access bind issues
by shmem (Chancellor) on Dec 11, 2008 at 22:06 UTC
|
From the DBI pod:
The "do()" method can be used for non repeated non-"SELECT" statement (or with drivers
that don't support placeholders):
$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");
As a pragmatic solution, I'd go with $dbh->do() rather than $sth = $dbh->prepare(); $sth->execute() to avoid having to write a patch ;-)
...but then, input sanitizing would rest heavily on my own shoulders...
update - uhm, scratch that, note the 'non-"SELECT" statement' above :-(
update 2: thinking about it again - the best solution is to grab a LART and make that user rename the columns.
| [reply]
[d/l]
[select] |
Re: Perl DBI + Access bind issues
by ikegami (Patriarch) on Dec 11, 2008 at 23:43 UTC
|
What does $dbh->quote_identifier('active?') return? Does it return [active?] or does it handle the "?"?
| [reply]
[d/l]
[select] |
Re: Perl DBI + Access bind issues
by KurtSchwind (Chaplain) on Dec 12, 2008 at 01:24 UTC
|
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);
Is that right?
--
I used to drive a Heisenbergmobile, but every time I looked at the speedometer, I got lost.
| [reply]
[d/l] |
|
|
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] |
|
|
| [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] |
|
|
|
|
|
|
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;
| [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] |
Re: Perl DBI + Access bind issues
by eric256 (Parson) on Dec 11, 2008 at 21:07 UTC
|
| [reply] |