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
