« February 2007 | Main | April 2009 »

March 20, 2009

Handle null values in MySQL Query

- Jagadish. M


The following examples facilitate in handling null values in MySQL query itself instead of handling them in jsp pages.
                 
    
Syntax:
               
Select IFNULL (exp1,exp2) , IFNULL (exp1,exp2) from tableName
                IFNULL return exp1 if it is not null
                Else
                If exp1 is null then returns exp2
      

 Example 1:
Table_1

          In Interface it looks like this:

Interface_1

 

Select IFNULL(Name,’ Not Available ’),IFNULL(Address,’Not Available  ’),IFNULL(emailed,’Not Available’) from tableName

           Result:

Table_2

 

 

 

           In Interface it looks like this:

Interface_2

 

    Example 2:


          Select IFNULL(Name,’  ’),IFNULL(Address,’  ’),IFNULL(emailed,’  ’) from tableName

          Result:

 Table_3

         In Interface it looks like this:

 

Interface_3

 

(The author, Jagadish.M, is a Software Engineer at Binary Spectrum).

Posted by Ratika at 12:34 AM | | Comments (0) | TrackBacks (0)

March 17, 2009

Google Protocol Buffer faster than XML

- Jagadish. M

Do you think XML is the only fastest way for serializing the structured data?
Then go ahead and read this. I am sure you will have a different opinion at the end of this discussion.


In 2001 Google had developed its own data format, called Protocol Buffers. Google’s documentation says that, “protocol buffers were initially developed at Google to deal with an index server request/response protocol”.


Google is using Protocol Buffers for its internal Remote Procedure Calls, or RPC, protocols and file formats.


Using Google protocol buffer you define how you want your data to be structured once, then you can use special generated source code to simply write and read your structured data to and from a variety of data streams and using a variety of languages.


Google protocol buffer is 3 to 10 times smaller and 20 to 100 times faster than XML for serializing structured data.


See the Google Developer Guide:
Protocol Buffer Basics: Java

(The author,  Jagadish. M, is a Software Engineer at Binary Spectrum)

Posted by Ratika at 01:33 AM | | Comments (1) | TrackBacks (0)

March 16, 2009

Fixing the Error: “ASN1 bad tag value met”!

-Tauseef. M

For some files in your system that are not digitally signed, an error message ASN1 bad tag value met would appear when you install any new software or any critical updates for Win XP.

To fix this error, you can follow the steps given below, after which you will be able to download correct critical updates for WinXP, etc and your problem will be solved!

  1. Basic step: Click START->RUN

  2. Type SIGVERIF

  3. Click ADVANCED -> then inside the SEARCH tab

  4. Click on Look for other files

  5. Then Down check the check box (Include Subfolders). Click on browse button. Then under the Windows Folder -> Select SYSTEM32 ->CatRoot.  Then click ok button. Then click again ok in the SEARCH tab. Then click START on File Signature Verification.

  6. After scanning you will get a list of files that have not been digitally signed. Now without closing this window, create a Folder name TEMP into your desktop.

NEXT FOLLOW THESE STEPS:

  1. Go to start-> run and type C:\WINDOWS\SYSTEM32\CATROOT

  2. Now search the list of files that are not digitally signed by comparing the files that you got in the previous step 6

  3. Now select those files from C:\WINDOWS\SYSTEM32\CATROOT and cut and paste into the TEMP folder created into your desktop

  4. THUS “ASN1 bad tag value met “error is fixed

  5. Now delete the TEMP folder from desktop.

 

(The author, Tauseef. M, is a Software Engineer at Binary Spectrum.)

Posted by Ratika at 07:18 PM | | Comments (2) | TrackBacks (0)

March 06, 2009

Securing Password in MySQL

- Jagadish.M

Storing plaint text password in the database is never a good idea. Fortunately MySQL provides you with some options to protect passwords.

MySQL provide following built-in functions do that:

1. SHA1: Secure Hash Algorithm version 1.0

2. SHA functions

3. MD5: Message Digest Algorithm 5

4. RFC 1321: The Message Digest Algorithm

5. AES: Advanced Encryption Standard

I will disscuss on AES over here,

To implement AES (Advanced Encryption Standard) encryption in your database, you will need to specify the password field as BLOB data type.

Syntax:
  mysql> INSERT INTO TableName(field1,field2) VALUES ('fieldValue1',AES_ENCRYPT('fieldValue2','my_keyword_to_encrypt') );

 

Note: - fieldValue2 is password to be stored

To create a new record, we have to call AES function and in addition to providing the password to be protected, we will also provide the keyword/String to encrypt.

For example:
mysql> INSERT INTO tbluser (useName, Password) VALUES ('Jagadish', AES_ENCRYPT ('Jagadish123','J007’) );
mysql>select * from tbluser;

                                         

userName 

Password

jagadish

]MÚ¤î¤hÁÉ[*,»

 

 

 

 
Now to authenticate a user, we have to decrypt the password which is stored in the database and compare with the password entered by the user to login with the same keyword/String as we used before. Also we can encrypt the password entered by the user and then compare it with the password stored in the database. And also we have to provide the keyword/String used before.

For example:
mysql> select userName, AES_DECRYPT (password,'J007') from tbluser where userName = 'Testing'       


userName 

Password

jagadish

Jagadish123
 


 

 

(The author Jagdish.M is a Software Engineer at Binary Spectrum.)

Posted by Ratika at 11:56 PM | | Comments (1) | TrackBacks (0)

March 05, 2009

Simple way for Applications to read Excel Workbooks

QUERY DATA FROM EXCEL FILE

-Jayanth Raj

 
If you want to retrieve or upload data from an Excel file having more than one sheet and large number of data elements, it is generally a complicated process to import such data elements into an oracle database.
The following approach is one possible way of simplifying the process of reading data from an excel workbook with multiple sheets.
 
We can retrieve data by writing a simple query like,
 
SELECT [column1.name], [column2.name], [column3.name] FROM
[Sheet1$]
Or
SELECT [column1.name], [column2.name], [column3.name] FROM
[Sheet1$]  WHERE [column1.name] = ‘TestData’
  
Here I have explained how we can achieve this using a simple java code.
 
1.   Create a DSN for Excel file
 

  • Add new DSN
  • Select Microsoft Excel Driver – Driver do Microsoft Excel [*.xls]
  • Provide a Data Source Name 
  • Select Workbook – Specifying the Excel file path

 
2.   Write a java class to retrieve the data from the Excel file
 

  • Load JDBC driver class – use Jdbc Odbc Driver, Eg.

 
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 

  • Create a Connection using Excel DSN URL , Eg.

 
                Connection conn = DriverManager.getConnection("jdbc:odbc:<DSN name>”)
 

  • Create a statement, Eg.  

              
                Statement stmt = con.createStatement();
 

  • Data is retrieved from the Excel sheet using a query mechanism      

        
                Statement stmt = conn.createStatement();
                try {
                    ResultSet rs = stmt.executeQuery( "SELECT [column1.name], [column2.name],                         [column3.name] FROM [Sheet1$] WHERE [column1.name] =  ‘Test’ " );
                    try {
                        while ( rs.next() ) { // Retrieve data  }
                    } finally { rs.close();
                    }
                        } finally { stmt.close();
                      }   
  
(The author Jayanth Raj is a Software Engineer at Binary Spectrum).

 

Posted by Ashok Kumar at 12:33 AM | | Comments (0) | TrackBacks (0)