Tag Archives: code

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.

16 Sep

MySQL Function to Convert Date To Words

Recently I saw a MySQL Stored Function requirement on Experts-Exchange for converting date into some specific words format.
You may find MySQL function for date to words conversion online; even udfs might be ready, but I decided to write my own.
I wrote this simple function mainly based on SELECT CASE to convert dates in to words as follows:

mysql>SELECT date_to_words(‘2010-05-08′);
Eighth Day of May Two Thousand Ten

Download sql file below the code.

DELIMITER $$

DROP FUNCTION IF EXISTS `date_to_words` $$
CREATE FUNCTION `date_to_words` (mydate DATE) RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN

/* Converts date into words */

DECLARE yr INT;
DECLARE dateval INT;
DECLARE thousand INT;
DECLARE hundred INT;
DECLARE tens INT;

DECLARE tensword VARCHAR(10);
DECLARE onesword VARCHAR(10);
DECLARE thousandsword VARCHAR(20);
DECLARE hundredsword VARCHAR(20);
DECLARE datevalsword VARCHAR(20);

SET yr=year(mydate);
SET dateval=day(mydate);

/* DAY TO WORDS */

SELECT CASE dateval
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 3 THEN ‘Third’
WHEN 4 THEN ‘Fourth’
WHEN 5 THEN ‘Fifth’
WHEN 6 THEN ‘Sixth’
WHEN 7 THEN ‘Seventh’
WHEN 8 THEN ‘Eighth’
WHEN 9 THEN ‘Ninth’
WHEN 10 THEN ‘Tenth’
WHEN 11 THEN ‘Eleventh’
WHEN 12 THEN ‘Twelfth’
WHEN 13 THEN ‘Thirteenth’
WHEN 14 THEN ‘Fourteenth’
WHEN 15 THEN ‘Fifteenth’
WHEN 16 THEN ‘Sixteenth’
WHEN 17 THEN ‘Seventeenth’
WHEN 18 THEN ‘Eighteenth’
WHEN 19 THEN ‘Nineteenth’
WHEN 20 THEN ‘Twentieth’
WHEN 21 THEN ‘Twenty-first’
WHEN 22 THEN ‘Twenty-second’
WHEN 23 THEN ‘Twenty-third’
WHEN 24 THEN ‘Twenty-fourth’
WHEN 25 THEN ‘Twenty-fifth’
WHEN 26 THEN ‘Twenty-sixth’
WHEN 27 THEN ‘Twenty-seventh’
WHEN 28 THEN ‘Twenty-eighth’
WHEN 29 THEN ‘Twenty-ninth’
WHEN 30 THEN ‘Thirtieth’
WHEN 31 THEN ‘Thirty-first’
END into datevalsword;

/* YEAR TO WORDS */
set thousand=floor(yr/1000) ;
set yr = yr – thousand * 1000;
set hundred = floor(yr / 100);
set yr = yr – hundred * 100;

IF (yr > 19) THEN
set tens = floor(yr / 10);
set yr = yr mod 10;
ELSE
set tens=0;
END IF;

SELECT CASE thousand
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO thousandsword;
SET thousandsword=concat(thousandsword,’ Thousand ‘);

SELECT CASE hundred
WHEN 0 then ”
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
END INTO hundredsword;
if (hundredsword<>”) then
SET hundredsword=concat(hundredsword,’ Hundred ‘) ;
else
set hundredsword=”;
end if;

/*TENS To WORDS*/
SELECT CASE tens
WHEN 2 THEN ‘Twenty’
WHEN 3 THEN ‘Thirty’
WHEN 4 THEN ‘Fourty’
WHEN 5 THEN ‘Fifty’
WHEN 6 THEN ‘Sixty’
WHEN 7 THEN ‘Seventy’
WHEN 8 THEN ‘Eigthy’
WHEN 9 THEN ‘Ninety’
ELSE ”
END INTO tensword;

/*ONES To WORDS*/
SELECT CASE yr
WHEN 0 THEN ”
WHEN 1 THEN ‘One’
WHEN 2 THEN ‘Two’
WHEN 3 THEN ‘Three’
WHEN 4 THEN ‘Four’
WHEN 5 THEN ‘Five’
WHEN 6 THEN ‘Six’
WHEN 7 THEN ‘Seven’
WHEN 8 THEN ‘Eight’
WHEN 9 THEN ‘Nine’
WHEN 10 THEN ‘Ten’
WHEN 11 THEN ‘Eleven’
WHEN 12 THEN ‘Twelve’
WHEN 13 THEN ‘Thirteen’
WHEN 14 THEN ‘Fourteen’
WHEN 15 THEN ‘Fifteen’
WHEN 16 THEN ‘Sixteen’
WHEN 17 THEN ‘Seventeen’
WHEN 18 THEN ‘Eighteen’
WHEN 19 THEN ‘Nineteen’
END into onesword;

