The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS i'm crazy to use this ? Tnks in advance MySQL , InnoDB and Linux ! -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS By the time your project is finished designing, it will be ready ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedures and Functions
Hello. Have a look here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html However, it isn't clear for me what originally read data means. Do you store the time of the first access to the data in some table or somewhere else? You might obtain a better answer from the list members if your add more details about table structure and your application logic. I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Blue Wave Software wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedures and Functions
I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Regards, Justin Elward Blue Wave Software Pty Limited [EMAIL PROTECTED] Ph. +61 2 4320 6090 Fx. +61 2 4320 6092 --- DISCLAIMER: This message is proprietary to Blue Wave Software Pty Limited (BWS) and is intended solely for the use of the individual or individuals to whom it is addressed. It may contain privileged or confidential information and should not be circulated with out informing BWS prior or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. BWS accepts no responsibility (except where required under Australian law) for loss or damage arising from the use or misuse of the information transmitted by this email including damage from virus. ---
Re: Stored Procedures and Functions
Justin, I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. First, a quibble about the logic. Between the time a row is re-read for changes and the time you write-lock it, it is possible for another process to update the row. It would seem sounder to explicitly write-lock it up front, make your changes, then unlock it. But secondly, LOCK is not permitted in MySQL stored procs, so if you want to use MyISAM and LOCK, you have to issue the LOCK outside the sproc, eg SET GLOBAL log_bin_trust_routine_creators=TRUE; DROP PROCEDURE IF EXISTS UpdTime; LOCK TABLES test WRITE; DELIMITER | CREATE PROCEDURE UpdTime( IN readid INT, IN newtime TIMESTAMP ) BEGIN UPDATE test SET time=newtime WHERE id=readid; END; | DELIMITER ; UNLOCK TABLES; or more elegantly, convert the table to InnoDB and use a transaction to accomplish the same effect. PB http://www.artfulsoftware.com - Blue Wave Software wrote: I don't have any experience with stored procedures and find the Documentation in the MYSQL manual a bit sketchy or maybe I am just miss reading it. Can any one point me to some documentation that will help with fully understanding Stored Procedures? What I am trying to migrate out of my program code is a procedure to do the following for update commands. 1) Determine that the ID Field and the Last Updated Timestamp Field is still the same as when the data was originally read. 2) If not the same then Raise an error back to the program so It can determine the action. 3) If the same then lock row and perform update. Future development of this could extend to remove more out of code to handle when the two don't match. The procedure there is 1) Compare Original Field Value to Current Value in Memory if the two don't match then 2) IF the Original Field Value and the Current Value Stored in Table Match then update Field IF not then raise error and prompt user for action. This may be more information than required, but some one out their might be doing similar things that they can point me in the direction of some more documentation or even better still a few Example scripts that I can pull apart and learn from. Regards, Justin Elward Blue Wave Software Pty Limited [EMAIL PROTECTED] Ph. +61 2 4320 6090 Fx. +61 2 4320 6092 --- DISCLAIMER: This message is proprietary to Blue Wave Software Pty Limited (BWS) and is intended solely for the use of the individual or individuals to whom it is addressed. It may contain privileged or confidential information and should not be circulated with out informing BWS prior or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. BWS accepts no responsibility (except where required under Australian law) for loss or damage arising from the use or misuse of the information transmitted by this email including damage from virus." --- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help in Stored procedures and functions
Hi, I am trying to write a function which returns multiple values. I don't knowif this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan 212-231-7187 From: Pam Keen [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 10:21 AMTo: Marcus, JeffCc: Pisula, Mike; Pedersen, Mads; Mishkind, Scott; Anchan, Dinesh; [EMAIL PROTECTED]; 'Philip Antoniades'; 'Kenneth Moses'Subject: RE: MySQL/24/7 Real Media support contract renewal Jeff, What is your availability to discuss/resolve the below and to discuss the Informix migration effort. I am available early next week - Monday all day and Tuesday, late morning,early afternoon. As an interim measure, I extended support till the end of this week for your team's development efforts. Thank you, Pam Keen 212 566 2714 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in Stored procedures and functions
Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't knowif this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan 212-231-7187 From: Pam Keen [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 07, 2005 10:21 AM To: Marcus, Jeff Cc: Pisula, Mike; Pedersen, Mads; Mishkind, Scott; Anchan, Dinesh; [EMAIL PROTECTED]; 'Philip Antoniades'; 'Kenneth Moses' Subject: RE: MySQL/24/7 Real Media support contract renewal Jeff, What is your availability to discuss/resolve the below and to discuss the Informix migration effort. I am available early next week - Monday all day and Tuesday, late morning,early afternoon. As an interim measure, I extended support till the end of this week for your team's development efforts. Thank you, Pam Keen 212 566 2714 No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in Stored procedures and functions
Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in Stored procedures and functions
Anchan, Dinesh [EMAIL PROTECTED] wrote on 04/07/2005 03:44:07 PM: Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan I don't speak Informix (and I haven't used MySQL's sprocs yet) but it seems as though you are looping through the JOIN of one table against another and returning a row of calculated information for each JOINed-row combination. Could you possibly perform your calculations and accumulate your return values in a temporary table (I suggest a temporary table because it is connection-specific) then return your results at the end of the statement with a simple SELECT from the temp table? If all of the values you need to return are in variables, you should be able to simulate a single row table by saying SELECT variable1 as variable1name, variable2 as variable2name, ...; You shouldn't need a FROM clause because you are only SELECTing constants. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Need help in Stored procedures and functions
Shawn, Thank you for your reply. It looks like this is the only option. But i was trying to avoid the creation of temp table and parsing of data at the application layer since it can potentially slow down the overall performance. Dinesh From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 4:03 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Need help in Stored procedures and functions Anchan, Dinesh [EMAIL PROTECTED] wrote on 04/07/2005 03:44:07 PM: Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan I don't speak Informix (and I haven't used MySQL's sprocs yet) but it seems as though you are looping through the JOIN of one table against another and returning a row of calculated information for each JOINed-row combination. Could you possibly perform your calculations and accumulate your return values in a temporary table (I suggest a temporary table because it is connection-specific) then return your results at the end of the statement with a simple SELECT from the temp table? If all of the values you need to return are in variables, you should be able to simulate a single row table by saying SELECT variable1 as variable1name, variable2 as variable2name, ...; You shouldn't need a FROM clause because you are only SELECTing constants. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Need help in Stored procedures and functions
Dinesh, Right, sorry for being dense, the C API doesn't yet have a published func for calling sprocs. You could put results in a temp table and query that table after your sproc returns. Or you might be able to rely on a stored procedure trick: even if the sproc issues one or more SELECT ... INTO statements, if its last SELECT has no INTO clause, the sproc will return its result just as if you'd issued that query directly. PB - Anchan, Dinesh wrote: Peter, Thank you for your reply. Actually that request was accidentally sent to mysql@lists.mysql.com instead of [EMAIL PROTECTED] MySQL support also suggested the same thing. Problem is, values in the OUT variable can be retrieved only from a future SQL query. But we are trying to call this procedure from a C program. C Program can parse the result set if it is an output from a simple SELECT statement. I am finding it difficult achieve something like below: Here is a sample of informix function: Create function test() Returning integer, smallint, char(1), varchar(65); Foreach select col1, col2 into p_col1, p_col2 from table1 where where_clause foreach select col1, col2, col3, col4 into i_col1, i_col2, i_col3, i_col4 from table2 where i_col1 = p_col1 and i_col2 = p_col2 if p_col3 = some_thing then do_some_calculations; let p_string1 = some_thing end if; if p_col4 = some_thing then do_some_calculations; let p_string2 = some_thing; end if; return i_col3, i_col4, p_string1, p_string2 with resume; end foreach End foreach End function; Thank you for your help Dinesh From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 12:24 PM To: Anchan, Dinesh Cc: mysql@lists.mysql.com Subject: Re: Need help in Stored procedures and functions Dinesh, A stored func returns a scalar. To return multiple values, use an sproc declaring the INOUT variables you need, pick up their values in the calling code after the sproc has executed. Peter Brawley http://www.artfulsoftware.com - Anchan, Dinesh wrote: Hi, I am trying to write a function which returns multiple values. I don't know if this is allowed or not but i am not able to create this function. I get the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' integer, integer, ' at line 7 I have attached the function i am trying to create. Please refer to the attached thread for contract related information. I would appreciate if somebody can give me a call so that i can clear few other questions. Thanks Dinesh Anchan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.4 - Release Date: 4/6/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
