StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhow to obtain a single row per table with Perl $dbh->tables?
text
Body
copied!<p>I want to retrieve sqlite database schema information as .schema does but with Perl DBI. The first try has been to retrieve table names with <code>$dbh->tables</code> but I obtain the table names repeated. They are one entry for the table itself and one entry for each index that I have (table_a has 3 indexes and b one) what is the rationale behind this?. </p> <pre><code>DB<7> x $dbh->tables; 0 '"main"."table_a"' 1 '"main"."table_a"' 2 '"main"."table_a"' 3 '"main"."table_b"' 4 '"main"."sqlite_master"' 5 '"temp"."sqlite_temp_master"' 6 '"main"."table_a"' 7 '"main"."table_b"' </code></pre> <p>I would appreciate is someone could give a hint for these three related questions: How can obtain only the tables (without doing a uniq) and why there is one row for each index? How can I obtain the indexes info? and all together, how to obtain the equivalent info of .schema? </p> <p>[upate] I have seen that at DBI 1.627</p> <pre><code>@names = $dbh->tables; # deprecated </code></pre> <p>But it does not mention why.</p> <p>and they suggest to use </p> <pre><code>@names = $dbh->tables( $catalog, $schema, $table, $type ); </code></pre> <p>but after reading the DBI table_info, where this params are explained, I have not fully understood how to populate them for obtaining the info of the tables to obtain the table names only once or if it is possible to have it all, the same info that with .schema.</p> <p>Any example or link to more detailed usage would be appreciated.</p> <p>[[UPDATE]]<br> just for future reference these are the two issues that prevented me to find the answer by myself before asking here:</p> <p>1- google direct me to a very old DBD-sqlite docs page with almost nothing in it and I did not pay attention to the link to the latest release at the top of the page.</p> <p>2- Before asking here I read in other threads about table_info (the right answer) but being several years without writing DBI code I fall in one of the newcomers traps. As $dbh->tables returns an array, I did not pay attention that table_info returns a statement handler, so when tried <code>x $dbh->table_info(...)</code> in the debugger and obtained an empty hash I got stuck. I forgot the need to call the fetch part to the $sth returned.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload