Subselects in database queries

While inserting records into a table A, if a need arises to fill a column with value from a table B, then use the following sql

insert into "CLASSICCARS"."EMPLOYEE"
(
"EMPLOYEENUMBER",
"OFFICECODE"

)
values
(
1703,
(select max(officecode) from classiccars.OFFICE)
)

Helpful Unix commands - Part 2

- When shell script complains of ^M character which is a windows character
sh: /opt/tmp/testPerson.sh: /usr/bin/ksh^M: bad interpreter: No such file or directory

Reason is there may be DOS linefeeds in your file. Convert the file to Unix linefeeds, either by copying the file using ASCII mode FTP, or one of:

tr -d '\r' < script > script.new

perl -pi -e 's/\r$//;' script

dos2unix < script > script.new



-To Change permissions
chmod 775 <>
chmod -R 775 <> [for recursive]


-To Move Files from One server to another
scp username@servername:/opt/tmp/filename filename


To connect to B server from A server
From A server type
ssh username@serverB


-To check if a directory exist

if [ -d mydir ]
then
echo "Directory found"
else
echo "Directory not found"
fi


-To check if a file exists

#(-f === if file exists)
if test -f $TMP;
then
if test ! -w $TMP ;
then
printf the temp file $TMP exists and cannot be overwritten aborting >&2
exit 192
fi
fi


- To copy a file
cp -p <> <>
- To copy a directory
cp -p -R <> <>

- To remove a file
rm <>
- To remove a directory
rm -d -R <,filename>>


- To find out if perl or bash or sh file exists
which perl
which bash
which sh


- To find out the version of linux
cat /etc/redhat-release

- To change group name of a file
chgrp <> <>

Pancakes

Ingredients:
2 cups plain flour
1.5 or 2 cups milk
2 eggs
2 tablespoon sugar
3 teaspoon baking soda
60g melted and cooled butter or ghee
some more butter or ghee for cooking

Serves: about 8 pancakes

Procedure:
Mix the four and sugar together in a vessel
In a bowl, mix the milk, eggs, baking soda and melted butter or ghee
Make a well in the flour. Gradually add the milk mixture to the well in flour and keep mixing to form a smooth batter
Keep aside the batter for about 10 mins

In a pan, add some butter or ghee and spread it nicely
Keep the flame in low heat
Pour the batter to the pan in small circles.
When bubbles start to appear on top, toss the pancake and cook the other side
Pancakes ready!
Serve hot. Can also be served with golden syrup or coconul milk + sugar

What Kevin says...

6 yrs

kevin: can you put the lumpic channel on tv
translated as: can you put the Olympic channel on tv

kevin: don't change the channel, its just the abatisan
translated as: don't change the channel, its just the advertisement

kevin: they are singing the national anshum on tv
translated as: they are singing the national anthem on tv

kevin: i accidentally delicate the picture
translated as: i accidentally delete the picture

Querying a date column in database

To query a date column, use

select * from person_table where date_of_birth >= to_date('01/01/2000', 'dd/MM/yyyy')

Querying date with java and sql

Two ways to query a date:
1. by formatting and querying as a string
2. by to_date sql function

public List getPersonList(Timestamp startDate, Timestamp endDate)
{
String startDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);
String endDateStr = new SimpleDateFormat("yyyy-MM-dd hh24:mm:ss").format(startDate);

List toReturn = new ArrayList();
StringBuffer personQuery = new StringBuffer();
personQuery.append("select user from User as user ");

if(startDate != null && endDate != null)
{
registrationGrowthQuery.append(" where activationSentTime >= '" + startDateStr + "' and activationSentTime <= '" + endDateStr + "'");

registrationGrowthQuery.append(" where activationSentTime >= to_date("+startDate+", 'yyyy-MM-dd hh24:mm:ss') and activationSentTime <= to_date("+endDate+", 'yyyy-MM-dd hh24:mm:ss')");
}

Google Map Example

Create a div with id = googleMap

<tr>
<td colspan=2 >
<table border=1>
<tr>
<td>
<div id="googleMap" style="width: 550px; height: 450px"></div>
</td>
</tr>
</table>
<td colspan=1 class="sidebar">
</td>
</tr>

Send the AJAX call to the server to retrieve map data

var request = GXmlHttp.create();
request.open("GET", "http://myserver:8080/MapServlet?method=getGoogleMap", true);

// the getGoogleMap method should be implemented on the server side to retrieve the lat and lng from database and populate the result as an XML and return back to front end

request.onreadystatechange = function()
{
if (request.readyState == 4)
{
if (request.status == 200)
{
var response = request.responseText;
if (response == '<%= MapServlet.ERROR_NO_DATA %>')
{
alert('<bean:message key="errors.map.notInDatabase"/>');
}
else
{
var xmlDoc = GXml.parse(response);
// obtain the array of markers and loop through it
var markers = xmlDoc.documentElement.getElementsByTagName("marker");

for (var i = 0; i < markers.length; i++)
{

var lat = parseFloat(GXml.value(markers[i].getElementsByTagName("lat")[0]));
var lng = parseFloat(GXml.value(markers[i].getElementsByTagName("lng")[0]));
var point = new GLatLng(lat, lng);
var html = GXml.value(markers[i].getElementsByTagName("stnName")[0]);

createMarker(point, html);
}
}
}


function createMarker(point, html)
{
var map = new GMap2(document.getElementById("map"));
map.setCenter(point, 3);
var marker = createInfoMarker(gLatLng, html);
map.addOverlay(marker);

GEvent.addListener(marker, "click", function()
{
marker.openInfoWindowHtml(html);
});
}

Sample XSD for creating the above map xml

<xs:element name="MapObject">
<xs:complexType>
<xs:sequence>
<xs:element name="marker" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="lat" type="xs:string" />
<xs:element name="lng" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>