Blog do projektu Open Source JavaHotel

niedziela, 28 marca 2010

Oracle - DB2 migration

Problem
Assume that for very important reason we have to keep some data as base64 encoded data. This data is very often sent as a part of pure xml file and we strongly believe that keeping this data in this format and thus avoiding converting them every time we improve efficiency of our mission critical application.

It is quite easy in Oracle PL/SQL query language to convert this data to human readable format by using utl_raw and UTL_ENCODE functions.
SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE;

SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE WHERE utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) LIKE '%25%';

But after migrating to DB2 there is a little surprise - there is nothing like UTL_RAW and UTL_ENCODE functions and we have a lot of data in base64 encoded format. How to resolve this issue without costly redesigning our application and converting data once again ?

Solution idea
The solution is very simple - UTFs.

Example
Assume that we have created the following table:
 CREATE TABLE TEST.CUST_ENCODED_TABLE
   (    "CUST_ID" VARCHAR2(45),
        "CUST_ADDRESS" VARCHAR2(200)
   );

CUST_ADDRESS contains customer address in base64 encoded format.

Now populate our table with sample data:

DECLARE

    i NUMBER := 1;
    CUST_ID VARCHAR(100);
    CUST_ADDRESS VARCHAR(200);
    CUST_ENCODED VARCHAR(200);
    SEQ NUMBER;

BEGIN

    LOOP
        i := i+1;
        SEQ := LTrim(TO_CHAR(i,'999'));
        CUST_ID := 'ID'||SEQ;
        CUST_ADDRESS := 'CITY'||SEQ||' '||'ZIP'||SEQ||' '||'ADDRESS'||SEQ;

        CUST_ENCODED := UTL_RAW.cast_to_varchar2(UTL_ENCODE.BASE64_ENCODE(utl_raw.cast_to_raw(CUST_ADDRESS))); 
        dbms_output.put_line(CUST_ID || ':' || CUST_ADDRESS || ':' || CUST_ENCODED);
        INSERT INTO TEST1.CUST_ENCODED_TABLE VALUES(CUST_ID,CUST_ENCODED); 

        EXIT WHEN i>100;

    END LOOP;
END;

Query examples
SELECT CUST_ID,utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE;

SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE WHERE utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) LIKE '%25%';
Migrate to DB2
Now let's migrate our table to db2. You can use IBM Migration Toolkit or accomplish it any other way.

After migration run a query - data are encoded as expected.
 db2 "select * from TEST.CUST_ENCODED_TABLE"

..........
ID87 Q0lUWTg3IFpJUDg3IEFERFJFU1M4Nw==
ID88 Q0lUWTg4IFpJUDg4IEFERFJFU1M4OA==
..........

Solution - UDF
Create and compile following simple java class.
import java.io.IOException;

import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;
import COM.ibm.db2.app.UDF;

public class UTL_ENCODE extends COM.ibm.db2.app.UDF {
   
    public static String BASE64_ENCODE(String in) {
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(in.getBytes());
    }

        public static String BASE64_DECODE (String in) throws IOException {
            BASE64Decoder decoder = new BASE64Decoder();
            byte[] b = decoder.decodeBuffer(in);
            return new String(b);
        }

}
And compile using javac provided together with DB 9.7.
 /opt/ibm/db2/V9.7/java/jdk32/bin/javac UTL_ENCODE.java

Then copy output UTL_ENCODE.class to the /home/db2inst1/sqllib/function directory.
Next step is to create SQL functions.

CREATE OR REPLACE FUNCTION UTL_ENCODE.BASE64_DECODE(VARCHAR(200))
RETURNS varchar(200) EXTERNAL NAME 'UTL_ENCODE!BASE64_DECODE'
FENCED
PARAMETER STYLE JAVA
LANGUAGE JAVA
NO EXTERNAL ACTION ;
Tip: After changing anything in the java code don't forget to run
db2 "CALL SQLJ.REFRESH_CLASSES()"
after copying .class file to functions directory. Otherwise DB2 engine will be using the old version of the function.

