DB1 Understanding and Leveraging the Latest ODBC and

DB-1: Understanding and Leveraging the Latest ODBC and JDBC Technology What’s new in Open. Edge® 10. 1 A? Rob Steward Director of Software Development

Agenda What’s New in 10. 1 A? § ODBC architecture changes § ODBC Unicode functionality § JDBC architecture changes § JDBC JTA support DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 2 © 2006 Progress Software Corporation

Agenda What’s New in 10. 1 A? § ODBC architecture changes § ODBC Unicode functionality § JDBC architecture changes § JDBC JTA support DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 3 © 2006 Progress Software Corporation

ODBC “Classic” Architecture Application Code Driver Manager ODBC Driver A ODBC Driver B ODBC Driver C Network Lib • Data Protocol • Wire Protocol A B DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology C 4 © 2006 Progress Software Corporation

ODBC Wire Protocol Architecture Application Code Driver Manager Open. Edge Wire Protocol Driver ODBC Driver A ODBC Driver B ODBC Driver C • Higher Performance • No Dependencies • Thin client install • Data Protocol • Wire Protocol A B DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology C 5 © 2006 Progress Software Corporation

Open. Edge Wire Protocol Advantages § Ease of Deployment § Versioning • 10. 1 A will be forward compatible with future versions of the database § Better performance DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 6 © 2006 Progress Software Corporation

How Database Middleware Works 1 Application makes a call select * from employees where location = ‘boston’ 2 Packets sent from driver DBMS SERVER API CALL DRIVER DATABASE NETWORK 3 DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology Packets sent back from DBMS Server 7 © 2006 Progress Software Corporation

Why better performance? 1 Application makes a call API CALL 2 DBMS SERVER Packets sent from driver DRIVER DATABASE NETWORK 3 Only one network read to process all packets. This occurs simultaneously as the DBMS server sends more packets Packets sent back from DBMS Server 3 NETWORK BUFFER different sized packets Private BUFFER SIMULTANEOUS DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 8 © 2006 Progress Software Corporation

ODBC Wire Protocol Availability § New driver available in 10. 1 A! • Compatible with 10. 1 A servers § Platforms • • Windows 98/Me/NT/2000/XP/2003 AIX Solaris HP-UX DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 9 © 2006 Progress Software Corporation

Agenda What’s New in 10. 1 A? § ODBC architecture changes § ODBC Unicode functionality § JDBC architecture changes § JDBC JTA support DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 10 © 2006 Progress Software Corporation

What is Unicode? § One character set standard to rule them all § A single encoding standard to represent § § “all” written characters-glyphs Unicode 4. 0 contains over 96, 000 characters Three Common Flavors • UCS-2 (strictly 2 bytes) • UTF-8 (1 to 4 bytes; preserves ASCII; favored by Unix) • UTF-16 (UCS-2 + surrogates) DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 11 © 2006 Progress Software Corporation

Why Unicode? § Internationalizes your code § Performance – can avoid costly character set translations DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 12 © 2006 Progress Software Corporation

Unicode Support in the ODBC Standard § Unicode support added in version 3. 5 § Spec assumes UCS-2 § Separate API calls for Unicode strings (“W” § § functions) Separate data types for Unicode strings (SQL_C_WCHAR) ODBC Driver Manager masks driver and application differences DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 13 © 2006 Progress Software Corporation

What does Unicode Support Encompass at the ODBC API level? § Arguments to API functions/methods § String parameters § String results DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 14 © 2006 Progress Software Corporation

How does Unicode work in ODBC? § Unicode App vs. non-Unicode app. • “W” functions § Unicode Driver vs. non-Unicode driver. • SQLConnect. W exported? DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 15 © 2006 Progress Software Corporation

How does Unicode work in ODBC? (cont. ) § DM tries to make all possible combinations work. • • ANSI App w/ ANSI driver Unicode App w/ Unicode driver ANSI App w/ Unicode driver Unicode App w/ ANSI driver (obvious limitations) • Unix Only: UTF 8 app w/ UTF-16 driver. • Unix Only: UTF-16 app w/ UTF 8 driver. DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 16 © 2006 Progress Software Corporation

