DB Query Resource Adaptor Guide

$ . <path to TimesTen Client>/bin/ttenv.sh
...
LD_LIBRARY_PATH set to <path to TimesTen library>
...
$ export LD_LIBRARY_PATH=<path to TimesTen library>
$ ./start-rhino.sh

$ telnet localhost 9999
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
help

 COMMANDS:
   q-s [short] - query short code synchronously (using current transaction if one exists)
   q-a [short] - query short code asynchronously (using current transaction if one exists)
   up-s [short] [long] - update short -> long mapping synchronously (starting a transaction if needed)
   up-a [short] [long] - update short -> long mapping asynchronously (starting a transaction if needed)
   p-s [short] [prefix] - execute stored procedure synchronously to extend a mapping with the prefix (starting a transaction if needed)
   p-a [short] [prefix] - execute stored procedure asynchronously to extend a mapping with the prefix (starting a transaction if needed)
   co - commit current transaction
   ro - rollback current transaction
   close - close the telnet connection

q-s 0000
 Short code '0000' maps to long code '11111111111'

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '11111111111'

up-s 0000 66666666666
 Transaction for synchronous requests created: DatabaseTransaction[101.100003,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '66666666666'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '66666666666'
 (There is an open transaction, it must be committed or rolled back)

ro
 Rolled back DatabaseTransaction[101.100003,age=9940ms,conn=DbConnectionHandle[id=4,state=CLOSED,transactional=true,connection=com.timesten.jdbc.ProxyConnection@608148fe]]

q-s 0000
 Short code '0000' maps to long code '11111111111'

up-s 0000 555555555555
 Transaction for synchronous requests created: DatabaseTransaction[101.100005,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '555555555555'
 (There is an open transaction, it must be committed or rolled back)

co
 Committed DatabaseTransaction[101.100005,age=4698ms,conn=DbConnectionHandle[id=6,state=CLOSED,transactional=true,connection=com.timesten.jdbc.ProxyConnection@6cbd7ea6]]

q-s 0000
 Short code '0000' maps to long code '555555555555'

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '555555555555'

up-a 0000 333333333333
 Transactional activity created: DatabaseQueryActivity[101.100008],I
 Submitted UPDATE mappings SET long_code = '333333333333' WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100008],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '555555555555'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100008],P
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

co
 Submitted commit request on transactional activity DatabaseQueryActivity[101.100008],P
 Transaction committed

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '333333333333'

up-a 0000 44444444444
 Transactional activity created: DatabaseQueryActivity[101.100011],I
 Submitted UPDATE mappings SET long_code = '44444444444' WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100011],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '44444444444'
 (There is an open transaction, it must be committed or rolled back)

ro
 Submitted rollback request on transactional activity DatabaseQueryActivity[101.100011],P
 Transaction rolled back

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '333333333333'

p-s 0000 0
 Transaction for synchronous requests created: DatabaseTransaction[101.100013,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '0333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '0333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

ro
 Rolled back DatabaseTransaction[101.100013,age=16806ms,conn=DbConnectionHandle[id=14,state=CLOSED,transactional=true,connection=com.timesten.jdbc.ProxyConnection@441e8828]]

p-a 0000 00
 Transactional activity created: DatabaseQueryActivity[101.100015],I
 Submitted CALL EXTEND_LONG_CODE_WITH_PREFIX(short_code = '0000' and prefix = '00') on activity DatabaseQueryActivity[101.100015],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '00333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100015],P
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '00333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

co
 Submitted commit request on transactional activity DatabaseQueryActivity[101.100015],P
 Transaction committed

q-s 0000
 Short code '0000' maps to long code '00333333333333'

close
 Goodbye!
Connection closed by foreign host.

$ mysql -u root -p < examples/src/com/opencloud/slee/example/dbquery/MySqlTestDB.sql

CREATE DATABASE mappings;
CREATE USER 'appuser' IDENTIFIED BY 'appuser';
GRANT ALL PRIVILEGES ON mappings.* TO 'appuser';
FLUSH PRIVILEGES;
USE mappings;

DROP TABLE IF EXISTS mappings;

CREATE TABLE mappings (
  short_code VARCHAR(10) PRIMARY KEY,
  long_code VARCHAR(64) NOT NULL
);

INSERT INTO mappings (short_code, long_code) VALUES ('0000', '11111111111');
COMMIT;

DROP PROCEDURE IF EXISTS extend_long_code_with_prefix;

DELIMITER //
CREATE PROCEDURE EXTEND_LONG_CODE_WITH_PREFIX(
    OUT p_concat VARCHAR(64),
    IN p_short_code VARCHAR(10),
    IN p_prefix VARCHAR(64))
BEGIN
    SELECT concat(p_prefix, long_code)
      INTO p_concat
      FROM mappings
     WHERE short_code = p_short_code;

    UPDATE mappings
       SET long_code = p_concat
     WHERE short_code = p_short_code;
END //
DELIMITER ;

[ODBC Data Sources]
mappingsCS1=TimesTen 11.2.1 Client Driver
mappingsCS2=TimesTen 11.2.1 Client Driver

[mappingsCS1]
TTC_SERVER=server1
TTC_SERVER_DSN=mappings

[mappingsCS2]
TTC_SERVER=server2
TTC_SERVER_DSN=mappings

DROP TABLE mappings;

CREATE TABLE mappings (
  short_code VARCHAR(10) PRIMARY KEY,
  long_code VARCHAR(64) NOT NULL
);

INSERT INTO mappings (short_code, long_code) VALUES ('0000', '11111111111');
COMMIT;

CREATE OR REPLACE PROCEDURE EXTEND_LONG_CODE_WITH_PREFIX(
    p_concat OUT mappings.long_code%TYPE,
    p_short_code IN mappings.short_code%TYPE,
    p_prefix IN mappings.long_code%TYPE)
AS
BEGIN
    SELECT concat(p_prefix, long_code)
      INTO p_concat
      FROM mappings
     WHERE short_code = p_short_code;

    UPDATE mappings
       SET long_code = p_concat
     WHERE short_code = p_short_code;
END;
/