return concat(datevalsword, ‘ Day of ‘, date_format(mydate,’%M’),’ ‘,thousandsword,hundredsword, tensword,’ ‘,onesword);
END $$

DELIMITER ;

Download SQL Code for converting date to words.date_to_words.sql

04 Aug

My worst mathematical program in perl

I have a Mathe-crap-matical friend. Who keeps digging into graves of dead formulas and equations and all possible Math related stuffs.
Since I started understanding Mathematics (that it’s not my cup of tea), I tried to stay away from it.

Now, that enthu creature mailed me his requirement. He says he “eat mathematics”, I prefer to stay away from such guys. But as a good friend I read his mails.

His requirement was to create a program. His requirements are:

N is a natural number falling in range [1 – 999999]
X is a number given by user with range [6000 – 20000]

You have to write a program such that you find a number N whose square root or cube root is such that the first digit of X comes in the Xth decimal place and second digit of X comes in the (X+1)th decimal place and so on…

e.g. user enters 12…
then the number N would be such that CubeRoot(N) or SqRoot(N) = dddd.ddddddddddd12

You may write program in any language.

Check screen shot to understand what it does [note the name]:

maths program

maths program

Alright, I understood the dooms day is closing in… Did you notice the decimal places correction he was asking? 6000!! Damn…

Any ways, I chose perl and accomplished the task. I’m not sure how and why he need this thing but still I hope it rested his soul. :)

This post is for people like me who have such math-e-manic friends.
You may download my script my_mathematics_pgm.pl.

print "Enter X:";
my $X = <STDIN>;
$X=trim($X);
my $N=1;
while ($N < 100) {
	$SQRT_VAL=$N**(1/2);
	$CUBERT_VAL=$N**(1/3);

	$PosAfterDesimal=index ($SQRT_VAL,'.');
	$PosOfXInSQRT =index ($SQRT_VAL, $X,$PosAfterDesimal);
	$PosOfXInSQRT = $PosOfXInSQRT -1;
	$PosAfterDesimal=index ($CUBERT_VAL,'.');
	$PosOfXInCUBERT =index ($CUBERT_VAL, $X, $PosAfterDesimal);
	$PosOfXInCUBERT= $PosOfXInCUBERT -1;

#	print $N . "->" . $SQRT_VAL . "->" . $X . "->> " .trim($PosOfXInSQRT). "\n";
	if (trim($PosOfXInSQRT) == $X) {
		print $X. " matched SQRT of " . $N . " at " . $X . "th Position \n" ;
		print "\t Where, N=" . $N . ", SQRT VAL=" . $SQRT_VAL . ", X=" . $X . "\n";
		exit;
	} elsif (trim($PosOfXInCUBERT) == $X) {
		print $X. " matched Cube Root of " . $N . " at " . $X . "th Position \n";
		print "\t Where, N=" . $N . ", CUBEROOT VAL=" . $CUBERT_VAL . ", X=" . $X . "\n";
		exit;
	}
	$N++;
}
sub trim($)
{
        my $string = shift;

        if ( $string eq undef || $string eq "" )
        {
                return "";
        }
        $string =~ s/^\s+//;
        $string =~ s/\s+$//;

        return $string;
}#trim Ends
24 May

Java termwork connect Access Database and operations

Prologue:
Long back there were golden days of post graduation in the “great” DDIT (Now Dharamsinh Desai University), Nadiad.
Assignments, termwork and journals or everything similar was in demand – I attempted a java code as my termwork on Monday, October 03, 2005. [That’s created date of .java file, I don’t remember anything.]
A “variable check” became very famous, notorious actually, for some unmentionable reasons. It was recently mentioned by Utkarsh AKA Bapu.
Later I decided to scan each bit of my harddisk and get that 8582 code.
So here is that java code and it was working 5 years back.
I don’t remember / have termwork’s definition but code is here. Seems a simple java program to connect DB and do regular activities.

Now,
This post is a remembrance for my colleagues and the great 8582 check.

For the rest go ahead and test Java-Access-Database connectivity code.
Download TermWork.

[ad#ad-2-300×250]


Epilogue:
I’m no more working with Java, I understand logic and nothing else. So if atall you face any errors, go through it – you can solve it, post it here too.
Enjoy.
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 ;

Sample output:

Generate-Inserts-mysql

Generate Inserts mysql

Download sql script to generate insert statements: 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.

-- Kedar Vaijanapurkar --