Tag Archives: stored procedure

30 Nov

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

You also can similarly create functions:
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.

Example – How to load txt file to MySQL using Stored Procedure & Load Data syntax:

Step-1. Creating table:

CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM

Step-2. Create a sample file to load:

vi loadtest.txt
1
2
3

Step-3. Create a shell script:

vi /tmp/load.sh
mysql -u mysql_user -p mysql_password -e “load data local infile \”$1\” into table $2;”

Step-4. Create a Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(“sh /tmp/load.sh “,in_filepath,” “, in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select “Success” as Result;
else
select “Please check file permissions and paths” as Result;
end if;
END $$
DELIMITER ;

Step 5. Execute:

CALL `load_data_SP`(‘/tmp/loadtest.txt’ , ‘test.t’);

…and that’s it Stored Procedure will return Success or Failure accordingly.

Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under /tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.

Hope this helps.

05 May

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 and then fires the simple sql query.

The procedure I named: InsGen
Input parameters:

in_db: Database name of the table for which you want to generate insert statements
in_table: Tabel name
in_file: complete file path [eg: C:/mysqlInserts.sql or /var/lib/data/mysqlInserts.sql]

DELIMITER $$

DROP PROCEDURE IF EXISTS `InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(in_db varchar(20),in_table varchar(20),in_file varchar(100))
BEGIN

declare Whrs varchar(500);
declare Sels varchar(500);
declare Inserts varchar(2000);
declare tablename varchar(20);

set tablename=in_table;
select tablename;
# Comma separated column names – used for Select
select group_concat(concat(‘concat(\'”\’,’,’ifnull(‘,column_name,’,””)’,’,\'”\’)’)) INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;

# Comma separated column names – used for Group By
select group_concat(‘`’,column_name,’`’) INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename;

#Main Select Statement for fetching comma separated table values
set @Inserts=concat(“select concat(‘insert into “, in_db,”.”,tablename,” values(‘,concat_ws(‘,’,”,@Sels,”),’);’) from “, in_db,”.”,tablename,” group by “,@Whrs, ” INTO OUTFILE ‘”, in_file ,”‘”);

PREPARE Inserts FROM @Inserts;
EXECUTE Inserts;

END $$

DELIMITER ;

[ad#ad-2-300×250]

Sample output:

Generate-Inserts-mysql

Generate Inserts mysql

InsGen.sql

I have not considered each and every scenarios yet, but this works for normal tables and it does error if file exists.
My mind is no more hungry atleast in this regard.

I hope it helps.

05 Mar

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:

  1. Ignorance is bliss. Select all(*) and ignore the column.
  2. Manually type column names or manage it with copy paste!
  3. Create a view from original table and drop the unnecessary column!
  4. MySQL Database command / syntax that allows us to do this; which is not there unfortunately.
  5. Use my stored procedure to select all but one columns:

No need to explain why all such hacks are initiated with the help of information_schema!! :)

The idea:

Prepare column list to be selected from COLUMNS table of information_schema database for specified database name and table name. Thus we can select all columns but one and prepare sql statement and execute.
It can be further extended for select all but SOME; instead of “column_name<>in_colm_nm” one can use “column_name not in …” to prepare the column list to be selected.

Here you go with the code: [Download selectAllButOne.sql at the end of the page.]

[ad#ad-2-300×250]

DELIMITER $$

DROP PROCEDURE IF EXISTS `selectAllButOne` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAllButOne`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20))
BEGIN

SET @stmnt= CONCAT(‘SELECT ‘,(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name=in_tbl_nm AND table_schema=in_db_nm AND column_name<>in_colm_nm), ‘ FROM ‘,in_db_nm,’.’,in_tbl_nm,';’);
PREPARE stmnt FROM @stmnt;
EXECUTE stmnt;
END $$

DELIMITER ;

As one of the ideas, #4 says about MySQL syntax come to our rescue.

I wish if MySQL can ease out us with syntax as follows:
SELECT * FROM TABLENAME EXCEPT COLUMN COLUMNNAME[,COLUMNNAME…]
But…

Download Select all but one mysql stored procedure.

Any other ideas?

08 Feb

Stored procedure to add-remove prefix by rename table mysql

Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes :)
A friend of mine was renaming 100+ tables by using replace methods in notepad.
I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some techie-solution to such things.

This procedure is outcome of that try and its as usual very simple one with two functionalities:

  • rename mysql table of a database by adding prefix
  • rename mysql table of a database by removing prefix
