Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

A more elegant solution?

by Cirollo (Friar)
on Jun 05, 2001 at 01:48 UTC ( [id://85624]=perlquestion: print w/replies, xml ) Need Help??

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

I'm trying to write a subroutine that, given a hash of column=>value pairs, will generate a SQL statement to insert those pairs into a database.

So, a statement might look something like INSERT INTO table (col_1, col_1, col_3) VALUES ('foo', 'bar', 'baz')

So, I would generate that string with the hash:

%data = ('col_1'=>'foo', 'col_2'=>'bar', 'col_3'=>'baz');
And then say
insert_hash("table", \%hash);

Here's my subroutine; it seems to work, but I'm hoping that somebody can give me pointers on any better or more elegant ways of doing it.

sub insert_hash { my $table_name = shift; my $rowref = shift; my %row = %$rowref; # ?c? and ?v? are placeholders # Any better ideas for placeholders to pretty up the regexes? my $sql = "insert into $table_name (?c?) values (?v?)"; foreach (keys %hash) { $sql =~ s/\?c\?/$_,\?c\?/g; $sql =~ s/\?v\?/$hash{$_},\?v\?/g; } # Pull off the last comma (extraneous) and the placeholders $sql =~ s/,\?c\?//g; $sql =~ s/,\?v\?//g; return $sql; }

Replies are listed 'Best First'.
Re: A more elegant solution?
by runrig (Abbot) on Jun 05, 2001 at 01:51 UTC
    Use placeholders. Substitute your hash keys in the SQL statement, but substitute the values in the execute. Generate the correct number of '?'s based on the number of fields in the hash.

    see What are placeholders in DBI, and why would I want to use them?.

    Or just execute the statement in the sub.

    Update:Took out the map and put in the 'x' operator like merlyn has.

    sub insert_hash { my $table = shift; my $ref = shift; # Sort fields so we can cache the handle effectively my @fields = sort keys %$ref; my @values = @{$ref}{@fields}; my $sql = "insert into $table (" . join(",", @fields). ") values (". join(",", ("?") x @fields).")"; my $sth = $dbh->prepare_cached($sql); $sth->execute(@values); }
      In particular, your code ends up as:
      sub insert_hash { my $table_name = shift; my $rowref = shift; my %row = %$rowref; my $dbh = shift; # extra parameter needed now my $places = join ",", ("?") x keys %row; my $sql = "insert into $table_name ($places) values ($places)"; $dbh->do($sql, undef, keys %rows, values %rows); }

      -- Randal L. Schwartz, Perl hacker

        Except I want to sort for handle-caching purposes. It doesn't count as the same handle if keys/values are in a different order, so I sort. No, I'm not sure if the benefit of caching outweights the cost of sorting. I'll let someone else benchmark. Probably depends on the database anyway :)

        Update:Umm, I DID use prepare_cached :) DBI won't do the hard work for me, though, unless I make sure the statement stays the same. And the statement won't stay the same unless I sort the field names (DBI's prepared_cached uses the entire SQL statement as a hash key). It also helps, though, to keep statements the same so that the db itself (well, some db's) can cache the statement w/o re-parsing the statement over several executions of the same script (not as efficient as perl/DBI caching, but every bit helps.

        I did not know you could do that join that way w/o map though :)

        IIRC, the DBI spec doesn't support using placeholders for the column names, only values. Programming the Perl DBI, p 221 says the following:

        With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. For example:

        "SELECT name, age FROM ?" #wrong (will probably fail) "SELECT name, ? FROM people" #wrong (but may not 'fail')

        While this may not fail with some databases like MySQL, it *may* fail on databases like Oracle that natively support placeholders. (can anyone confirm this?)

      D'oh. *slaps forehead*

      Another classic case of focusing on one solution, so the other one that is staring you in the face gets ignored.

      But, these hashes will not have a fixed number of elements; do I need to put a string of ?, ?, ... , ? in my statement and then just pass a list through execute? Or is there some placeholder that says "put a list here"?

        But, these hashes will not have a fixed number of elements; do I need to put a string of ?, ?, ... , ? in my statement and then just pass a list through execute? Or is there some placeholder that says "put a list here"?

        Well, if there is not an unholy number of combinations of columns, then this is not a bad solution. If there is going to be a large number of 'different' statements prepared, then this is still ok, but I would just use prepare() instead of prepare_cached.

      Why use prepare_cached?

      It is mainly of use when you have a few statements, that you execute often, but where you are unable to hold a reference to the $sth.

      And it is only usefull if the driver supports it. (Not all drivers do presently).

      You should also note that for some databases prepared statements and placeholders take more time than just $dbh->do'ing the statement with values in place.

      With those databases the only advantage of placeholders is that you don't have to do quoting - which is a big advantage in this situation, as we don't beforehand know the type of the columns, whether numeric or string. And yes that does make a big difference - some databases refuse to accept numbers in quotes as numbers!

        Why use prepare_cached?

        It is mainly of use when you have a few statements, that you execute often, but where you are unable to hold a reference to the $sth. I've never yet been unable to hold a reference, you can store them in package variables or closures. I'd say its more for when its inconvienient to store the handle, so you lazily decide to let DBI do it for you :)

        And I've seen this happen occasionally when people have hashes of column names and values which is when this solution is usually ideal.

Re: A more elegant solution?
by Brovnik (Hermit) on Jun 05, 2001 at 02:35 UTC
    Or, use the set notation :
    my $sql = "INSERT INTO $table_name SET "; for (keys %hash) { $sql .= "$_ = '$hash{$_}' ,"; } chop $sql;
    Ought to do it. It is better to call $dbh->quote($hash{$_}) to quote the string value if you have that handle.
    --
    Brovnik
      Not alway legal.

      Accourding the the databases I use, SET is only allowd in an UPDATE statement.

      You should definitely always use $dbh->quote() insted of just adding '-characters. The latter will fail if the string contains any 's (if you get my meaning).

(dkubb) Re: (1) A more elegant solution?
by dkubb (Deacon) on Jun 05, 2001 at 10:00 UTC

    Here's my attempt at a more elegant way of generating an SQL INSERT statement:

    sub insert_into { my $table = shift; my $row = shift; return sprintf( 'INSERT INTO %s (%s) VALUES (%s)', $table, join(', ', keys %$row), join(', ', ('?') x keys %$row), ); }
Re: A more elegant solution?
by princepawn (Parson) on Jun 05, 2001 at 05:39 UTC
    There are several such modules on CPAN, the most powerful of which is DBIx::Recordset

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://85624]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others about the Monastery: (3)
As of 2024-03-19 07:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found