Win32::OLE ADO Table Field names

Discussion in 'Perl Misc' started by goldtech, Dec 23, 2007.

  1. goldtech

    goldtech Guest

    Hi,

    Given code below, I can get tables and run SQL on an ms-access db -
    this works well. But, I want to get the field names per each column
    of a table. I'm sure there's an ADO way of doing it. Help appreciated.
    Thanks.

    Question: How do I get the field names of each column of a table?

    #!/usr/bin/perl

    # use strict;
    use Win32::OLE();
    $Win32::OLE::Warn=2;

    my $conn = Win32::OLE->new("ADODB.Connection");
    my $db = 'C:\Folder4\usa.mdb';
    $conn->Open('Provider = Microsoft.Jet.OLEDB.4.0; Data Source='.$db);
    my $rs= $conn->OpenSchema(20);

    $rs->MoveFirst();
    while(!$rs->{EOF}){
    my $tn= $rs->Fields(2)->Value;
    if (grep /^$tn$/i, 'States') {
    my $rowcount = $conn->Execute("SELECT COUNT(*) AS ROW_COUNT
    FROM " .$tn.'"');
    print "$tn : ".$rowcount->Fields('ROW_COUNT')->Value."\n";
    }
    $rs->MoveNext;
    }

    __END__

    states : 51
    DC included.
     
    goldtech, Dec 23, 2007
    #1
    1. Advertising

  2. On Sun, 23 Dec 2007 10:15:38 -0800 (PST), goldtech
    <> wrote:


    It's been a while since I poked around with this... but does this
    help?

    $rs-> Fields->{Count}; # number of columns for this row
    $rs-> Fields( N )->{Name}; # name of the Nth column
    $rs-> Fields( N )->{Value}; # value of the Nth column

    # extract column names from first record
    for my $i ( 0 .. $rs-> Fields->{Count}-1 ) {
    $names{ $rs-> Fields(0)->{Name} } = $i;
    }




    >Hi,
    >
    >Given code below, I can get tables and run SQL on an ms-access db -
    >this works well. But, I want to get the field names per each column
    >of a table. I'm sure there's an ADO way of doing it. Help appreciated.
    >Thanks.
    >
    >Question: How do I get the field names of each column of a table?
    >
    >#!/usr/bin/perl
    >
    ># use strict;
    >use Win32::OLE();
    >$Win32::OLE::Warn=2;
    >
    >my $conn = Win32::OLE->new("ADODB.Connection");
    >my $db = 'C:\Folder4\usa.mdb';
    >$conn->Open('Provider = Microsoft.Jet.OLEDB.4.0; Data Source='.$db);
    >my $rs= $conn->OpenSchema(20);
    >
    >$rs->MoveFirst();
    >while(!$rs->{EOF}){
    > my $tn= $rs->Fields(2)->Value;
    > if (grep /^$tn$/i, 'States') {
    > my $rowcount = $conn->Execute("SELECT COUNT(*) AS ROW_COUNT
    >FROM " .$tn.'"');
    > print "$tn : ".$rowcount->Fields('ROW_COUNT')->Value."\n";
    > }
    > $rs->MoveNext;
    >}
    >
    >__END__
    >
    >states : 51
    >DC included.
     
    still just me, Dec 24, 2007
    #2
    1. Advertising

  3. goldtech

    goldtech Guest

    Got it finally:

    ....
    $oRS = Win32::OLE->new("ADODB.Recordset");
    $oRS->{'ActiveConnection'} = $conn;
    $oRS->Open('States'); # "States" is the table name
    $zztop=$oRS->Fields->Count;
    $who =$oRS->Fields->Item(2)->Name;
    print $zztop.' '.$who.' ';
    ....

    :^)
     
    goldtech, Dec 25, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bob
    Replies:
    1
    Views:
    401
    Lucas Tam
    Jul 30, 2004
  2. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    514
    Patrick.O.Ige
    Jul 16, 2006
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    259
    Lance Hoffmeyer
    Nov 17, 2003
  4. Domenico Discepola

    Win32::OLE and creation of pivot table in Excel

    Domenico Discepola, Dec 3, 2003, in forum: Perl Misc
    Replies:
    4
    Views:
    605
    Domenico Discepola
    Dec 4, 2003
  5. Sound
    Replies:
    2
    Views:
    476
    Randy Webb
    Sep 28, 2006
Loading...

Share This Page