New version of JSPWiki In The Cloud

I deployed new version of Google App Engine JSPWiki port. Caching mechanism was added (by means of Google App Engine MemCache). Performance improves a little bit but it is not a significant change.
Sources are available on GitHub.
I also moved demo version to http://myjspwiki.appspot.com/ because previous location is infested by spam.

Pro*C to DB2 Embedded SQL migration, vars, structures, SP and UDF

Vars and structs

Pro*C Oracle code

#define SIZEKEY 20

int CalculateSum(float *sum,int keyid,struct retinfo *ret)
{

  char key[SIZEKEY+1];
/* Type equivalence key to the string external datatype.*/
  EXEC SQL VAR key is string(SIZEKEY);
  float sumk;
  int failed = 0;

  sprintf(key,"%u",keyid);

  printf("Calculate sum for %s\n",key);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Calculate sum",&failed);
  
  EXEC SQL select sum(num) into :sumk FROM numhistory WHERE numkey = :key  ;

  if (failed) return 0; 
  
  ret->keyid = keyid;
  EXEC SQL select count(num) into :ret->no FROM numhistory WHERE numkey = :key  ;

  if (failed) return 0; 
  
  *sum = sumk;
  return 1;
}

Two problems require attention here while migrating to DB2.
First

EXEC SQL VAR key is string(SIZEKEY);

DB2 Embedded SQL does not support it. All hosts variables should be enclosed in :

EXEC SQL BEGIN DECLARE SECTION;
 .....
EXEC SQL END DECLARE SECTION;

Another

 EXEC SQL select count(num) into :ret->no FROM numhistory WHERE numkey = :key  ;

DB2 does not support struct member as a host variable. So value should be firstly assigned to simple variable and secondly to struct member. So finally DB2 Embedded SQL code equivalent to Pro*C code

int CalculateSum(float *sum,int keyid,struct retinfo *ret) {
  
  struct sqlca sqlca;
  
EXEC SQL BEGIN DECLARE SECTION;
    char key[21];
    float sumk;
    int no;
EXEC SQL END DECLARE SECTION;
  int error = 0;

  sprintf(key,"%u",keyid);

  printf("Calculate sum for %s\n",key);
  
  EXEC SQL select sum(num) into :sumk FROM numhistory WHERE numkey = :key  ;
  EVALRESULT("Calculate sum");
  if (error) return 0;
  ret->keyid = keyid;
  EXEC SQL select count(num) into :no FROM numhistory WHERE numkey = :key  ;
  EVALRESULT("Calculate num");
  ret->no = no;   
  *sum = sumk;

  return 1;
}

SP calling
Pro*C Oracle code

int CallProc(int *res,char* sou) {
  
  int failed = 0;  
  
  printf("Call SP for %s\n",sou);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Call SP",&failed);
  
  EXEC SQL EXECUTE
  BEGIN
    SIMPLEPROC( :res, to_number( :sou ) );
  END;
  END-EXEC;

  if (failed) return 0; 
  
  return 1;
}

DB2 supports notation EXEC SQL EXECUTE ... END-EXEC after setting "Oracle Compatibile Mode" in db2 prep invocation (example) :

db2 prep $1.sqc bindfile SQLWARN NO COMPATIBILITY_MODE ORA

Another problem is related to

SIMPLEPROC( :res, to_number( :sou ) );

DB2 does not support UDF invocation as SP parameter. So this launching sequence should be split into two : UDF invocation and consecutive SP calling.
DB2 equivalent to Pro*C code

int CallProc(int *res,char* sou) {
  
  int error = 0;  
  struct sqlca sqlca;
  
  EXEC SQL BEGIN DECLARE SECTION;
    char psou[100];
    int number;
    int resp;
  EXEC SQL END DECLARE SECTION;

  printf("Call SP par=%s\n",sou);
  
  strcpy(psou,sou);
  
  EXEC SQL VALUES (TO_NUMBER(:psou)) INTO :number;
  EVALRESULT("Call TO_NUMBER");
  if (error) return 0;

  
  EXEC SQL EXECUTE
  BEGIN
    SIMPLEPROC( :resp, :number );
  END;
  END-EXEC;
  
  EVALRESULT("Call SP");

  if (error) return 0; 
  
  *res = resp;
  
  return 1;

}

UDF calling
Pro*C Oracle code

int CallFunc(int *res, int par) {
  int failed = 0;  
  
  printf("Call Func for %u\n",par);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Call UDF",&failed);
  
  EXEC SQL EXECUTE
  BEGIN
    :res := RETPROC( :par );
  END;
  END-EXEC;
  
  if (failed) return 0; 
  
  return 1;

}

DB2 does not support this launching method even in Compatibility Mode. DB2 Embedded SQL equivalent

int CallFunc(int *res, int par) {
  int error = 0;  
  struct sqlca sqlca;
  
  EXEC SQL BEGIN DECLARE SECTION;
    int resp;
    int parp;
  EXEC SQL END DECLARE SECTION;
  
  printf("Call UDF par=%u\n",par);
  
  parp = par;  
  
  EXEC SQL VALUES (RETPROC(:parp)) INTO :resp;
  EVALRESULT("Call UDF");
  if (error) return 0;
  
  *res = resp;
  
  return 1;
}  

Additional remarks

• Full source code (including makefiles and sql) can be found here.
• Next : BLOB handling

Pro*C to DB2 Embedded SQL migration, connection

Introduction
Embedded SQL is traditional method of database programming but still very robust and effective. All major SQL vendors supports Embedded SQL technology. Unfortunately, although general structure for different Embedded SQL implementation contains a lot of shared elements migration from one vendor to another is not easy. Nevertheless,  "not easy" does not mean impossible, if one recognizes some common pattern then migration is pretty simple and allows keep the main solution logic almost intact.
Pro*C is Embedded SQL implementation for Oracle, just Embedded SQL for DB2. Below I will describe some common problems and solutions found during migrating Oracle C++ Pro*C code to DB2 starting from connecting to and disconnecting from database.
Oracle Compatibility Mode
Starting from version 9.7 DB2 contains Oracle Compatibility Mode (constantly improved and enhanced in subsequent version up to 10.5) making migration of SQL procedure code almost painless. There is also "compatibility mode" for Pro*C. It contains a lot of useful features but most of the problems should be resolved manually.
Sample program
I created a simple program which connects to and disconnects from database the show the first basic problems. The source code is available here.
Compiling, linking, declaration
Implementation for DB2 and Oracle is different. Below are  examples of make files.
Oracle: mk.m
DB2 : mk.m
Additional differences
DB2 requires all host variables (passing data between SQL and C++ code) being declared in a special section. Example:

  EXEC SQL BEGIN DECLARE SECTION;
    char dbAlias[15];
    char user[128 + 1];
    char pswd[15];
  EXEC SQL END DECLARE SECTION;

Also every procedure containing any Embedded SQL code should have a direct struct sql declaration or EXEC SQL INCLUDE SQLCA clause.

void ConnectTo() 
{    
  struct sqlca sqlca;
...
}

Connecting and disconnecting in Oracle
Oracle : Source file

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

#include "../utillib.h"

static char * connection = "test/test@think:1521/testdb";

void sql_error(char *msg,int *failed) 
{ 
    char err_msg[512];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    *failed = 1;
} 


void ConnectTo() 
{    
    int failed = 0;
    printf("Connection string : %s\n",connection);
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
       printf("Not connected\n");
       exit(EXIT_FAILURE);
    }       

    printf("Connected to ORACLE\n"); 
}

void Disconnect() 
{
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL COMMIT WORK RELEASE;
   printf("\nDisconnected");
}

Migration to DB2
The main problem is hidden in :

    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
    ...
    }

In case of any error sql_error routine is called and the routine prints error message and set error variable to 1 just allowing implement a logic for connection failure. This routine can be shared between all SQL invocations and provide common error handling.
Although DB2 Embedded SQL supports declaring custom routine for SQL error handling the routine does not accept any parameter just making direct migration of above code impossible. Passing data using global static variable is very bad programming practice and should be omitted.
The best solution is to use a reusable macro providing common method of error handling.

void SqlInfoPrint(char *appMsg, struct sqlca *pSqlca);

#define EVALRESULT(mess) if (sqlca.sqlcode < 0) { SqlInfoPrint(mess,&sqlca); error = 1; }
    

And DB2 version of Pro*C code above :

EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  EVALRESULT("Database -- connect with userid and password");
  if (error) {
    ...
  }

It requires manual changes but the execution flow is exactly the same. The migrated DB2 code (with implementation of SqlInfoPrint procedure) is here.
Next
Oracle EXEC SQL VAR

OpenLDAP, Ubuntu, TLS

Problem
I installed OpenLDAP on my Ubuntu 14.04 machine. It worked fine until I tried to authenticate from RHEL 6.6 box. RHEL 6.6 LDAP client requires TLS connection and there is not way to persuade it to change its mind. So I enabled OpenLDAP server for TLS connection using advice provided here. But in turn OpenLDAP server refused to restart. In /var/log/syslog file I found enigmatic entry:

Mar 29 00:47:57 sb-ThinkPad-W540 slapd[11071]: connections_destroy: nothing to destroy.
Mar 29 00:47:57 sb-ThinkPad-W540 kernel: [14766.527083] type=1400 audit(1427586477.983:83): 
apparmor="DENIED" operation="open" profile="/usr/sbin/slapd" name="/usr/share/p11-kit/modules/" 
pid=11070 comm="slapd" requested_mask="r" denied_mask="r" fsuid=122 ouid=0

