Hi, it's not possible to UPDATE without knowing the column names. You could get them via a SELECT prior to your UPDATE.

In any case it would not be a good design to do what you want since your program would break as soon as the DB schema changes with a new column added or one removed, etc.

Hope this helps!

If you have a primary key then maybe use REPLACE

A "kludgey" solution however could be as follows:

• SELECT the record you want to update and put this record's data into an array.
• Update this array with the new data.
• DELETE this record.
• INSERT a new record with the updated data from the array.

Hello Anonymous Monk,

I do not think that dynamically updating the DB is possible, a great module that could help you a lot with your queries is SQL::Abstract.

Hope this helps. BR / Thanos

Thank you for the precious suggestions. As far as I can see from the answers, it seems not to be a common things to have applications with a "flexible" database. What is common practice if the number of table columns may vary from user to user. Let's say user A will need x=5 columns while user B x=10. I wanted to create all my statements (create, insert, update and so on) dynamically on the basis of the choosen x value. Is this way to work deprecated? The alternative is to have always x=10 and let user A use less columns... not very elegant. I know this is no more a Perl question...but I would appreciate any thought on this.

I just wrote the program pasted below some days ago which basically takes hashes and makes an INSERT statement out of them.

Update: Upon re-reading your question, I realize that my program completely misses the point, as you want to do UPDATE statements, not INSERT statements. This would be easy if you have the column names and know which column is to be used as the primary key. My program does not know about that though, sorry.

In your case, the interesting parts are where the program constructs the INSERT statement from a hash. It uses placeholders and assigns the hash values to a the list of values based on the order of the column names. I recommend to always use column names and never use positional INSERT statements as the column order of a database might change when columns are dropped or renamed and then other columns with the same name are again.

my $data = [
    { col1 => 'data1', col3 => 'data3' },
    { col2 => 'data2', col3 => 'data3plus' },
];
if( ! @columns ) {
    # Collect all (potential) columns we want to write
    my %columns;
    for( @$data ) {
        $columns{ $_ } = 1
            for keys %$_;
    }
    @columns = sort keys %columns;
};

my $columns = join ",", map { qq("$_") } @columns;
my $placeholders = join ",", ("?") x @columns;

# Run the insert statement for each row in our list
my $sql_insert = qq(insert into "$table" ($columns) values ($placehold
+ers));
[download]

The full program is here:

#!perl -w
use strict;
use DBI;
use JSON;
use Getopt::Long;
use Pod::Usage;
use Path::Class 'file';

GetOptions(
    'table:s'    => \my $table,
    'dsn:s'      => \my $dsn,
    'user:s'     => \my $user,
    'password:s' => \my $password,
    'file:s'     => \my $json_file,
    'json:s'     => \my $json_text,
    'columns:s'  => \my @columns,
) or pod2usage(2);

my $data;
if( $json_file ) {
    $data = decode_json( file( $json_file )->slurp($json_file, iomode 
+=> '<:raw' ));
} elsif( $json_text ) {
    $data = decode_json( $json_text );
} else {
    binmode STDIN, ':raw';
    local $/;
    $data = decode_json( <STDIN> );
};

if( ref $data eq 'HASH' ) {
    # Convert single row to a set of rows
    $data = [$data]
};

if( ! @columns ) {
    # Collect all (potential) columns we want to write
    my %columns;
    for( @$data ) {
        $columns{ $_ } = 1
            for keys %$_;
    }
    @columns = sort keys %columns;
};

my $columns = join ",", map { qq("$_") } @columns;
my $placeholders = join ",", ("?") x @columns;

# Run the insert statement for each row in our list
my $sql_insert = qq(insert into "$table" ($columns) values ($placehold
+ers));
my $dbh = DBI->connect( $dsn, $user, $password, { RaiseError => 1, Pri
+ntError => 0 });
my $sth_insert = $dbh->prepare( $sql_insert );
if( ! $sth_insert->execute_for_fetch( sub {
        my $item = shift @$data;
        if( $item ) {
            my $res = [@{$item}{ @columns }];
            return $res
        } else {
            return $item
        }
    }, \my @results)) {
    for my $err (grep { ref $_ } @results) {
        warn $err->[1];
    };
};
[download]

The traditional way is to have 3 columns.

User ColNo Value
A    1    A1
A    2    A2
B    1    B1
B    2    B2
B    3    B3
B    4    B4
B    5    B5

There are more flexible databases such as mongoDb

poj

What is common practice if the number of table columns may vary from user to user.

Views

Hello again Anonymous Monk,

Since there is no limit on how many tables can SQLite see here Limits In SQLite, why not create different table for each user and then use the REPLACE that fellow Monk poj proposed. By doing so you can have different number of columns for each user based on their requirements.

Hope this helps. BR / Thanos

Seeking for Perl wisdom...on the process of learning...not there...yet!

I feel that having a different table for each user completely removes the entire purpose of a single source of information.

Now, it feels like I'm missing something here, but to maintain such a structure (especially to simply fetch information across all tables) would be a nightmare.

I'm not saying its right or wrong, I'm saying it feels like a kludge that actually goes against any efficiencies and scalability.

The real solution I think is to add a bit more code and do the UPDATE accordingly. That'll keep the DB consistent, and allow OP to still dyanmically do things in a way that would be scalable going forward. I just got to a hotel after a three hour drive so I need more time to digest this (perhaps OP can share more insight into the reason for needing this extreme conciseness) before I have any formal solution.

Could be trivially easy to add another column to the table, perhaps even comma separated that explains which columns each user can populate by name, then grab that info based on the userid, dump it into a hash, and then you know which columns user X has, and just dump undef, NULL or whatever to the others (again, using UPDATE).