DBI Sqlite create table dynamically

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

Dear monks

This may be a silly question, but I haven't found a solution. I want to create a table in a Sqlite database with the column names passed to the statement as an array. The number of columns should correspond to the number of elements of the array and the column names to the values of each element. The following doesn't work.

my @columns = qw/author year day month/;
    my $dbh = DBI->connect( "dbi:SQLite:my.db" ) || die "Cannot connec
+t: $DBI::errstr";
    $dbh->do( "CREATE TABLE Data (@columns )" );
    $dbh->disconnect;
[download]

Is it possibile to achive what I described?

Comment on DBI Sqlite create table dynamically Download Code

Replies are listed 'Best First'.
Re: DBI Sqlite create table dynamically by choroba (Cardinal) on May 05, 2017 at 15:18 UTC
Array in double quotes interpolates into its members separated by spaces. That's not the syntax for CREATE TABLE, though: you need the column names separated by commas. Also, if the input is dynamic, there can be a control character in the column name, so you need quoting. #!/usr/bin/perl use warnings; use strict; use feature qw{ say }; use DBI; my @columns = qw( author year` day" month; 42 ); my $dbh = DBI->connect('dbi:SQLite::memory:') or die "Cannot connect: $DBI::errstr"; $dbh->{RaiseError} = 1; my $column_list = join ', ', map $dbh->quote_identifier($_), @columns; $dbh->do("CREATE TABLE data ($column_list)"); $dbh->do('INSERT INTO data VALUES(' . join(', ', 1 .. @columns) . ')') +; my $sth = $dbh->prepare("SELECT $column_list FROM data"); $sth->execute; while (my @row = $sth->fetchrow_array) { say "@row"; } [download] ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l] [select]
Re^2: DBI Sqlite create table dynamically by Anonymous Monk on May 05, 2017 at 16:07 UTC
Perfect! Thank you!	[reply]
Re: DBI Sqlite create table dynamically by MidLifeXis (Monsignor) on May 05, 2017 at 17:20 UTC
As an aside, also be aware of the BobbyTables potential if the array is populated from a potentially uncontrolled source. --MidLifeXis	[reply]
Re^2: DBI Sqlite create table dynamically by dsheroh (Monsignor) on May 06, 2017 at 09:06 UTC
Note that choroba's posted code uses `$dbh->quote_identifier` on the column names which should cover that concern. (The truly paranoid - and this is a case where being paranoid is generally a good thing! - may wish to also do their own checks on the column names, but it shouldn't be necessary unless `DBD::SQLite`'s `quote_identifier` has a major bug which has somehow gone undiscovered.)	[reply] [d/l] [select]
Re^2: DBI Sqlite create table dynamically by Anonymous Monk on May 05, 2017 at 21:23 UTC
Further advices, always look both ways when crossing the street.	[reply]