Ashish Mahajan Senior Software Engineer IAD Tools, Sybase Inc. ashishm@sybase

1. IA368 - PowerBuilder JDBC and Other Database Interfaces for Enterprise Application Server (EAServer) Ashish Mahajan Senior Software Engineer IAD Tools, Sybase Inc. ashishm@sybase.com

2. Introduction to PowerBuilder JDBC interface PB8 db related changes Other PowerBuilder Database interfaces supporting EAServer TransactionServer Service Object EAServer support for 2 phase commit CORBACurrent Service Object Agenda

3. What is the PowerBuilder JDBC Interface ? PowerBuilder JDBC Interface + = • Designed to load, and make calls to any Java JDBC compliant database driver • Sybase jConnect for Adaptive Server Enterprise and Adaptive Server Anywhere • Oracle, Informix and IBM DB2

4. Standard Java Database Connectivity API defined by Sun Concepts “borrowed” from Microsoft’s ODBC Smaller and easier to implement than ODBC What is JDBCTM? continued...

5. What is JDBCTM? • Set of classes and interfaces in the java.sql Package: • Interfaces like Connection, Statement, Result Set, Database MetaData • Classes like DriverManager, Type, Date, Time. TimeStamp

6. Why a JDBC Interface ? • Support for PowerBuilder/PowerJ EAServer components sharing the same EAServer Transaction • DataWindow ActiveX • Development vs. Runtime consistency for Java DataWindow • Traditional PowerBuilder client/server support • Thread Safety issues in EAServer

7. In PowerBuilder 7.0 release pbjds70.dll, pbjdm70.dll classes.zip com.sybase.powerbuilder.jdbc java package In PowerBuilder 8.0 Maui release pbjdb80.dll pbjds80.dll*, pbjdm80.dll* (Deprecated) pbjdbc11.jar, pbjdbc12.jar com.sybase.powerbuilder.jdbc java package What constitutes the PB JDBC Interface ?

8. The third-party JDBC driver is 100% Java byte code This requires a Java Virtual Machine to run this code The two most popular VMs support different APIs* to invoke Java methods Therefore we have provided : SQLCA.DBMS = “JDS” for Sun VM (Sun JDK or Java Runtime Environment JRE 1.1.7B) SQLCA.DBMS = “JDM” for Microsoft VM (Microsoft SDK for Java Version 3.1) Why so many DLLs ? continued...

9. In PowerBuilder 8.0 Maui and later releases SQLCA.DBMS = “JDB” for Sun VM (JRE 1.1 and JRE 1.2) and Microsoft VM Future Direction Supported Interface “JDB” End of Life Interfaces “JDS” and “JDM” Why so many DLLs ?

10. PBJDSxx.DLL uses Sun Java Native Interface (JNI) included in JDK 1.1.8 PBJDMxx.DLL uses Microsoft Raw Native Interface (RNI) included in Microsoft SDK for Java Version 3.1 PBJDBxx.DLL uses both mechanisms (JNI and RNI) depending on the DBPARM JavaVM or EAServer setting How do PowerBuilder JDBC Interface DLLs make calls to Java ?

11. DB PowerBuilder JDBC Interface Architecture PB (Application) Database PBVM PBVMnn.DLL PBJDBC Interface PBJDxnn.DLL 100% Java JDBC Driver jConnect JavaVM PBJDBC Java Classes zip/jar file

12. Register the interface - “regsvr32 pbjdb80.dll” (pb7 - Check the DBMS entries in PB.INI) Check CLASSPATH Check PATH Create a new Database Profile Connect... Getting Started...

13. Database Profile Setup • Min. parameters required: • Profile Name • Driver Name • URL • Login ID • Password • JavaVM (JDB - Interface)

14. Database Profile Setup

15. Specify Driver Name, URL Specify Driver Name, URL, Driver-Specific Properties Other ways to connect

16. For Example: com.sybase.jdbc.SybDriver oracle.jdbc.driver.OracleDriver First 3 tokens are usually the Java package name, and fourth the Java class name The package usually corresponds to a directory structure, either zipped or unzipped, containing multiple Java class files Driver Name Syntax

