stored procedure

Providing Friday Proofs to MySQL Consultants

In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe that it actually is a Friday. Regardless of what they see on their watches and calendars, it becomes a crucial task of the day to prove it. Prove it technically that it actually is a Friday. This blog post is my attempt to share the proofs of existence of a “Friday”. Using MySQL datetime function: dayname Running system command from MySQL prompt

Using LOAD DATA INFILE with Stored Procedure Workaround-MySQL

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround. Download MySQL UDF: [root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz [refer: http://www.mysqludf.org/] Extract and Install: [root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz install.sh lib_mysqludf_sys.c lib_mysqludf_sys.html lib_mysqludf_sys.so lib_mysqludf_sys.sql Makefile [root@localhost kedar]# sh install.sh Compiling the MySQL UDF gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so MySQL UDF compiled successfully Please provide your MySQL root password Enter password: MySQL UDF installed successfully Create function sys_exec as follows: CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so’; sys_exec – executes an arbitrary command, and returns it’s exit...

MySQL Stored procedure to Generate-Extract Insert Statement

A lot of places I saw people asking for ways to generate Insert statements. We do have GUI Tools which can extract insert statements for us readily. of the time I choose the MySQLDump way to generate insert statements. mysqldump -uroot -ppassword –complete-insert –no-create-info DATABASE TABLENAME > TABLENAME.sql But mind is very unstable and hungry, we don’t stop at one solution. So to remove my mind’s starvation for the Stored Procedure way to extract Insert statement I created following routine. As you can see this is really a simple procedure revolves around Information_schema mainly to get details of any table...

Ideas for select all columns but one mysql stored procedure

Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is headache of actually typing out all 99 required columns!! Solutions / Ideas to above problem are: Ignorance is bliss. Select all(*) and ignore the column. Manually type column names or manage it with copy paste! Create a view from original table and drop the unnecessary column! MySQL Database command / syntax that allows us to do this; which is not there unfortunately. Use my stored procedure to select all but one columns: No need to explain why all such hacks are...