Re: Stored procedure debuggers
- Original Message - From: Larry Martell larry.mart...@gmail.com Subject: Stored procedure debuggers Does anyone know of any debuggers for stored procs that run on Mac and/or Linux? Not aware of native ones, but I seem to remember that I managed to get the one that occasionally gets advertised on this list (can't even remember the name) to work under Wine at some point. Took some messing with .net things, though. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored procedure debuggers
Hello Larry, Subject: Stored procedure debuggers Does anyone know of any debuggers for stored procs that run on Mac and/or Linux? Although all our tools are Windows tool, we have customers running Database Workbench under Wine without major problems. We have a standalone debugger tool called Hopper, I haven tried it under Wine yet, but there's a good chance it runs fine. Here's my latest (successful) attempt running Database Workbench under Wine, as a complete Linux novice. ;) http://www.upscene.com/support.php?page=dbw_ubuntu_wine With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- (c) 850-449-1912 (f) 423-930-8646
Re: Stored Procedure help
The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
Anders, I didn't see that at first, but now. I'd agree. Maybe I should read up on stored procedures. On Mon, July 14, 2014 16:25, Anders Karlsson wrote: The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
maybe try 'order by sort_id desc'? On Mon, Jul 14, 2014 at 12:42 PM, Don Wieland d...@pointmade.net wrote: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
why do you need the 'order by' in your update at all? The statement, if innodb, will certainly be atomic; the order in which they are updated means nothing. On Jul 13, 2014 11:46 PM, kitlenv kitl...@gmail.com wrote: maybe try 'order by sort_id desc'? On Mon, Jul 14, 2014 at 12:42 PM, Don Wieland d...@pointmade.net wrote: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure help
Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure Question?
2012/11/23 10:49 +0530, Girish Talluru I have a scenario where I have to screen a huge bunch of records for in db using certain rules. I have done in traditional php style record by record and it took 90 mins for 4000 records. I have 800k - 900k records in production which might possibly lead to days of execution. I have figured out that the php script does wait for the record to execute and then only after it it will process the next record. For this if it is java I should have used stored procedure and multithreading concept to run multiple threads in parallel. But I don't think PHP supports multithreading. Now I have idea to create a stored procedure to do all the checks and my question here is when I call a stored procedure does the control get backs immediately to the php script? Bcoz I want to pick other record immediately while the first one going through the process and call the procedure again. Sounds to me that if your data are in a character form like a CSV file, or you can put them into such a form, you can use LOAD DATA to insert into the database. Then you would use a separate procedure, outside SQL, beforehand to screen the data, and maybe turn them into MySQL s CSV-ish form. If you are using a PHP procedure, I suspect that you can do that. Furthermore, since your screener does not wait for MySQL, but only for PHP s own input-output, there is not that wait. Once LOAD DATA begins, it very swiftly runs, and your (other?) PHP procedure waits for _all_ the records to be inserted, not each one by one. Of course, you could batch them, too, instead of making one CSV file of 900,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure Question?
when I call a stored procedure does the control get backs immediately to the php script? No, sprocs wil lvery likely slow you down. Probably best to split the job into several part-tasks (i) read rows into a work buffer, (ii) walk the work buffer and mark done rows, (iii) walk the done list and insert them. PB - On 2012-11-22 11:19 PM, Girish Talluru wrote: Hi There, I have a scenario where I have to screen a huge bunch of records for in db using certain rules. I have done in traditional php style record by record and it took 90 mins for 4000 records. I have 800k - 900k records in production which might possibly lead to days of execution. I have figured out that the php script does wait for the record to execute and then only after it it will process the next record. For this if it is java I should have used stored procedure and multithreading concept to run multiple threads in parallel. But I don't think PHP supports multithreading. Now I have idea to create a stored procedure to do all the checks and my question here is when I call a stored procedure does the control get backs immediately to the php script? Bcoz I want to pick other record immediately while the first one going through the process and call the procedure again. Thanks, Girish Talluru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure Debugging?
In my community, interest would be VERY high. I often counsel putting logic on the server; the biggest point of relunctance is the difficulty debugging. - michael dykman On Wed, Feb 15, 2012 at 10:45 AM, Martijn Tonies m.ton...@upscene.com wrote: Hi all, As you probably now, we created Database Workbench, a developer tool for MySQL and other DBMSses. This tool includes a Stored Routine Debugger for several DBMSses, including Firebird and InterBase, but not MySQL. Both Firebird and InterBase do not provide a debugging API, so our tool emulates stored code behaviour at the client side. As far as I know, MySQL doesn't have a debugging interface either. I'm wondering if there would be a market to add such emulation to Database Workbench. For info and screenshots, see: http://upscene.com/documentation/dbw4/tools_debugger_basics.htm Would you like to be able to debug stored routines like this? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Stored Procedure Question
SELECT id INTO @row_id FROM myTable WHERE blah blah LIMIT 1; Source http://dev.mysql.com/doc/refman/5.5/en/select-into-statement.html On Sep 21, 2011, at 2:23 PM, Brandon Phelps wrote: Hello all, I would like to create a stored procedure that does the following: 1. Accepts 4 values as parameters 2. SELECTS 1 record (LIMIT 1) from a table where the 4 parameters match fields in that table a. If a record was returned then UPDATE the table b. If a record was not returned then INSERT into a different table My main question here is how can I execute a SELECT id FROM ... LIMIT 1 statement within a stored procedure then use the returned id field later in the procedure? Something like this: @row_id = SELECT id FROM myTable WHERE blah blah LIMIT 1; IF @row_id != nothing THEN UPDATE myTable ... ELSE INSERT INTO anotherTable ... END IF So if no rows were returned from the select I perform the ELSE block, otherwise I perform the main IF block. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=de...@orange-pants.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored Procedure Question [SOLVED]
Ah ha! Thanks Derek. I thought INTO was used strictly for inserting the selected records into another table. Much appreciated. On 09/21/2011 02:34 PM, Derek Downey wrote: SELECT id INTO @row_id FROM myTable WHEREblah blah LIMIT 1; Source http://dev.mysql.com/doc/refman/5.5/en/select-into-statement.html On Sep 21, 2011, at 2:23 PM, Brandon Phelps wrote: Hello all, I would like to create a stored procedure that does the following: 1. Accepts 4 values as parameters 2. SELECTS 1 record (LIMIT 1) from a table where the 4 parameters match fields in that table a. If a record was returned then UPDATE the table b. If a record was not returned then INSERT into a different table My main question here is how can I execute a SELECT id FROM ... LIMIT 1 statement within a stored procedure then use the returned id field later in the procedure? Something like this: @row_id = SELECT id FROM myTable WHEREblah blah LIMIT 1; IF @row_id != nothing THEN UPDATE myTable ... ELSE INSERT INTO anotherTable ... END IF So if no rows were returned from the select I perform the ELSE block, otherwise I perform the main IF block. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=de...@orange-pants.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: stored procedure insert statement
It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
Re: stored procedure insert statement
That's what is bad of SP in MySQL, debugging. Just out of the blue, can you try to disable query cache? *SET GLOBAL query_cache_size = 0;* * SET GLOBAL query_cache_type = 0; * it could be a bug Claudio 2011/7/9 Johnny Withers joh...@pixelated.net It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- Claudio
Re: stored procedure insert statement
Thanks Johnny, In this case I wouldn't be able to insert a completely new row but replace the existent one, so row count would stay the same. This is a storage table with the only unique constraints on: dda_debits_id column. the test data is very small, so I would've noticed any duplicates and they wouldn't make it to the table anyway with or without INSERT IGNORE. +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | dda_debits_id | int(11) | NO | PRI |0 || | created_on| datetime| YES | | NULL || | reference_number | varchar(18) | YES | | NULL || | user_format_debit_ref | varchar(18) | YES | | NULL || | amount| int(11) | YES | | NULL || | debit_date| datetime| YES | | NULL || | status| tinyint(1) | YES | | NULL || | debit_type| tinyint(1) | YES | | NULL || | recharge_for_id | int(11) | YES | | NULL || | processed_on | datetime| YES | | NULL || | service_user_id | int(11) | YES | | NULL || +---+-+--+-+-++ Claudio, good point. Unfortunately, didn't work. I tried it before but no luck. Thanks, Igor On 07/09/2011 02:43 PM, Johnny Withers wrote: It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work. On Jul 9, 2011 3:49 AM, Igor Shevtsov nixofort...@googlemail.com mailto:nixofort...@googlemail.com wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
Re: Stored procedure
HI All Thank you for the responses. I have been going through the documentation the whole of today thus far and it seems to be easy enough. I am still however confused on how to achieve the following though , and this might be due to a lack of experience or I might just not be thinking straight... - From what I can tell the scheduled event is created and contains the body of what needs to be run at the times,etc... specified. - The command I need to run though will be somthing like this: -- call procedure (yesterday's date at 00:00:00) - The purpose of the procedure is to delete all records from specific tables older than () the specified date. The procedure is already working and if I run it manually entering the date it works 100%. However, I need to schedule an event to run each day @ 02h00 for instance which will then call the procedure as per above. My problem (which I had with the bash script as well) is to get the full correct date (yesterday's date at 00:00:00) passed to the call procedure() statement. Can anybody give me some ideas as I have tried so many options and yet none of them has worked as yet. Regards Machiel -Original Message- From: petya pe...@petya.org.hu To: Machiel Richards machi...@rdc.co.za Cc: mysql@lists.mysql.com Subject: Re: Stored procedure Date: Wed, 05 Jan 2011 12:44:07 +0100 http://dev.mysql.com/doc/refman/5.1/en/events.html On 01/05/2011 12:21 PM, Machiel Richards wrote: HI How do I use the mysql event scheduler? I have not used this as yet so not sure how to use it. Regards Machiel -Original Message- *From*: petya pe...@petya.org.hu mailto:petya%20%3cpe...@petya.org.hu%3e *To*: Machiel Richards machi...@rdc.co.za mailto:machiel%20richards%20%3cmachi...@rdc.co.za%3e, mysql@lists.mysql.com mailto:mysql@lists.mysql.com *Subject*: Re: Stored procedure *Date*: Wed, 05 Jan 2011 12:15:59 +0100 Hi, Use the mysql event scheduler instead of cron, the bash script is quite pointless, and call your stored procedure with now() - interval 1 day parameter. Peter On 01/05/2011 11:00 AM, Machiel Richards wrote: Good day all I am hoping that someone can assist me here. As per a client requirement, I am writing a script/stored procedure combination in order to do the following: - Script to be run within a cron once a day according to a set schedule. - script to connect to mysql and call a stored procedure - stored to procedure to do the following: * retrieve row id of the record that indicates the last record of a specified date (i.e 00:00 yesterday) [select max(id) into max_id from table1 where utc dt] * delete records from table2 where id max_id * delete records from table1 where id max_id After a struggle to get the script and stored procedure working I am now stuck at the following point. the date that needs to be specified to the stored procedure must be in the following format: 2011-01-04 00:00 (i.e. yesterday 00:00) meaning that everything before this date and time needs to be deleted. However when trying to run the script with the date like this, then I get the following message: ERROR 1064 (42000) at line 1: 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 '00:00)' at line 1 I initially had the script create the date in a different manner but then the 00:00 was seen as a seperate argument which didn't work. After some changes the date is now being read correctly from what I can tell but now I get the message above. Herewith my script and stored procedure definitions: Script: #!/bin/bash DATE=`date --date=1 days ago +%Y-%m-%d` 00:00 echo$DATE mysqldump -u root -ppassword --databasesDB /backups/DB_backup.dump mysql -u root -ppassword -DDB -ecall select_delete_id_2($DATE) exit Stored Proc: begin declare max_id int(11); select max(id) into max_id from table1 where utc dt; delete from table2 where id max_id; delete from table1 where id max_id; end Does anybody perhaps have any suggestions? Regards Machiel
Re: Stored procedure
Hi, Use the mysql event scheduler instead of cron, the bash script is quite pointless, and call your stored procedure with now() - interval 1 day parameter. Peter On 01/05/2011 11:00 AM, Machiel Richards wrote: Good day all I am hoping that someone can assist me here. As per a client requirement, I am writing a script/stored procedure combination in order to do the following: - Script to be run within a cron once a day according to a set schedule. - script to connect to mysql and call a stored procedure - stored to procedure to do the following: * retrieve row id of the record that indicates the last record of a specified date (i.e 00:00 yesterday) [select max(id) into max_id from table1 where utc dt] * delete records from table2 where id max_id * delete records from table1 where id max_id After a struggle to get the script and stored procedure working I am now stuck at the following point. the date that needs to be specified to the stored procedure must be in the following format: 2011-01-04 00:00 (i.e. yesterday 00:00) meaning that everything before this date and time needs to be deleted. However when trying to run the script with the date like this, then I get the following message: ERROR 1064 (42000) at line 1: 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 '00:00)' at line 1 I initially had the script create the date in a different manner but then the 00:00 was seen as a seperate argument which didn't work. After some changes the date is now being read correctly from what I can tell but now I get the message above. Herewith my script and stored procedure definitions: Script: #!/bin/bash DATE=`date --date=1 days ago +%Y-%m-%d` 00:00 echo $DATE mysqldump -u root -ppassword --databasesDB /backups/DB_backup.dump mysql -u root -ppassword -DDB -e call select_delete_id_2($DATE) exit Stored Proc: begin declare max_id int(11); select max(id) into max_id from table1 where utc dt; delete from table2 where id max_id; delete from table1 where id max_id; end Does anybody perhaps have any suggestions? Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored procedure
HI How do I use the mysql event scheduler? I have not used this as yet so not sure how to use it. Regards Machiel -Original Message- From: petya pe...@petya.org.hu To: Machiel Richards machi...@rdc.co.za, mysql@lists.mysql.com Subject: Re: Stored procedure Date: Wed, 05 Jan 2011 12:15:59 +0100 Hi, Use the mysql event scheduler instead of cron, the bash script is quite pointless, and call your stored procedure with now() - interval 1 day parameter. Peter On 01/05/2011 11:00 AM, Machiel Richards wrote: Good day all I am hoping that someone can assist me here. As per a client requirement, I am writing a script/stored procedure combination in order to do the following: - Script to be run within a cron once a day according to a set schedule. - script to connect to mysql and call a stored procedure - stored to procedure to do the following: * retrieve row id of the record that indicates the last record of a specified date (i.e 00:00 yesterday) [select max(id) into max_id from table1 where utc dt] * delete records from table2 where id max_id * delete records from table1 where id max_id After a struggle to get the script and stored procedure working I am now stuck at the following point. the date that needs to be specified to the stored procedure must be in the following format: 2011-01-04 00:00 (i.e. yesterday 00:00) meaning that everything before this date and time needs to be deleted. However when trying to run the script with the date like this, then I get the following message: ERROR 1064 (42000) at line 1: 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 '00:00)' at line 1 I initially had the script create the date in a different manner but then the 00:00 was seen as a seperate argument which didn't work. After some changes the date is now being read correctly from what I can tell but now I get the message above. Herewith my script and stored procedure definitions: Script: #!/bin/bash DATE=`date --date=1 days ago +%Y-%m-%d` 00:00 echo $DATE mysqldump -u root -ppassword --databasesDB /backups/DB_backup.dump mysql -u root -ppassword -DDB -e call select_delete_id_2($DATE) exit Stored Proc: begin declare max_id int(11); select max(id) into max_id from table1 where utc dt; delete from table2 where id max_id; delete from table1 where id max_id; end Does anybody perhaps have any suggestions? Regards Machiel
Re: Stored procedure
http://dev.mysql.com/doc/refman/5.1/en/events.html On 01/05/2011 12:21 PM, Machiel Richards wrote: HI How do I use the mysql event scheduler? I have not used this as yet so not sure how to use it. Regards Machiel -Original Message- *From*: petya pe...@petya.org.hu mailto:petya%20%3cpe...@petya.org.hu%3e *To*: Machiel Richards machi...@rdc.co.za mailto:machiel%20richards%20%3cmachi...@rdc.co.za%3e, mysql@lists.mysql.com mailto:mysql@lists.mysql.com *Subject*: Re: Stored procedure *Date*: Wed, 05 Jan 2011 12:15:59 +0100 Hi, Use the mysql event scheduler instead of cron, the bash script is quite pointless, and call your stored procedure with now() - interval 1 day parameter. Peter On 01/05/2011 11:00 AM, Machiel Richards wrote: Good day all I am hoping that someone can assist me here. As per a client requirement, I am writing a script/stored procedure combination in order to do the following: - Script to be run within a cron once a day according to a set schedule. - script to connect to mysql and call a stored procedure - stored to procedure to do the following: * retrieve row id of the record that indicates the last record of a specified date (i.e 00:00 yesterday) [select max(id) into max_id from table1 where utc dt] * delete records from table2 where id max_id * delete records from table1 where id max_id After a struggle to get the script and stored procedure working I am now stuck at the following point. the date that needs to be specified to the stored procedure must be in the following format: 2011-01-04 00:00 (i.e. yesterday 00:00) meaning that everything before this date and time needs to be deleted. However when trying to run the script with the date like this, then I get the following message: ERROR 1064 (42000) at line 1: 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 '00:00)' at line 1 I initially had the script create the date in a different manner but then the 00:00 was seen as a seperate argument which didn't work. After some changes the date is now being read correctly from what I can tell but now I get the message above. Herewith my script and stored procedure definitions: Script: #!/bin/bash DATE=`date --date=1 days ago +%Y-%m-%d` 00:00 echo$DATE mysqldump -u root -ppassword --databasesDB /backups/DB_backup.dump mysql -u root -ppassword -DDB -ecall select_delete_id_2($DATE) exit Stored Proc: begin declare max_id int(11); select max(id) into max_id from table1 where utc dt; delete from table2 where id max_id; delete from table1 where id max_id; end Does anybody perhaps have any suggestions? Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: stored procedure syntax error
I think, you have to use prepare() before run that select statement.
i.e
SET @s = CONCAT(SELECT * INTO OUTFILE, c ,FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
BY '' LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b);
PREPARE stmt FROM @s;
EXECUTE stmt;
regards,
nilnandan
DAREKAR, NAYAN (NAYAN) wrote:
Hi all !
I m getting an error while writing stored procedure, the code is as below and
error
CODE
--
DELIMITER $$
DROP PROCEDURE IF EXISTS `aaa` $$
CREATE definer=`ro...@`%mailto:definer=`ro...@`%` PROCEDURE `aaa`()
BEGIN
DECLARE b VARCHAR(255);
DECLARE c VARCHAR(255);
SET b= CONCAT(SUBDATE(CURDATE(), INTERVAL 15 DAY), 00:00:00);
SET c= CONCAT(',C://cells_summary.csv,');
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b; //wrong
END $$
DELIMITER ;
--
ERROR
---
Script line: 4 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 '(c)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n''
at line 7
-
I guess there is a wronge syntax,
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b;
kindly anyone can help with correct syntax.
Thanx
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: stored procedure and random table name - temp table, merge, prepared statement
Creating a temporary merge table works fine for me on 5.0.
Your table isn't innodb is it? That will fail with an error like you're
getting.
Regards,
Gavin Towey
-Original Message-
From: Dante Lorenso [mailto:da...@lorenso.com]
Sent: Thursday, December 10, 2009 3:20 PM
To: mysql@lists.mysql.com
Subject: stored procedure and random table name - temp table, merge, prepared
statement
All,
I have a stored procedure that I'm writing where I need to run a lot of
queries against a particular table. The name of the table will be a
parameter to the stored procedure ... example:
CALL normalize_data('name_of_table_here');
Since I want to run queries against this table, I don't want to have to use
prepared statements for all the queries because treating my queries as
strings gets ugly. Ideally I want to use the table name as a variable in
the stored procedure, but as a hack around that, I thought about trying this
trick instead: give the table name an alias.
-- remove our temporary table if it already exists
DROP TABLE IF EXISTS dante;
--
-- clone the table structure
CREATE TEMPORARY TABLE dante LIKE name_of_table_here;
--
-- change the temporary table to a merge table which references the named
table
ALTER TABLE dante ENGINE=MERGE UNION(name_of_table_here);
Once these 3 statements were run, the merge table would essentially just
be a view on the underlying table and all my following queries could
reference the dante table and not the strangely named random table.
Note, that queries above that use name_of_table_here would need to be
prepared and executed using the string concat approach.
The problem I am having is that this strategy is not working. After running
the statements above, I check my new dante table and it doesn't work:
DESC dante;
Error Code : 1168
Unable to open underlying table which is differently defined or of
non-MyISAM type or doesn't exist
So, how can I accomplish what I am trying to do? I just want to alias a
random table to a fixed name (preferably as a temporary table name so that
it won't conflict with other connections running similar code
simultaneously) so that I can avoid having to use prepared statements
through my whole stored procedure. I may potentially perform 20-30 queries
to the table which is passed in and want to keep this code looking clean.
I could avoid this problem altogether if I can assign an alias to a table:
ALIAS dante TO name_of_table_here;
or use a variable table name in a query inside a stored procedure:
SET @table_name = 'name_of_table_here';
INSERT INTO some_table (value)
SELECT something
FROM @table_name
WHERE ...;
Am using MySQL 5.1.36. Any pointers?
-- Dante
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Re: stored procedure and random table name - temp table, merge, prepared statement
Gavin Towey wrote:
Creating a temporary merge table works fine for me on 5.0.
Your table isn't innodb is it? That will fail with an error like you're
getting.
Strange. Now that I am on my home network and trying this again, it
seems to be working.
mysql SELECT version();
+--+
| version()|
+--+
| 5.1.41-community |
+--+
1 row in set (0.00 sec)
Here is my stored procedure:
8
CREATE PROCEDURE `test_massage_table`(IN in_table_name VARCHAR(64))
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
-- remove temp table
DROP TABLE IF EXISTS test_temp;
-- clone table structure from submitted table
SET @s = CONCAT('CREATE TABLE test_temp LIKE ', in_table_name);
PREPARE stmt FROM @s;
EXECUTE stmt;
-- convert table type to MERGE. Pass through to original table
SET @s = CONCAT('ALTER TABLE test_temp ENGINE=MERGE UNION(',
in_table_name, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;
-- test query 1
UPDATE test_temp SET value = value * value;
-- test query 2
UPDATE test_temp SET modified = NOW();
-- test query 3
DELETE FROM test_temp WHERE value 10;
-- test query 4
SELECT *
FROM test_temp;
END;
8
Then, here is the code I used to test it:
8
-- destroy tables
DROP TABLE IF EXISTS test_table_odds;
DROP TABLE IF EXISTS test_table_evens;
DROP TABLE IF EXISTS test_temp;
--
-- create new tables
CREATE TABLE `test_table_odds` (
`value` int(11) unsigned NOT NULL,
`modified` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
--
-- check table
SELECT * FROM test_table_odds;
SELECT * FROM test_table_evens;
--
-- run new procedure stuff
CALL test_massage_table('test_table_odds');
CALL test_massage_table('test_table_evens');
8
And here is my output:
8
mysql -- destroy tables
mysql DROP TABLE IF EXISTS test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_table_evens;
Query OK, 0 rows affected (0.00 sec)
mysql DROP TABLE IF EXISTS test_temp;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- create new tables
mysql CREATE TABLE `test_table_odds` (
- `value` int(11) unsigned NOT NULL,
- `modified` datetime DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE test_table_evens LIKE test_table_odds;
--
-- add sample data
INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 0 rows affected (0.00 sec)
mysql CREATE TABLE test_table_evens LIKE test_table_odds;
Query OK, 0 rows affected (0.00 sec)
mysql --
mysql -- add sample data
mysql INSERT INTO test_table_odds (value) VALUES (1), (3), (5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql INSERT INTO test_table_evens (value) VALUES (2), (4), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql --
mysql -- check table
mysql SELECT * FROM test_table_odds;
+---+--+
| value | modified |
+---+--+
| 1 | NULL |
| 3 | NULL |
| 5 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql SELECT * FROM test_table_evens;
+---+--+
| value | modified |
+---+--+
| 2 | NULL |
| 4 | NULL |
| 6 | NULL |
+---+--+
3 rows in set (0.00 sec)
mysql --
mysql -- run new procedure stuff
mysql CALL test_massage_table('test_table_odds');
+---+-+
| value | modified|
+---+-+
| 1 | 2009-12-10 21:18:59 |
| 9 | 2009-12-10 21:18:59 |
+---+-+
2 rows in set (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql CALL test_massage_table('test_table_evens');
+---+-+
| value | modified|
+---+-+
| 4 | 2009-12-10 21:19:01 |
+---+-+
1 row in set (0.15 sec)
Query OK, 0 rows affected (0.15 sec)
8
So, thanks for the reply. I hope someone can review what I've done here
and let me know if there is a smarter way to accomplish what I'm trying
to do. Otherwise, I'll have to review what I was working on at the
office and figure out why that wasn't working for me. It might be a
mysql version difference, but I'm guessing it was something to do with
the original table being too complex for the Merge table to work. Maybe
indexes not matching?
Anyhow, let me know what you think of this strategy for sidestepping the
need for prepared statements when working with MyISAM tables passed to
Re: Stored Procedure Data Types
Hi, Use a temporary table to store the ids and join to it for the final update? That will at least avoid an error when the cursor selects zero records. Cheers, -Janek On Wed, 2009-05-20 at 16:05 -0400, W. Scott Hayes wrote: Hello, I would like to do a select on a table to get back the IDs of some of the records. Then take those IDs and do a single update using a WHERE clause like (recordID IN (2,44,21)) My question is: Can I build a string using a cursor that has all of the IDs and then issue an update using the string as part of the WHERE clause? Are there functions that facilitate this better? I'm wondering if there is some sort of column function that will grab the IDs from the initial select. Below is my code. Thanks for any advice. DELIMITER $$ DROP PROCEDURE IF EXISTS sp_getNextQueueBlock$$ CREATE PROCEDURE sp_getNextQueueBlock() BEGIN DECLARE l_LinkQueueID INTEGER; DECLARE no_more_queue_items INT DEFAULT 0; DECLARE l_updateString VARCHAR(2000) DEFAULT ''; DECLARE queue_csr CURSOR FOR SELECT LinkQueueID FROM linkqueue WHERE Completed 0 LIMIT 200; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_queue_items=1; START Transaction; OPEN queue_csr; queue_loop:LOOP FETCH queue_csr INTO l_LinkQueueID; IF no_more_queue_items=1 THEN LEAVE queue_loop; END IF; SET l_updateString=CONCAT(l_updateString,', ',l_LinkQueueID); END LOOP queue_loop; IF LENGTH(l_updateString) 2 THEN SET l_updateString=SUBSTRING(l_updateString,3,LENGTH(l_updateString)-2); END IF; UPDATE linkqueue SET Completed = 0 WHERE (LinkQueueID IN (l_updateString)); commit; END$$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [Stored Procedure] - Error handling
Ratheesh, There's a specific list for PHP database functions, Databases and PHP (PHP-DB), which you can find at http://php.net/mailinglists . I'm forwarding your message over to there, and recommend that you subscribe to that list. You'll probably get more direct responses by narrowing-down the lists to the support you need. LIST: Original message follows. On Tue, Apr 15, 2008 at 12:53 PM, Ratheesh K J [EMAIL PROTECTED] wrote: Hello folks, Any way to retrieve the error code/error number from a stored proc. Scenario -- calling a stored proc from PHP - using mysqli_multi_query() The stored proc has multiple queries. Lets say one of the queries generates an exception. How do I retrieve the error message within the procedure itself? OR Is there any way from PHP to get the last error msg? I tried with mysqli_error().. Did not work.. Any inputs will be appriciated. Thanks, Ratheesh -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure problem
Dynamic SQL would work for Imbedding String for the IN clause.
That would be too messy for such a little query.
Here is a crazy suggestion
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE
INSTR(CONCAT(',',grammatures,','),CONCAT(',',article_grammatures,',')) 0
SELECT @newids;
END;
By the way, is it ' article_grammature ' or ' article_garmmature ' ?
Give it a try !!!
-Original Message-
From: Barry [mailto:[EMAIL PROTECTED]
Sent: Friday, February 15, 2008 5:28 AM
To: mysql@lists.mysql.com
Subject: Stored Procedure problem
Hello everyone!
Hopefully somone can enlight me, i am hanging on this vor a few hours now :/
i have this stored procedure:
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids
FROM articles
WHERE article_garmmature IN (grammatures);
SELECT @newids;
END;
What i wan't is to store the id's as a comma seperated list into the
newids VAR.
I get the error that there is more than one result set.
I also tried group_concat, but that didn't worked :(
Anyone has an idea how to store multiple ids into one VAR ?
Thanks for reading!
Best wishes
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Just use the type varchar.I always use it in my regular life.
On Feb 11, 2008 4:44 PM, Magne Westlie [EMAIL PROTECTED] wrote:
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here,
wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
)
, so if I am to rely on the doc, it should be very quick the way I use
it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: stored procedure, parameter type help needed
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in;)
, so if I am to rely on the doc, it should be very quick the way I use it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi again,
I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the IN-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
---
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
user_id INT,
user_name VARCHAR(100),
PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'), (4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
);
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
EXECUTE stmt;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT uid
FROM temp_uids);
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I wanted
to make
the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure, parameter type help needed
-Original Message-
From: Magne Westlie [mailto:[EMAIL PROTECTED]
Sent: Friday, February 08, 2008 5:37 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: stored procedure, parameter type help needed
Hi again,
I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the IN-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes
to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
---
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
user_id INT,
user_name VARCHAR(100),
PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'),
(4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
);
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
EXECUTE stmt;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only problem
is that the query I actually want to use this in, is a 100 line query
with lots of arguments. I don't feel to good about creating it into a
bunch of strings (16) that I have to concatenate with the variables
inbetween.
Also, I was moving the query into a stored procedure because I wanted to
make the request fast, and the concatenating and string handling takes
some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne, I want to create a stored procedure that runs a query using the IN operator ... See 'Variable-length argument for query IN() clause' at http://www.artfulsoftware.com/queries.php PB - Magne Westlie wrote: Dear List, I want to create a stored procedure that runs a query using the IN operator (or is IN a function???) on values sent as argument. That is, my procedure should be called with something like: CALL get_users((1,2)); and I was hoping to implement something like this: CREATE PROCEDURE get_users(uids LIST) -- what type to use here? BEGIN SELECT * FROM user_test WHERE user_id IN uids; END:: Thanks, Magne Westlie Working test code for getting one user only: -- DROP TABLE IF EXISTS user_test; CREATE TABLE user_test ( user_id INT, user_name VARCHAR(100), PRIMARY KEY (user_id) ) ENGINE=MyIsam; INSERT INTO user_test VALUES(1, 'Bob'); INSERT INTO user_test VALUES(2, 'Ann'); INSERT INTO user_test VALUES(3, 'Bill'); DROP PROCEDURE IF EXISTS get_users; DELIMITER :: CREATE PROCEDURE get_users(uid INT) BEGIN SELECT * FROM user_test WHERE user_id = uid; END:: DELIMITER ; CALL get_users(3); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi Magne
...the query I actually want to use this in, is a 100 line query with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I wanted
to make
the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN (
|
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (', ('''abc'',''def'',''ghi''' );
|
||
PB
Re: stored procedure not working in legacy ASP
Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure not working in legacy ASP
The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? -- Dave Michael Dykman wrote: Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure not working in legacy ASP
Would you care to send the source of that procedure plus tell us why you believe it works when called by something other than C#? On 5/30/07, Critters [EMAIL PROTECTED] wrote: The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? -- Dave Michael Dykman wrote: Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure not working in legacy ASP
[snip] The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? [/snip] You need a while loop. Does the SP work from the command line properly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure parameters problem
Hi Paul, I've spent the past two days trying to reproduce my problem and I think I may have brought this on myself. I've had been trying to create a procedure with the Query Browser GUI and was unable to do so. So I tried to create them with MySQL-Front and MySQL Control Center and somehow I was able to get the procedure added to the proc table. Once the procedure was created I went into the mysql.proc table with MySQL-Front and modified the values of param_list and body fields to the values I had. So I probably put the strVal VarChar in the param_list without ever going through a Create or Alter Procedure statement. My bad. I'm still not able to create a procedure with Query Browser, which confuses me. I can create one using the command line client but the same commands do not work in Query Browser. That's a problem for another post. Paul DuBois [EMAIL PROTECTED] 12/2/06 10:38 AM At 11:34 AM -0800 12/1/06, Chris White wrote: On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. Hmm ... I don't know. CHAR is equivalent to CHAR(1), but for VARCHAR the length is not optional. For example, if you try to create a table using a length-less VARCHAR, this happens: mysql create table t (c varchar); 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 ')' at line 1 If you are able to create the procedure when no VARCHAR length is given, I think that might be considered a bug. And it's certainly a problem that you get proc table is missing, corrupt, or contains bad data. when attempting to invoke the procedure. Ed, could you file a bug report about this issue at bugs.mysql.com? That way, one of the developers can figure out what's going on and resolve the issue. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure parameters problem
At 11:34 AM -0800 12/1/06, Chris White wrote: On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. Hmm ... I don't know. CHAR is equivalent to CHAR(1), but for VARCHAR the length is not optional. For example, if you try to create a table using a length-less VARCHAR, this happens: mysql create table t (c varchar); 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 ')' at line 1 If you are able to create the procedure when no VARCHAR length is given, I think that might be considered a bug. And it's certainly a problem that you get proc table is missing, corrupt, or contains bad data. when attempting to invoke the procedure. Ed, could you file a bug report about this issue at bugs.mysql.com? That way, one of the developers can figure out what's going on and resolve the issue. Thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure parameters problem
On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure parameters problem
Got it. Thanks Chris White [EMAIL PROTECTED] 12/1/06 11:34 AM On Friday 01 December 2006 11:22, Ed Reed wrote: I have a problem/question I'd like to find someone else to verify/answer for me. I'm using MySQL 5.1.09 running on Netware. I've created a stored procedure that has one parameter, Create Procedure MyTest(strVal VarChar) Begin Select Field1, Field2, Field3 From MyTable Where Field3=strVal; End varchar is meant to be variable, so it MUST have a length supplied. If you want the same flexibility without specifying a specific length, use TEXT instead. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Security Question
What you are asking for is exactly what DEFINER security does. The applicxation owner grants appuser the right to execute the procedure, but not to SELECT from any tables. The procedure is then run with the security attributes of the definer of the procedure, the application owner, even though it is the application user that runs it. This is no different than other DBMS systems, the difference being that you have the option of defining a procedure with INVOKER rights, in which case the procedure will run with the security attributes of the application user, and you need to grant that user access to any tables that are accessed within the procedure. So in essence, MySQL doesn't limit you compared to most other DBMS's, it gives you more options. Cheers /Karlsson [EMAIL PROTECTED] wrote: When creating a stored procedure, you can set the sql security characteristic to either definer or invoker. As an example, I have a stored procedure that does a select from a table, and an application user (appuser) that calls the stored procedure. If the sql security is set to invoker, then I have to give appuser both select and execute privileges. If the sql security is set to definer, then the definer needs select privileges and appuser only needs execute. What I'd like to be able to do is to give appuser the execute privilege and not have to give any privileges on the underlying tables to the definer. Is this possible? We do almost 100% of our work through stored procedures. It would be a lot easier to manage just the execute privilege. Are there reasons why this is not a good idea? This is how we manage security with our other DBMS and it's worked quite well, but it doesn't have the definer/invoker characteristic for stored procs either. Any suggestions about how to manage users/privileges would be appreciated. Donna -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure TYPE
*Disclaimer - this will be kind of vague mainly because I only vaguely recall the material. I am trying to create a stored procedure. Now I vaguely recall reading something that says I have to match a column type if I am messing with one. Something like I want to match a table column type... even if I don't know what it is. I think it was something like TYPE table.column... I'm not sure if it was only in a DECLARE or if I can set one of the INOUT vars to the column type as well. Oracle does that. MySQL, as far as I know, doesn't. 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 Procedure Problem
Josh wrote: I posted this same item on the mysql forum but the only place that looked remotely appropriate was under the Newbie section... I'm not sure if it will be answer there so I thought I might toss it out here to see if there were any takers. I'm baffled as to why this stored procedure is acting this way. See the below sample table and examples. The query as provided doesn't parse--chgID doesn't exist. Did you try naming the sproc params differently from the corresponding columns, eg pEMPID, pBDID? PB - mysql select * from Rates; +--+--+---+-+---+ | rtID | bdID | empID | rtStartDate | rtBillingRate | +--+--+---+-+---+ |1 | NULL | NULL | -00-00 | 0.00 | |2 | NULL | 1 | 2004-01-01 | 2.00 | |3 | NULL | 1 | 2004-05-10 | 4.00 | |4 | NULL | 1 | 2005-01-10 | 6.00 | |5 | NULL | 1 | 2005-04-12 | 8.00 | |6 | NULL | 1 | 2006-01-02 | 10.00 | |8 | 37 | 1 | 2005-10-01 | 25.00 | +--+--+---+-+---+ DELIMITER $ CREATE PROCEDURE test_rate (EMPID int, BDID int, CURRENTDATE date) BEGIN SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID IS NULL and rtStartDate = CURRENTDATE)) LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and rt3.chgID IS NULL and rt3.bdID=BDID and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID=BDID and rtStartDate = CURRENTDATE)) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; END$ DELIMITER ; mysql call test_rate (1,NULL,'2005-09-01'); +---+ | rtBillingRate | +---+ | 8.00 | +---+ CORRECT! mysql call test_rate (1,37,'2005-10-10'); +---+ | rtBillingRate | +---+ | 25.00 | +---+ CORRECT! mysql call test_rate (1,NULL,'2005-10-10'); +---+ | rtBillingRate | +---+ | 0.00 | +---+ 1 row in set (0.01 sec) WRONG! This should have returned 8.00. When I run this query by itself (outside the procedure) I get the correct result: (notice I'm plugging in EMPID, BDID, and CURRENTDATE parameters) SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=1 and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID IS NULL and rtStartDate = '2005-10-10')) LEFT JOIN Rates rt3 ON (rt3.empID=1 and rt3.chgID IS NULL and rt3.bdID=NULL and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID=NULL and rtStartDate = '2005-10-10')) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; +---+ | rtBillingRate | +---+ | 8.00 | +---+ 1 row in set (0.00 sec) CORRECT! What's going wrong in the stored procedure? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Problem
Peter, nice catch. Changing the parameter names did the trick. Thanks. --- Peter Brawley [EMAIL PROTECTED] wrote: Josh wrote: I posted this same item on the mysql forum but the only place that looked remotely appropriate was under the Newbie section... I'm not sure if it will be answer there so I thought I might toss it out here to see if there were any takers. I'm baffled as to why this stored procedure is acting this way. See the below sample table and examples. The query as provided doesn't parse--chgID doesn't exist. Did you try naming the sproc params differently from the corresponding columns, eg pEMPID, pBDID? PB - mysql select * from Rates; +--+--+---+-+---+ | rtID | bdID | empID | rtStartDate | rtBillingRate | +--+--+---+-+---+ |1 | NULL | NULL | -00-00 | 0.00 | |2 | NULL | 1 | 2004-01-01 | 2.00 | |3 | NULL | 1 | 2004-05-10 | 4.00 | |4 | NULL | 1 | 2005-01-10 | 6.00 | |5 | NULL | 1 | 2005-04-12 | 8.00 | |6 | NULL | 1 | 2006-01-02 | 10.00 | |8 | 37 | 1 | 2005-10-01 | 25.00 | +--+--+---+-+---+ DELIMITER $ CREATE PROCEDURE test_rate (EMPID int, BDID int, CURRENTDATE date) BEGIN SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID IS NULL and rtStartDate = CURRENTDATE)) LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and rt3.chgID IS NULL and rt3.bdID=BDID and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID=BDID and rtStartDate = CURRENTDATE)) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; END$ DELIMITER ; mysql call test_rate (1,NULL,'2005-09-01'); +---+ | rtBillingRate | +---+ | 8.00 | +---+ CORRECT! mysql call test_rate (1,37,'2005-10-10'); +---+ | rtBillingRate | +---+ | 25.00 | +---+ CORRECT! mysql call test_rate (1,NULL,'2005-10-10'); +---+ | rtBillingRate | +---+ | 0.00 | +---+ 1 row in set (0.01 sec) WRONG! This should have returned 8.00. When I run this query by itself (outside the procedure) I get the correct result: (notice I'm plugging in EMPID, BDID, and CURRENTDATE parameters) SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=1 and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID IS NULL and rtStartDate = '2005-10-10')) LEFT JOIN Rates rt3 ON (rt3.empID=1 and rt3.chgID IS NULL and rt3.bdID=NULL and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID=NULL and rtStartDate = '2005-10-10')) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; +---+ | rtBillingRate | +---+ | 8.00 | +---+ 1 row in set (0.00 sec) CORRECT! What's going wrong in the stored procedure? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure issue.
I'm having some issues creating a stored procedure to optimize tables
in the database.
PREPARE accepts only CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT,
SET and UPDATE.
PB
-
DreamWerx wrote:
I'm having some issues creating a stored procedure to optimize tables
in the database. I'm pulling the table names from the information
schema. The tablename doesn't seem to be correctly being replaced in
the optimize command..
I've tried used prepared statements which seem to correctly replace
the tableName, but I get an error that prepared statements do not
support that kind of query.
With this current version the error is: table queue.tableName doesn't exist..
Any thoughts from some SP gurus? Thanks.
--
DELIMITER $$;
DROP PROCEDURE IF EXISTS `queue`.`sp_OptimizeDatabase`$$
CREATE PROCEDURE `queue`.`sp_OptimizeDatabase` ()
BEGIN
DECLARE exitValue INT;
DECLARE tableName CHAR(120);
DECLARE cursorList CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables where
TABLE_SCHEMA = 'queue' AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET exitValue = 1;
OPEN cursorList;
REPEAT
FETCH cursorList INTO tableName;
OPTIMIZE TABLE tableName;
-- SET @optSQL := concat('OPTIMIZE TABLE ', tableName);
-- PREPARE pOptimize FROM @optSQL;
-- EXECUTE pOptimize;
-- DEALLOCATE PREPARE pOptimize;
UNTIL exitValue = 1
END REPEAT;
CLOSE cursorList;
END$$
DELIMITER ;$$
-
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.7/259 - Release Date: 2/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure work badly with binlog
Hello.
This is a bug:
http://bugs.mysql.com/bug.php?id=16378
AESYS S.p.A. [Enzo Arlati] wrote:
I found a problem using stored procedure and bin-log enabled.
Suppose I stored procedure like this:
==
DELIMITER $$;
DROP PROCEDURE IF EXISTS `pmv_manager`.`pAggiornaStatusNotificaPMV`$$
CREATE PROCEDURE `pAggiornaStatusNotificaPMV`( ipAddrPMV varchar(16),
ipAddrST varchar(16), ipAddrSNMP varchar(16))
BEGIN
declare ifound int default -1;
-- --
select ipAddrPMV, ipAddrST, ipAddrSNMP;
-- --
select count(ip_addr_pmv) into ifound from status_notifica_pmv
where ip_addr_pmv = ipAddrPMV;
if( ifound = 0 ) then
insert into status_notifica_pmv ( ip_addr_pmv, ip_addr_srv_st,
ip_addr_srv_snmp, dt_mod )
values( ipAddrPMV, ipAddrST , ipAddrSNMP,
current_timestamp );
else
update status_notifica_pmv
set ip_addr_srv_st = ipAddrST,
ip_addr_srv_snmp = ipAddrSNMP,
dt_mod = current_timestamp
where ip_addr_pmv = ipAddrPMV;
end if;
END$$
DELIMITER ;$$
==
where tablke status_notifica_pmv are defined as:
==
CREATE TABLE `status_notifica_pmv`
`ip_addr_pmv` varchar(16) NOT NULL,
`ip_addr_srv_st` varchar(16) default NULL,
`ip_addr_srv_snmp` varchar(16) default NULL,
`dt_mod` timestamp NULL default NULL,
PRIMARY KEY (`ip_addr_pmv`),
CONSTRAINT `status_notifica_pmv_ibfk_1` FOREIGN KEY
(`ip_addr_pmv`) REFERENCES `lista_pmv` (`IPAddress`) ON DELETE CASCADE ON
UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
==
if I call the procedure the task is performed as expected
it create a new record or modify it if present with the right value
==
call pAggiornaStatusNotificaPMV( '192.168.200.222', '192.168.200.218',
'192.168.200.218' );
- ipAddrPMVipAddrST ipAddrSNMP
--- --- ---
192.168.200.222 192.168.200.218 192.168.200.218
==
but if I look inside the binlog file I found corrupted data
==
pmv_manager_log_bin.011864 Query1 644
use `pmv_manager`; update status_notifica_pmv
set ip_addr_srv_st = NAME_CONST('ipAddrST',4оQÐоQDÑQ$Ñ_
WRONG DATA
==
==
Maybe I wrong something or should be a bug ?
Enzo Arlati
[EMAIL PROTECTED]
--
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]
Re: Stored Procedure Question
Jesse Castleberry [EMAIL PROTECTED] wrote on 11/07/2005 01:26:59 PM: I've got a stored procedure I'm trying to convert from MS SQL. I've gotton so far with it, but it's complaining about the INSERT command. It's a very simple stored procedure, so it should easy to figure out, but I'm not familiar with the MySQL Stored Procedure syntax. If someone can point out what I'm doing wrong here, I'd appreciate it: CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30), out AddedID Int ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // ERROR RIGHT HERE. AddedID = LAST_INSERT_ID() END; It complains about the INSERT INTO command where I've indicated above, with a Syntac error. What is the proper syntax for this? Thanks, Jesse Within the stored procedures, you need to tell MySQL when you have reached the end of each command. In order to do that, you have to use a semicolon(;), just as you would while working interactively. In order to define a stored procedure interactively you have to change the CLI's command delimiter so that you can use a semicolon within the definition of your stored procedure and not end the CREATE PROCEDURE statement too early. Look at the interactive examples on this page: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html Can you see where they temporarily reset the command delimiter, created their stored procedure (with each statement within the SP ending with a ; ), then reset the interactive command delimiter? The error message is saying that you didn't end your INSERT statement... you forgot your semicolon (;) to separate it from the statement where you tried to set the value AddedID (that's going to be a different error, you neglected to use SET or SELECT). Don't give up, you almost got it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Stored Procedure Question
Jesse, BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // ERROR RIGHT HERE. AddedID = LAST_INSERT_ID() END; First, there's a right parenthesis missing. Second, the expression AddedID = LAST_INSERT_ID() will evaluate to 1, 0 or Null depending on whether AddedID = Last_Insert_Id() or whether either is Null---likely not what you intend. To assign a value inline, use the ':=' operator. PB -- Jesse Castleberry wrote: I've got a stored procedure I'm trying to convert from MS SQL. I've gotton so far with it, but it's complaining about the INSERT command. It's a very simple stored procedure, so it should easy to figure out, but I'm not familiar with the MySQL Stored Procedure syntax. If someone can point out what I'm doing wrong here, I'd appreciate it: CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30), out AddedID Int ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // ERROR RIGHT HERE. AddedID = LAST_INSERT_ID() END; It complains about the INSERT INTO command where I've indicated above, with a Syntac error. What is the proper syntax for this? Thanks, Jesse -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 11/5/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Conversion
Jesse, VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. A stored procedure cannot return a value. PB - Jesse Castleberry wrote: I am converting a MS SQL Server ASP application over to use MySQL. I have two simple stored procedures that I need to convert. I have very little experience with MS SQL stored procedures, and none-what-so-ever with stored procedures in MySQL, so I really don't know what this should look like. I'll post the first one, and if I'm able to figure it out, I'll attempt my second one by myself. Here's the stored procedure converted as much as I can get it. CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30) ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. Thanks, Jesse -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure, Dates, and Between
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure, Dates, and Between
Kent Roberts [EMAIL PROTECTED] wrote on 08/09/2005 10:42:24 AM:
I have a simple SP that is selecting rows based on a date range
using parameters. I've tried several permutations that all return 0
rows.
The select statement looks like this:
select * from 'mit_log'.'mitlog' where StartDateTime between
'2005-04-01' and '2005-04-07'
and returns over 300,000 rows. The SP looks like this:
-
DELIMITER $$
DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$
Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime,
endDate datetime)
BEGIN
SELECT *
FROM mitlog
WHERE (mitlog.StartDateTime between @begDate And @endDate);
END$$
-
and compiles ok. The calling statement looks like this:
call spPatientsLikeUsersByDate ('2005-04-01','2005-04-07')
Like I said, I've tried several guesses at syntax with no luck. When
I take out the parameters and hard code the dates, it works.
Any ideas?
Thanks in advance,
Kent in Montana
Could it be that you are calling one procedure (spPatientsLikeUsersByDate)
but making all of your changes in another (spUsingDateRange)? What if you
tried calling `spUsingDateRange` instead?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Stored Procedure, Dates, and Between
That's it. Thanks Mark. I think I was confusinged by MS SQL Server syntax which prefixes both session variables and parameters with @. And you're right Scott, I changed the name of the SP for posting simplicity and forgot to change the colling statement to match. Thanks a lot both of you for getting back to quickly! Mark Matthews 08/09/05 08:46AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE-
Re: Stored Procedure, Dates, and Between
Sorry, I'll try and proof reed more karefullly in the futchure. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure, Dates, and Between
Now that I think about it, if MySql forced declaration of session variables it would avoid some nasty bugs in SPs. Mark Matthews 08/09/05 08:46AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE-
RE: STORED PROCEDURE
CREATE PROCEDURE title() BEGIN DECLARE title VARCHAR(255); SET title = '%Unconditional%'; SELECT title; END; // mysql CALL title()// +-+ | title | +-+ | %Unconditional% | +-+ 1 row in set (0.01 sec) HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: 02 August 2005 15:12 To: 'Mysql ' Subject: STORED PROCEDURE I'm used with MS SQL and could not understand MySQL's document. I'm trying to create something like the simple example DECLARE @title varchar(255) SET @title='%Unconditional%' SELECT @title; How do I get around to it in MySQL? -- Power to people, Linux is here. -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STORED PROCEDURE
Scott Hamm [EMAIL PROTECTED] wrote on 08/02/2005 10:12:01 AM: I'm used with MS SQL and could not understand MySQL's document. I'm trying to create something like the simple example DECLARE @title varchar(255) SET @title='%Unconditional%' SELECT @title; How do I get around to it in MySQL? You have to be running a MySQL v5.0.x or better to even try using a stored procedure. What version server are you using? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote: CREATE PROCEDURE title() BEGIN DECLARE title VARCHAR(255); SET title = '%Unconditional%'; SELECT title; END; // mysql CALL title()// +-+ | title | +-+ | %Unconditional% | +-+ 1 row in set (0.01 sec) HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: 02 August 2005 15:12 To: 'Mysql ' Subject: STORED PROCEDURE I'm used with MS SQL and could not understand MySQL's document. I'm trying to create something like the simple example DECLARE @title varchar(255) SET @title='%Unconditional%' SELECT @title; How do I get around to it in MySQL? -- Power to people, Linux is here. -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 Something similiar to CREATE PROCEDURE Select_title() BEGIN DECLARE u_title varchar(255) SET u_title='%Unconditional%' SELECT T.Title, B.BAND_Name, C.Type, T.Track FROM Title T LEFT JOIN Bands B ON B.BandID=T.B_ID LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID WHERE Title LIKE u_title ORDER BY Title; END; -- Power to people, Linux is here.
Re: STORED PROCEDURE
MySQL 5.0.9 Beta :) On 8/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Hamm [EMAIL PROTECTED] wrote on 08/02/2005 10:12:01 AM: I'm used with MS SQL and could not understand MySQL's document. I'm trying to create something like the simple example DECLARE @title varchar(255) SET @title='%Unconditional%' SELECT @title; How do I get around to it in MySQL? You have to be running a MySQL v5.0.x or better to even try using a stored procedure. What version server are you using? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Power to people, Linux is here.
RE: STORED PROCEDURE
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:38
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
CREATE PROCEDURE title()
BEGIN
DECLARE title VARCHAR(255);
SET title = '%Unconditional%';
SELECT title;
END;
//
mysql CALL title()//
+-+
| title |
+-+
| %Unconditional% |
+-+
1 row in set (0.01 sec)
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:12
To: 'Mysql '
Subject: STORED PROCEDURE
I'm used with MS SQL and could not understand MySQL's
document. I'm
trying to create something like the simple example
DECLARE @title varchar(255)
SET @title='%Unconditional%'
SELECT @title;
How do I get around to it in MySQL?
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Something similiar to
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255)
SET u_title='%Unconditional%'
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Yea, that will work - just modify the example I gave above to yours above..
What the hell, I'll even write it:
DELIMITER //
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Seems a bit of a strange use for a stored procedure though - I would have
thought something like this would be of more use:
CREATE PROCEDURE Select_title( u_title VARCHAR(255) )
BEGIN
CASE
WHEN u_title = '' THEN SET u_title='%Unconditional%';
ELSE SET u_title = CONCAT('%',u_title,'%');
END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: STORED PROCEDURE
-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:56
To: mysql@lists.mysql.com
Subject: RE: STORED PROCEDURE
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:38
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
CREATE PROCEDURE title()
BEGIN
DECLARE title VARCHAR(255);
SET title = '%Unconditional%';
SELECT title;
END;
//
mysql CALL title()//
+-+
| title |
+-+
| %Unconditional% |
+-+
1 row in set (0.01 sec)
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:12
To: 'Mysql '
Subject: STORED PROCEDURE
I'm used with MS SQL and could not understand MySQL's
document. I'm
trying to create something like the simple example
DECLARE @title varchar(255)
SET @title='%Unconditional%'
SELECT @title;
How do I get around to it in MySQL?
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Something similiar to
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255)
SET u_title='%Unconditional%'
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Yea, that will work - just modify the example I gave above to
yours above..
What the hell, I'll even write it:
DELIMITER //
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Seems a bit of a strange use for a stored procedure though -
I would have thought something like this would be of more use:
CREATE PROCEDURE Select_title( u_title VARCHAR(255) ) BEGIN CASE
WHEN u_title = '' THEN SET u_title='%Unconditional%';
ELSE SET u_title = CONCAT('%',u_title,'%'); END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
Hmm nice screwed formatting, but anyway.. ;)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STORED PROCEDURE
Hmm nice screwed formatting, but anyway.. ;) Please guys - the overquoting!! Thank you. -- Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STORED PROCEDURE
Your second statement hit the spot. :) Thanks!
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:38
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
CREATE PROCEDURE title()
BEGIN
DECLARE title VARCHAR(255);
SET title = '%Unconditional%';
SELECT title;
END;
//
mysql CALL title()//
+-+
| title |
+-+
| %Unconditional% |
+-+
1 row in set (0.01 sec)
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:12
To: 'Mysql '
Subject: STORED PROCEDURE
I'm used with MS SQL and could not understand MySQL's
document. I'm
trying to create something like the simple example
DECLARE @title varchar(255)
SET @title='%Unconditional%'
SELECT @title;
How do I get around to it in MySQL?
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Something similiar to
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255)
SET u_title='%Unconditional%'
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Yea, that will work - just modify the example I gave above to yours
above..
What the hell, I'll even write it:
DELIMITER //
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Seems a bit of a strange use for a stored procedure though - I would have
thought something like this would be of more use:
CREATE PROCEDURE Select_title( u_title VARCHAR(255) )
BEGIN
CASE
WHEN u_title = '' THEN SET u_title='%Unconditional%';
ELSE SET u_title = CONCAT('%',u_title,'%');
END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Power to people, Linux is here.
Re: Stored Procedure in MySQL 5.x
How effective? Very effective. Actually, I am testing it, and once I overcame a few syntax issues, it's working great. Procedures and functions both. I havent tested triggers yet. But so far I am using a mini-blog thing on my web site with them and I am loving the ease of Mysql combined (finally!) with the power of sprocs. Of course, it is beta right now, so I wouldnt stick it into anything you need to rely on yet, but get it going and try it for yourself. One thing to note, while the sprocs and functions work good on Windows, connecting with some client languages (python in my case) isnt working. Same goes for ODBC. If you use Linux, then it works fine. Oh, and another thing to note. If you use MySQL Query Browser to create the procecures, it has a bug and inserts the wrong delimiters in the drop if exists statement. That really messed me up at first. So, remove the // and insert the $$ and it will work great. Greg On 7/15/05, Scott Hamm [EMAIL PROTECTED] wrote: How effective is Stored Procedure in MySQL 5.x? -- Power to people, Linux is here. -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedure and proc table
Sorry, I have found the reason why. I have MySQL 4.1 and MySQL 5.0 running on different port. I was not passing --port=3307 to mysql client to connect to the correct server. -Original Message- From: Frondoni, Giorgio Sent: Tuesday, June 14, 2005 10:23 PM To: 'mysql@lists.mysql.com' Subject: Stored procedure and proc table I have just installed MySQL 5.0.6-beta on windows following the standard installation procedure. I have noticed that the table proc is missing from the mysql database and I can not run the CREATE PROCEDURE statement. What am I missing? I need to creare store procedure to port my application. Please help. Thank you Giorgio Frondoni AVP, Chief of System Development TransCore phone: (858) 826-4750 cell: (760) 214-4092 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure
Actually you can do it, it is tricky though. Below is my example, I have used similar in many procedures when I want to variabl-ize (made up word I know) table names. The same holds true for setting field values or order/group by. delimiter // DROP PROCEDURE IF EXISTS TestSelect // CREATE procedure TestSelect(IN field1 INT) BEGIN SET @QueryStmt = CONCAT( SELECT * FROM testTable WHERE X=1412 GROUP BY , field1 ); PREPARE stmt FROM @QueryStmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // Hope this helps. David Norman Wells Fargo Services This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 4:46 PM To: Paul Beer; mysql@lists.mysql.com Subject: Re: stored procedure At 17:15 -0400 4/8/05, Paul Beer wrote: I'm trying to pass a value into a stored procedure to dynamically set which column(s) to sort by. The following code doesn't work. The query executes but ignores my order by parameter. I assume there is a simple answer to this that I'm just missing. Yes. Unfortunately, the simple answer is that you can't do it. Parameters are for data values, not column names. (You'll encounter a similar problem in many database APIs if you try to use a parameter in a prepared statement for anything but a data value.) create procedure sp_equipment_find ( IN L_ORDER_BY MEDIUMTEXT ) BEGIN SELECT * from mytable ORDER BY @L_ORDER_BY; END$ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stored procedure
Actually you can do it, it is tricky though. Below is my example, I have used similar in many procedures when I want to variabl-ize (made up word I know) table names. The same holds true for setting field values or order/group by. delimiter // DROP PROCEDURE IF EXISTS TestSelect // CREATE procedure TestSelect(IN field1 INT) BEGIN SET @QueryStmt = CONCAT( SELECT * FROM testTable WHERE X=1412 GROUP BY , field1 ); PREPARE stmt FROM @QueryStmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // Hope this helps. David Norman Wells Fargo Services This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 4:46 PM To: Paul Beer; mysql@lists.mysql.com Subject: Re: stored procedure At 17:15 -0400 4/8/05, Paul Beer wrote: I'm trying to pass a value into a stored procedure to dynamically set which column(s) to sort by. The following code doesn't work. The query executes but ignores my order by parameter. I assume there is a simple answer to this that I'm just missing. Yes. Unfortunately, the simple answer is that you can't do it. Parameters are for data values, not column names. (You'll encounter a similar problem in many database APIs if you try to use a parameter in a prepared statement for anything but a data value.) create procedure sp_equipment_find ( IN L_ORDER_BY MEDIUMTEXT ) BEGIN SELECT * from mytable ORDER BY @L_ORDER_BY; END$ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure
At 17:15 -0400 4/8/05, Paul Beer wrote: I'm trying to pass a value into a stored procedure to dynamically set which column(s) to sort by. The following code doesn't work. The query executes but ignores my order by parameter. I assume there is a simple answer to this that I'm just missing. Yes. Unfortunately, the simple answer is that you can't do it. Parameters are for data values, not column names. (You'll encounter a similar problem in many database APIs if you try to use a parameter in a prepared statement for anything but a data value.) create procedure sp_equipment_find ( IN L_ORDER_BY MEDIUMTEXT ) BEGIN SELECT * from mytable ORDER BY @L_ORDER_BY; END$ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure error is misleading
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am using mysql 5.0.3, running under Solaris 8, and I believe the error
I am getting is not possible, but I don't know what I did wrong.
I do:
call assignItem('user', 1999, 97);//
I get:
ERROR 1172 (42000): Result consisted of more than one row
But, there is only one row possible, as the rid is unique.
I am wondering if there is something obvious I missed.
Thanx.
mysql CREATE PROCEDURE assignItem (
- user CHAR(15),
- rid int,
- start int)
- BEGIN
- DECLARE itemtype CHAR(13);
- DECLARE curusecount INT DEFAULT 0;
- SELECT itemtype INTO itemtype FROM items WHERE rid=rid;
- END;//
SELECT itemtype FROM items WHERE rid=1999;//
+--+
| itemtype |
+--+
| PC |
+--+
mysql describe items;//
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| rid | int(11) | NO | PRI | NULL| auto_increment |
| lid | int(11) | NO | MUL | 0 | |
| itemtype | char(4) | NO | MUL | | |
| label| char(12) | NO | UNI | | |
| status | char(1) | NO | | | |
| layoutx | int(11) | YES | | NULL| |
| layouty | int(11) | YES | | NULL| |
| theta| int(11) | YES | | NULL| |
+--+--+--+-+-++
- --
Love is mutual self-giving that ends in self-recovery. Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCVX3qikQgpVn8xrARAjUHAJ4xvgPiGge494hydhmzCfnLuyasegCcDEqk
94SdQEwxIdqFlktjGDWmySY=
=3ciU
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure error is misleading
On Apr 7, 2005, at 2:37 PM, James Black wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am using mysql 5.0.3, running under Solaris 8, and I believe the
error
I am getting is not possible, but I don't know what I did wrong.
I do:
call assignItem('user', 1999, 97);//
I get:
ERROR 1172 (42000): Result consisted of more than one row
But, there is only one row possible, as the rid is unique.
I am wondering if there is something obvious I missed.
Thanx.
mysql CREATE PROCEDURE assignItem (
- user CHAR(15),
- rid int,
- start int)
- BEGIN
- DECLARE itemtype CHAR(13);
- DECLARE curusecount INT DEFAULT 0;
- SELECT itemtype INTO itemtype FROM items WHERE rid=rid;
WHERE rid=rid? That is, WHERE 1999=1999? That would match all rows,
would it not?
- END;//
SELECT itemtype FROM items WHERE rid=1999;//
+--+
| itemtype |
+--+
| PC |
+--+
mysql describe items;//
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| rid | int(11) | NO | PRI | NULL| auto_increment |
| lid | int(11) | NO | MUL | 0 | |
| itemtype | char(4) | NO | MUL | | |
| label| char(12) | NO | UNI | | |
| status | char(1) | NO | | | |
| layoutx | int(11) | YES | | NULL| |
| layouty | int(11) | YES | | NULL| |
| theta| int(11) | YES | | NULL| |
+--+--+--+-+-++
- --
Love is mutual self-giving that ends in self-recovery. Fulton Sheen
James Black[EMAIL PROTECTED]
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure has very poor performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It would appear, based on my testing, so far, that using stored procedures is some between 25% slower and 10x slower, depending on the test. I am using jdk1.5, on Solaris 8, and mysql 5.0.3. I hope that when 5 comes out of beta that the performance is improved. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVCSEikQgpVn8xrARAkaSAJwOaAyQSfKKZXj0a1VNbiegkInKkwCfaEl1 HF3YdJVxevg7r/f6o2vkSBw= =k8yO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure calling another database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is it possible to have a stored procedure query another database? I have two databases where the second (B) uses information from (A) to make decisions. It would be great if the stored procedure on database B could query A, so that it can make decisions. I am using mysql 5.0.3 if it matters. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTbkVikQgpVn8xrARAk3tAJ9hgTrZZktxWhQc7JsH5jObS9NbcACfZBo0 NmYnKlIeJEzBiqUpaYsdTzg= =eDx8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: stored procedure calling another database
James Black [EMAIL PROTECTED] wrote on 04/01/2005 04:11:49 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is it possible to have a stored procedure query another database? I have two databases where the second (B) uses information from (A) to make decisions. It would be great if the stored procedure on database B could query A, so that it can make decisions. I am using mysql 5.0.3 if it matters. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTbkVikQgpVn8xrARAk3tAJ9hgTrZZktxWhQc7JsH5jObS9NbcACfZBo0 NmYnKlIeJEzBiqUpaYsdTzg= =eDx8 -END PGP SIGNATURE- If you mean can you query another database on the same server, the answer is YES. If you mean query another database on a different server, I don't know for certain but I don't think so. All you have to do to query a table in any database on your server is to qualify the table's name with the name of the database it is in. For instance, I can see a list of all of the user accounts of the server I am logged into if I say SELECT * from mysql.user; and because I put the database's name in front of the table name, it doesn't matter which database I run it from (which database I USE-ed last). I don't expect that behavior to change just because a query is inside a stored procedure. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
re: stored procedure slower than not using stored procedure?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this should be the norm, that the stored procedure took 879 ms when I called it 9 times, with slightly different values, and the non-stored procedure test took 512ms with also slightly different values. I am using jdk1.5 and mysql 5.0.3 on Solaris 8. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCTcI+ikQgpVn8xrARAic6AJ0QiAlSYq/MGpNNLj7sEfHabKUkPQCdEjIO Ccq+YOUiTNeXI/wF0xar+fM= =namZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure debugger
matt_lists wrote: Anybody have any recommendations for a stored procedure editor/debugger? We are developing a test program with 5.x.x and procedures to see if it'll work for us stuck trying to get variables sorted out, and without a proper debugger it's extremely hard thanks in advance Nobody using stored procedures yet? Ours are hundreds of lines long, debugging on the command line really really sucks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure for advance reporting
From 5.0.1 you can write database-specific stored procs but the language is SQL not Perl, see http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html. PB sam wrote: Hi, Can anyone tell me where I can download examples for creating Stored Procedure in perl DBI? It seems that I can't create advance reporting with basic operations (select, create) in perl dbi, so I m looking for whether Stored Proc in MySQL can solve my problem. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Rhino wrote:
Well, I think you've just succeeded in demolishing my wonderful example; it
turns out that we don't need to use a stored procedure to find a median
after all ;-)
That wasn't my intention. I thought I was adding support to your example.
Just because it can be done in SQL doesn't necessarily mean it should be.
You're right that the algorithm I described in my original reply is a bit
simplified and assumes an odd number of rows; it doesn't handle the case
where the number of rows is even. I assume that was just for the convenience
of the person who wrote the course materials I was teaching; they didn't
want to get bogged down in the subtleties of the details of calculating a
median.
I was specifically responding to Wolfram's suggested solution select ...
limit count/2, 1, rather than your description which didn't really include
the algorithm, but this leads directly to Shawn's point about the SP version
helping the end user by shielding him/her from the details. This is at
least the third time the subject of medians has been discussed on this list
in recent memory. In each case, most proposed solutions were incorrect,
because they were based on the flawed assumption that there is always a
middle value, an assumption which is false roughly half the time (n even).
If you write a correct solution, you not only make the end-user's life
easier, you protect him/her from getting the wrong answer.
I have to admit I've never seen an SQL query that would compute a median
before. I'm not sure I completely understand your query, particularly the
GROUP BY and HAVING clauses - I know what GROUP BY and HAVING do in general,
I'm just not sure what they are accomplishing in *this* case - but you're a
mathematician so I'll assume that the query is accurate and will work for
both odd and even numbered sets of rows ;-)
I came up with this solution almost exactly a year ago in another median
thread http://lists.mysql.com/mysql/155528. I got the idea from an
*incorrect* solution in O'Reilly's Transact-SQL Cookbook
http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html.
The median is often described as the middle value, but that is a slight
simplification. The median, which I'll call m, has the following properties:
* At least 50% of the values are = m
* At least 50% of the values are = m
The at least part makes sense when you consider the possibility of
repeated values in the middle.
I'll repeat the simpler of the two solutions (median of all values in one
column) and explain it:
CREATE TEMPORARY TABLE medians
SELECT x.val medians
FROM data x, data y
GROUP BY x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2;
SELECT AVG(medians) AS median FROM medians;
DROP TABLE medians;
We join the table to itself, with *no* join condition (Cartesian product).
For each value on the left (GROUP BY x.val), we get a row for every value on
the right (y.val). We count how many of the rows have values on the right
which are less than or equal to x.val {SUM(y.val = x.val)}, and how many
have values which are greater than or equal to x.val {SUM(y.val = x.val)}.
We only accept rows (HAVING) where both counts are at least 50% of the
total rows. In the odd rows case, this can only be satisfied by the value
in the middle, in the even case, this can only be satisfied by the two
values on either side of the middle. In both cases, the average of the
result(s) is the median.
This works, but it is hardly efficient. We create a Cartesian product in
order to get 1 or 2 rows. The programmatic solution is surely more
efficient (pseudocode):
#get the number of rows, N
SELECT COUNT(*) FROM data; # fast with MyISAM, slow with InnoDB
If N is odd
{ # median is the middle value
# middle position is (N+1)/2, starting with row 1,
# but LIMIT starts with row 0, so use (N-1)/2
offset = (N-1)/2
SELECT val FROM data ORDER BY val LIMIT offset, 1;
return val as median
}
Else #N is even
{ # median is average of middle 2 values
# middle 2 positions are N/2 and N/2 + 1, starting from row 1,
# but LIMIT starts with row 0, so use N/2 - 1 and N/2
offset = (N/2) - 1
SELECT val FROM data ORDER BY val LIMIT offset, 2;
return (val from row 1 + val from row 2)/2 as median
}
but it cannot be done in SQL (no flow control, can't use user variables in
LIMIT). That leaves client side or SP, I think.
It looks like I'll have to come up with a more bulletproof example of a
stored procedure before I next teach the concepts.
You'll have to be the judge, but it still seems a good example to me.
Median is a relatively simple concept, yet most people get the formula wrong
(even in a published book!), so it is perhaps best not left to the client.
The straightforward, efficient solution requires flow control, so a stored
procedure seems appropriate.
Rhino
Michael
--
MySQL General Mailing List
For list archives:
Re: Stored Procedure?
[...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. Mike Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Mike Wolfram Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Heyho! [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? That would be the optimal solution for MySQL 5.x ;-) The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. My original posting was a little bit short, sorry for that! I know what SPs are, I only wanted to point out that you don't need SPs to get the median without heavy calculations on the client. The definition of user levels/roles is another story. Btw: Rhino was missing/hiding the part with hiding complexity from users in his excellent explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Point taken, nice example ;-) I am not really an DBA, I am more like a db-user (not in your way of definition) ;-) As I said above: definition of user levels/roles are a complete different thing. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure?
I think a much better example of store procedures is one showing actual
procedural processing. Whilst they also allow a lot less communication
over the network, and a lot less SQL knowledge for the user(!) - a
stored procedure is also a way of doing all the work that you currently
get the client to do (thinking things like PHP with IF, WHILE or LOOP
statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql -
then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error
( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message along
with the substitued username to be used as the message body. It will
then generate an application error to the user with another procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
Best regards
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Wolfram Kraus
Sent: 30 November 2004 12:58
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
Michael J. Pawlowsky wrote:
Because you will be downloading 500,000 rows... And I don't really
think that was the point. Who cares what the example is. Personally I
was quite impressed with great explanation.
I second that it was a great explanation! Please don't get me wrong! And
furthermore I will only download one row two times: select count - one
row select ... limit count/2, 1 - one row
I would have simply said a chunk of code that runs on the server that
the client can call. And this guy took the time to put together a
really good reply with an example.
I don't say that the example is bad, I only said that in MySQL you can
do this without a SP.
Mike
Wolfram
Wolfram Kraus wrote:
[...]
Suppose you have a table with a million records, test scores from a
widely taken exam for example. You need to find the median mark
- NOT the average! - so your algorithm needs to read all million
records, sort them into ascending or descending sequence by the test
score, then read exactly half way through the sequenced list to find
the middle record; that's the one that contains the median score.
If that work were to happen on the client, the client would have to
fetch a million records, sort them all, then read through half of
those records until it found the middle record; then it would report
on the median mark. There would clearly be a lot of network traffic
involved in getting all those records to the client, sorting them
and then reading through the sorted records.
[...] Pardon my ignorance, but why can't you do this (in MySQL) with
a select count ... and afterwards a select ... order by... LIMIT?
All the work is done on the server, too. No need for a SP here.
Wolfram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
- Original Message - From: Wolfram Kraus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 10:04 AM Subject: Re: Stored Procedure? Heyho! [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? That would be the optimal solution for MySQL 5.x ;-) The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. My original posting was a little bit short, sorry for that! I know what SPs are, I only wanted to point out that you don't need SPs to get the median without heavy calculations on the client. The definition of user levels/roles is another story. Btw: Rhino was missing/hiding the part with hiding complexity from users in his excellent explanation. You're absolutely right; I failed to mention the benefits of making the users lives easier by letting the administrators do the heavy lifting via stored procedures. In truth, I simply didn't think of that benefit at the time (it was late and I was overdue for bedtime ;-) but I probably would have omitted it any way simply because the original question didn't make me think of those issues. I was mostly just focusing on what a stored procedure was since that is what the questioner seemed to want. Shawn was absolutely right to add the benefits of hiding the complexity from users. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Do you mean that you could run the same code that was in the stored procedure from the command line, therefore eliminating the need for an SP? Well, yes, that is true but how would you get the result to a client program? Or would you force users to sign on to the server to execute the code from the server's command line? Assuming stored procedures are implemented similarily in MySQL to the way they are in DB2, a stored procedure would work from both the server's command line *and* a client program. That means you simply build your stored procedure once and can handle both scenarios. If you want to invoke it from a client program, you simply call it, passing the necessary parameters and then handle the result within the client program. If you prefer to execute it right at the server, you can do that too with the same call statement you used from the client program, except that you hard code the values; then, the operating system displays the result of the stored procedure on the console. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Point taken, nice example ;-) I am not really an DBA, I am more like a db-user (not in your way of definition) ;-) As I said above: definition of user levels/roles are a complete different thing. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Stored Procedure?
- Original Message -
From: Mark Leith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 10:47 AM
Subject: RE: Stored Procedure?
I think a much better example of store procedures is one showing actual
procedural processing. Whilst they also allow a lot less communication
over the network, and a lot less SQL knowledge for the user(!) - a
stored procedure is also a way of doing all the work that you currently
get the client to do (thinking things like PHP with IF, WHILE or LOOP
statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql -
then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error
( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message along
with the substitued username to be used as the message body. It will
then generate an application error to the user with another procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
No offense taken, Mark. You've simply given a more advanced example that
illustrates even more capabilities of a stored procedure. It's a great
supplemental example.
I was simply citing the 'classic' example that I've taught in DB2 courses.
Those courses were for people who were new to stored procedures (and many
other aspects of DB2) and needed to know the basic concepts before trying to
write one. That seemed to be the level of the person who asked the original
question. However, your example illustrates how much more a stored procedure
can do so it should certainly be strongly considered by all developers,
particularly advanced ones.
I just wish that MySQL was a year or two further along than it is so that
5.x would be fairly mature and we could actually start coding stored
procedures, views, etc
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure?
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Oh indeed, neither can I! Not just procedures and views either, but also
triggers and sequences! And a job scheduling system would be fantastic
as well!!
Mark dreaming away the day
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: 30 November 2004 16:04
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
- Original Message -
From: Mark Leith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 10:47 AM
Subject: RE: Stored Procedure?
I think a much better example of store procedures is one showing
actual procedural processing. Whilst they also allow a lot less
communication over the network, and a lot less SQL knowledge for the
user(!) - a stored procedure is also a way of doing all the work
that you currently get the client to do (thinking things like PHP
with IF, WHILE or LOOP statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql
- then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error ( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message
along with the substitued username to be used as the message body. It
will then generate an application error to the user with another
procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
No offense taken, Mark. You've simply given a more advanced example that
illustrates even more capabilities of a stored procedure. It's a great
supplemental example.
I was simply citing the 'classic' example that I've taught in DB2
courses. Those courses were for people who were new to stored procedures
(and many other aspects of DB2) and needed to know the basic concepts
before trying to write one. That seemed to be the level of the person
who asked the original question. However, your example illustrates how
much more a stored procedure can do so it should certainly be strongly
considered by all developers, particularly advanced ones.
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Rhino
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
[EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. As a mathematician, I'd like to point out that medians aren't quite that simple. select ... limit count/2, 1 will not work at least half the time. There are two possibilities: * count is odd - The median is the value in the middle, but count/2 is a decimal, so you have something like LIMIT 13.5, 1. Mysql (4.1.7, anyway) handles this by ignoring the decimal and gives the correct answer, but this is problematic. The manual http://dev.mysql.com/doc/mysql/en/SELECT.html clearly states LIMIT takes one or two numeric arguments, which must be integer constants. Hence we are relying on an undocumented feature which could easily disappear. * count is even - In this case, there is no middle value! The median is the average of the 2 values on either side of the middle. count/2 is a positive integer, however, so limit count/2, 1 will retrieve a row, but it is *not* the median. Hence, network traffic is not an issue, but there is still work to be done. You have to get the count, check if it is even or odd, then proceed accordingly. In the even case, you have to retrieve two rows, then average them. You can do all this in code on the client end, or do it on the server in a stored procedure, making the client's life easier (and improving his/her chances of getting it right). For completeness, here's a method to get the median in SQL: To get the median of the values in a column (val): CREATE TEMPORARY TABLE medians SELECT x.val medians FROM data x, data y GROUP BY x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT AVG(medians) AS median FROM medians; DROP TABLE medians; To get the groupwise median of the values in a column (val) for each value in another column (name): CREATE TEMPORARY TABLE medians SELECT x.name, x.val medians FROM data x, data y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT name, AVG(medians) AS median FROM medians GROUP BY name; DROP TABLE medians; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Well, I think you've just succeeded in demolishing my wonderful example; it turns out that we don't need to use a stored procedure to find a median after all ;-) You're right that the algorithm I described in my original reply is a bit simplified and assumes an odd number of rows; it doesn't handle the case where the number of rows is even. I assume that was just for the convenience of the person who wrote the course materials I was teaching; they didn't want to get bogged down in the subtleties of the details of calculating a median. I have to admit I've never seen an SQL query that would compute a median before. I'm not sure I completely understand your query, particularly the GROUP BY and HAVING clauses - I know what GROUP BY and HAVING do in general, I'm just not sure what they are accomplishing in *this* case - but you're a mathematician so I'll assume that the query is accurate and will work for both odd and even numbered sets of rows ;-) It looks like I'll have to come up with a more bulletproof example of a stored procedure before I next teach the concepts. Rhino - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Wolfram Kraus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; news [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 11:49 AM Subject: Re: Stored Procedure? [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. As a mathematician, I'd like to point out that medians aren't quite that simple. select ... limit count/2, 1 will not work at least half the time. There are two possibilities: * count is odd - The median is the value in the middle, but count/2 is a decimal, so you have something like LIMIT 13.5, 1. Mysql (4.1.7, anyway) handles this by ignoring the decimal and gives the correct answer, but this is problematic. The manual http://dev.mysql.com/doc/mysql/en/SELECT.html clearly states LIMIT takes one or two numeric arguments, which must be integer constants. Hence we are relying on an undocumented feature which could easily disappear. * count is even - In this case, there is no middle value! The median is the average of the 2 values on either side of the middle. count/2 is a positive integer, however, so limit count/2, 1 will retrieve a row, but it is *not* the median. Hence, network traffic is not an issue, but there is still work to be done. You have to get the count, check if it is even or odd, then proceed accordingly. In the even case, you have to retrieve two rows, then average them. You can do all this in code on the client end, or do it on the server in a stored procedure, making the client's life easier (and improving his/her chances of getting it right). For completeness, here's a method to get the median in SQL: To get the median of the values in a column (val): CREATE TEMPORARY TABLE medians SELECT x.val medians FROM data x, data y GROUP BY x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT AVG(medians) AS median FROM medians; DROP TABLE medians; To get the groupwise median of the values in a column (val) for each value in another column (name): CREATE TEMPORARY TABLE medians SELECT x.name, x.val medians FROM data x, data y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val = x.val) = COUNT(*)/2 AND SUM(y.val = x.val) = COUNT(*)/2; SELECT name, AVG(medians) AS median FROM medians GROUP BY name; DROP TABLE medians; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: Stored Procedure?
- Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, November 29, 2004 10:55 PM Subject: Stored Procedure? Ok, I'm new to MySql (or SQL in general). I'm curious what exactly a stored procedure in MySql is, and what the benefit would be? Stored procedures are new in MySQL 5.0 but very few people are running 5.0 yet. I think 5.0 is available in a pre-beta but I haven't heard anything about how stable it is. In other words, you may have to wait a while to use stored procedures unless you are really eager to be 'bleeding edge'. Stored procedures are very popular on databases that already have them, like DB2. Their main advantage is when they move a lot of the processing of a given task to the server from the client. The classic example goes something like this: Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. Let's say you moved the majority of the work to the server and simply invoked the program that did the work from the client. The client simply tells the program (which we call a stored procedure) at the server to read all the rows, sort them, read the first half of them and report on the median mark. In this scenario, the network traffic drops to almost nothing: there is the instruction that invokes the stored procedure and then the median mark returning from the stored procedure. Everything else takes place within the stored procedure ON THE SERVER. The server is often a particularly powerful computer with extra fast devices, more memory, etc. so it is able to do the work faster than the client would in many cases, even if network traffic wasn't an issue. The net result is that the same work gets done with far less network traffic. So, a stored procedure is, in essence, simply a program that runs on a server without a network between it and the database. The stored procedure is invoked by a client program and returns a result to the client. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure Limitation
Thanks Daniel. Finally, I am making use of the C API and the flag 'CLIENT_MULTI_RESULTS' as suggested. It's working !! :)) -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 5:07 AM To: Nawal Lodha; [EMAIL PROTECTED] Subject: Re: Stored Procedure Limitation Nawal Lodha wrote: Dear All, On executing a Stored Procedure (in MySQL 5.0.0-alpha) containing multiple Select statements, I get the error SELECT in a stored procedure must have INTO. I found that this is a MyODBC bug. http://bugs.mysql.com/bug.php?id=2273 and http://bugs.mysql.com/bug.php?id=2658 Mark Matthews has suggested to use the client flag 'CLIENT_MULTI_RESULTS' until we get the 'MyODBC 3.53' Release. Can someone help me in using it? I wish to Call the Stored Procedure from my C++ as well as VB code through ADO. Any input would be of immense help. Thanks, Nawal Lodha. Thanks for noticing my bug :) My interpretation of the response given to both bugs is that the current driver ( MyODBC-3.51.x ) will not work AT ALL with MySQL stored procedures that return a result set. The 'CLIENT_MULTI_RESULTS' is only available in client libraries that are compiled against MySQL-4.1 or newer, and MyODBC-3.51.x is not. According to the comments at the bottom of my bug: MyODBC 3.53 is near completion but no date has been set yet. ie you will be waiting for quite some time. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
Re: Stored Procedure Limitation
Nawal Lodha wrote: Dear All, On executing a Stored Procedure (in MySQL 5.0.0-alpha) containing multiple Select statements, I get the error "SELECT in a stored procedure must have INTO". I found that this is a MyODBC bug. http://bugs.mysql.com/bug.php?id=2273 and http://bugs.mysql.com/bug.php?id=2658 Mark Matthews has suggested to use the client flag 'CLIENT_MULTI_RESULTS' until we get the 'MyODBC 3.53' Release. Can someone help me in using it? I wish to Call the Stored Procedure from my C++ as well as VB code through ADO. Any input would be of immense help. Thanks, Nawal Lodha. Thanks for noticing my bug :) My interpretation of the response given to both bugs is that the current driver ( MyODBC-3.51.x ) will not work AT ALL with MySQL stored procedures that return a result set. The 'CLIENT_MULTI_RESULTS' is only available in client libraries that are compiled against MySQL-4.1 or newer, and MyODBC-3.51.x is not. According to the comments at the bottom of my bug: MyODBC 3.53 is near completion but no date has been set yet. ie you will be waiting for quite some time. -- sig Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure in mysql 5.0 failure!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NanFei Wang wrote:
Hi,
somebody can help me!
my database is: mysql-5.0.0a-alpha
the connection driver is:mysql-connector-java-3.1.1-alpha-bin.jar
I want to test stored procedure of the new feature in MySQL version
5.0. A
I can call the stored procedure procPara in Window Console as next show,
but when I run in procTest.java,I get error Messages.
The error is at the line:
CallableStatement cs=conn.prepareCall({call procPara(?)});
between checkpoint 1 and checkpoint 2
your answer is highly appreciated!
from: NanFei
-
mysql delimiter //
mysql
mysql CREATE PROCEDURE procPara(IN name varchar(16))
- BEGIN
-SELECT note FROM kmdoc where username=name;
- END
- //
Query OK, 0 rows affected (0.22 sec)
mysql call procPara(John)//
+--+
| note |
+--+
| mysql Manul |
| Office2000 |
| PDF|
| PowerPoint Animation Runtime |
| Office2003 |
| Test Title|
| Say Hello |
+-+
7 rows in set (1.16 sec)
Query OK, 0 rows affected (1.67 sec)
mysql
-
--
procTest.java as following:
package km;
import java.sql.*;
public class procTest{
public static void main(String[] args)throws Exception {
String driverConnection=jdbc:mysql://localhost/;
String catalog=mycatloge;
String user=myname;
String psw=mypsw;
String connDbUserPsw=driverConnection+catalog+
? user=+user+password=+psw;
try {
Class.forName(com.mysql.jdbc.Driver);
}
catch (ClassNotFoundException e) {}
Connection conn = DriverManager.getConnection(connDbUserPsw);
System.out.println(checkpoint 1);
CallableStatement cs=conn.prepareCall({call procPara(?)});
System.out.println(checkpoint 2);
cs.setString(1,john);
java.sql.ResultSet rst=cs.executeQuery();
while(rst.next()){
String s=rst.getString(1);
System.out.println(s);
}
}
}
Messages:
checkpoint 1
java.lang.StringIndexOutOfBoundsException: String index out of range: -9
at java.lang.String.substring(String.java:1480)
at
com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.init(DatabaseMetaData.java:7031)
at
com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:6615)
at
com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:2637)
at
com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:904)
at com.mysql.jdbc.CallableStatement.init(CallableStatement.java:72)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:999)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:978)
at km.procTest.main(procTest.java:17)
Exception in thread main
-
Use a nightly snapshot of Connector/J 3.1 from
http://downloads.mysql.com/snapshots.php
-Mark
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAdBy8tvXNTca6JD8RAlioAJ0V49MIcbWpMuG1sjQnbGHp1Y7yoQCghoFn
HZn4vmYgdTFxMnhNliW9bkM=
=wBMx
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedure strange behavior?
Philip Markwalder [EMAIL PROTECTED] wrote: I have a few questions concerning stored procedures: 1. If I create a stored procedure (like the one below), why does the returned values not change, though in the stored prcoedure the id has been generated? 2. Is there any better way to hand over multiple values and how can I unset global varaibles? thx Philip delimiter | drop procedure if exists create_obj | CREATE PROCEDURE `create_obj` ( out success int(2), out success_msg varchar(255), out obj_id int(10), inout obj_hostname varchar(255), inout obj_type varchar(25) ) LANGUAGE SQL not deterministic begin declare done int default 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; insert into idsdb.obj values (NULL,obj_hostname,obj_type); if ! done then select LAST_INSERT_ID() into obj_id; set success = 1; set success_msg = concat(added host with object id: , obj_id); else set success = -1; set success_msg=Could not insert new object; end if; end | call create_obj(@a,@b,@id,'test1','ddd')| select @a,@b,@id | LAST_INSERT_ID() returns wrong result inside stored procedure. I entered simple test case to the bug database: http://bugs.mysql.com/bug.php?id=3117 Thanks! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: stored procedure support
And I hope triggers as a collegue and I have to submit a evaluation report on why our department should consider Mysql to be used in conjuction with Postgrsql which is our default RDBMS in our organisation. So I have a few link's regarding performaces etc as well as the Mysql V PostGresql Any other Plz post Victoria Reznichenko wrote: Tonino, Friday, October 11, 2002, 10:05:42 AM, you wrote: T Just a short question - Does anyone know the progress of stored T procedure support in MySQL 4 Stored procedures will come in v5.0 -- Regards Chuck Robert Amadi ICT Dept Systems Programmer. Rhaglenydd Systemau Adran ICT. Roll on Linux Power too deliver. Rholiwch ar Linux Y gallu i y Dosbarthu. Before you criticize someone, you should walk a mile in their shoes. That way, when you criticize them, you're a mile away, and you have their shoes. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: stored procedure support
Tonino, Friday, October 11, 2002, 10:05:42 AM, you wrote: T Just a short question - Does anyone know the progress of stored T procedure support in MySQL 4 Stored procedures will come in v5.0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stored Procedure
So sprach »Cafetechno« am 2001-12-26 um 10:34:59 +0700 : If mysql will include the stored procedure feature, what kind of langguage to be used for the stored procedure ? If you use myperl, it will be perl. Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.iso-top.de | Jabber: [EMAIL PROTECTED] iso-top.de - Die günstige Art an Linux Distributionen zu kommen Uptime: 9 days 20 hours 11 minutes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stored Procedure
Explain how to configure the handlers for myperl under the win32 platform... Alexander Skwar wrote: So sprach »Cafetechno« am 2001-12-26 um 10:34:59 +0700 : If mysql will include the stored procedure feature, what kind of langguage to be used for the stored procedure ? If you use myperl, it will be perl. Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.iso-top.de | Jabber: [EMAIL PROTECTED] iso-top.de - Die günstige Art an Linux Distributionen zu kommen Uptime: 9 days 20 hours 11 minutes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stored Procedure
[ obfilter: mysql database ] Sinisa Milivojevic wrote: Cafetechno writes: When The Stored Procedure Capability will be included in mySQL Take a look at myperl on http://freshmeat.net Interesting start, of course. What would be nice is to support a proper create or replace procedure/function type syntax with a body in Perl or Java. Java functions would either need to use JDBC to perform queries and access results (painful), or need a preprocessor like SQLJ to convert high-level SQL syntax to JDBC statements. Perl would need a module like Perl::DBI. But this is definitely a major undertaking, worthy of a project of its own.. -- Shankar. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stored Procedure
- Original Message - From: Shankar Unni [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 27, 2001 12:57 AM Subject: Re: Stored Procedure [ obfilter: mysql database ] Sinisa Milivojevic wrote: Cafetechno writes: When The Stored Procedure Capability will be included in mySQL Take a look at myperl on http://freshmeat.net Interesting start, of course. What would be nice is to support a proper create or replace procedure/function type syntax with a body in Perl or Java. Java functions would either need to use JDBC to perform queries and access results (painful), or need a preprocessor like SQLJ to convert high-level SQL syntax to JDBC statements. Perl would need a module like Perl::DBI. But this is definitely a major undertaking, worthy of a project of its own.. -- Shankar. worthy project of its own ? So, mysql will not implement a stored procedure as built-in object in near future. For my experience, the stored procedure was so powerfull rather than creating some query in client side and more easily to be maintain in server side. I heard in version 4.01 the stored procedure will be implemented too ? is this correct or not ? regards cafe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
