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.

04 May

My Digital confusion for Camera

In the huge task of selecting a digital camera, one more day has passed.
I’m still not decided.

Yes, I have now felt an extreme need of purchasing a digital camera and I’m trying hard to wrap-up this chapter by end of this week.

I started getting idea from Ashish as he knows much more in cameras and he’s good at capturing as well.
After finishing his analysis and study he suggested me Sony DSC-W380 in my suggested price band of Rs. 15000.

Mean while I went on Sony store where I found and compared it with DSC-W370 and again get back to Mr. Ashish. W370 is having higher optical zoom (7X) as compared to 5x of W380 but still it was cheaper!! I learned that the processor is a main distinction between them W380 has BIONZ while W370 is having Sony-Processor.

Well the afternoon I happen to talk to Vic. Who owns Canon 400D and want to sell it and buy 5D.
According to him, all these Sony S8000, CoolPix or DSC series are “Ladies” cameras :) He suggested me to raise the price band or buy a “pink” camera. Proper SLRs I will not get below 30K and I’m not much interested in jumping to that range.
Any ways Vic, I’m not gonna get Pink for sure, it’ll be Black, My favorite :)

Readers, do check Vic’s flicker here: www.flickr.com/photos/vicky300374

[ad#ad-2-300×250]

My need is simple, maximum value for minimum money [Is it possible for free?] ;)
A camera having good zoom (at least 10x), Higher Pixels (at least 10) with some good lens and processor is my target.

In the evening Rakesh aka Raka aka Sabu told me to go for higher configuration and said some lens name “Carl Zuuuuuuiss” or something. I found the exact spelling later, he meant “Carl Zeiss”.

Then came the Hardik aka the Hard One (title given by Mr. Paul). He recollected my needs and started searching. Well since past two days I’ve visited almost all camera vendor’s sites and camera details; but seems like when Hardik again came to rescue, I started hunting for my DG-cam again.

Hardik come up with suggesting Sony’s DSC-H20 and Nikon’s Coolpix P100. Earlier we had discussed about Nikon S8000, but I was not much confident to go for it. I compared them with Sony DSC-H55/B and W370.
Now Nikon’s Coolpix P100 has got 26X Optical zoom but the price difference is almost 10,000.
Later I found Nikon’s COOLPIX L110 with 15X Optical Zoom with 12.1 MP @ ~15000.  Seems like I might have to settled for somewhere in 20K budget.
I didn’t stop here, I Googled for lens details and went on comparing Carl Zeiss VS Nikkor Lenses and their performance. At places I heard Carl Zeiss over Nikkor and anyways I have heard more about Zeiss :) I’m not much in to that.

My friends, who have ever been to shopping with me, know such “indecisive” attribute about choosing things, but I need help.

Anyways I’m still not done with deciding which one to buy and I hope I pick a good one.

04 May

Steps to Move Copy Rename MySQL Database

Moving, copying or renaming database is a very basic activity. I have just noted a few commands for reference to quickly follow the required operation.

1. Rename database on Linux Machine:

A. Use RENAME DATABASE Command [MySQL 5.1].

RENAME DATABASE db_name TO new_db_name;

B.

mysql -uroot -pPASSWORD -e “drop database if exists NEW-DB-NAME; create database NEW-DB-NAME” && mysqldump -uroot -pPASSWORD SOURCE-DB-NAME | mysql -uroot -pPASSWORD NEW-DB-NAME && mysql -uroot -pPASSWORD -e “drop database SOURCE-DB-NAME”

2. Create Duplicate of a database:

mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

3. Copy MySQL Database to Remote MySQL Server:

[ad#ad-2-300×250]

3A. By using -h (hostname) option.

Create MySQL Dump:

mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Load MySQL Dump:

mysql -uroot -ppassword -hHOSTNAME < DB_TO_BE_COPIED.sql OR Using Single Command: mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword -hHOSTNAME DB_TO_BE_CREATED 3B. By moving dump file to remote server. Create MySQL Dump: mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Copy SQL file to remote MySQL Server:

scp DB_TO_BE_COPIED.sql username@remote-machin:/path/to/copy

Login to remote-machin.

Load MySQL Dump:

mysql -uroot -ppassword DB_TO_BE_CREATED < /path/to/copy/DB_TO_BE_COPIED.sql

Here, I haven’t considered copying and moving data-files for MyISAM databases as these are just the commands-way.

27 Apr

Google:Earth View on Google maps

When have you lastly visited Google Maps? Google have now Introduced Google Earth on Google Maps. Now using Browser you can  see 3D maps online; they named it “Earth View”.

Till now we were just looking at flat terrains but after installing the Earth View Plugin, you will be able to get actual 3D view. You don’t need to install the Google Earth Software, this is simple browser plugin that will do the trick.

You can see the places like Pyramids of Egypt, the Taj Mahal, Eiffel, the Great Wall of China, the Eiffel Tower and many other favourites in 3D mode.

3D Earth View of Effiel Tower

[ad#ad-2-250x250img]

3D Taj Mahal from Earth View

3D Taj Mahal from Earth View

You just need to download the Google Earth View plugin: http://earth.google.com/plugin/

Enjoy.

08 Apr

MySQL Load Data Infile Syntax Generator Tool Download

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

I have already written the basic how to follow that considering additional cases.

A lot of questions I have seen for loading separated data to MySQL, so here I’ve created a very beginner level excel tool that will allow you to generate the LOAD DATA syntax as per your choices.
Presently sample table name and field/line separators are specified, which you may alter as per your own file.

Load-data-syntax-v1.0

Load-data-syntax-v1.0

Download the sheet here: Load Data

Load Data Syntax:

Options column specifies options that are required for syntaxes generation.
Under Selection column you will choose your option while under Description column, for each Option description is provided.

Using basic knowledge of load data you can quite easily generate the syntax by specifying option, choosing file to be loaded and finally clicking the Syntax button.

Please refer the syntax on MySQL documentation as well.

This is a “early release”, please check for the updates.
If you have any suggestions or bug report please comment / mail.

Thanks.

-- Kedar Vaijanapurkar --