Solution
After browsing I discovered that there exists linux kernel guardian called AppArmor and this Cerberus denies OpenLDAP server access to some configuration directories. But the solution was quite simple. Adding several lines to slapd profile opens this gate and now my LDAP server works as expected
File /etc/apparmor.d/usr.sbin.slapd

/usr/share/p11-kit/modules/ r,
/usr/share/p11-kit/modules/* r,
/usr/lib/x86_64-linux-gnu/pkcs11/ m,
/usr/lib/x86_64-linux-gnu/pkcs11/* m,

SQL Server to DB2 migration, UDF

Introduction
One of the element of any migration is built-in functions conversion. Some of them are the same, some are similar but different and some does not have any direct equivalent and requires additional developing. Freely available http://www.redbooks.ibm.com/redbooks/pdfs/sg246672.pdf ("Function mapping" chapter) contains a good number of examples how to convert SQL Server built-in functions to DB2.
Conversion is quite easy but what to do if we want to get SQL statements migrated to DB2 and keep backward compatibility at the same time.
It is more complicated but also possible. I created a simple project with examples - it is available here.

• SQL Server functions
• SQL Server tests
• DB2 functions (equivalence of SQL Server)
• DB2 tests

Solution for DateAdd, DateDiff and DatePart

The main problem is DateAdd and DateDiff functions. Example:

SELECT DATEADD(month, 1, '2006-08-30');

Unfortunately, this statement cannot be migrated directly to DB2 because DB2 does not support enumeration type. So it is necessary to modify also SQL Server statement to achieve backward compatibility.
Instead of using DATEADD(month ...) function replace it with DATEADD_MM function.

CREATE FUNCTION DATEADD_MM(@mins INT, @da DATETIME)
RETURNS DATETIME
AS 
BEGIN
  RETURN DATEADD(mm,@mins,@da)
END 

and modify the statement (all not built-in UDF in SQL Server should be qualified with schema name).

SELECT dbo.DATEADD_MM(1, '2006-08-30');

DB2 equivalent

CREATE OR REPLACE FUNCTION DBO.DATEADD_MM(IN NOD INT, IN DAT TIMESTAMP)
RETURNS TIMESTAMP
RETURN DAT + NOD MONTHS
@

and DB2 SQL stamement

SELECT dbo.DATEADD_MM(1, '2006-08-30') FROM SYSIBM.DUAL;

After this transformation function dbo.DATEADD_MM can be used any way in the SQL statements executable in SQL Server and DB2 without any modification.
In the files:  DB2 and SQL Server are more examples for DATEADD, DATEDIFF and DATEPART function.
Solution for other built-in scalar functions
Other functions are easy to migrate, just create its DB2 equivalent.
Example:

SELECT SUBSTRING('ABCDEF',2,2)

DB2 equivalence

CREATE OR REPLACE FUNCTION SUBSTRING (IN STR VARCHAR(32672),IN STA INT, IN LEN INT)
RETURNS VARCHAR(32672)
  RETURN SUBSTRING(STR,STA,LEN,CODEUNITS16)
@

More examples:  DB2 and SQL Server
Performance issue
A question can be raised if the performance will not suffer. The question is worth considering because instead of replacing SQL Server function by DB2 equivalent we are adding additional wrapper function to keep backward compatibility. Using additional UDF (particularly in WHERE clause ) can impact performance.  But it is not the case because in most case it is "inline" function or even "source" DB2 function which does not involve any additional function calling.
Conclusion
In most cases it is not a problem to migrate SQL Server build-in scalar function to DB2. Adding some consideration also migrating and keeping backward compatibility is possible.

New version of JavaHotel, taxation

Introduction
I uploaded new version of JavaHotel application. Demo version (U/P user/user) is available here, source code here.
Taxation
While issuing a document (receipt, bill) a taxation is calculated using tax level assigned to the service.

Detailed information related to the document is also available. This information if stored in the database (as XML file) for every document issued.
Taxation information is also exposed in "Stay summary" window providing information about the current status of the stay.

Next step 
Receptionist journal registering the information about user activity.

Ubuntu 14.04 and USB tethering

Introduction
For some reason I was unable to connect to a hotel wi-fi network from my Ubuntu 14.04 desktop. What is more interesting, I connected without any problem my Kindle reader and mobile (Galaxy S2, Android 2.3.6). The hotel receptionist was very kind but also threw up his hand.
Solution
After spending some time googling on my mobile I found the solution and it was extremely simple, connect to the Internet through a mobile using USB tethering.

1. Find a public place with wi-fi connection and install usbip package (yum install usbip)
2. Load vhci-hcd module (modprobe vhci-hcd)
3. Be sure that mobile is connected to wifi network
4. Connect mobile and desktop via USB cable
5. Enable USB Tethering in your mobile (Application -> Setting -> Wireless and network -> Tethering and portable hotsp -> Enable Tethering

And that's final, I have raised from death and connected to the world again.