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

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??


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.

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.

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; }

In reply to Re^4: Interpolating subroutine call in SQL INSERT INTO SELECT statement by shadowsong
in thread Interpolating subroutine call in SQL INSERT INTO SELECT statement by shadowsong

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (8)
    As of 2019-07-17 20:23 GMT
    Find Nodes?
      Voting Booth?

      No recent polls found