Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Generate A DDL From Access

by Limbic~Region (Chancellor)
on Mar 02, 2012 at 23:29 UTC ( #957566=perlquestion: print w/ replies, xml ) Need Help??
Limbic~Region has asked for the wisdom of the Perl Monks concerning the following question:

All,
Normally when I need to interact with an Access DB, I use DBD::ODBC. Unfortunately, $dbh->column_info() doesn't have enough information to recreate the DDL for the table definition and Access doesn't have system tables containing that information.

According to this article, you can get at the information you want if you Use the ADO and ADOX libraries, or the DAO library in VBA instead.

Is that possible from Win32::OLE? If so, how would you go about starting to create a DDL from an existing table? I never shy away from lots of reading but having some sample code would be greatly appreciated.

Cheers - L~R

Comment on Generate A DDL From Access
Re: Generate A DDL From Access
by Gangabass (Priest) on Mar 03, 2012 at 00:08 UTC
    I don't know but this may be helpfull.

    Thanks.
    Roman

Re: Generate A DDL From Access
by remiah (Hermit) on Mar 03, 2012 at 11:58 UTC

    $dbh->column_info() doesn't provide index, pk information.
    But $dbh->statistics_info() will return it.
    I am using it through DBD::ADO and it seems working.

    ddl like
    create table test1 (test1 varchar(14)); create index test1_pk on test1 (test1);
    creates "tabel1" with primary key and column_info() returns information like this
    ###Table: test1, test1, VarChar, 14,
    And statistics_info() will return index information like this.
    ###Indeces: test1_pk( btree,non unique) columns=1:test1
    I hope this helps you.

      remiah,
      Grrr. I had a nice long response and lost it.

      Would it be possible to share what you are using for a connection string and a complete copy/paste of what you see for table_info(), column_info() and statistics_info(). I am trying to determine if things like collate, constraints, etc will be available and I am having problems getting a connection working.

      Cheers - L~R

        I am not sure about collate. If you find one, please tell me. Here is my code. Method "describe" calls collumn_info() and statistics_info().

        And my quistion: I want to make a package which treats MS access dialect (they treat '*' for '%' and no limit, offset keyword). Package which can do like this.

        my $db=connect(\%dbinfo) or die $!; my $sql ="select * from test1 where fld1 like 'monk%' offset 10 limit +10"; foreach my $r ( $db->fetch($sql) ){ print "$r->{fld1}\n"; } $db->disconnect;
        I want to make a package which can easily switch MS Access and other database like SQLite. I am interested in DBIX::Class but I am not sure it is the way to go... If you have some advice for me, I am glad. Any comments, advice, welcome.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2014-08-29 08:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (277 votes), past polls