Help Guide For Software Engineer

Thursday, January 6, 2011

Mysql data unload

Use the following SQL to unload data from Mysql server without executing command. This method is good for very large set of data.

select * from table into outfile "file.txt"

Note :- file.txt will be created at"MySQL/data/database" directory.

Thursday, December 16, 2010

Mysql triggers are like schedulers run on table changes. When INSERT, DELETE, UPDATE command executions, these triggers are executed automatically.

Below is a sample of SQL commands to create and test Triggers when insert happens to "test1" table.

Create below four tables and insert values into "test3" & "test4" tables.

Trigger

This Trigger is executed when insert happens into "test1" table. It gets "test1.a1" value and insert it into "test2" table. And, delete it from "test3" table. Update "test4" b4 value.

Create Tables

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

b4 INT DEFAULT 0

);

Create Trigger

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END;

|

Insertions

delimiter ;

INSERT INTO test3 (a3) VALUES

(NULL), (NULL), (NULL), (NULL), (NULL),

(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Executes following SQL to run the Trigger.

INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

Monday, November 15, 2010

IReport dynamic table sql

In IReport tool, you can dynamically generate SQL by changing table name.

1) Create parameter for table name. Ex :- $P{tableName}
2) Change IReport SQL as following.

      SELECT * FROM $P!{tableName}

Wednesday, November 10, 2010

limit Resultset size in informix

Use the following SQL to limit the size of the result set in informix.

SELECT FIRST 1 * FROM orders

Tuesday, October 26, 2010

Get map for latitude & longitude using Google Maps API

Google provides a FREE Java Script map API for longitude & latitude. Simple demonstration is given below. Position marker, mouse wheel zooming, Map view/Satelite view/Hybrid view controls are enabled in the example.

NOTE :- Map searching is commented.

<html>

<head>

<script type="text/javascript"

src="http://www.google.com/jsapi?key=ABCDEFG"></script>

<script type="text/javascript">

google.load("maps", "2");

//  google.load("search", "1");

// Call this function when the page has been loaded

function initialize() {

var map = new google.maps.Map2(document.getElementById("map"));

map.setCenter(new google.maps.LatLng(7, 80), 13);

map.enableRotation();

map.addControl(new GOverviewMapControl());

map.enableDoubleClickZoom();

map.enableScrollWheelZoom();

map.addControl(new GMapTypeControl());

map.addControl(new GSmallMapControl());

var marker = new GMarker(new GLatLng(7, 80));

map.addOverlay(marker);

map.setMapType(G_SATELLITE_MAP);

//        var searchControl = new google.search.SearchControl();

//        searchControl.addSearcher(new google.search.WebSearch());

//        searchControl.addSearcher(new google.search.NewsSearch());

//        searchControl.draw(document.getElementById("searchcontrol"));

}

google.setOnLoadCallback(initialize);

</script>

</head>

<body>

<div id="map" style="width: 500px; height: 500px"></div>

<!-- <div id="searchcontrol"></div> -->

</body>

</html>

Reference :- http://code.google.com/apis/ajax/documentation/ 

Tuesday, October 12, 2010

Shell Script to load data from a TableA to TableB in mysql

Suppose you have to select custom fields in TableA and insert those fields into TableB in mysql, then the following shell script command would be appropriate.

mysql -u user -p -D database -e 'insert into TableB select id, addr from TableA;'

Insert above code in a file and save it as ".sh" file extension. Then run it as "./Test.sh".

NOTE :- Test.sh must be stored in the server that mysql db server is running.

Thursday, October 7, 2010

Remove non empty directory in linux

Use the below command to remove non empty directory in Linux without prompting for verification "yes" or "no".

rm -rf directory

This will remove all the contents in the directory without asking further verifications for files deletion.