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