17. For Example: jdbc:sybase:Tds:199.93.178.151:5007/tsdata jdbc:oracle:thin:@ora80nt:1521:orcl Usually jdbc, followed by :vendor, followed by :driver-type or protocol, followed by :server name/IP Address, followed by :port, followed by / or :database URL Syntax

18. May be omitted If included in the URL DBPARM: jdbc:oracle:thin:system/manager@ora80nt:1521:orcl If included in the Properties DBPARM user=dba;password=sql;SQLINITSTRING=set textsize 500000; Login ID & Password

19. Properties DBPARM Translates to the Java Properties class If any properties are specified, Login ID & Password have to be entered here, or in the URL Read the vendor’s documentation for a list of Properties supported by the vendor Driver-Specific Parameters

20. Isolation Level AutoCommit Mode Table Criteria Disable Bind Static Bind Trim Trailing Spaces in CHAR data Login Timeout Other Properties and DBPARMS

21. Both are generic* interfaces Both rely on built in DDL Syntax ODBC on pbodbxx.ini JDBC on built in and NT registry Both compile, sort, present, and use a list of data types that are native to the back-end to emulate as much as possible the behavior of a native interface PB JDBC vs PB ODBC - I

22. Comparable performance/speed C++ to Java overhead seems unnoticeable Near-zero installation or Data Source administration on the client No threading issues in EAServer PB JDBC vs PB ODBC - II

23. Modify NT registry to override DDL Syntax Properties/Default settings etc egreg.txt provided as an example Order of Preference URL Driver Name DBMS Name Overriding the internal registry

24. Previously, the DataWindow ActiveX used to link with the PB ODBC interface, making an ODBC installation/Data Source administration necessary on the client The PB JDM interface replaces this linkage, and since third party JDBC driver can be downloaded along with the ActiveX in CAB files, the client requires no installation or Data Source administration DataWindow ActiveX Support

25. DB DataWindow ActiveX Mechanism HTML + CAB File Web Server Client Database

26. DB Web DataWindow Mechanism HTML Web Server App Server Client Database

27. Utilizes Microsoft Internet Component Download mechanism Allows IE to pull down and install programs from the web automagically Distribution Unit CABinet file Open Software Description (OSD) in XML Java packages ActiveX controls (NativeCode) DataWindow ActiveX

28. PowerJ developers were limited to an ODBC connection when building DataWindow objects using PowerBuilder or DataWindow Builder for subsequent migration to Java/JDBC under PowerJ PB JDBC facilitates the design-time consistency to minimize deviation in datatypes, behavior, etc. Java DataWindow Support

29. Like other PB Database interfaces, the PB JDBC interface supports DataWindow design and runtime Pipeline design and runtime Database Painter Table Painter DBA Notepad SQLCA PowerScript Object PowerBuilder Design/Runtime Support

30. Stored Procedure Support • Two ways to call stored procedures from PowerScript: • Declare and execute procedure - for those stored procedures that return result sets and optionally use input/output parameters and/or return value. • RPCFUNC - for those stored procedures that do NOT return result sets. They use input/output parameters and/or return value.

31. Stored Procedure declare and execute DECLARE empproc PROCEDURE for sp_get_employee @emp_id = :id; EXECUTE empproc; DO WHILE SQLCA.SQLCODE = 0 FETCH empproc INTO :fname, :lname; LOOP

32. Simpler PowerScript: LONG col_length String table_name, column_name table_name = “dba.customer” column_name = “fname” col_length = mysqlca.col_length(table_name, column_name) Calling Stored Procedure as RPCFUNC

33. Supports DataWindows created using Stored Procedures returning a ResultSet Against Sybase Like SYC & ODB interfaces Against Oracle REFCURSOR in out parameters inserted and handled transparently to the programmer Like O73 & O84 interfaces Stored Procedure ResultSet Support

34. Can execute a SQL statement returning a ResultSet to a DataWindow asynchronously Controlled by Async and DBGetTime DBPARMs User can continue using UI and DBCancel() statement if desired PB JDBC interface handles this by spawning an additional Java Thread Only useful if Java VM internally uses native threads Asynchronous Processing

