MySQL Parameterized Query with Functions

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

Hello Monks! I have been modifying an existing Perl script to add some database inserts and updates. I configured the changes I needed, then set about trying to make some it more efficient and organized for the next person. This is a basic example, but I have an insert command that will get called dozens of times in a script:

my $sql_ins_log = "insert into download_log (download_id,status,update
+_time)" 
                . " values(?,getValueByMeaning('$status_meaning','17')
+,?)";
[download]

I have this inside of a for loop, but the sql itself should be the same, with only the variables changing. So I thought, well I can just move that getValueByMeaning() function as a parameter (like the download_id and update_time) and set the $sql_ins_log outside of the for loop to avoid setting the variable over and over to the same thing, like this:

my $sql_ins_log = "insert into download_log (download_id,status,update
+_time)" 
                . " values(?,?,?)";
foreach(@X){
  $now = localtime->mysql_datetime;
  $sth = $dbh->prepare($sql_ins_log);
  $sth->execute($download_id,getValueByMeaning('$status_meaning','17')
+,$now);
}
[download]

I tried a variety of things, but I'm having issues that I think relates to the fact that it's a function and there's a variable in it (meaning, it's not just a function like NOW()). To be clear, the getValueByMeaning is a user defined MySQL function, not a Perl function in this script. So I want the actual SQL to get passed into MySQL like this:

insert into download_log (download_id,status,update_time) values(1,get
+ValueByMeaning('DONE',17'),'2014-09-15 14:16:00')
[download]

Thanks for ay insight!

Comment on MySQL Parameterized Query with Functions Select or Download Code

Replies are listed 'Best First'.
Re: MySQL Parameterized Query with Functions by roboticus (Chancellor) on Sep 15, 2014 at 22:36 UTC
hoyt: Go with the first statement: you won't be able to plug functions in at the last moment. When you prepare the statement, the SQL server compiles the statement with the expectation that you're going to provide the actual values to stick into the table, not instructions on how to compute the value to stick into the table. Similarly, you can't use table names or join columns as parameters either. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re: MySQL Parameterized Query with Functions by locked_user sundialsvc4 (Abbot) on Sep 15, 2014 at 22:54 UTC
When you “prepare” an SQL statement (which the server will always do, whether you ask for it or not), the server “sort-of compiles it.” This step creates what’s called an “execution plan,” which is a breakdown of the actual steps that the guts of the database server will actually carry out in order to get the results that you wanted. (The plan considers not only the SQL statement, but various statistics about the current state of the tables themselves.) Well, the server can happily execute the same prepared query many times, substituting parameter-values into it each time (these are merely inputs ...), but it can’t handle changes to the statement itself. You have to present a new SQL statement, which will be prepared according to its content. &nbp; A statement that needs to call a different server-side function is, by definition, a new statement. Now, if you are doing a lot of this sort of thing, you might wish to minimize the number of prepares (if that makes sense in this case) by preparing each variation of the SQL statement in turn, and running all the data that needs to go through that particular variation, before moving on to the next variation and its set of input data.
Re^2: MySQL Parameterized Query with Functions by hoyt (Acolyte) on Sep 15, 2014 at 23:10 UTC
Got it. That makes sense. I was trying to minimize the number of prepares by putting it outside of the for loop, but I think determining which variations of each type of $status_meaning would take just as much time to deal with as preparing it during each for loop iteration. Thank you both for the input!	[reply]
Re: MySQL Parameterized Query with Functions by mje (Curate) on Sep 16, 2014 at 07:48 UTC
As others have explained you cannot change the SQL itself with parameters, you can only change values. However, there should be no reason why you cannot use: `insert into download_log (download_id,status,update_time) values(?,get +ValueByMeaning(?,?),?)` [download] and pass 4 parameters. Then you can prepare the SQL outside of the loop.	[reply] [d/l]