Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement

by shadowsong (Monk)
on Aug 28, 2015 at 09:16 UTC ( #1140308=note: print w/replies, xml ) Need Help??


in reply to Re^3: Interpolating subroutine call in SQL INSERT INTO SELECT statement
in thread Interpolating subroutine call in SQL INSERT INTO SELECT statement

chacham,

What I was trying ascertain was whether I could invoke my perl function (at the end of this comment) within the midst of the SQL INSERT INTO/SELECT FROM statement. Now, the following responses from other monks confirmed my suspicions...

You might be able to do simple interpolation using nothing but SQL inline with a few subqueries and some trivial math.

If you truley need a perl function to do the job, you'll have to do a select in one call. And then prep an insert data set for a second call.
KurtSchwind


The very-short (but definitive) answer is ... “no.”

And here’s why: “any ... SQL server has no idea who-or-what its client is ... nor does it care.” The server knows nothing about you ... therefore, nothing about your programming language (or even that you are using one ...) ... therefore, nothing about your “functions.” It has no ability,whatsoever, to “call them.”

If you desire to “do something fancy,” then you must provide all of the code to do it. You must issue a SELECT statement, and, for each row returned, create and issue appropriate-to-you INSERTs, probably within a transaction.
sundialsvc4

I also considered this from 1nickt...

Even a broken clock is right once every 86400 seconds. But did you read and follow the other advice given in response to your question, where a number of experienced programmers explained how you can create a function in your SQL?

Vicar poj was also very kind to provide examples of how to create SQL functions in perl and use them. So I believe I'm now straight on what I can and can't do... Here's the function I coded in perl to modify COLUMN3 before inserting it back into TABLE1.

sub convert { ### This subroutine accepts a single parameter (a scalar which it +treats as a string) ### and sequentially substitutes out "invalid" characters with the +ir "valid" counterparts ### based on a fixed list of characters to replace. my $input_stream = shift; return undef unless $input_stream; my %char_swap_hash = ( 1 => {OLD => '&[^(amp;)|(lt;)|(gt;)]', NEW => '&amp;'}, # don' +t match legit '&' codes 2 => {OLD => '<', NEW => '&lt;'}, 3 => {OLD => '>', NEW => '&gt;'} ); $input_stream =~ s/$char_swap_hash{$_}{OLD}/$char_swap_hash{$_}{NE +W}/g for keys %char_swap_hash; return $input_stream; }
  • Comment on Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement
  • Download Code

Replies are listed 'Best First'.
Re^5: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by poj (Abbot) on Aug 28, 2015 at 15:14 UTC

    The match for 1 is incorrect, you can't use a character class like that. Try this test

    #!perl use strict; use YAPE::Regex::Explain; my $str = '&gt; milk &amp; honey &lt; < bill&ben >'; sub convert { my $input_stream = shift; return undef unless $input_stream; my %char_swap_hash = ( 1 => {OLD => '&[^(amp;)|(lt;)|(gt;)]', NEW => '&amp;'}, # don't mat +ch legit '&' codes 2 => {OLD => '<', NEW => '&lt;'}, 3 => {OLD => '>', NEW => '&gt;'} ); $input_stream =~ s/$char_swap_hash{$_}{OLD}/$char_swap_hash{$_}{NEW} +/g for keys %char_swap_hash; return $input_stream; } print YAPE::Regex::Explain->new('&[^(amp;)|(lt;)|(gt;)]')->explain; print convert($str),"\n";

    Alternative

    my %htm = ( '&' => '&amp;', '>' => '&gt;', '<' => '&lt;', ); my $REx = qr'(&(?!(?:amp|lt|gt);)|[><])'; $str =~ s/$REx/$htm{$1}/g; print $str."\n\n"; print YAPE::Regex::Explain->new($REx)->explain;
    poj

      poj - you're right...

      While testing it I realized I was getting back dodgy results, it turns out what I needed to do was use negative lookahead

      I ended up changing the value of key 1 in %char_swap_hash to this:

      1 => {OLD => '&(?!(amp;)|(lt;)|(gt;))', NEW => '&amp;'}, # don't match + legit '&' codes

      I then saw your solution which not only confirmed that attempting to use a character class to evaluate lookaheads was bonkers but introduced me YAPE::Regex::Explain - which at the moment isn't being found by cpanm (so I am unable to easily install and use it; which is more than a little irritating)

      However, many thanks poj - I appreciate all your help.

        When you get YAPE::Regex::Explain installed the result for your 'old' regex should be

        The regular expression: (?-imsx:&[^(amp;)|(lt;)|(gt;)]) matches as follows: NODE EXPLANATION ---------------------------------------------------------------------- (?-imsx: group, but do not capture (case-sensitive) (with ^ and $ matching normally) (with . not matching \n) (matching whitespace and # normally): ---------------------------------------------------------------------- & '&' ---------------------------------------------------------------------- [^(amp;)|(lt;)|(gt;) any character except: '(', 'a', 'm', 'p', ] ';', ')', '|', '(', 'l', 't', ';', ')', '|', '(', 'g', 't', ';', ')' ---------------------------------------------------------------------- ) end of grouping ----------------------------------------------------------------------
        and for mine
        The regular expression: (?-imsx:(&(?!(?:amp|lt|gt);)|[><])) matches as follows: NODE EXPLANATION ---------------------------------------------------------------------- (?-imsx: group, but do not capture (case-sensitive) (with ^ and $ matching normally) (with . not matching \n) (matching whitespace and # normally): ---------------------------------------------------------------------- ( group and capture to \1: ---------------------------------------------------------------------- & '&' ---------------------------------------------------------------------- (?! look ahead to see if there is not: ---------------------------------------------------------------------- (?: group, but do not capture: ---------------------------------------------------------------------- amp 'amp' ---------------------------------------------------------------------- | OR ---------------------------------------------------------------------- lt 'lt' ---------------------------------------------------------------------- | OR ---------------------------------------------------------------------- gt 'gt' ---------------------------------------------------------------------- ) end of grouping ---------------------------------------------------------------------- ; ';' ---------------------------------------------------------------------- ) end of look-ahead ---------------------------------------------------------------------- | OR ---------------------------------------------------------------------- [><] any character of: '>', '<' ---------------------------------------------------------------------- ) end of \1 ---------------------------------------------------------------------- ) end of grouping ----------------------------------------------------------------------
        and your new regex
        The regular expression: (?-imsx:&(?!(amp;)|(lt;)|(gt;))) matches as follows: NODE EXPLANATION ---------------------------------------------------------------------- (?-imsx: group, but do not capture (case-sensitive) (with ^ and $ matching normally) (with . not matching \n) (matching whitespace and # normally): ---------------------------------------------------------------------- & '&' ---------------------------------------------------------------------- (?! look ahead to see if there is not: ---------------------------------------------------------------------- ( group and capture to \1: ---------------------------------------------------------------------- amp; 'amp;' ---------------------------------------------------------------------- ) end of \1 ---------------------------------------------------------------------- | OR ---------------------------------------------------------------------- ( group and capture to \2: ---------------------------------------------------------------------- lt; 'lt;' ---------------------------------------------------------------------- ) end of \2 ---------------------------------------------------------------------- | OR ---------------------------------------------------------------------- ( group and capture to \3: ---------------------------------------------------------------------- gt; 'gt;' ---------------------------------------------------------------------- ) end of \3 ---------------------------------------------------------------------- ) end of look-ahead ---------------------------------------------------------------------- ) end of grouping ----------------------------------------------------------------------
        poj
Re^5: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by chacham (Prior) on Aug 28, 2015 at 14:21 UTC

    SQL forums are often filled with question of how to do fancy things, and the answer is usually not with a custom function. Between joins, where clauses, with clauses, and recursive queries, you can do quite a bit. It's important to state what you are to do as opposed to deciding a solution and asking how to implement it. For all you know, the best answer might even be perl based!

    You can choose to share or not to share. People here are often willing to help if you let them.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (9)
As of 2019-06-25 13:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (106 votes). Check out past polls.

    Notices?