Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello Mogens,
On 8/18/2018 2:32 PM, Mogens Melander wrote:
Guys,
I think I remember this from way back.
You could ask for a lock, and get an OK if it is safe.
Something like, if there is pending transactions, on your target tables,
you would get a NO.
But then again. I could be wrong, and Shawn is the authority on this.
Your request for a lock would have waited until all existing readers or
writers (depending on the type of lock you asked for) had finished using
the tables you wanted to lock. By extension, that means that any
transactions active against the tables you wanted to lock would have
also needed to have committed or rolled back before your request would
have been granted. Any new actions against the table would have been
queued up behind your LOCK request. This has confused more than one DBA
as they didn't realize that the LOCK was going to be such a tight
bottleneck.
These kinds of whole table locks live above the blocking/locking
coordination of the individual storage engines or the transaction
control code. They are managed in the "server layer" of our code.
This separation of scope is one reason why blending transactional and
non-transactional tables in the same data management process is
generally frowned on. Either be all-transactional (InnoDB) or not. The
behavior will be easier to predict allowing your developers to use
either the transaction control commands (BEGIN/COMMIT/ROLLBACK/... ) or
the LOCK commands with confidence.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
=== original thread ===
On 2018-08-18 23:59, shawn l.green wrote:
Hello Jeff,
On 8/13/2018 12:05 PM, j...@lxvi.net wrote:
Hello, I have read through several pages of the reference manual, and
I've seen several instances where it is stated that LOCK TABLES (and
UNLOCK TABLES) is not allowed in a stored procedure, but so far, I
haven't found an explanation as to *why* that is. Could someone please
enlighten me?
Thanks
Normally, the list is more responsive than this. This is a pretty easy
question and someone usually handles those before I need to step in as
a backstop.
The key why you cannot execute a LOCK TABLE command within a stored
program is here:
https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
###
LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.
###
Stored programs execute under the scope of the transaction in which
they are started. That determines which sets of rows are "visible" to
the routine and sets boundaries on what may be committed or rolled
back should the need arise.
(a simple example)
* your session: START TRANSACTION
* your session: ...other data activity ...
* your session (INSERT ... )
* causes an INSERT trigger to fire
* which calls a stored procedure
If that stored procedure or that trigger called a LOCK TABLE command,
it would forcibly COMMIT the existing transaction you had been working
within until that moment. Your half-completed work would have become
fully committed even if a later step had needed you to issue a
ROLLBACK command.
Note, even if you are not in a multi-statement transaction that any
stored programs called by or executed within the scope of your user
command are part of that little mini (auto-committed) transaction.
Does that help?
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Guys, I think I remember this from way back. You could ask for a lock, and get an OK if it is safe. Something like, if there is pending transactions, on your target tables, you would get a NO. But then again. I could be wrong, and Shawn is the authority on this. On 2018-08-18 23:59, shawn l.green wrote: Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net wrote: Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop. The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html ### LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. ### Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise. (a simple example) * your session: START TRANSACTION * your session: ...other data activity ... * your session (INSERT ... ) * causes an INSERT trigger to fire * which calls a stored procedure If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command. Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction. Does that help? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to *why* that is. Could someone please enlighten me? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] ODB C++ ORM 2.4.0 released, adds MySQL stored procedure support
I am pleased to announce the release of ODB 2.4.0. ODB is an open source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. Major new features in this release: * Support for bulk operations in Oracle and SQL Server. Bulk operations can be used to persist, update, or erase a range of objects using a single database statement execution which often translates to a significantly better performance. * Ability to join and load one or more complete objects instead of, or in addition to, a subset of their data members with a single SELECT statement execution (object loading views). * Support for specifying object and table join types in views (LEFT, RIGHT, FULL, INNER, or CROSS). * Support for calling MySQL and SQL Server stored procedures. * Support for defining persistent objects as instantiations of C++ class templates. A more detailed discussion of these features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2015/02/11/odb-2-4-0-released/ For the complete list of new features in this version see the official release announcement: http://codesynthesis.com/pipermail/odb-announcements/2015/41.html ODB is written in portable C++ (both C++98/03 and C++11 are supported) and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64/ARM), Windows (x86/x86-64), Mac OS X (x86/x86_64), and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-5.x, MS Visual C++ 2005, 2008, 2010, 2012, and 2013, Sun Studio 12u2, and Clang 3.x. The currently supported database systems are MySQL, SQLite, PostgreSQL, Oracle, and SQL Server. ODB also provides optional profiles for Boost and Qt, which allow you to seamlessly use value types, containers, and smart pointers from these libraries in your persistent classes. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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
Stored procedure debuggers
Does anyone know of any debuggers for stored procs that run on Mac and/or Linux? -- 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
Stored Procedure help
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
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: Get Affected Rows after Stored Procedure COMMIT
Fetch rows_affected after each INSERT/UPDATE. Tally them in @variables, if you like. The information is not (I think) available after COMMIT. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, July 02, 2013 4:30 AM To: [MySQL] Subject: Get Affected Rows after Stored Procedure COMMIT Hi, I have a number of INSERT and UPDATE statements in a MySQL Stored Procedure, that works in the form of START TRANSACTION followed by COMMIT. Also I am handling any EXCEPTION. However, after calling COMMIT, how can I get the number of Rows that were affected either INSERTED or UPDATTED ? Thanks, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Get Affected Rows after Stored Procedure COMMIT
Hi, I have a number of INSERT and UPDATE statements in a MySQL Stored Procedure, that works in the form of START TRANSACTION followed by COMMIT. Also I am handling any EXCEPTION. However, after calling COMMIT, how can I get the number of Rows that were affected either INSERTED or UPDATTED ? Thanks, Neil
Re: Get Affected Rows after Stored Procedure COMMIT
2013/07/02 12:29 +0100, Neil Tompkins I have a number of INSERT and UPDATE statements in a MySQL Stored Procedure, that works in the form of START TRANSACTION followed by COMMIT. Also I am handling any EXCEPTION. However, after calling COMMIT, how can I get the number of Rows that were affected either INSERTED or UPDATTED ? Can you use function ROW_COUNT to any effect? If you can, probably you have to add its yields up in your own code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to return resultset from MySQL Stored Procedure using prepared statement?
What language are you using?
In Perl, there is
$sth-more_results;
-Original Message-
From: Girish Talluru [mailto:girish.dev1...@gmail.com]
Sent: Wednesday, March 13, 2013 5:24 AM
To: mysql@lists.mysql.com
Subject: How to return resultset from MySQL Stored Procedure using
prepared statement?
DELIMITER $$
CREATE PROCEDURE List_IL()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Temp_List_val VARCHAR(255);
DECLARE Project_List_val VARCHAR(255);
DECLARE FoundCount INT;
DECLARE Project_Number INT;
DECLARE db_Name VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE projects_curCURSOR FOR
SELECT Project_Id
FROMProject_Details;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN projects_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH projects_cur
INTO Project_Number_val;
IF no_more_rows THEN
CLOSE projects_cur;
LEAVE the_loop;
END IF;
SET Project_List_val = CONCAT(Project_Number_val, '_List');SET
db_Name='panel';
SELECT COUNT(1) INTO FoundCount FROM information_schema.tables WHERE
table_schema = `db_Name` AND table_name = `Project_List_val`;
IF FoundCount = 1 THENSET @Project_Number=Project_Number_val;
SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
FROM ', @Project_List_val,' Where status=1');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END IF;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
END $$
*
**In the above stored procedure How can I get the all the rows selected
during execution of prepared statement and after the loop terminates I
want to return the entire result set whichever calls the stored
procedure. Can you please help me how to do this?*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Please check the stored procedure
To adjust a table name within a SQL statement, you need to create a
string with the updated values and use PREPARE/EXECUTE
See below, I am replacing your CONCAT with the complete insert statement
The example below is also assuming the value in the WHERE clause should
be adjusted to the number, instead of hard-coded to '9'
set @str = concat('Insert Into test (Panel_Id) select Panel_Id
from ',Project_Number_val,'_List where Project_Number_val='
,',Project_Number_val,');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
-Original Message-
From: Girish Talluru [mailto:girish.dev1...@gmail.com]
Sent: Tuesday, February 12, 2013 1:37 AM
To: mysql@lists.mysql.com
Subject: Please check the stored procedure
DROP PROCEDURE IF EXISTS Cursor_Test;# MySQL returned an empty result
set
(i.e. zero rows).
DELIMITER $$
CREATE PROCEDURE Cursor_Test()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Project_List_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE projects_cur CURSOR FOR
SELECT Project_Id
FROM Project_Details;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN projects_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH projects_cur
INTO Project_Number_val;
IF no_more_rows THEN
CLOSE projects_cur;
LEAVE the_loop;
END IF;
SET Project_List_val=CONCAT(`Project_Number_val`,'_List') Please
check am I doing CONCAT correct here?
Insert Into test (Panel_Id) select Panel_Id from Project_List_val where
Project_Number_val='9'; ---Is this taking 9_List as table name?
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
select num_rows, loop_cntr;
END $$# MySQL returned an empty result set (i.e. zero rows).
DELIMITER
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Random Code Stored Procedure
Rather than trying to reinvent the wheel, I was wondering if anyone might have a stored procedure already for what I want to do. I want to start with 4 characters using any unique combo and when all unique matches are used, it will move to 5 characters and so on. For example in any random order: AAAB ... A ... Z AA ... ZZ The codes would be validated for a dupe against an existing column which this will seed called code. Suggestions? Thanks Steffan -- 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
ANN: Hopper (stored procedure debugger), version 1.0.3 released
ANN: Hopper, version 1.0.3 released Dear ladies and gentlemen, Upscene Productions is proud to announce version 1.0.3 of our product called Hopper. Hopper is a Windows-based Stored Routine and Trigger Debugger, available for InterBase, Firebird and MySQL. This version fixes an imporant error with the MySQL version, amongst other small issues. For more information, see http://www.upscene.com/displaynews.php?item=20120801 With regards, Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ANN: Hopper (stored procedure debugger), version 1.0.1 released
Hello Jan, others, I do find your juvenile comments about worshipping, changing product names etc just that. Ah, you must be from the marketing department -- always willing to make friends and influence people. Please, Jan, I'm obviously not, I'm but a simple programmer. And given the fact that you started out by implying I worshipped Bill Gates, I'll take it you aren't willing to make new friends either. Website has been modified, product announcement as well. Can we close the book on this now? 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: ANN: Hopper (stored procedure debugger), version 1.0.1 released
I do find your juvenile comments about worshipping, changing product names etc just that. Ah, you must be from the marketing department -- always willing to make friends and influence people. In examinations, the foolish ask questions that the wise cannot answer. -- Oscar Wilde Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ANN: Hopper (stored procedure debugger), version 1.0.1 released
Hello Jan, Can you PLEASE note in your listing when a product is Microsloth-only? While you're at it, can you PLEASE note it prominently on your website? I looked through your product description and saw no specific requirements beyond what databases were supported. It wasn't until I tried to download it that I noticed the warning sign. (.EXE in the file name) Hard as it is to believe, the entire world does not worship at the alter of Bill Gates. We don't allow any Microsloth products on our site. Although you have a point about the product announcement and lack of mentioning the required OS on our website, I do find your juvenile comments about worshipping, changing product names etc just that. I'll adjust the website and announcements. :) Have a nice weekend. 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: ANN: Hopper (stored procedure debugger), version 1.0.1 released
Can you PLEASE note in your listing when a product is Microsloth-only? While you're at it, can you PLEASE note it prominently on your website? I looked through your product description and saw no specific requirements beyond what databases were supported. It wasn't until I tried to download it that I noticed the warning sign. (.EXE in the file name) Hard as it is to believe, the entire world does not worship at the alter of Bill Gates. We don't allow any Microsloth products on our site. In a low-energy future... the wealth of nations will be measured by the quantity and quality of their forests. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ANN: Hopper (stored procedure debugger), version 1.0.1 released
ANN: Hopper, version 1.0.1 released Dear ladies and gentlemen, Upscene Productions is proud to announce version 1 of a new product called Hopper. Hopper is a Stored Routine and Trigger Debugger, available for InterBase, Firebird and MySQL. For more information, see http://www.upscene.com/displaynews.php?item=20120620 With regards, Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: why can not pass constant to stored procedure?
On 10 Mar, 2012, at 7:06 pm, Cifer Lee wrote: when we call procedure normally we declare the parameter out of the procedure and pass the variable to procedure like this set @x=1; call *a_procedure*(@x); why can not directly pass the digit 1 to the* a_procedure* ? You can pass the argument directly as long as it is not an INOUT parameter. Antony T Curtis atcur...@gmail.com 0523 C487 9187 6972 6894 AEC7 3087 F819 B477 B687
Stored Procedure Debugging?
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
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
Variables in stored procedure
I am getting the error that TABLENAME does not exist. How do I get it
to substitute the value stored in TABLENAME, and not the literal string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0', 'statpress_mincap',
'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Variables in stored procedure
On 10/4/2011 4:20 PM, Adam Gerson wrote:
I am getting the error that TABLENAME does not exist. How do I get
it to substitute the value stored in TABLENAME, and not the literal
string?
See the manual page for PREPARE.
PB
-
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0', 'statpress_mincap',
'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Variables in stored procedure
you can use:
set @sql = concat( INSERT INTO , TABLENAME, VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'));
prepare stm from @sql;
execute @sql;
2011/10/4 Adam Gerson agers...@cgps.org
I am getting the error that TABLENAME does not exist. How do I get it to
substitute the value stored in TABLENAME, and not the literal string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0', 'statpress_mincap',
'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?**
unsub=luizrodrigomottin@gmail.**comhttp://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.com
Re: Variables in stored procedure
Thanks Luiz,
That got me closer. I was able to save the stored proc. It should be
execute stm; not execute @sql; right?
I get this when I try to execute it:
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 ''309',
'0', 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_co' at line 1
begin
declare v_max int unsigned default 21;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
set @sql = concat( INSERT INTO , TABLENAME, VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_collectloggeduser', 'checked', 'yes'), ('307', '0',
'statpress_autodelete', '1 year', 'yes'), ('308', '0',
'statpress_daysinoverviewgraph', '31', 'yes'), ('310', '0',
'statpress_donotcollectspider', 'checked', 'yes'), ('311', '0',
'statpress_autodelete_spider', '1 day', 'yes'), ('312', '0',
'statpress_number_display_post_and_page', '20', 'yes'), ('313', '0',
'statpress_number_display_ip_spy_visitor', '20', 'yes'), ('314', '0',
'statpress_number-display_visit_spy_visitor', '20', 'yes'););
prepare stm from @sql;
execute stm;
set v_counter=v_counter+1;
end while;
commit;
END
--
Adam Gerson
Assistant Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
On 10/4/11 5:29 PM, luiz rodrigo mottin wrote:
you can use:
set @sql = concat( INSERT INTO , TABLENAME, VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'));
prepare stm from @sql;
execute @sql;
2011/10/4 Adam Gerson agers...@cgps.org mailto:agers...@cgps.org
I am getting the error that TABLENAME does not exist. How do I get
it to substitute the value stored in TABLENAME, and not the literal
string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 tel:212-749-6200 ex. 321
fax. 212-428-6806 tel:212-428-6806
ager...@cgps.org mailto:ager...@cgps.org
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?__unsub=luizrodrigomottin@gmail.__com
http://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.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: Variables in stored procedure
yes
2011/10/4 Paul Nickerson paul.nicker...@escapemg.com
You need a space before the word VALUES
--
*From: *Adam Gerson agers...@cgps.org
*To: *luiz rodrigo mottin luizrodrigomot...@gmail.com
*Cc: *mysql@lists.mysql.com
*Sent: *Tuesday, October 4, 2011 6:00:24 PM
*Subject: *Re: Variables in stored procedure
Thanks Luiz,
That got me closer. I was able to save the stored proc. It should be
execute stm; not execute @sql; right?
I get this when I try to execute it:
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 ''309',
'0', 'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_co' at line 1
begin
declare v_max int unsigned default 21;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
set @sql = concat( INSERT INTO , TABLENAME, VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'), ('306', '0',
'statpress_collectloggeduser', 'checked', 'yes'), ('307', '0',
'statpress_autodelete', '1 year', 'yes'), ('308', '0',
'statpress_daysinoverviewgraph', '31', 'yes'), ('310', '0',
'statpress_donotcollectspider', 'checked', 'yes'), ('311', '0',
'statpress_autodelete_spider', '1 day', 'yes'), ('312', '0',
'statpress_number_display_post_and_page', '20', 'yes'), ('313', '0',
'statpress_number_display_ip_spy_visitor', '20', 'yes'), ('314', '0',
'statpress_number-display_visit_spy_visitor', '20', 'yes'););
prepare stm from @sql;
execute stm;
set v_counter=v_counter+1;
end while;
commit;
END
--
Adam Gerson
Assistant Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax. 212-428-6806
ager...@cgps.org
http://www.cgps.org
On 10/4/11 5:29 PM, luiz rodrigo mottin wrote:
you can use:
set @sql = concat( INSERT INTO , TABLENAME, VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes'));
prepare stm from @sql;
execute @sql;
2011/10/4 Adam Gerson agers...@cgps.org mailto:agers...@cgps.org
I am getting the error that TABLENAME does not exist. How do I get
it to substitute the value stored in TABLENAME, and not the literal
string?
begin
declare v_max int unsigned default 1;
declare v_counter int unsigned default 21;
declare TABLENAME text;
start transaction;
while v_counter = v_max do
SET TABLENAME = CONCAT('wp_',v_counter, '_options');
INSERT INTO TABLENAME VALUES ('309', '0',
'statpress_mincap', 'edit_posts', 'yes');
set v_counter=v_counter+1;
end while;
commit;
END
Thanks,
Adam
--
Adam Gerson
Co-Director of Technology
Columbia Grammar and Prep School
phone. 212-749-6200 tel:212-749-6200 ex. 321
fax. 212-428-6806 tel:212-428-6806
ager...@cgps.org mailto:ager...@cgps.org
http://www.cgps.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?__unsub=luizrodrigomottin@gmail.__com
http://lists.mysql.com/mysql?unsub=luizrodrigomot...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=paul.nicker...@grooveshark.com
Stored Procedure Question
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=arch...@jab.org
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
stored procedure insert statement
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=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
Ranking a table within a stored procedure
Dear list, since this is the first time that I submit a question to this list, I hope that it is not to silly. My problem is as follows. I need to assing a ranking to the rows a large table. In general, I would do it as follows: SET @i = 0; UPDATE data_DgSt SET ii=@i:=@i + i ORDER BY datumtijd,laden_lossen Where table is the name of the table, ii is the column that I want to use for the ranking and datumtijd and laden_lossen are the two columns on which the ranking will depend. Now, I need the ranking in a procedure which transfers the data of this table to a series of tables in my database. My problem is that I am not able to use this simple code within the stored procedure. To overcome this, I iterate through the table following the order defined by the columns datumtijd and number. The code which I use to rank the table within the stored procedure is this: DECLARE iINT(20)DEFAULT 0; DECLARE dsidiINT(20); DECLARE klaar BOOLDEFAULT 0; DECLARE cur CURSOR FOR SELECT dsid FROM data_DgSt ORDER BY DATUMTIJD,laden_lossen; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET klaar = TRUE; OPEN cur; mijnloop: LOOP FETCH cur INTO dsidi; SET i = i + 1; UPDATE data_DgSt SET ii = i WHERE dsid = dsidi; IF klaar THEN CLOSE cur; LEAVE mijnloop; END IF; END LOOP; The problem is that this code is much slower than the initial code. In my data, the first code takes approximately 10 seconds while the code in the loop takes more than 3 minutes. Therefore, I would like to improve the speed of the code in the procedure. I would very much appreciate any help. Thanks in advance, Albart Coster -- Albart Coster Tel: (0031) 64 24 02 923 Fax: (0031) 84 75 98 558 Dairyconsult www.dairyconsult.nl -- 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 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
Stored procedure
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 --databases DB /backups/DB_backup.dump mysql -u root -ppassword -D DB -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
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: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/4/2011 23:23, James Dekker wrote: Peter, Thanks for the response! Unfortunately, that worked but a new error arose: check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1 Is there a better way to generate incremented sequence IDs? Can this be done in a stored function? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Happy programming, James On Jan 5, 2011, at 10:01 AM, Shawn Green (MySQL) wrote: On 1/4/2011 23:23, James Dekker wrote: Peter, Thanks for the response! Unfortunately, that worked but a new error arose: check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1 Is there a better way to generate incremented sequence IDs? Can this be done in a stored function? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/5/2011 13:31, James Dekker wrote: Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Maybe some variation of this will help? http://stackoverflow.com/questions/805808/emulating-a-transaction-safe-sequence-in-mysql I don't understand the need for a SEQUENCE. In my history, if there is some kind of object identifier you want to use, then an auto_increment field on the row that defines the object itself is sufficient. Then all child elements of that object can include the autogenerated ID value from their parent object as you create them alongside of any unique identifiers they may require. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id Have you also explored the use of auto_increment columns as part of a multiple-column index on MyISAM tables as described here? http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from a
sequence table and then increments and assigns it to its corresponding table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table, 'SET
ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table, ');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table ,'
set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored Functions
or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying to
concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Should be:
CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
PB
On 1/4/2011 9:28 PM, James Dekker wrote:
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from a
sequence table and then increments and assigns it to its corresponding table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table,
'SET ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table,
');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table ,'
set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored Functions
or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying to
concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Peter,
Thanks for the response!
Unfortunately, that worked but a new error arose:
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from
my_table_t)' at line 1
Is there a better way to generate incremented sequence IDs?
Can this be done in a stored function?
Happy programming,
James
On Jan 4, 2011, at 7:45 PM, Peter Brawley wrote:
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
Should be:
CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
PB
On 1/4/2011 9:28 PM, James Dekker wrote:
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from
a sequence table and then increments and assigns it to its corresponding
table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id
varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table,
'SET ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table,
');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table
,' set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored
Functions or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying
to concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=james.dek...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
stored procedure syntax error
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
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: Replication of MySQL Stored Procedure
SP generally goes as per the database you have created. Set you binlog off while creating for the sql. sql_log_bin is the variable to do it. On Tue, Jun 8, 2010 at 1:01 AM, Sabika Gmail sabika.makhd...@gmail.comwrote: I already have mysql in the replicate wild ingore table. I am running mysql 5.1.40sp1 Could it be a bug? On Jun 7, 2010, at 8:30 AM, Rolando Edwards redwa...@logicworks.net wrote: I think this is normal because stored procedures live in mysql.proc. You would have to filter out mysql.proc by adding this to /etc/my.cnf replicate-ignore-table=mysql.proc Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 07, 2010 11:14 AM To: mysql@lists.mysql.com Subject: Replication of MySQL Stored Procedure Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: Replication of MySQL Stored Procedure
I think even if you ignore the mysql database from replication and set Is_Deterministic = YES then your stored procedures will be replicated. Please set it to NO if you do not wish the stored procedures will not be replicated. You can set this in mysql.proc table. -- Regards, Manasi Save On Mon, 7 Jun 2010 12:31:13 -0700, Sabika Gmail wrote: I already have mysql in the replicate wild ingore table. I am running mysql 5.1.40sp1 Could it be a bug? On Jun 7, 2010, at 8:30 AM, Rolando Edwards redwa...@logicworks.net wrote: I think this is normal because stored procedures live in mysql.proc. You would have to filter out mysql.proc by adding this to /etc/my.cnf replicate-ignore-table=mysql.proc Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 07, 2010 11:14 AM To: mysql@lists.mysql.com Subject: Replication of MySQL Stored Procedure Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.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: Strange behavior by MySQL Stored Procedure
Does anyone have any sort of any idea on how to deal with this problem?
This is happening again and again and not all the time but randomly anytime.--Regards,Manasi Save
On Wed, 02 Jun 2010 06:46:56 -0400, Manasi Save
wrote:
Dear Venugopal,Here's theSample
Java Code Which Calls stored procedure :-//get the
connection to databaseConnection dbConnection = getConnection();//create the call for procedureString procedureCallStmtStr =
"Call XYZ()";//create callable statement objectCallableStatement cs = conn.prepareCall(procedureCallStmtStr);//execute the procedurecs.execute();//obtain
resultsetResultSet result = cs.getResultSet();//Iterate
to get the resultSet, if present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if
I am executing a stored procedure anywhere? Well, I am not aware of Java so
really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST),
Venugopal Rao wrote:
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the
Query/Calling the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: Friday, 28 May, 2010, 5:44 PM
Dear All,I have one stored procedure
Which inserts data into one table.But sometimes it does not
insert record. This happens when I called it from java application. But If I
called same query from mysql command line. It executes successfully.Also I have one procedure which only retrieves data from table.
and it only gives one row sometime even if there are 10 rows available in for
matching condition. This too happen when I called it from Java application and
if I called it from mysql command line it gives me proper result set of 10
rows.I am not able to understand Is it something known for
mysql? Or am I doing something wrong?Any input will be a great
help.--Thanks and Regards,Manasi Save
Replication of MySQL Stored Procedure
Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication of MySQL Stored Procedure
I think this is normal because stored procedures live in mysql.proc. You would have to filter out mysql.proc by adding this to /etc/my.cnf replicate-ignore-table=mysql.proc Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 07, 2010 11:14 AM To: mysql@lists.mysql.com Subject: Replication of MySQL Stored Procedure Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication of MySQL Stored Procedure
I already have mysql in the replicate wild ingore table. I am running mysql 5.1.40sp1 Could it be a bug? On Jun 7, 2010, at 8:30 AM, Rolando Edwards redwa...@logicworks.net wrote: I think this is normal because stored procedures live in mysql.proc. You would have to filter out mysql.proc by adding this to /etc/my.cnf replicate-ignore-table=mysql.proc Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 07, 2010 11:14 AM To: mysql@lists.mysql.com Subject: Replication of MySQL Stored Procedure Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
Dear Venugopal,Here's theSample Java Code Which Calls
stored procedure :-//get the connection to databaseConnection dbConnection = getConnection();//create the call
for procedureString procedureCallStmtStr = "Call XYZ()";//create callable statement objectCallableStatement cs =
conn.prepareCall(procedureCallStmtStr);//execute the
procedurecs.execute();//obtain resultsetResultSet
result = cs.getResultSet();//Iterate to get the resultSet, if
present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if I am executing a stored procedure
anywhere? Well, I am not aware of Java so really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST), Venugopal Rao wrote:
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the
Query/Calling the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: Friday, 28 May, 2010, 5:44 PM
Dear All,I have one stored procedure
Which inserts data into one table.But sometimes it does not
insert record. This happens when I called it from java application. But If I
called same query from mysql command line. It executes successfully.Also I have one procedure which only retrieves data from table.
and it only gives one row sometime even if there are 10 rows available in for
matching condition. This too happen when I called it from Java application and
if I called it from mysql command line it gives me proper result set of 10
rows.I am not able to understand Is it something known for
mysql? Or am I doing something wrong?Any input will be a great
help.--Thanks and Regards,Manasi Save
Re: Strange behavior by MySQL Stored Procedure
mysql Version :- 5.1.42-community-log
mysql Connector/J Version :- mysql-connector-java-5.1.6-bin.jar
Sample Java Code Which Calls stored procedure :-
//get the connection to database
Connection dbConnection = getConnection();
//create the call for procedure
String procedureCallStmtStr = Call XYZ();
//create callable statement object
CallableStatement cs = conn.prepareCall(procedureCallStmtStr);
//execute the procedure
cs.execute();
//obtain resultset
ResultSet result = cs.getResultSet();
//Iterate to get the resultSet, if present
//commit transaction
conn.commit();
//close resultset, callableStatement
result.close();
cs.close();
Stored procedure which is getting called :-
CREATE definer=`myus...@`localhost` PROCEDURE `AddCust`(InputUserID
BigInt, InputCustID BigInt, InputDBID BigInt, InputTimeStamp DateTime)
DETERMINISTIC
BEGIN
Declare DBName Varchar(45);
Select InputDBID into DBName;
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
UserID BigInt,
CustID BigInt,
MarkForDeletion Boolean
);
SET @stmt = Concat('Insert into Temp(UserID, CustID, MarkForDeletion)
Select FK_UserID, FK_CustID, MarkForDeletion
From `',DBName,'`.Tbl1
Where FK_UserID = ',InputUserID,'
and FK_CustID = ',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
IF Exists (Select CustID From Temp Where CustID = InputCustID)
Then
SET @stmt = Concat('Update `',DBName,'`.Tbl1
Set MarkForDeletion = 0,
TimeStamp =
','',InputTimeStamp,'','
Where FK_UserID =
',InputUserID,'
and FK_CustID =
',InputCustID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
ELSE
SET @stmt = Concat('Insert into ',
'`',DBName,'`.Tbl1 (FK_CustID, FK_UserID, MarkForDeletion, TimeStamp) ',
'Select ', '', InputCustID, '', ',',
'',InputUserID,'',', False',',','',InputTimeStamp,'',';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
Select InputUserID as RecordInserted;
END IF;
Thanks in advance.
--
Regards,
Manasi Save
On Fri, 28 May 2010 15:40:05 0200, Mattia Merzi wrote:
2010/5/28 Manasi Save manasi.s...@artificialmachines.com:
[...]
Or am I doing something wrong?
probably;
you better send us another e-mail writing at least:
- mysql version you are using
- mysql Connector/J version you are using
- piece of java code you are using to call the stored procedure
- source of the stored procedure (or part of it)
... probably, a subset of all of these infos will not be enough
to understand the problem.
In any case, if you have troubles using the mysql jdbc driver
but no problem using the mysql CLI and you suspect a
Connector/J bug, maybe you better write to the mysql java
support mailing list: http://lists.mysql.com/java
Greetings,
Mattia.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
Stored procedures are not executed like a query.
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the Query/Calling
the Procedure.
Regards,
VR Venugopal Rao
--- On Fri, 28/5/10, Manasi Save manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.com
Subject: Strange behavior by MySQL Stored Procedure
To: mysql@lists.mysql.com
Date: Friday, 28 May, 2010, 5:44 PM
Dear All,
I have one stored procedure Which inserts data into one table.
But sometimes it does not insert record. This happens when I called it from
java application. But If I called same query from mysql command line. It
executes successfully.
Also I have one procedure which only retrieves data from table. and it only
gives one row sometime even if there are 10 rows available in for matching
condition. This too happen when I called it from Java application and if I
called it from mysql command line it gives me proper result set of 10 rows.
I am not able to understand Is it something known for mysql? Or am I doing
something wrong?
Any input will be a great help.
--
Thanks and Regards,
Manasi Save
Strange behavior by MySQL Stored Procedure
Dear All, I have one stored procedure Which inserts data into one table. But sometimes it does not insert record. This happens when I called it from java application. But If I called same query from mysql command line. It executes successfully. Also I have one procedure which only retrieves data from table. and it only gives one row sometime even if there are 10 rows available in for matching condition. This too happen when I called it from Java application and if I called it from mysql command line it gives me proper result set of 10 rows. I am not able to understand Is it something known for mysql? Or am I doing something wrong? Any input will be a great help. --Thanks and Regards, Manasi Save
Re: Strange behavior by MySQL Stored Procedure
2010/5/28 Manasi Save manasi.s...@artificialmachines.com: [...] Or am I doing something wrong? probably; you better send us another e-mail writing at least: - mysql version you are using - mysql Connector/J version you are using - piece of java code you are using to call the stored procedure - source of the stored procedure (or part of it) ... probably, a subset of all of these infos will not be enough to understand the problem. In any case, if you have troubles using the mysql jdbc driver but no problem using the mysql CLI and you suspect a Connector/J bug, maybe you better write to the mysql java support mailing list: http://lists.mysql.com/java Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Strange behavior by MySQL Stored Procedure
Hello Manasi, If possible can you please send in the code that you mentioned (procedure or trigger). Please give a detailed technical explanation explaining the query which you used from command line and the query used in the procedure. Please mention the table structure, show table status and few records from the query executed. Cheers, Anirudh Sundar 9594506474 DataVail Mumbai. On Fri, May 28, 2010 at 5:44 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, I have one stored procedure Which inserts data into one table. But sometimes it does not insert record. This happens when I called it from java application. But If I called same query from mysql command line. It executes successfully. Also I have one procedure which only retrieves data from table. and it only gives one row sometime even if there are 10 rows available in for matching condition. This too happen when I called it from Java application and if I called it from mysql command line it gives me proper result set of 10 rows. I am not able to understand Is it something known for mysql? Or am I doing something wrong? Any input will be a great help. -- Thanks and Regards, Manasi Save
Stored Procedure/Function Question
Hi there, I have a WEIRD question, that I can't find an answer too... Here is my stored function: DELIMITER $$ USE `mydatabase`$$ DROP FUNCTION IF EXISTS `SPLIT_STR`$$ CREATE definer=`thisus...@`%` FUNCTION `SPLIT_STR`( X VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) CHARSET latin1 DETERMINISTIC RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(X, delim, pos), LENGTH(SUBSTRING_INDEX(X, delim, pos -1)) + 1), delim, '')$$ DELIMITER ; Basically, as it sits, only the user 'thisuser' at any location can use this function, but I want to be able to allow ALL the users of this database access to it, as well, if I were to change this function, i have to go in, and manage every user that would be attached to it, to allow to use it again. I've tried '%'@'%', and I get the error that this user does not exist. Any help? Is it possible? Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Get count of number of lines from mysql stored procedure
Hi All,I want to find out number of lines are there in all stored procedure written.Is it possible to get the number of lines using a SQL query.Thanks in advance. -- Regards, Manasi Save
Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10
Hi all. I am running into a very frustrating problem trying to created a stored procedure. I had originally assumed I was using bad syntax, but even examples copied and pasted directly from the manual are giving the same error. mysql CREATE DEFINER = 'walton'@'localhost' PROCEDURE account_count() - BEGIN - SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; 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 3 mysql This example can be found at: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html Google has failed me on this one. Can anyone advise me as to what I need to do to troubleshoot this? Also if it is in error in the documentation, how would I go about notifying someone so it can be corrected? Any help would be greatly appreciated.
RE: Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10
You need to use DELIMITER // Or some other symbol besides ; to change the client's end-of-statement symbol. Otherwise it ends the statement at the first ; inside the procedure you use, but it's not yet complete. This is described in the manual on that same page. Regards Gavin Towey -Original Message- From: Walton Hoops [mailto:wal...@vyper.hopto.org] Sent: Wednesday, December 16, 2009 10:46 AM To: mysql@lists.mysql.com Subject: Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10 Hi all. I am running into a very frustrating problem trying to created a stored procedure. I had originally assumed I was using bad syntax, but even examples copied and pasted directly from the manual are giving the same error. mysql CREATE DEFINER = 'walton'@'localhost' PROCEDURE account_count() - BEGIN - SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; 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 3 mysql This example can be found at: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html Google has failed me on this one. Can anyone advise me as to what I need to do to troubleshoot this? Also if it is in error in the documentation, how would I go about notifying someone so it can be corrected? Any help would be greatly appreciated. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10
-Original Message- From: Walton Hoops [mailto:wal...@vyper.hopto.org] Hi all. I am running into a very frustrating problem trying to created a stored procedure. I had originally assumed I was using bad syntax, but even examples copied and pasted directly from the manual are giving the same error. mysql CREATE DEFINER = 'walton'@'localhost' PROCEDURE account_count() - BEGIN - SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; 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 3 mysql This example can be found at: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html Figures, I find the answer just as soon as I send the request for help. I just needed to read the documentation better. As penance, here is the answer I found: From http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html: If you use the mysql client program to define a stored program that contains the semicolon characters within its definition, a problem arises. By default, mysql itself recognizes semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server. So the example I was using becomes: DELIMITER | CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count() SQL SECURITY INVOKER BEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; END| DELIMITER ; Sorry for the unnecessary question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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
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
Select from remote server from stored procedure
Ok, I feel silly for asking this, but I am going to do it anyway. I have a huge stored procedure that does quite a bit of logic, and gathering/splitting of data. I currently have our customer database on one server, and our logging on another. What i need to do, is to pull the customer id from the other server, so that the logs are tied back to the customer. Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? Does my question make sense? Currently what I am doing, is every new customer that gets created, my php app adds the username/customerid to that server, then makes a connection to the logging server and creates the same record, same with deleting and updating... but there just has to be a simpler way :) Thanks in advance. Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select from remote server from stored procedure
Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? The only way I know to access tables from different servers from a single connection is federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Once you do that, you are accessing it like a local table. I hope this helps. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select from remote server from stored procedure
Posted this before, but beware: federated tables do NOT use indices. Every select is a full table scan, and if you're talking about a logging table that could become very expensive very fast. On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote: Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? The only way I know to access tables from different servers from a single connection is federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Once you do that, you are accessing it like a local table. I hope this helps. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Re: Select from remote server from stored procedure
Hello Johan, On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote: Posted this before, but beware: federated tables do NOT use indices. Every select is a full table scan, and if you're talking about a logging table that could become very expensive very fast. This is not entirely true. If you define an index on the local federated table, and it makes sense to use it, then a remote WHERE clause will be passed through and hence use the remote index. Not all types of index accesses can be passed through such as this, however for a single row lookup on a primary key, it should be fine. It is still not as fast as local access, but it's not as bad as always doing a full table scan remotely. On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote: Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? The only way I know to access tables from different servers from a single connection is federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Once you do that, you are accessing it like a local table. I hope this helps. Neil Regards, Harrison -- Harrison C. Fisk, MySQL Staff Support Engineer MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select from remote server from stored procedure
So what I am reading, I guess it would be safer to just do it how I am currently doing it, as it really isn't that slow... it's just duplicating the data elsewhere (I suppose maybe making this a slave table to the other server... nah... lots of work there :P) Thanks, and I did search it before, but I guess my searching keywords were insufficient ;) Steven Staples -Original Message- From: harrison.f...@sun.com [mailto:harrison.f...@sun.com] Sent: December 9, 2009 2:07 PM To: Johan De Meersman Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com Subject: Re: Select from remote server from stored procedure Hello Johan, On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote: Posted this before, but beware: federated tables do NOT use indices. Every select is a full table scan, and if you're talking about a logging table that could become very expensive very fast. This is not entirely true. If you define an index on the local federated table, and it makes sense to use it, then a remote WHERE clause will be passed through and hence use the remote index. Not all types of index accesses can be passed through such as this, however for a single row lookup on a primary key, it should be fine. It is still not as fast as local access, but it's not as bad as always doing a full table scan remotely. On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal n...@jammconsulting.comwrote: Is this possible to do? To make a connection, inside the stored procedure to a completely different machine and access the mysql there? The only way I know to access tables from different servers from a single connection is federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Once you do that, you are accessing it like a local table. I hope this helps. Neil Regards, Harrison -- Harrison C. Fisk, MySQL Staff Support Engineer MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09 02:32:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select from remote server from stored procedure
Steve: I suppose maybe making this a slave table to the other server... nah... lots of work there Setting your local server to be a slave of the remote server is not too hard and would be a MUCH better solution. The steps are fairly staightforward: 1. Add a slave user to the remote database 2. Tell the remote server to create a binary log 3. Tell the local server to be a slave of the remote 4. Start the slave It should take less than 1 hour to set it up. I have done it many times. It is probably not as hard as you are thinking it will be. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- 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 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
Stored Procedure Data Types
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: Log querys from stored procedure
Hello Mauricio, Mauricio Tellez wrote: Hi, I'm trying to debug a stored procedure. This SP has a few queries and also call another SP. I'm starting mysql with the --log=my_queries.log but I found that mysql only log the call my_sp(param1, param2) but don't log any query inside my_sp neither log the nested SP call. By the way, how can I log the triggers queries? Do I need to set some kind of query log verbose level? thanks in advance. Neither the statements within a stored procedure nor the individual statements within SQL functions are written to any query log. There are two ways to trace your functions: 1) run the mysqld in a debugger and trace it that way 2) embed statements within your procedures that write to a log table at various points during the execution. This is a technique that works well for all sorts of development problems (MS SQL, ASP, MySQL stored procedures, etc) -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Log querys from stored procedure
Hi, I'm trying to debug a stored procedure. This SP has a few queries and also call another SP. I'm starting mysql with the --log=my_queries.log but I found that mysql only log the call my_sp(param1, param2) but don't log any query inside my_sp neither log the nested SP call. By the way, how can I log the triggers queries? Do I need to set some kind of query log verbose level? thanks in advance. -- Mauricio Tellez
Re: facing problem with is null in stored procedure
hai shawn green,
thanks for ur response.My situation is to generate a production
plan.for this I collect the data from sales orders and compare the stock
availability in rthe warehouse and if the stock is less than the ordered
quantity I plan for production and for this I have to issue the raw material
required for the production by calculating based on bom(bill of material)
details.I am doing this but for every item in the sales oredr I am inserting
the record even though same item is in the another order.I have to update
the quantity i the item is already in the list otherwise i have to insert.
please give me solution if u got my situation.
On Sun, Apr 12, 2009 at 1:07 AM, Shawn Green shawn.gr...@sun.com wrote:
syed basha wrote:
delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from
prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
How do you know your condition is true?
Could this be part of a transaction that is rolled-back instead of
committed?
There are too many unknowns about your situation to make a clear judgement
on this particular problem.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
sibasha
Re: facing problem with is null in stored procedure
syed basha wrote:
delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
How do you know your condition is true?
Could this be part of a transaction that is rolled-back instead of
committed?
There are too many unknowns about your situation to make a clear
judgement on this particular problem.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
facing problem with is null in stored procedure
delimiter //
create procedure sample1(in p_item varchar(30),
in p_size varchar(6),
in p_quantity decimal(10,3),
in p_unit varchar(3),
in p_autoincrement varchar(30),
out v_mess varchar(50)
)
begin
declare done int default 0;
declare v_bhqty decimal(10,3);
declare v_bhunit varchar(3);
declare v_blrawm varchar(30);
declare v_blqty decimal(10,3);
declare v_blunit varchar(3);
declare v_puid varchar(30);
declare v_conqty decimal(10,3);
declare v_plid int(3) default 1;
declare v_calqty decimal(10,3);
declare c_bomdet cursor for select bh.qty,bh.unit,bl.rawm,bl.qty,bl.unit
from bomhead bh,bomlins bl
where bl.item=bh.item and bl.szid=bh.szid and bl.item=p_item and
bl.szid=p_size;
declare continue handler for not found set done=1;
select ppid into v_puid from prplrawm where ppid=(select
concat('pph',max(convert(substr(ppid,4),unsigned integer))) from prpllins);
open c_bomdet;
repeat
fetch c_bomdet into v_bhqty,v_bhunit,v_blrawm,v_blqty,v_blunit;
if not done then
call convertion(p_unit,v_bhunit,p_item,p_size,p_quantity,v_conqty);
set v_calqty=v_conqty*v_blqty;
if v_puid is not null then
set v_mess='max id is exists';
else
insert into prplrawm(ppid,plid,item,szid,rawm,rqty,runt)
values(p_autoincrement,v_plid,p_item,p_size,v_blrawm,v_blrqty,v_blrunt);
set v_plid=v_plid+1;
end if;
end if;
until done end repeat;
close c_bomdet;
end
//
delimiter ;
when i call this procedure it is not performing the insert statement even
though the condition is true.
please tell me why.
thank you
--
sibasha
how can i determine default database within a stored procedure?
The database() function returns the default database, so: mysql use scratch; Database changed mysql select database(); ++ | database() | ++ | scratch| ++ mysql use mysql; Database changed mysql select database(); ++ | database() | ++ | mysql | ++ However, if the database function is invoked from within a stored procedure, it only returns the name of the database in which it exists: use scratch; delimiter $$ create procedure thisdb() begin select database(); end$$ delimiter ; use scratch; call scratch.thisdb(); scratch use mysql; call scratch.thisdb(); scratch use customer; call scratch.thisdb(); scratch This is documented behavior. Is there anyway for a stored procedure to determine what the deafault schema of the user invoking it is? We are trying to track down cross-schema invocations of sp's and this is the last piece I have to figure out. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: how can I make a stored procedure executable by public?
Hello Jim, If I unserstand well your needs the steps you need to do are: Create one user X with insert privileges on the mydb.audit_table Create the stored procedure specifying the user X both in the DEFINER section and in the SQL_SECURITY section of the create procedure statement ( http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) In this way who calls the stored procedure will have the table written with the grants of the original definer X. Is it what you are looking for? Claudio 2009/3/17 Jim Lyons jlyons4...@gmail.com I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: how can I make a stored procedure executable by public?
Thanks, Claudio, but that's not quite it. I'm not writing any procedure. I'm inserting code into procedures other people write. I am taking each procedure out of the mysql.proc table, inserting a few lines of code right at the start of the body, and saving back into the proc table. These lines of code insert a line into my audit table. I don't have any control over what other people write, I just want to record when their procedures get called. The genral log logs original calls to procedures, but I don't see that it records calls made to one procedure from within another. On Wed, Mar 18, 2009 at 6:42 AM, Claudio Nanni claudio.na...@gmail.comwrote: Hello Jim, If I unserstand well your needs the steps you need to do are: Create one user X with insert privileges on the mydb.audit_table Create the stored procedure specifying the user X both in the DEFINER section and in the SQL_SECURITY section of the create procedure statement ( http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) In this way who calls the stored procedure will have the table written with the grants of the original definer X. Is it what you are looking for? Claudio 2009/3/17 Jim Lyons jlyons4...@gmail.com I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: how can I make a stored procedure executable by public?
Ok sorry, I did not understand at first. GRANT INSERT on mydb.audit_table to ''@'%'; should do the work. Cheers Claudio Jim Lyons wrote: Thanks, Claudio, but that's not quite it. I'm not writing any procedure. I'm inserting code into procedures other people write. I am taking each procedure out of the mysql.proc table, inserting a few lines of code right at the start of the body, and saving back into the proc table. These lines of code insert a line into my audit table. I don't have any control over what other people write, I just want to record when their procedures get called. The genral log logs original calls to procedures, but I don't see that it records calls made to one procedure from within another. On Wed, Mar 18, 2009 at 6:42 AM, Claudio Nanni claudio.na...@gmail.com mailto:claudio.na...@gmail.com wrote: Hello Jim, If I unserstand well your needs the steps you need to do are: Create one user X with insert privileges on the mydb.audit_table Create the stored procedure specifying the user X both in the DEFINER section and in the SQL_SECURITY section of the create procedure statement (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) In this way who calls the stored procedure will have the table written with the grants of the original definer X. Is it what you are looking for? Claudio 2009/3/17 Jim Lyons jlyons4...@gmail.com mailto:jlyons4...@gmail.com I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.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: how can I make a stored procedure executable by public?
great! thanks much. On Wed, Mar 18, 2009 at 1:52 PM, Claudio Nanni claudio.na...@gmail.comwrote: Ok sorry, I did not understand at first. GRANT INSERT on mydb.audit_table to ''@'%'; should do the work. Cheers Claudio Jim Lyons wrote: Thanks, Claudio, but that's not quite it. I'm not writing any procedure. I'm inserting code into procedures other people write. I am taking each procedure out of the mysql.proc table, inserting a few lines of code right at the start of the body, and saving back into the proc table. These lines of code insert a line into my audit table. I don't have any control over what other people write, I just want to record when their procedures get called. The genral log logs original calls to procedures, but I don't see that it records calls made to one procedure from within another. On Wed, Mar 18, 2009 at 6:42 AM, Claudio Nanni claudio.na...@gmail.commailto: claudio.na...@gmail.com wrote: Hello Jim, If I unserstand well your needs the steps you need to do are: Create one user X with insert privileges on the mydb.audit_table Create the stored procedure specifying the user X both in the DEFINER section and in the SQL_SECURITY section of the create procedure statement (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html) In this way who calls the stored procedure will have the table written with the grants of the original definer X. Is it what you are looking for? Claudio 2009/3/17 Jim Lyons jlyons4...@gmail.com mailto:jlyons4...@gmail.com I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
how can I make a stored procedure executable by public?
I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
how can trace stored procedure usage?
I am trying to track the usage of stored procedures on our system. My solution so far is to parse the general log for call queries. This works well for procedures that are called from the command line, but the general log does not seem to report procedures called from within other procedures. Is there a way to do that? Is there a better overall way to track procedure calls? What about function calls? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: how can trace stored procedure usage?
Jim- you can try to port Oracles utldtree.sql Caveat Emptor: Hasnt been worked on since 92 and is VERY buggy! Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Mon, 26 Jan 2009 11:01:58 -0600 Subject: how can trace stored procedure usage? From: jlyons4...@gmail.com To: mysql@lists.mysql.com I am trying to track the usage of stored procedures on our system. My solution so far is to parse the general log for call queries. This works well for procedures that are called from the command line, but the general log does not seem to report procedures called from within other procedures. Is there a way to do that? Is there a better overall way to track procedure calls? What about function calls? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_allup_howitworks_012009
Question on returning multiple rows from a stored procedure into a session variable
Hi Is there a way to write a stored procedure that returns a result set containing multiple rows? More specifically, I'm trying to return the multi-row result set as an OUT parameter, which I can then access via a session variable. If this is possible, could someone direct me to the appropriate documentation or post an example. Thanks. Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
