Escape special chars fom String- Mysql May 31, 2008

Class StringEscapeUtils

java.lang.Object
extended by org.apache.commons.lang.StringEscapeUtils

public class StringEscapeUtils
extends java.lang.Object

Escapes and unescapes Strings for Java, Java Script, HTML, XML, and SQL.

escapeJava

public static void escapeJava(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Escapes the characters in a String using Java String rules to a Writer.

A null string input has no effect.

Parameters:
out – Writer to write escaped string into
str – String to escape values in, may be null
Throws:
java.lang.IllegalArgumentException – if the Writer is null
java.io.IOException – if error occurs on underlying Writer
See Also:
escapeJava(java.lang.String)

escapeJavaScript

public static java.lang.String escapeJavaScript(java.lang.String str)

Escapes the characters in a String using JavaScript String rules.

Escapes any values it finds into their JavaScript String form. Deals correctly with quotes and control-chars (tab, backslash, cr, ff, etc.)

So a tab becomes the characters ‘\\’ and ‘t’.

The only difference between Java strings and JavaScript strings is that in JavaScript, a single quote must be escaped.

Example:

input string: He didn’t say, “Stop!”
output string: He didn\’t say, \”Stop!\”

Parameters:
str – String to escape values in, may be null
Returns:
String with escaped values, null if null string input

escapeJavaScript

public static void escapeJavaScript(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Escapes the characters in a String using JavaScript String rules to a Writer.

A null string input has no effect.

Parameters:
out – Writer to write escaped string into
str – String to escape values in, may be null
Throws:
java.lang.IllegalArgumentException – if the Writer is null
java.io.IOException – if error occurs on underlying Writer
See Also:
escapeJavaScript(java.lang.String)

unescapeJava

public static java.lang.String unescapeJava(java.lang.String str)

Unescapes any Java literals found in the String. For example, it will turn a sequence of ‘\’ and ‘n’ into a newline character, unless the ‘\’ is preceded by another ‘\’.

Parameters:
str – the String to unescape, may be null
Returns:
a new unescaped String, null if null string input

unescapeJava

public static void unescapeJava(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Unescapes any Java literals found in the String to a Writer.

For example, it will turn a sequence of ‘\’ and ‘n’ into a newline character, unless the ‘\’ is preceded by another ‘\’.

A null string input has no effect.

Parameters:
out – the Writer used to output unescaped characters
str – the String to unescape, may be null
Throws:
java.lang.IllegalArgumentException – if the Writer is null
java.io.IOException – if error occurs on underlying Writer

unescapeJavaScript

public static java.lang.String unescapeJavaScript(java.lang.String str)

Unescapes any JavaScript literals found in the String.

For example, it will turn a sequence of ‘\’ and ‘n’ into a newline character, unless the ‘\’ is preceded by another ‘\’.

Parameters:
str – the String to unescape, may be null
Returns:
A new unescaped String, null if null string input
See Also:
unescapeJava(String)

unescapeJavaScript

public static void unescapeJavaScript(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Unescapes any JavaScript literals found in the String to a Writer.

For example, it will turn a sequence of ‘\’ and ‘n’ into a newline character, unless the ‘\’ is preceded by another ‘\’.

A null string input has no effect.

Parameters:
out – the Writer used to output unescaped characters
str – the String to unescape, may be null
Throws:
java.lang.IllegalArgumentException – if the Writer is null
java.io.IOException – if error occurs on underlying Writer
See Also:
unescapeJava(Writer,String)

escapeHtml

public static java.lang.String escapeHtml(java.lang.String str)

Escapes the characters in a String using HTML entities.

For example:

“bread” & “butter”
becomes:

“bread” & “butter”.

Supports all known HTML 4.0 entities, including funky accents. Note that the commonly used apostrophe escape character (‘) is not a legal entity and so is not supported).

Parameters:
str – the String to escape, may be null
Returns:
a new escaped String, null if null string input
See Also:
unescapeHtml(String), ISO Entities, HTML 3.2 Character Entities for ISO Latin-1, HTML 4.0 Character entity references, HTML 4.01 Character References, HTML 4.01 Code positions

escapeHtml

public static void escapeHtml(java.io.Writer writer,
java.lang.String string)
throws java.io.IOException

Escapes the characters in a String using HTML entities and writes them to a Writer.

For example:
“bread” & “butter”

becomes:
“bread” & “butter”.

Supports all known HTML 4.0 entities, including funky accents. Note that the commonly used apostrophe escape character (‘) is not a legal entity and so is not supported).

Parameters:
writer – the writer receiving the escaped string, not null
string – the String to escape, may be null
Throws:
java.lang.IllegalArgumentException – if the writer is null
java.io.IOException – when Writer passed throws the exception from calls to the Writer.write(int) methods.
See Also:
escapeHtml(String), unescapeHtml(String), ISO Entities, HTML 3.2 Character Entities for ISO Latin-1, HTML 4.0 Character entity references, HTML 4.01 Character References, HTML 4.01 Code positions

unescapeHtml

public static java.lang.String unescapeHtml(java.lang.String str)

Unescapes a string containing entity escapes to a string containing the actual Unicode characters corresponding to the escapes. Supports HTML 4.0 entities.

For example, the string “” will become “”

If an entity is unrecognized, it is left alone, and inserted verbatim into the result string. e.g. “>&zzzz;x” will become “>&zzzz;x”.

Parameters:
str – the String to unescape, may be null
Returns:
a new unescaped String, null if null string input
See Also:
escapeHtml(Writer, String)

unescapeHtml

public static void unescapeHtml(java.io.Writer writer,
java.lang.String string)
throws java.io.IOException

Unescapes a string containing entity escapes to a string containing the actual Unicode characters corresponding to the escapes. Supports HTML 4.0 entities.

For example, the string “” will become “”

If an entity is unrecognized, it is left alone, and inserted verbatim into the result string. e.g. “>&zzzz;x” will become “>&zzzz;x”.

Parameters:
writer – the writer receiving the unescaped string, not null
string – the String to unescape, may be null
Throws:
java.lang.IllegalArgumentException – if the writer is null
java.io.IOException – if an IOException occurs
See Also:
escapeHtml(String)

escapeXml

public static void escapeXml(java.io.Writer writer,
java.lang.String str)
throws java.io.IOException

Escapes the characters in a String using XML entities.

For example: “bread” & “butter” => “bread” & “butter”.

Supports only the five basic XML entities (gt, lt, quot, amp, apos). Does not support DTDs or external entities.

Note that unicode characters greater than 0×7f are currently escaped to their numerical \\u equivalent. This may change in future releases.

Parameters:
writer – the writer receiving the unescaped string, not null
str – the String to escape, may be null
Throws:
java.lang.IllegalArgumentException – if the writer is null
java.io.IOException – if there is a problem writing
See Also:
unescapeXml(java.lang.String)

escapeXml

public static java.lang.String escapeXml(java.lang.String str)

Escapes the characters in a String using XML entities.

For example: “bread” & “butter” => “bread” & “butter”.

Supports only the five basic XML entities (gt, lt, quot, amp, apos). Does not support DTDs or external entities.

Note that unicode characters greater than 0×7f are currently escaped to their numerical \\u equivalent. This may change in future releases.

Parameters:
str – the String to escape, may be null
Returns:
a new escaped String, null if null string input
See Also:
unescapeXml(java.lang.String)

unescapeXml

public static void unescapeXml(java.io.Writer writer,
java.lang.String str)
throws java.io.IOException

Unescapes a string containing XML entity escapes to a string containing the actual Unicode characters corresponding to the escapes.

Supports only the five basic XML entities (gt, lt, quot, amp, apos). Does not support DTDs or external entities.

Note that numerical \\u unicode codes are unescaped to their respective unicode characters. This may change in future releases.

Parameters:
writer – the writer receiving the unescaped string, not null
str – the String to unescape, may be null
Throws:
java.lang.IllegalArgumentException – if the writer is null
java.io.IOException – if there is a problem writing
See Also:
escapeXml(String)

unescapeXml

public static java.lang.String unescapeXml(java.lang.String str)

Unescapes a string containing XML entity escapes to a string containing the actual Unicode characters corresponding to the escapes.

Supports only the five basic XML entities (gt, lt, quot, amp, apos). Does not support DTDs or external entities.

Note that numerical \\u unicode codes are unescaped to their respective unicode characters. This may change in future releases.

Parameters:
str – the String to unescape, may be null
Returns:
a new unescaped String, null if null string input
See Also:
escapeXml(String)

escapeSql

public static java.lang.String escapeSql(java.lang.String str)

Escapes the characters in a String to be suitable to pass to an SQL query.

For example,

statement.executeQuery(“SELECT * FROM MOVIES WHERE TITLE=’” +
StringEscapeUtils.escapeSql(“McHale’s Navy”) +
“‘”);

At present, this method only turns single-quotes into doubled single-quotes (“McHale’s Navy” => “McHale’’s Navy”). It does not handle the cases of percent (%) or underscore (_) for use in LIKE clauses.
see http://www.jguru.com/faq/view.jsp?EID=8881

Parameters:
str – the string to escape, may be null
Returns:
a new String, escaped for SQL, null if null string input

escapeCsv

public static java.lang.String escapeCsv(java.lang.String str)

Returns a String value for a CSV column enclosed in double quotes, if required.

If the value contains a comma, newline or double quote, then the String value is returned enclosed in double quotes.

Any double quote characters in the value are escaped with another double quote.

If the value does not contain a comma, newline or double quote, then the String value is returned unchanged.
see Wikipedia and RFC 4180.

Parameters:
str – the input CSV column String, may be null
Returns:
the input String, enclosed in double quotes if the value contains a comma, newline or double quote, null if null string input
Since:
2.4

escapeCsv

public static void escapeCsv(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Writes a String value for a CSV column enclosed in double quotes, if required.

If the value contains a comma, newline or double quote, then the String value is written enclosed in double quotes.

Any double quote characters in the value are escaped with another double quote.

If the value does not contain a comma, newline or double quote, then the String value is written unchanged (null values are ignored).
see Wikipedia and RFC 4180.

Parameters:
str – the input CSV column String, may be null
out – Writer to write input string to, enclosed in double quotes if it contains a comma, newline or double quote
Throws:
java.io.IOException – if error occurs on underlying Writer
Since:
2.4

unescapeCsv

public static java.lang.String unescapeCsv(java.lang.String str)

Returns a String value for an unescaped CSV column.

If the value is enclosed in double quotes, and contains a comma, newline or double quote, then quotes are removed.

Any double quote escaped characters (a pair of double quotes) are unescaped to just one double quote.

If the value is not enclosed in double quotes, or is and does not contain a comma, newline or double quote, then the String value is returned unchanged.
see Wikipedia and RFC 4180.

Parameters:
str – the input CSV column String, may be null
Returns:
the input String, with enclosing double quotes removed and embedded double quotes unescaped, null if null string input
Since:
2.4

unescapeCsv

public static void unescapeCsv(java.io.Writer out,
java.lang.String str)
throws java.io.IOException

Returns a String value for an unescaped CSV column.

If the value is enclosed in double quotes, and contains a comma, newline or double quote, then quotes are removed.

Any double quote escaped characters (a pair of double quotes) are unescaped to just one double quote.

If the value is not enclosed in double quotes, or is and does not contain a comma, newline or double quote, then the String value is returned unchanged.
see Wikipedia and RFC 4180.

Parameters:
str – the input CSV column String, may be null
out – Writer to write the input String to, with enclosing double quotes removed and embedded double quotes unescaped, null if null string input
Throws:
java.io.IOException – if error occurs on underlying Writer
Since:
2.4

validate email – Regular expression May 30, 2008

$email = “someone@example.com”;

if(eregi(“^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+
(\.[a-z0-9-]+)*(\.[a-z]{2,3})$”, $email)) {

echo “Valid email address.”;

}

else {

echo “Invalid email address.”;

}

image resize with php May 30, 2008

// This is the temporary file created by PHP
$uploadedfile = $_FILES['photo']['tmp_name'];

// Create an Image from it so we can do the resize
$image = $_FILES['photo'];
$ext = strtolower(substr(strrchr($image['name'], “.”), 1));

if($ext==’jpg’ || $ext==’jpeg’)
{
$src = imagecreatefromjpeg($uploadedfile);
}
else if($ext==’gif’)
{
$src = imagecreatefromgif($uploadedfile);
}
else if($ext==’png’)
{
$src = imagecreatefrompng($uploadedfile);
}
// Capture the original size of the uploaded image
list($width,$height)=getimagesize($uploadedfile);

// For our purposes, I have resized the image to be
// 100 pixels wide, and maintain the original aspect
// ratio. This prevents the image from being “stretched”
// or “squashed”. If you prefer some max width other than
// 100, simply change the $target variable
$target=100;
if ($width > $height)
{
$percentage = ($target / $width);
}
else
{
$percentage = ($target / $height);
}
$newwidth = round($width * $percentage);
$newheight = round($height * $percentage);

$tmp=imagecreatetruecolor($newwidth,$newheight);

// this line actually does the image resizing, copying from the original
// image into the $tmp image
imagecopyresampled($tmp,$src,0,0,0,0,$newwidth,$newheight,$width,$height);

// now write the resized image to disk. I have assumed that you want the
// resized, uploaded image file to reside in the ./images subdirectory.
$filename = “images/”. $_FILES['photo']['name'];
imagejpeg($tmp,$filename,100);

imagedestroy($src);
imagedestroy($tmp); // NOTE: PHP will clean up the temp file it created when the request
// has completed.

return $filename;

To parse a date which may be delimited with slashes, dots, or hyphens: May 28, 2008

<?php
// Delimiters may be slash, dot, or hyphen
$date = “04/30/1973″;
list($month, $day, $year) = split(‘[/.-]‘, $date);
echo “Month: $month; Day: $day; Year: $year
\n”;
?>

PHP: Upload and Resize an Image May 28, 2008

You have created a cool contact directory and you want to allow people to upload their own photos, or you want to create an image repository which you upload images and create thumbnails out of them. Whatever it is, you need to be able to upload images and resize them.

PHP has the ability to upload files such as documents or images using the multipart/form-data protocol, but how do you use this and how do you resize the images after they are uploaded?

What you need:

* PHP installed properly
* *NIX OS such as Linux
* djpeg, cjpeg and pnmscale UNIX utility programs (see below)

This script works only with JPEG images. Also I’ll resize the uploaded image only if it is larger than 250×200. Else, I’ll leave it as it is.

Create Upload Form
First we need to create the form to upload the image. The MAX_FILE_SIZE variable needs to be set to the maximum allowable file size (in bytes) for upload. This is set using a hidden field and for this example will set to 50,000 bytes (approx. 50 kb).

Upload Image:
Click browse to upload a local file

Process Uploaded Data
Next we need to process the uploaded information when it is submitted. I’ll do it all on the same page checking for the REQUEST_METHOD to know if the page was POSTed to.

PHP uploads the file to a temp location on your server (defined in php.ini) It also includes the following:

Variable Name Description
$imgfile temporary filename (pointer)
$imgfile_name original filename
$imgfile_size size of uploaded file
$imgfile_type mime-type of uploaded file

NOTE: imgfile is the name given on the form

Before copying the file, we check that a malicious user is not trying to abuse the script by trying to work on files it should not be, such as /etc/passwd. We do this with the PHP function is_uploaded_file(). More detail about this function is at the PHP.net site.

If is_uploaded_file returns TRUE, copy the file from its temp location to where you want it using the PHP copy() function.

if (is_uploaded_file($imgfile))
{
$newfile = $uploaddir . “/” . $final_filename”;
if (!copy($imgfile, $newfile))
{
// if an error occurs the file could not
// be written, read or possibly does not exist
print “Error Uploading File.”;
exit();
}
}

Re-Sizing the Uploaded Image
To resize the uploaded image we use the pnmscale function which scales images in the PNM format. We use djpeg to convert the JPEG images to PNM, and cjpeg to convert them back. Here’s the code to convert, scale and write out a scaled JPG image.

/*== where storing tmp img file ==*/
$tmpimg = tempnam(“/tmp” “MKPH”);
$newfile = “$uploaddir/scaled.jpg”;

/*== CONVERT IMAGE TO PNM ==*/
if ($ext == “jpg”) { system(“djpeg $imgfile >$tmpimg”); }
else { echo(“Extension Unknown. Please only upload a JPEG image.”); exit(); }

/*== scale image using pnmscale and output using cjpeg ==*/
system(“pnmscale -xy 250 200 $tmpimg | cjpeg -smoo 10 -qual 50 >$newfile”);

The above functions are just the basics of the script. There are a few little things added to complete the script. Such as checking the file extension, handling where to copy the file, and changing the upload filename.

Download the complete Script Source
The directory this script runs in (or the upload directory) must be writable by the server

Things to Look Out For

* UNIX does not like filenames with spaces, though it’s fine in Windows and Mac.

* It would be better to not use the filename submitted but instead use a unique id. For example, “EMPID.jpg” where EMPID is the unique id of the employee’s record.

* Make sure your web server has write access to the upload directory

* Pay attention to file locations and paths. Most of the functions in the script use the full filename path. So a slash at the front is much different than a slash at the front for your web server.

Relevant PHP Functions

On php.net, there are User Contributed Notes for all PHP fuctions. These are incredibly helpful. I highly recommend them.

is_uploaded_file()

Handling file uploads (a simple example)

Getting Required Software

The djpeg and cjpeg programs are provided in the libjpeg library package.

The pnmscale programs are provided in the libgr-progs library package.

Both of these packages are available as RPMs for RedHat-compatible systems and can be found at your local RPM Repository such as RPM Find.

Retrieving Values from Result Sets May 28, 2008

The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.

The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.

The sensitivity of the ResultSet object is determined by one of three different ResultSet types:

1. TYPE_FORWARD_ONLY — The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
2. TYPE_SCROLL_INSENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
3. TYPE_SCROLL_SENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

Now, you’ll see how to send the above SELECT statements from a program written in the Java™ programming language and how you get the results we showed.

JDBC™ returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.

Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

This code is similar to what you have used earlier, except that it adds two arguments to the createStatement method. The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int, the compiler will not complain if you switch the order.

Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

Using the ResultSet Methods

The variable srs, which is an instance of ResultSet, contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices. A ResultSet object maintains a cursor, which points to its current row of data.

When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:

• next() – moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
• previous() – moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
• first() – moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
• last() – moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
• beforeFirst() – positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
• afterLast() – positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
• relative(int rows) – moves the cursor relative to its current position.
• absolute(int row) – positions the cursor on the row-th row of the ResultSet object.

Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom.

Using the getXXX Methods

The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

The getXXX method of the appropriate type retrieves the value in each column. For example, the first column in each row of srs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type FLOAT, and the method for retrieving values of that type is getFloat. The following code accesses the values stored in the current row of srs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.

The method getString is invoked on the ResultSet object srs, so getString retrieves (gets) the value stored in the column COF_NAME in the current row of srs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s.

Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Further, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(
    "SELECT COF_NAME, PRICE FROM COFFEES");
while (srs.next()) {
        String name = srs.getString("COF_NAME");
        float price = srs.getFloat("PRICE");
        System.out.println(name + "     " + price);
}

The output will look something like this:

Colombian     7.99
French_Roast     8.99
Espresso     9.99
Colombian_Decaf     8.99
French_Roast_Decaf     9.99

You can process all of the rows is srs going backward, but to do this, the cursor must start out located after the last row. You can move the cursor explicitly to the position after the last row with the method afterLast. Then the method previous() moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where the method previous() returns false .

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                         ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
    srs.afterLast();
    while (srs.previous()) {
        String name = srs.getString("COF_NAME");
        float price = srs.getFloat("PRICE");
        System.out.println(name + "     " + price);
    }

The printout will look similar to this:

French_Roast_Decaf     9.99
Colombian_Decaf     8.99
Espresso     9.99
French_Roast     8.99
Colombian     7.99

As you can see, the printout for each has the same values, but the rows are in the opposite order.

The situation is similar with the method getFloat except that it retrieves the value stored in the column PRICE, which is an SQL FLOAT, and converts it to a Java float before assigning it to the variable n.

JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:

String s = srs.getString(1);
float n = srs.getFloat(2);

The first line of code gets the value in the first column of the current row of rs (column COF_NAME), converts it to a Java String object, and assigns it to s. The second line of code gets the value stored in the second column of the current row of rs , converts it to a Java float, and assigns it to n. Note that the column number refers to the column number in the result set, not in the original table.

You can move the cursor to a particular row in a ResultSet object. The methods first, last, beforeFirst, and afterLast move the cursor to the row indicated in their names. The method absolute will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling absolute(1) puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling absolute(-1) puts the cursor on the last row. The following line of code moves the cursor to the fourth row of srs:

srs.absolute(4);

If srs has 500 rows, the following line of code moves the cursor to row 497:

 
srs.absolute(-4);

Three methods move the cursor to a position relative to its current position. As you have seen, the method next moves the cursor forward one row, and the method previous moves the cursor backward one row. With the method relative, you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:

srs.absolute(4); // cursor is on the fourth row
. . .
srs.relative(-3); // cursor is on the first row
. . .
srs.relative(2); // cursor is on the third row

The method getRow lets you check the number of the row where the cursor is positioned. For example, you can use getRow to verify the current position of the cursor in the previous example as follows:

 
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3

Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: isFirst, isLast, isBeforeFirst, isAfterLast. These methods all return a boolean and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method previous in a while loop. If the method isAfterLast returns false, the cursor is not after the last row, so the method afterLast is invoked. This guarantees that the cursor will be after the last row and that using the method previous in the while loop will cover every row in srs.

if (srs.isAfterLast() == false) {
        srs.afterLast();
}
while (srs.previous()) {
        String name = srs.getString("COF_NAME");
        float price = srs.getFloat("PRICE");
        System.out.println(name + "     " + price);
}

In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.

JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR. The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE , TIME, or TIMESTAMP.

Common SQL Commands May 28, 2008

SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.

A list of the more common DML commands follows:

• SELECT — used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
• INSERT — adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
• DELETE — removes a specified row or set of rows from a table
• UPDATE — changes an existing value in a column or group of columns in a table

The more common DDL commands follow:

• CREATE TABLE — creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
• DROP TABLE — deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
• ALTER TABLE — adds or removes a column from a table. It also adds or drops table constraints and alters column attributes

JDBC Introduction May 28, 2008

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.

JDBC helps you to write java applications that manage these three programming activities:

1. Connect to a data source, like a database
2. Send queries and update statements to the database
3. Retrieve and process the results received from the database in answer to your queryThe following simple code fragment gives a simple example of these three steps:

   Connection con = DriverManager.getConnection
              ( "jdbc:myDriver:wombat", "myLogin","myPassword");
   
   Statement stmt = con.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
   while (rs.next()) {
   	int x = rs.getInt("a");
   	String s = rs.getString("b");
   	float f = rs.getFloat("c");
   	}

   This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It’s that simple.

JDBC Product Components

JDBC includes four components:

1. The JDBC API — The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.

   The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.

2. JDBC Driver Manager — The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.

   The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.

3. JDBC Test Suite — The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
4. JDBC-ODBC Bridge — The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

This Trail uses the first two of these these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test Relational Database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.

How can we encrypt the username and password using PHP? May 28, 2008

You can use the MySQL PASSWORD() function to encrypt username and password. For example,
INSERT into user (password, …) VALUES (PASSWORD($password”)), …);

How can we know the number of days between two given dates using PHP? May 28, 2008

$date1 = date(’Y-m-d’);
$date2 = ‘2006-07-01′;
$days = (strtotime($date1) – strtotime($date2)) / (60 * 60 * 24);
echo “Number of days since ‘2006-07-01′: $days”;