Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: perl and psql

by Xilman (Friar)
on Dec 02, 2009 at 13:03 UTC ( #810547=note: print w/replies, xml ) Need Help??


in reply to perl and psql

Yes you can and in a very simple manner. I'll sketch the outline and leave you to fill in the details as it's a very useful learning exercise.

Instead of printing, call a SQL command to insert them into a table. That is, use something like something like:

$dbh->foreach my $cluster (@clusters) { do ("INSERT INTO $table (start, end) VALUES (cluster[0], cluster[1]);" +); } # Ensure that the insertion is committed (or set auto-commit when fir +st connecting to the DB). $dbh->commit();

This presumes you have created a psql table, the name of which is held in $table, with columns called 'start' and 'end'. Creating that table is left as an easy exercise.

Paul

Replies are listed 'Best First'.
Re^2: perl and psql
by almut (Canon) on Dec 02, 2009 at 14:18 UTC
    $dbh->do ("INSERT INTO $table (start, end) VALUES (cluster[0], cluster[1]);");

    In general, it's a good idea to use placeholders (like the OP already did in the SELECT statement), because that way you avoid potential quoting issues, and don't run the risk of SQL injections...

    foreach my $cluster (@clusters) { my $sth = $dbh->prepare("INSERT INTO $table (start, end) VALUES (? +, ?)") or die $dbh->errstr; $sth->execute($cluster->[0], $cluster->[1]) or die $sth->errstr;; }

    (assuming the interpolated variable $table is "safe", i.e. initialised from a static name stored program-internally...)

Re^2: perl and psql
by gogoglou (Beadle) on Dec 02, 2009 at 14:14 UTC
    thanks for your reply. I am really new in perl soany help is usefull. I get a small error message though. first I create at he top of the script my $table which has a start and an end position, but then when I replace the print with the example you gave me I get an error that says that my clusters requires explicit package name. I don't really understand why this happens in this case, since the clusters have been declared. Any ideas would be really usefull. Thanks

      There was a bug in the code that was posted as a reply. I don't think Xilman intended that code to be blindly copy-and-pasted, but here's what's probably going on:

      # This should not have the '$dbh->' at the beginning, but the real iss +ue is # $cluster here is a scalar, but foreach my $cluster (@clusters) { # here, they are being treated as arrays (if they were interpolated as + written. # $dbh->do ("INSERT INTO $table (start, end) VALUES (cluster[0], clust +er[1]);"); # a better approach, if your DBI module supports placeholders: $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", @{$cluster} +) # or even: $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", +$cluster->[0], $cluster->[1]) }

      Again, this is untested code, and may not work as advertised. If the -> and @{} are confusing you, check out perlreftut for more info on references.


      @_=qw; Just another Perl hacker,; ;$_=q=print "@_"= and eval;
        $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?);", @{$cluster})

        When you use do with placeholders and bind values, you also need to specify the \%attr hashref (for syntactical reasons, because the synopsis is $dbh->do($statement, \%attr, @bind_values)).  You may use undef if you don't have any attributes.

        $dbh->do ("INSERT INTO $table (start, end) VALUES (?,?)", undef, @$clu +ster)

        Thank you. It was an unfortunate cut&paste error on my part.

        I'm well aware, of course, that place holders are a Good Idea(tm). The original Perl script from which I made the c&p used them but I removed them from the posted snippet to simplify it.

        As I said originally, the OP should take my fragment as a hint for a learning exercise and not as a complete answer.

        Paul

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://810547]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2020-05-29 19:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If programming languages were movie genres, Perl would be:















    Results (170 votes). Check out past polls.

    Notices?