Perl DBI::SQLite: How to Dump a table with column names?

Question

Similar to How to get a list of column names on Sqlite3 database?, but still different:

For debugging purposes I wrote a closure that dumps an SQLite3 table. The output is not very pretty, but it works:

sub something($)
{
    my $dbh = shift;
    my $me = '_dump_tables';
    my $sep = '-' x length($me);
    my $dump_table = sub ($) {          # dump specified table or view
        if (defined(my $rows = $dbh->selectall_arrayref(
                        "SELECT * FROM $_[0]"))) {
            my $nsep = '-' x length($_[0]);

            print "$me: $_[0]\n";
            print "${sep}--${nsep}\n";
            foreach (@$rows) {
                print join('|', map { $_ // 'NULL' } @$_), "\n";
            }
        } else {
            print "$me: Houston, we have a problem! ;-)\n";
        }
    };

    #...
    $dump_table->('table_name');
    #...
}

The output might look like this:

_dump_tables: EXAMPLE
---------------------
1|D1|K1
2|D2|K2
3|D3|K3
4|D4|K4
5|D5|K5
6|D6|K6

I'd like to add the column names as the first row, looking for a preferably simple solution.

Some Details

The EXAMPLE table could be considered to be:

CREATE TABLE EXAMPLE (
ID      INTEGER PRIMARY KEY NOT NULL
A       VARCHAR(128) NOT NULL,
B       VARCHAR(128) NOT NULL
);
INSERT INTO EXAMPLE (A, B)
VALUES ('D1', 'K1'), ('D2', 'K2'), ('D3', 'K3'),
       ('D4', 'K4'), ('D5', 'K5'), ('D6', 'K6');

Note: For some reason this worked, even if ID does not have AUTOINCREMENT.

Answer 1

You can use the NAME method, but you need to split the selectall into a prepare, execute and fetchall to have the statement handle to call it:

        my $select = $dbh->prepare("SELECT * FROM $_[0]");
        $select->execute;
        if (defined(my $rows = $select->fetchall_arrayref)) {

            my $nsep = '-' x length($_[0]);

            print "$me: $_[0]\n";
            print "${sep}--${nsep}\n";
            my $header = $select->{NAME};
            foreach ($header, @$rows) {
                print join('|', map { $_ // 'NULL' } @$_), "\n";
            }

or use the column_info method directly on the database. This is less general as it only works for SELECT *.

    my $dump_table = sub ($) {          # dump specified table or view
        if (defined(my $rows = $dbh->selectall_arrayref(
                        "SELECT * FROM $_[0]"))) {

            my @colnames;
            my $info = $dbh->column_info(undef, undef, $_[0], '%');
            while (my $col = $info->fetchrow_arrayref) {
                # COLUMN_NAME is at index 3 (4th field)
                unshift @colnames, $col->[3];
            }

            my $nsep = '-' x length($_[0]);

            print "$me: $_[0]\n";
            print "${sep}--${nsep}\n";
            foreach (\@colnames, @$rows) {
                print join('|', map { $_ // 'NULL' } @$_), "\n";
            }