Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 6

Displaying posts with tag: procedure (reset)

Use of DECLARE and SET in MySQL stored program
+0 Vote Up -0Vote Down
DELIMITER $$                    -- Change the delimiter

DROP PROCEDURE IF EXISTS demo$$ -- Remove if previously exists
CREATE PROCEDURE demo()         -- Create Stored Procedure
BEGIN                           -- Start of definition
    DECLARE my_int INT;         -- Declare variables
    DECLARE my_big_int BIGINT;
    DECLARE my_text TEXT;

    DECLARE my_name VARCHAR(30) -- Declare variables with 
        DEFAULT 'Rookie Dev';   -- default value set

    SET my_int = 20;  

  [Read more...]
MySQL Global status difference using MySQL procedures / functions
+2 Vote Up -0Vote Down
As a MySQL DBA, I guess you use the SHOW GLOBAL STATUS command or the corresponding INFORMATION_SCHEMA.GLOBAL STATUS table to show current mySQL status quite often. And many of us use different tools to collect that data and view it.

But sometimes we use same command from the SQL prompt, because we have to, because it's the only option or just because that is the most convenient way. And often you kick of two such command in a row, a few seconds apart, just to see how things are moving:
... DBA picks his or her nose for a few seconds ...

And then you can see how things are advancing. But how much? To figure of how much, you have to calculate the differnce between the values returned by those two statements. And then there is another

  [Read more...]
Using MySQL Partitioning Instead of MERGE Tables
+8 Vote Up -0Vote Down

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

Now, add the partition definition. This can

  [Read more...]
OpenNMS JDBC Stored Procedure Poller with MySQL
+0 Vote Up -0Vote Down

Since a few months we are monitoring our infrastructure at Days of Wonder with OpenNMS. Until this afternoon we were running the beta/final candidate version 1.5.93.

We are monitoring a few things with the JDBC Stored Procedure Poller, which is really great to monitor complex business operations without writing remote or GP scripts.

Unfortunately the migration to OpenNMS 1.6.1 led me to discover that the JDBC Stored Procedure poller was not working anymore, crashing with a NullPointerException in the MySQL JDBC Driver while trying to fetch the output parameter.

In fact it turned out I was plain wrong. I was using a MySQL PROCEDURE:

CREATE PROCEDURE `check_for_something`()
  [Read more...]
Source Controlling the Database Schema
+0 Vote Up -0Vote Down
In a linkage of biblical proportions, Log Buffer #83 pointed me to Tom Kyte’s reiteration which pointed me to Coding Horror’s rant about source controlling the database schema. Now, for starters, I agree with Tom’s sarcasm and Coding Horror’s rant — the database schema really should be source controlled in the same place as the application [...]
Hiding SQL in a Stored Procedure
+0 Vote Up -0Vote Down

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I

  [Read more...]
Showing entries 1 to 6

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.