My New DB GUI

I've been playing with various GUIs off and on over the last couple of months. I find that I drop to mysql.exe quite often no matter which tool I use. My favorites until recently have been the MySQL GUI Tools and NaviCat.

I was using the Lite edition of Navicat. I actually started using navicat with postgres a few years ago. I like it but the lite version is limited in some annoying ways. It's nice in that it runs (at least the mysql version) in Linux and windows. I don't use a mac so that doesn't really do anything for me.

Of course the MySQL gui tools run just about anywhere.

I just remembered that Quest Software has a mysql tool called TOAD for MySQL. I have been a user of TOAD for Oracle for well over a decade. I started using it before Quest owned it.

TOAD started at the Tool for Oracle Application Developers. So calling it Tool for Oracle Application Developers for MySQL sounds kind of stupid. ;-)

TOAD is still the most advanced gui tool for Oracle. I have started using Oracle's SQL Developer and it's a nice tool but TOAD still has it beat on a feature and performance basis right now.

Anyway, my point with all of this is that TOAD for MySQL is now my preferred tool. It is completely free although it runs only on windows. It has the most advanced feature set of any of the database tools I've been using recently.

In addition to MySQL and Oracle, it also supports MS SQL Server, DB2 and Sybase. If they came out with a Postgres version it would be a complete set.

The query builder and erd tool are highly functional. When I am new to an application, the first thing I look for is an ERD. Most MySQL applications do not have one available (at least in my experience). So, I start off by building one.

The schema browser also blows away the competition. Grid view and edit with out the pain of separate pop up windows. very nice. You can also see all of the databse objects.

My suggestion, when you install, choose the TOAD for Oracle view. If you prefer an explorer view, choose the SQL Navigator view.

Give it a try. You can read my initial review (kind of old now though).

LewisC

MySQL vs Postgres Wiki

There is a new wiki comparing MySQL to PostgreSQL. Because it's a wiki, hopefully it can be kept updated so that it's current AND accurate. The wiki is MySQL vs PostgreSQL. Personally, I'd like to see this grow into a universal comparison site that the community could keep updated. LewisC

Sun Buys MySQL for $1 Billion!

Holy simoleans, Batman. I wake up this morning, write a nice little Oracle tip and then next thing I see is Josh's post about Sun->MySQL. That was totally unexpected to me. I browsed over to PlanetMySQL to get the scoop and what do I see, Sun buys MySQL for $1 billion to take centerstage in the web economy. I don't often agree with Matt but on this topic, I think I have to. This just makes a lot of sense for everyone involved. Sun, MySQL and MySQL users. The first post I saw that shows concern about Sun's Postgres support is this one from the 451 Group, Sun acquiring MySQL for $1bn. That post led me to Jonathan Schwartz's blog. I love his post title, Helping Dolphins Fly. Jonathan always sounds pumped but this blog entry makes him sound ecstatic. I have not been impressed with many Sun decisions over the last couple of years but I have to admit, this is a big one. I think it's a good direction. If you want to read the press release and get some of the details, you can do so at MySQL AB. Lot's of changes this year, I think. LewisC

My Book Has Been Printed

Well, it's taken over a year but it has finally arrived. I started writing the book back in Aug 2006. I finished in late Jan 2007 and the technical editor finished his work in March. Now, In Jan 2008, EnterpriseDB: The Definitive Reference is available. I'm glad I didn't wait for the movie. ;-) It's kind of ironic. Just this morning, I posted that I am working on my second book. My wife called me at work and told me two boxes of books had arrived. 2008 is turning out to be a good year. I wonder if I should start planning my MySQL book? Anyway, the book looks good. I've already found a few typos and grammar errors just skimming around. I like the font and the pages look really crisp. All in all, I have to say I am very happy that the book is out. Check out some photos: I think I want to give a couple away. Maybe some kind of contest for the blog readers. Any ideas of something fair? I am willing to pay shipping for a couple but that would only be here in the US. Overseas shipping gets expensive. LewisC

MySQL Adds XML and XPath Support

I was browsing around the web and ran across this article at xml.com, XML Moves to mySQL. Being a heavy XML user, I had to read the article. It looks like MySQL is expanding the built-in support for XML.

