Re: Evaluating text as an expression
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
Re: Evaluating text as an expression
The kind of thing you are talking about, where youexecute 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 tosee if it was there, since I was concerned that I might havesimply overlooked it earlier. As it turns out, I *had* indeed missed the existence of dynamic SQL in MySQL! Mind you, the MySQL manualuses 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.htmland 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'tsupport 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 againRhino 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.
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 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
Re: Evaluating text as an expression
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 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]
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
