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

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

Using DBI, is there a way for me to access the field names programmatically? Thanks.

Replies are listed 'Best First'.
Re: accessing field names in Access table?
by bart (Canon) on Sep 18, 2003 at 09:07 UTC
    You can access a list of the field names out of a prepared SELECT statement, by using $sth->{NAME}, which returns an array reference — you can turn it into an array/list using @{$sth->{NAME}}. If you want them all converted to uppercase or lowercase, use $sth->{NAME_uc} or $sth->{NAME_lc}.

    Perhaps you want something else. Perhaps you want a complete description of all tables in a database. You don't say. There are ways, yes, but they're generally very driver (red: "DBD") specific. I'm not familiar enough with DBD::ODBC to know the specifics for this driver. I could check if you're really interested.

Re: accessing field names in Access table?
by digger (Friar) on Sep 18, 2003 at 14:12 UTC
    I am just getting into a project that requires me to use an Access db for the backend. I settled on using Win32::ODBC . There is an excellent tutorial here on getting started with the module, and it is very easy.

    The only issue I struggled with is the ODBC connection string. If you can create a system DSN, you don't need to worry. But if not, you will have to write your own connection string.

    The format is
    my $connstring = 'Driver={Microsoft Access Driver (*.mdb)}; Dbq=d:path +\\to\\your\\db.mdb; Uid=user; Pwd=password'
    After you have your connection string, get the field names with the following code:
    my $db = new Win32::ODBC($connstring); $db->Sql('SELECT * FROM table WHERE field = param') $db->FieldNames();
    This will give you an array containing all of the field names in the data set retrieved by the SQL statement.

    One last gotcha - the method calls are case sensitive in this module, so make sure your calls have the same capitalization as the docs.

    Of course, all of this assumes you are working on a win32 platform. If you are on *nix platform, ignore everything I said. There is currently no way to use an Access db natively on *nix. There are the mdbtools mentioned above, and some software that will create a remote connection to win32 box to work with an Access db, but nothing native...yet.

    Good Luck and Happy Coding,
    digger
Re: accessing field names in Access table?
by mpd (Monk) on Sep 18, 2003 at 07:10 UTC
    Supposedly this is possible if you have a running Access server w/ DBD::ODBC.
    If you just have the .mdb file laying around, there's no way currently to access it directly (that I am aware of. I don't use Windows however, so something may be possible in that case.) You don't specify what OS you're using, but under a UNIX-like OS, you can use mdbtools to dump the database to a flatfile and convert at your leisure.
Re: accessing field names in Access table?
by Anonymous Monk on Sep 18, 2003 at 06:38 UTC

    Yes.