Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Capturing warnings from DBD:PG

by HeadScratcher (Novice)
on Nov 15, 2015 at 09:57 UTC ( [id://1147720]=perlquestion: print w/replies, xml ) Need Help??

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

Hi I'm hoping someone can help with this?

I'm executing code similar to the code bellow (Not actual but it replicates the problem)

I'm inserting point data into a PostgreSQL/PostGIS database

I spotted this warning and managed to hit the keyboard pause button before it disappeared of the screen.

"WARNING: OGC WKT expected, EWKT provided - use GeomFromEWKT() for this"

The error was caused by badly formatted coordinates that is secondary and will need human intervention to resolve!

My question is how do I trap the warning so I can then do something sensible with the problem record(s).

The database doesn’t return an error through DBI->errstr but returns a non existent lastID

I thought it was Perl warnings but that code doesn’t fire

I’m using version 3.5.1 of DBD::PG

#!C:/strawberry/perl/bin/perl.exe use strict; use warnings; use DBI; use DBD::Pg; my $pwd ="MyPwd"; # my $dbh = DBI->connect('dbi:Pg:dbname=my_geo_table;host=dbhost', 'user', $pwd, { PrintError => 0, HandleError => \&handle_error, } ) or handle_error(DBI->errstr); my $some_text ="hello World"; my $LongDEC = "017.12.270"; my $LatDEC = "44.82115"; my $post_id = 123; my $cmd = "insert into latlong (post_id,some_text,geom) VALUES (" +. $dbh->quote($post_id) . "," . $dbh->quote($some_text) . ", ST_GeomFromText('POINT($LongDEC $LatDEC)', 4326) )"; my $query_handle = $dbh->prepare($cmd); $query_handle->execute(); my $lastID = $dbh->last_insert_id(undef,undef,"latlong",unde +f); if(defined(DBI->errstr)) { ## not getting here my $ErrMsg .= "Error: " . handle_error(DBI->errstr); print $ErrMsg . "\n"; } else { print "Finished with $post_id lastid =[$lastID]\n"; } sub handle_error { my $message = shift; return ("### The Error message from DB is '$message' ###\n"); } ## These subs are for trapping warnings local $SIG{__WARN__} = sub { my $message = shift; print "warning $message\n"; # not getting here logger('warning', $message); }; sub logger { my ($level, $msg) = @_; if (open my $out, '>>', $dir . "\\log.txt") { chomp $msg; print $out "$level - $msg\n"; } } #### print out to cmd WARNING: OGC WKT expected, EWKT provided - use GeomFromEWKT() for thi +s Finished with 123 lastid =[567] 1 records processed 0 error-ed

Replies are listed 'Best First'.
Re: Capturing warnings from DBD:PG
by RichardK (Parson) on Nov 15, 2015 at 10:52 UTC

    Are you sure that you're checking error message of the right call?

    The DBI docs say this :-

    $str = $h->errstr; Returns the native database engine error message from the last DBI + method called. ... and $DBI::err Equivalent to "$h->err". $DBI::errstr Equivalent to "$h->errstr". $DBI::state Equivalent to "$h->state".

    So didn't you just check if the last_insert_id call succeeded?

    You need to check the err and maybe the state after the execute. So at least something like

    $rv = $sth->execute or die $sth->errstr;

    BTW it's probably worth asking this question on the posrgresql mailing list or IRC, as they'll know the best way to do this and it's more of a DB problem than a perl one.

Re: Capturing warnings from DBD:PG
by kennethk (Abbot) on Nov 15, 2015 at 17:30 UTC
    I thought it was Perl warnings but that code doesn’t fire
    That's because of order of operations. local $SIG{__WARN__} = sub {... is a command that executes to modify the warnings handler until you exit the current scope. But if you look at your script, it doesn't get called until immediately before you exit. Try moving that code to before the command that issues the warning.

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

      Thanx

      I moved the block of code to the top of the script and it worked

Re: Capturing warnings from DBD:PG
by Happy-the-monk (Canon) on Nov 15, 2015 at 10:11 UTC

    My question is how do I trap the warning

    Warnings go to STDERR, a file handle that is already open.
    You can redirect them somewhere, as in below example to a file:

    close(STDERR) or die qq(This should not go wrong, but this happened: $!\n); my $warnings = qq(pid.$$.err_warn); # file to contain warnings & error + messages open(STDERR, ">", $warnings) or die qq(unable to open "$warnings" for writing because of: $!\n);

    Cheers, Sören

    Créateur des bugs mobiles - let loose once, run everywhere.
    (hooked on the Perl Programming language)

Re: Capturing warnings from DBD:PG
by Pope-O-Matik (Pilgrim) on Nov 16, 2015 at 14:03 UTC

    $dbh->quote($post_id) . "," .

    A side point, if you don't mind: The code uses dynamic SQL. Instead of building the statement on the fly, please use placeholders instead. That is, something like:

    my $cmd = "insert into latlong (post_id,some_text,geom) VALUES (?, ?, +?);" my $query_handle = $dbh->prepare($cmd); $query_handle->execute($post_id, $some_text, "ST_GeomFromText('POINT($ +LongDEC $LatDEC)', 4326)");

    Using placeholders tends to be safer, more clear, and does not (usually) require special quoting.

      Yes I know however in the past I have had trouble with creating sql cmds that involve geometry entries using placeholders It is thought due to escaping internal quotes this method works for this kind of SQL. For all other SQL commands I use placeholders

        Hmm... I guess that could be a problem. But, it's still worthwhile to use placeholders. Better to escape characters than to escape security.

        A function may do you well though. A little longer just once, and more secure in every script.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (9)
As of 2024-03-28 08:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found