This article isn't very detailed but it links to Using XML in MySQL 5.1 and 6.0 at mySQL.com which is very detailed.

I like the ability that is built in to support loading XML from files. That's a feature I wish Oracle would work on. Even in 11g, that functionality is still limited.

MySQL also adds ExtractValue() and UpdateXML() support. If you're manipulating XML much, you know that these two functions are needed.

From what I know of MySQL and what I read here, MySQL is just getting started with XML support. This article points out some baby steps on the road to XML maturity. You have to start somewhere and I am glad MySQL is adding this.

LewisC

Google Contributes to MySQL

According to this article in ComputerWorld, MySQL to get injection of Google code. Google has signed an agreement to contribute to MySQL (oddly enough called a contributer license agreement) and will contribute source code for a variety of technical items. In a way, this can be considered a very strong endorsement of MySQL by Google. Google even has an engineer dedicated to working with MySQL and the MySQL development team. I didn't realize that Google was such a heavy user of MySQL. Google will contribute some code related to replication and monitoring. Also noted in the article is that MySQL, in the future of course, will support role based security and even Transparent Data Encryption (TDE). TDE is a big selling point for financial companies and other companies heavily regulated by privacy concerns. TDE puts the burden of encryption on the database instead of on the developer. Oracle has had it since 10g. Some good info in the article. Check it out! LewisC

Hiding SQL in a Stored Procedure

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 found.

My create table command and inserts ran unchanged. I did run into an issue with the timestamp though.

mysql> create table test_data (

-> name text,

-> address text,

-> create_date timestamp );

Query OK, 0 rows affected (0.09 sec)



mysql> insert into test_data values (

-> 'lewis',

-> '123 abc st',

-> timestamp '2001-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)



mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2091-01-01 10:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 0000-00-00 00:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Notice the timestamp in the "george" record is all 0s. I figure that's a configurable issue but I don't really care to research it at this moment so I'll just delete it and use a timestamp that's a little closer to NOW.

mysql> delete from test_data where name = 'george';

Query OK, 1 row affected (0.03 sec)

mysql> insert into test_data values (

-> 'george',

-> '456 def dr',

-> timestamp '2021-01-01 10:00:00');

Query OK, 1 row affected (0.02 sec)

mysql> select * from test_data;

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

mysql>

Ok. Now I'm ready to go. I look at the proc that I wrote for Postgres:

CREATE OR REPLACE FUNCTION get_data_by_creation(

          timestamp without time zone, 

          timestamp without time zone)

RETURNS SETOF test_data 

AS

$$ 

SELECT name, address, create_date

  FROM test_data

  WHERE create_date >= $1

    AND create_date <= $2;

$$

LANGUAGE 'sql' VOLATILE;

That's obviously not going to work but like I said above, the changes are fairly minor. I need to add a delimiter call and drop the postgres specific stuff:

delimiter //

CREATE PROCEDURE get_data_by_creation(

    IN param1 timestamp, 

    IN param2 timestamp)

BEGIN

  SELECT name, address, create_date

    FROM test_data

    WHERE create_date >= param1

      AND create_date <= param2;

END;

//

That compiles fine. Now for the test. I can't use select so I will do a call. I write three call statements: one to return both records, one to "george" and one to return "lewis".

call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

When I run these, I get the expected results:

mysql> call get_data_by_creation('2000-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.05 sec)



mysql> call get_data_by_creation('2002-01-01 10:00:00','2025-01-01 10:00:00');

+--------+------------+---------------------+

| name | address | create_date |

+--------+------------+---------------------+

| george | 456 def dr | 2021-01-01 10:00:00 |

+--------+------------+---------------------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call get_data_by_creation('2000-01-01 10:00:00','2010-01-01 10:00:00');

+-------+------------+---------------------+

| name | address | create_date |

+-------+------------+---------------------+

| lewis | 123 abc st | 2001-01-01 10:00:00 |

+-------+------------+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Sweet! This code is actually not all that far from Oracle's PL/SQL. I'll do up an example of that next.

LewisC