35. Supports both PowerBuilder DBI tracing using pbtraxx.dll Third party JDBC driver Tracing using TraceFile DBPARM Tracing

36. Supports both Text blobs using Ascii Stream Binary blobs using Binary Stream jConnect has a limitation on the BLOB size which can be set using SQLINITSTRING=set textsize <SIZE>; Properties DBPARM BLOB Support

37. Introduction to PowerBuilder JDBC interface PB8 db related changes Other PowerBuilder Database interfaces supporting EAServer TransactionServer Service Object EAServer support for 2 phase commit CORBACurrent Service Object Agenda

38. “JDB” interface in lieu of “JDS” and “JDM” Support for Sun JRE1.2.x Self registering dlls for setting the Vendor List Profiles no longer in pb.ini Stored in the Registry PB8 db related changes continued...

39. Native driver dlls not freed on disconnect Application object “FreeDBLibraries” property to change this behavior For design time db connection, system option is provided CLASSPATH can be set in PowerBuilder system options for design time access to JDBC drivers PB8 db related changes

40. Introduction to PowerBuilder JDBC interface PB8 db related changes Other PowerBuilder Database interfaces supporting EAServer TransactionServer Service Object EAServer support for 2 phase commit CORBACurrent Service Object Agenda

41. Sybase’s Application Server Three tier architecture results in Thin clients Better scalability Easier maintenance/upgrades Can run PowerBuilder components Provides Database Connection Pooling, Thread Pooling and Transaction Support EAServer Support - I

42. PowerBuilder Database interfaces supporting EAServer Transactions and Connection Pooling: SQLCA.DBMS = “ODB” SQLCA.DBMS = “SYJ” SQLCA.DBMS = “O73” SQLCA.DBMS = “O84” SQLCA.DBMS = “JDB” (“JDS” in PB 7.x*) EAServer Support - II

43. Database interfaces supporting EAServer Support EAServer Connection Pooling Support EAServer Transactions Support EAServer Specific DBPARMS like UseContextObject CacheName GetConnectionOption ReleaseConnectionOption PBJDMxx.DLL does not Support EAServer EAServer Support - III

44. EAServer Support - IV • EAServer supports multiple Java VMs • The JavaVM DBParm should match the VM running in EAServer else a WARNING will be logged and JavaVM will be switched to that used by EAServer. Therefore: • Don’t set JavaVM DBParm or set it to match the VM EAServer is using • For MS VM use “MS-JNI” instead of “MS” • VMs supported in EAServer are • JavaVM=‘Sun1.1’ (default) • JavaVM=‘Sun1.2’ • JavaVM=“MS-JNI”

45. Database Profile Setup EAServer Tab

46. Introduction to PowerBuilder JDBC interface PB8 db related changes Other PowerBuilder Database interfaces supporting EAServer TransactionServer Service Object EAServer support for 2 phase commit CORBACurrent Service Object Agenda

47. Controls component life-cycle if automatic demarcation/deactivation is not checked Controls EAServer Transaction outcome through ‘voting’ Useful functions supported: EnableCommit()/DisableCommit() SetComplete()/SetAbort() Initialized by calling GetContextService(“TransactionServer”, txn) TransactionServer Service Object

48. Introduction to PowerBuilder JDBC interface PB8 db related changes Other PowerBuilder Database interfaces supporting EAServer TransactionServer Service Object EAServer support for 2 phase commit CORBACurrent Service Object Agenda

49. Allows multiple Databases to be updated in a single transaction EAServer uses “MS DTC” or “OTS/XA (Encina)” as Transaction Coordinator for 2 Phase Commit “MS DTC” supports only ODBC or JDBC-ODBC bridge going against MS SQL Server 6.5 or later “OTS/XA (Encina)” supports Oracle and Sybase ASE “OTS/XA (Encina)” requires a setup of XA resources 2 Phase Commit

50. Creating XA Resource for ASE12using jConnect5.2