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

ok, I am writing a few programs in perl that make use of mysql databases, in several of them I have to deal with files or file contents (text) that contain every character on the table. So now I have 2 questions:

1) is there any utility subroutine that will properly format a string for the mysql insert within 'single quotes', I have tried my own escape subroutines, they work most of the time, but I have so many files and possible cases that I have just about given up on my own cleaning subroutine. -If this subroutine does not exist then is there one that can change the contents of a scalar changing non a-z0-9 characters into something else like hex codes (maybe through CGI) so that I can then re-convert them when I pull the data out of the database?

2) These problems cause the whole script to die w/ a mysql error. I want a way to have the script realize the error occured and just move on to the next file the script will process then make the next entry, etc. I tried using eval but it still closed the script upon error. - In the stuff I am working on in this case having every single entry work is not critical.

  • Comment on Perl DBD::mysql - Clean a scalar, keep running after error.

Replies are listed 'Best First'.
Re: Perl DBD::mysql - Clean a scalar, keep running after error.
by chargrill (Parson) on Feb 07, 2007 at 03:45 UTC

    Check DBI and look for "placeholder" and "bind_param" (primarily for the discussion therein - "execute" will bind your params to an insert statement if you pass it an array)

    Quick example:

    my $dbh;
eval { $dbh = DBI->connect( $dsn, $user, $pass, { 'RaiseError' => 1 } 
+) };
my $insert_query = 'insert into queues values( ?, ?, ?, ?, ?, ?, ?, ?,
+ ? );';
my $st_handle;
eval { $st_handle = $dbh->prepare( $insert_query ) };
eval { $st_handle->execute( @row_values ) };

    [download]

    Checking $@ after each call lets you handle the errors encountered your own way. (Example is woefully incomplete, but culled from an existing program. Modification is left as an exercise for the reader.)



    --chargrill 
    s**lil*;  $*=join'',sort split q**;  s;.*;grr; &&s+(.(.)).+$2$1+; $; =
qq-$_-;s,.*,ahc,;$,.=chop for split q,,,reverse;print for($,,$;,$*,$/)

    [download]
[reply]
[d/l]
[select]
      Thank you, I will give it a try tomorrow.
[reply]
Re: Perl DBD::mysql - Clean a scalar, keep running after error.
by glasswalk3r (Friar) on Feb 07, 2007 at 19:40 UTC

    Regarding the issue number 2, eval should be able to capture the error and give a chance for your code to keep working. Maybe you're evaling the wrong part of the code?

    Please post some of your code so we can give you some more hints.

    Alceu Rodrigues de Freitas Junior
    ---------------------------------
    "You have enemies? Good. That means you've stood up for something, sometime in your life." - Sir Winston Churchill
[reply]
[d/l]

Back to Seekers of Perl Wisdom