Example using a mysql cursor in a stored procedure. Mysql Cursor example DELIMITER $$ DROP PROCEDURE IF EXISTS mysql_cursor_example $$ CREATE PROCEDURE mysql_cursor_example ( IN in_name VARCHAR(255) ) BEGIN -- First we declare all the variables we will need DECLARE l_name VARCHAR(255); -- flag which will be set to true, when cursor reaches end [...]
Refer to part 1 for the rationale behind the code or you can skip to part 3 for a working example as well as how you can debug the stored procedure.
Important: The SP will create a table named `dynamic_cursor`. Make sure this table does not exist in the database where you will be storing the procedure. Here's the 1st iteration of a general purpose dynamic cursor:
DELIMITER $$[Read more]
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
IN selectStmt TEXT,
IN whatAction VARCHAR(255),
Skip to part 2 for the code snippet or to part 3 for a working example and how to debug the stored procedure.
Also another workaround on a missing MySQL functionality, enabling/disabling triggers, can be found here.
As of version 5.5, MySQL still does not have the native ability to execute a dynamic cursor. This can be worked around but the resulting stored procedure will have a few limitations.
This stored procedure is a general purpose …
Okay… So I’ve blogged many times before about the Storage Engine API in Drizzle. This API is somewhat inherited from MySQL. We have very much attempted to make it a much cleaner interface. Our goals in making changes include: make it much easier to write and maintain a storage engine, make the upper layer code obviously correct and clear in what it’s doing and being able to more easily introduce optimisations.
I’ve recently added a Storage Engine that is only used in testing: storage_engine_api_tester. I’ve blogged on it producing call graphs (really state transition graphs) before both for Storage Engine and Cursor.
I’ve been expanding the test. My test engine is now a wrapper around a real engine instead of just a fake one. …[Read more]
Following on from my post yesterday on the various states of a Storage Engine, I said I’d have a go with the Cursor object too. A Cursor is used by the Drizzle kernel to get and set data in a table. There can be more than one cursor open at once, and more than one per thread. If your engine cannot cope with this, it is its responsibility to figure it out and return the appropriate errors.
Let’s look at a really simple operation, inserting a couple of rows and then reading them back via a full table scan.
Now, this graph is slightly incomplete as there is no doEndTableScan() call. But you can see in which order things are meant to happen. In this case, “store_lock()” means that store_lock() has been called, …[Read more]
Drizzle still has a number of quirks inherited from the MySQL Storage Engine API (e.g. BLOBs, row buffer, CREATE SELECT and lack of DDL transaction boundaries, key tuple format). One of the things we fixed a long time ago was to have proper methods for StorageEngines to be called for: startTransaction, startStatement, endStatement, commit and rollback.
If you’ve had to implement a transactional storage engine in MySQL you will be well aware of the pattern of “in every …[Read more]
Abstract - As described by Walter Heck, MySQL database size
can be visualized using Google
Charts. With a minor code improvement the URL for the chart
can be obtained twice as fast. With some more modification, the
number of lines can be cut down resulting in a function that is
half as long.
Hi!It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of course family life.
A month ago or so I …