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

Interpolating subroutine call in SQL INSERT INTO SELECT statement

by shadowsong (Monk)
on Aug 26, 2015 at 09:35 UTC ( #1139989=perlquestion: print w/replies, xml ) Need Help??

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

Dear Monks,

I am trying to ascertain whether it's possible to include a function call within an SQL insert statement which obtains its values from a select statement.

The SQL statement looks like this:

INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3) SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1 WHERE COLUMN4='FOOBAR'

My script will therefore have something like this

... my $statement = "INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3) \n". "SELECT COLUMN1, COLUMN2, COLUMN3 \n". "FROM TABLE1 WHERE COLUMN4=?"; my $rv = $dbh->do($statement, {}, 'foo');

Now, what I'd like to do is to modify COLUMN3 before inserting it into TABLE2 by passing it into a subroutine... but I'm not sure how to go about it (without splitting the INSERT statement into two parts and handling them separately).

What I have below isn't going to work but hopefully it should give you an clear picture of what I'd like to achieve

... my $statement = "INSERT INTO TABLE2 (COLUMN1, COLUMN2, ". my_subrt(COLUMN3) .") \n". "SELECT COLUMN1, COLUMN2, COLUMN3 \n". "FROM TABLE1 WHERE COLUMN4=?"; my $rv = $dbh->do($statement, {}, 'foo');

Many thanks

Replies are listed 'Best First'.
Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by poj (Abbot) on Aug 26, 2015 at 12:37 UTC

    Which database ?. This is for MySQL. Note user must have the correct permissions to create functions. The function is simply a demo, obviously you don't need a function to concat something to a field.

    #!perl use strict; use warnings; use DBI; use Data::Dump 'pp'; my $dbh = get_dbh(); my $proc = " CREATE FUNCTION my_subrt(s CHAR(30)) RETURNS CHAR(50) RETURN CONCAT(s,'####'); "; $dbh->do('DROP FUNCTION IF EXISTS my_subrt'); $dbh->do($proc); #$dbh->do('DELETE FROM TABLE2'); my $sql = ' INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3) SELECT COLUMN1, COLUMN2, my_subrt(COLUMN3) FROM TABLE1 WHERE COLUMN4=?'; my $rv = $dbh->do($sql, {}, 'foo'); my $ar = $dbh->selectall_arrayref('SELECT * FROM TABLE2'); pp $ar; $dbh->do('DROP FUNCTION my_subrt'); sub get_dbh{ my $database = "test"; my $user = ""; my $pw = ""; my $dsn = "dbi:mysql:$database:localhost:3306"; my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1, AutoCommit=>1 } ); return $dbh; }
    poj

      @poj

      Although I decided to split up the statement and process the insert separately - with values from the select, I will definitely be trying this out later on one of my other projects.

      Thanks!

        Depending on your volume of data, in the past I have used the bulk loader in MSSQL to insert many records at once. It does require the load file to be accessible by the server. One advantage is you can check your function is working as expected before changing the database.

        #!perl use strict; use DBI; my $dbh = dbh(); my $sql = 'SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE1 WHERE COLUMN4 = ?'; my $sth = $dbh->prepare($sql); $sth->execute('foo'); my $tmpfile = "c:\\temp\\public\\temp1.dat"; open TMP,'>',$tmpfile or die "$!"; while (my @f = $sth->fetchrow_array){ $f[2] = subrt($f[2]); print TMP (join "\t",@f)."\n"; } close TMP; my $rv = $dbh->do('DELETE FROM TABLE2'); print "$rv records deleted from TABLE2\n"; $rv = $dbh->do( " BULK INSERT TABLE2 FROM '$tmpfile' WITH ( FIELDTERMINATOR = '\t' )" ); print "$rv records insert into TABLE2"; sub subrt { reverse shift } # connect sub dbh { my $dsn = "DBI:ODBC:mssql"; my $dbh = DBI->connect($dsn, 'sa', '', {RaiseError => 1, PrintError => 1}) or die (Error connecting " $DBI::errstr"); }
        poj
Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by Anonymous Monk on Aug 26, 2015 at 09:56 UTC
Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by 1nickt (Abbot) on Aug 26, 2015 at 11:46 UTC

    I agree with Anonymous Monk that there is nothing wrong with using two SQL statements in this situation. But what is the function of the subroutine to which you want to pass the value of COLUMN3 ? Could you use an existing SQL function in your SELECT query? Or could you build a stored routine for your DB?

    Built-in SQL functions
    Stored routines in MySQL


    The way forward always starts with a minimal test.
      But what is the function of the subroutine to which you want to pass the value of COLUMN3 ? Could you use an existing SQL function in your SELECT query? Or could you build a stored routine for your DB?

      It did not even cross my mind to attempt either using an existing SQL function or building a stored routine within my DB and using that.

      I have written a Perl function that takes a string and substitutes out certain characters from that string (the characters I'm taking out belong to a hash that I can extend as and when I need to

      I'm not sure whether I can use an existing SQL function to achieve this, but it seemed like a good idea to have that function coded in Perl - seeing as how great Perl is with RegExps and the other programmer likely to be maintaining the code knows Perl..

      Thanks for the advice! Once I get a handle on creating stored procedures in MSSQL I'm sure I'll be looking for every excuse to use them.

Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by KurtSchwind (Chaplain) on Aug 26, 2015 at 12:34 UTC

    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.

    --
    “For the Present is the point at which time touches eternity.” - CS Lewis
      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.

      That is what I suspected, but I'm very new to Perl and still far from sure of what is and isn't possible.

      Thanks again for the advice. I appreciate it.

Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by chacham (Prior) on Aug 26, 2015 at 14:33 UTC

    "INSERT INTO TABLE2 (COLUMN1, COLUMN2, ". my_subrt(COLUMN3) .") \n".

    In SQL, the column list only supports columns, nothing else. The column list is simply a complete or partial list of columns in the table, in arbitrary order.

    Function can only affect data, in which case it would be in the select clause, which definitely supports functions.

Re: Interpolating subroutine call in SQL INSERT INTO SELECT statement
by sundialsvc4 (Abbot) on Aug 27, 2015 at 07:16 UTC

    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.

      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.

      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?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2019-06-20 17:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (90 votes). Check out past polls.

    Notices?