Re: Evaluating text as an expression

[Rhino]

The kind of thing you are talking about, where you execute a text string that is supplied at runtime, is called dynamic SQL. I've used it in the database which I use most of the time, DB2, but had never seen it in MySQL when I was making my initial reply to your question. I just had a look at the MySQL manual to see if it was there, since I was concerned that I might have simply overlooked it earlier.   As it turns out, I *had* indeed missed the existence of dynamic SQL in MySQL! Mind you, the MySQL manual uses the term 'prepared statements' instead of 'dynamic SQL'.   You didn't say what version of MySQL you are running but if you are using 5.0 or later, dynamic SQL is possible for you. Have a look at http://dev.mysql.com/doc/refman/5.0/en/sqlps.html. This would appear to be what you originally wanted.   If you are on MySQL 4.1.x, prepared _SQL_ statements are, apparently, not available but you can accomplish the same effect with the C API. If you look at http://dev.mysql.com/doc/refman/4.1/en/c-api-prepared-statements.html and the subsequent parts of that chapter, you will see the API described. Now, I don't know how you feel about writing statements that aren't SQL but this may offer an acceptable way for you to do what you want to do. By the same token, Java offers ways to send text strings to a program at runtime and having them interpreted to return whatever you want. However, I know that some people want to be able to do everything in pure SQL without any application code in the picture; if that is your situation, you may want to upgrade to 5.0 or 5.1 so that you have the capabilities you need.   Naturally, stored procedures and user defined functions are still valid ways of solving the problems you posed.   I apologize profusely for giving you the impression that what you wanted was not available in MySQL; I simply had never looked for dynamic SQL capabilities in MySQL and had never stumbled across their existence in the manual when looking for other things. I spend most of my time in DB2 and only occasionally work with MySQL and my own copy of MySQL is 4.0.x which doesn't support prepared statements so I just never knew that they were there in the later releases.   Sorry for any confusion I caused!   Rhino ----- Original Message ----- From: Duncan Miller To: Rhino Cc: mysql@lists.mysql.com Sent: Monday, November 07, 2005 1:22 AM Subject: Re: Evaluating text as an _expression_ Thanks. I see what you mean. I used the 'Evaluate' as an example because in some code you can use that function to execute a text string as code. I sort of thought there may be something similar in SQL / MySQL to allow the execution of a resultant string as if it were code. And yeah a lot of things that were workarounds before can now be done as stored procedures. Of course I could just pass the string to a generic stored procedure to return the result. I'm assuming I can call a stored procedure within an SQL command. Will check it out further. Thanks again Rhino wrote: See comments interspersed below. Rhino ----- Original Message ----- From: "Duncan Miller" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, November 06, 2005 8:36 PM Subject: Evaluating text as an _expression_ I am trying to set up a table where the returned value is a result of evaluating an _expression_ that is stored as text (or varchar). The idea is to have a table with a couple of fields that can contain numeric values or expressions eg Name Fred Years 3 Commission base 10% Commission Commission Base + (Years * 2)% I sort imagines that I could do it like SELECT Name, Evaluate(Commission) or as a subquery. Assuming you want to invoke this code with a function name, as in your example, what you're requesting is called a UDF (user-defined function). These are supported as early as MySQL 4.1. Basically, you create a function with a name of your choosing (usually with some restrictions), then write some code behind it to do the work you want. Then you drop that code into MySQL and it becomes just another function that you can use, just like the standard ones built into MySQL. See this page of the 4.1 manual for more information: http://dev.mysql.com/doc/refman/4.1/en/create-function.html. Another example I have is to be able to store queries in a table and be able to call them in one call to the database rather than through the provider eg Select evaluate(queryText) from queryTable where queryId = x This is probably a bit more redundant now that 5 has stored procedures etc but still... I haven't seen the exact functionality you are describing in either DB2 or MySQL but what you are describing is not too different from stored procedures. A stored procedure is basically the name of some code that you can invoke, passing in parameters if you like, and that returns a result set. They are invoked via CALL statements though, not via SELECT statements. Rhino No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]