Why are Character Set Conversions Bad? § Degrades performance. § Frequently data is lost (Substituted), or § corrupted during conversions. For example, here’s how the Euro character, “€”, is represented in various character sets: ISO 8859 -1: Cp 1252: UCS-2/UTF-16: UTF-8 : N/A 0 x 80 0 x 20 AC 0 x. E 2 A 2 AC DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 17 © 2006 Progress Software Corporation

When are Conversions Necessary and Why? § Conversions by DM • Non-Unicode apps • Non-Unicode drivers § Conversions by Unicode Driver • Between Unicode and DB codepage for SQL when DB’s codepage is not Unicode • Conversions for parameters and result columns as necessary based on C type and SQL type DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 18 © 2006 Progress Software Corporation

Agenda What’s New in 10. 1 A? § ODBC architecture changes § ODBC Unicode functionality § JDBC architecture changes § JDBC JTA support DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 19 © 2006 Progress Software Corporation

Architecture differences in JDBC drivers The JDBC specification formally defines 4 different types of drivers • Type 1: JDBC-ODBC bridge • Type 2: partial Java™ driver • Type 3: pure Java driver for database middleware • Type 4: pure Java driver for direct-todatabase DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 20 © 2006 Progress Software Corporation

JDBC Architecture – Type 1 • Authored by Data. Direct • Free with JDK • JDBC 2. 0 API • For Prototyping Application Code JDBC/ODBC Bridge ODBC Driver Manager ODBC Driver A ODBC Driver B ODBC Driver C A B C DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 21 © 2006 Progress Software Corporation

JDBC Architecture – Type 2 Application Code • Platform Dependent JDBC Driver A JDBC Driver B JDBC Driver C Network Lib A B C DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 22 © 2006 Progress Software Corporation

JDBC Architecture – Type 4 Application Code Open. Edge 10. 1 A Type 4 Driver • No Client Dependencies • More Platforms • Better Performance JDBC Driver A JDBC Driver B JDBC Driver C A B C DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 23 © 2006 Progress Software Corporation

JDBC Architecture § Open. Edge 10. 1 A Driver • Type 4 • JDBC 3. 0 API • CTS Certified Java Application Code JDBC Driver A JDBC Driver B JDBC Driver C A B C DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 24 © 2006 Progress Software Corporation

Open. Edge Type 4 JDBC Advantages § Ease of Deployment § Versioning • No dependence on native code § No platform dependence • Any J 2 SE 1. 4 or higher § Better performance DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 25 © 2006 Progress Software Corporation

Agenda What’s New in 10. 1 A? § ODBC architecture changes § ODBC Unicode functionality § JDBC architecture changes § JDBC JTA support DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 26 © 2006 Progress Software Corporation

What is JTA? § JTA is the Java Transaction API § JTA specifies standard Java interfaces between a transaction manager and the parties involved in a distributed transaction system: the resource manager, the application server, and the transactional applications. § JTA is based on the XA specification DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 27 © 2006 Progress Software Corporation

Why is JTA important? § All common application servers support XA transactions through JTA and they will use them if necessary and available § If you need to enlist Open. Edge connections in a distributed transaction with some other data source DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 28 © 2006 Progress Software Corporation

How Does JTA work? 1 User. Transaction javax. transaction. User. Transaction Provides the application the ability to control transaction boundaries. It starts a global transaction and associates the transaction with the calling thread. 2 Transaction. Manager javax. transaction. Transaction. Manager Allows the application server to control transaction boundaries on behalf of the application being managed. 3 XAResource javax. transaction. xa. XAResource A Java mapping of the industry standard XA interface. DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 29 © 2006 Progress Software Corporation

A Distributed Transaction with JTA. . . xa. DS = get. Data. Source(); xa. Con = xa. DS. get. XAConnection("jdbc_user", "jdbc_password"); xa. Res = xa. Con. get. XAResource(); con = xa. Con. get. Connection(); stmt = con. create. Statement(); xid = new My. Xid(100, new byte[]{0 x 01}, new byte[]{0 x 02}); DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 30 © 2006 Progress Software Corporation

A Distributed Transaction with JTA xa. Res. start(xid, XAResource. TMNOFLAGS); stmt. execute. Update("insert into test_table values (100)"); xa. Res. end(xid, XAResource. TMSUCCESS); ret = xa. Res. prepare(xid); if (ret == XAResource. XA_OK) { xa. Res. commit(xid, false); }. . . DB-1: Understanding & Leveraging Latest ODBC and JDBC Technology 31 © 2006 Progress Software Corporation