Don't forget to grant execute right for just created function.
db2 grant execute on FUNCTION UTL_ENCODE.BASE64_DECODE to /user/
Final
db2 "SELECT CUST_ID,UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) from TEST1.CUST_ENCODED_TABLE"

ID87   CITY87 ZIP87 ADDRESS87
ID88   CITY88 ZIP88 ADDRESS88

SELECT CUST_ID,UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) FROM TEST1.CUST_ENCODED_TABLE WHERE UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) LIKE '%25%';

CUST_ID
--------------------------------------------- -----------
ID25    CITY25 ZIP25 ADDRESS25

poniedziałek, 22 marca 2010

sanctimonious pirate

Very often developers add a lot of assertion or debug code to the program during developing phase but remove it in the production release.

Measure for Measure

LUCIO

    Thou concludest like the sanctimonious pirate that
    went to sea with the Ten Commandments, but scraped
    one out of the table.

Second Gentleman

    'Thou shalt not steal'?

LUCIO

    Ay, that he razed.

poniedziałek, 8 marca 2010

ODBC, 64 bit Linux

Question
ODBC is associated mostly with Windows platform but there exists also a Linux implementation of this API (http://www.unixodbc.org/). I was interested in answering the questions :
  • How it runs on 64 bit Linux platform.
  • Is it possible to develop multi-platform (Windows/Linux) C++ application having access to any data via ODBC driver provided.
How to answer ?

To answer that questions I had to develop a small wrapper around ODBC API. ODBC API is very complicated, I can hardly imagine any program littered with sequences: 

SQLAllocHandle - SQLSetEnvAttr - SQLConnect - SQLExecDirect - SQLFetch ....

To keep stuff as simple as possible I implemented only columns returned as string. I avoided problem with different data types and complicated C++ - SQL data types conversion. There is also nothing about  transactions, stored procedures, code pages, binding parameters etc. But I believe that this interface can be easily extended to meet more demanding requirements.

Solution, simple wrapper around C ODBC
ODBCDrivers.h
  • getODBCDrivers : retrieves list of  ODBC drivers installed.
  • getODBCDataSources : retrieves list of ODBC data sources.
  • class ODBCConnection 
  •    method open: opens connection
  •    method execute : run SQL statements
  •    method close: closes connection
 ODBCDrivers.cpp

In several places it was necessary to put statement like:

#ifdef WIN32
....
#endif
But test cases (look beneath) does not include anything like that, it is hidden behind facade (wrapper).

Test program

To test that interfaces and answer the questions set up above I created a program as a C++ unit test based on googletest framework.

The test cases are very simple:

Verify number of ODBC drivers available on the host.
testdrivers.cpp

Verify number of ODBC data sources available.
testdatasources.cpp

Main test
  1. Create table
  2. Add some tuples having columns like SYMBOL{number} and NAME{number}
  3. Run some tests and verifications.
testconnection.cpp

Execute the test
I run the test on the following platforms:

Linux 64 bit (RedHat)
  • Test program compiled as 64 bit application
  • Database: Oracle 11g
  • ODBC driver: EasySoft. I was unable to run Oracle ODBC driver.
Windows 64 bit (Windows 7)
  • Test program compiled as 64 bit application (using MS Visual Studio 2008)
  • Database: MySql 
  • ODBC driver: MySql
Windows XP (32 bit)
  • Test program compiled as 32 bit application (using MS Visual Studio 2008 Express)
  • Database: DB2
  • ODBC driver: DB2
Linux 32 bit (Ubuntu)
  • Test program compiled as 32 bit application
  • Database: Progress
  • ODBC driver: Progress
Conclusion
So the answer is positive. It runs as expected on 64 bit linux. Also it is possible to have portable C++ code accessing database via ODBC API. This example is very simple but I dare say that this conclusion can be applied also for more serious applications.

    środa, 3 marca 2010

    Legacy code

    A motto of all developers working with legacy code:

    John Keats, "Hyperion"
    “But cannot I create?  
    “Cannot I form? Cannot I fashion forth  
    “Another world, another universe,  
    “To overbear and crumble this to nought?  
    “Where is another chaos? Where?