Thanks again, I am certainly learning a lot more about MySQL.
It not quite what I was looking for but it does give me another option
to use.
I am not really a developer or anything so I keep to fairly simple
things and like to use SQL and a scripting language. I can do what I
want on the client script side.
I was thinking of something like the Javascript Eval method (and the
similar VBScript Eval function) that allow you to construct a string by
whatever means and then execute it as a command. This gives the scripter
some flexibility that probably is a lazy way to a better solution but ...
Probably the best example I can offer is
Table = MathsOperations
Val1 = 5
Val2 = 3
Operation = 'Val1 * Val2'
Select Val1,Val2,Operation, Eval(Operation) From MathsOperations
Would return
5,3,'Val1 * Val2',15
As I say I can do this at the client side but it occurred to me that it
would be useful to be able to do it within SQL. But I am happy with what
I have learned.
Rhino wrote:
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 <mailto:[EMAIL PROTECTED]>
*To:* Rhino <mailto:[EMAIL PROTECTED]>
*Cc:* mysql@lists.mysql.com <mailto: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
