See remarks intererspersed in question. Rhino
----- Original Message ----- From: "Duncan Miller" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Tuesday, November 08, 2005 12:42 AM Subject: Re: Evaluating text as an expression > 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 > I understand what you're looking for. Prepared statements/dynamic SQL are the closest thing you'll find in standard SQL to your Eval() function in terms of handling a text string of your choice, typically generated at runtime, that is interpreted and executed at runtime. But they aren't USED like your Eval() function is used in your example. A user-defined function LOOKS like your Eval() function - because it IS a function - but you can't simply hand it any old text string and expect it to be evaluated. By the same token, a stored procedure doesn't quite fit your requirements because it is called in a statement by itself, e.g. call storedProc01(1, 'ABC', '1988-01-01'). There simply isn't anything that is *PRECISELY* what you want in SQL. You'll just have to think of your question a little differently - in the form of a UDF or stored procedure or prepared statements - in order to take advantage of those features. > 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. > Nobody says you have to use UDFs, stored procedures or prepared statements but it's good to know that they're there when you do need to use them :-) Good luck! > > > 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 > > > ---------------------------------------------------------------------------- ---- 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]
