Z-Car

MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax.

girl looking at computerMySQL 5.0+ gave DBA’s the ability to write stored procedures and functions. This is a great addition, and one that all “real” DB engines should provide. But, as many DBA have found out, writing stored procedures using most MySQL clients can be a bit of a challenge. Usually after writing their first attempt, they are greeted with, “Error Code : 1064 You have have an error in your SQL syntax;”.

So, what is going on?  Well, by default the MySQL statement delimiter is the semi-colon (;).  However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:

DELIMITER //

CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )

BEGIN

SELECT Min(prod_price) INTO low_price FROM sku_items;

SELECT Max(prod_price) INTO high_price FROM items;

SELECT Avg(prod_price) INTO avg_price FROM items;

END; //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.   Any character may be used as the delimiter except for , as it is an escape character in mySQL.


Check out Lion Electric Vehicles

I recently stumbled across Lion Electric Vehicles.  These guys have some great new technology that enables you to hop-up your existing hybrid by swapping out your existing batteries with new batteries can greatly increase your range.

cells_shipping

And even more incredible is that they are selling Ford Escape Hybrids that have already been modified with their new technology.  Changes to the battery pack and the sensor program allow it to run on EV entirely within city speed ranges.  On the highway it operates as an ICE vehicle with electric boost.

ford-escape-hybrid_courtesy_photo_ford

These guys are based out of VA, but have facilities all over the world.  It is nice to see a US company developing new technology that will help power the new energy-independence economy.  I am hopeful that our current energy crisis well help pour capital into companies like Lion which will help develop the new technologies required to achieve our goal of energy independence.