Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement

by sundialsvc4 (Abbot)
on Aug 27, 2015 at 07:16 UTC ( #1140164=note: print w/replies, xml ) Need Help??


in reply to Interpolating subroutine call in SQL INSERT INTO SELECT statement

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.

  • Comment on Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement

Replies are listed 'Best First'.
Re^2: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by chacham (Prior) on Aug 27, 2015 at 16:35 UTC

    It has no ability,whatsoever, to “call them.”

    whatsoever? That is not correct. It definitely can, it's just unlikely you'd want to. In general, the database can react with the OS, and ask it to execute a program and capture the output. This is easily done in an sp or trigger, and, depending on what is done, in the actual statement, via a function to call the OS.

    While a little trickier, you can even have a file stored on disk and join it into the database as an external table. If that table were actually a perl program, or--sneakier--an interpreter, you would indeed be able to put a function directly into the query.

Re^2: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by shadowsong (Pilgrim) on Aug 27, 2015 at 08:47 UTC

    That reply has hit the nail on the head.

    I suspected as much but certainly didn't want to rule it out because I didn't know for sure if there was a clever way to intersperse/embed my function within the INSERT INTO/SELECT construct...

    Thanks for setting me straight!

      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?


      The way forward always starts with a minimal test.

      if there was a clever way to intersperse/embed my function within the INSERT INTO/SELECT construct

      That depends on what you want done and what language the function would be in. What exactly are you trying to do?

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2019-07-19 16:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?