So below mysql stored procedure just not renames to add but also removes prefixes from table names.
Download the Procedure: prefix_all

How to use:
1. Execute / Create Stored procedure by downloading or copy pasting sql script.
2. Execute: call prefix_all(‘DATABASE-NAME’,’PREFIX’,0);
Where 0 will remove the prefix from mysql table name, while 1 will add prefix.
Code:
DELIMITER $$

DROP PROCEDURE IF EXISTS `prefix_all` $$
CREATE PROCEDURE `prefix_all` (in_db varchar(20),in_prefix varchar(10),in_add_rem TINYINT(1))
BEGIN

DECLARE done INT default 0;
DECLARE tbl_nm VARCHAR(30);
DECLARE ren VARCHAR(200);

DECLARE table_cur CURSOR FOR select table_name from information_schema.tables where table_schema=in_db;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN table_cur;
  rename_loop:LOOP
    FETCH table_cur INTO tbl_nm;
    IF done=1 THEN
      LEAVE rename_loop;
    END IF;
    if in_add_rem=1 then #ADD
      SET @ren = concat("rename table ", in_db,'.',tbl_nm ," to ",in_db,'.',in_prefix,tbl_nm,";");
    else
      set @ren= concat("rename table ", in_db,'.',tbl_nm ," to ",in_db,'.',right(tbl_nm,length(tbl_nm)-length(in_prefix)),';');
    end if;
#    select @ren;
    prepare ren from @ren;
    execute ren;
  END LOOP;
CLOSE table_cur;
select table_name 'Tables' from information_schema.tables where table_schema=in_db;

END $$

DELIMITER ;

05 Dec

Stored procedure to Find database objects

This procedure lists available database objects under passed database name.

It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database.
It also lists storage engine of tables.
It uses information schema database to gather information and storing in a temporary table.

[ad#ad-2-300×250]

Usage: call xplore(database-name);
– Procedure will search through information schema for database objects under database-name.

Download Stored Procedure: explore-database
[ad#lnkunt-468×15]

DELIMITER $$

DROP PROCEDURE IF EXISTS `xplore` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `xplore`(IN_DB varchar(100))
BEGIN
DECLARE DB VARCHAR(100);
DECLARE NO_TABLES INT(10);
DECLARE NO_VIEWS INT(10);
DECLARE NO_FUNCTIONS INT(10);
DECLARE NO_PROCEDURES INT(10);
DECLARE NO_TRIGGERS INT(10);
DECLARE SUMMARY VARCHAR(200);
SET DB=IN_DB;

drop temporary table if exists objects;
create temporary table objects
(
object_type varchar(100),
object_name varchar(100),
object_schema varchar(100)
)
engine=myisam;

INSERT INTO objects
/* query for triggers */
(SELECT ‘TRIGGER’,TRIGGER_NAME ,TRIGGER_SCHEMA
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA like DB)

UNION

/* query for views*/
(SELECT ‘VIEW’, TABLE_NAME,TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’VIEW’ and TABLE_SCHEMA like DB)

UNION

/* query for procedure*/
(SELECT ‘PROCEDURE’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’PROCEDURE’ and ROUTINE_SCHEMA like DB)

UNION

/* query for function*/
(SELECT ‘FUNCTION’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’FUNCTION’ and ROUTINE_SCHEMA like DB)

UNION

/* query for tables*/
(SELECT concat(ENGINE,’ TABLE’), TABLE_NAME, TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’BASE TABLE’ and TABLE_SCHEMA like DB
GROUP BY ENGINE, TABLE_NAME);

/* show gathered information from temporary table */
SELECT object_name,object_type,object_schema
FROM objects;

/* Prepare and show summary */
SELECT object_schema AS `DATABASE`,
SUM(IF(object_type like ‘%TABLE’, 1, 0)) AS ‘TABLES’,
SUM(IF(object_type=’VIEW’, 1, 0)) AS ‘VIEWS’,
SUM(IF(object_type=’TRIGGER’, 1, 0)) AS ‘TRIGGERS’,
SUM(IF(object_type=’FUNCTION’, 1, 0)) AS ‘FUNCTIONS’,
SUM(IF(object_type=’PROCEDURE’, 1, 0)) AS ‘PROCEDURES’
FROM objects
GROUP BY object_schema;

END $$

DELIMITER ;

I have also published this as an article on EE.
http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

I have also published this as an article on EE.

http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

-- Kedar Vaijanapurkar --