An ODBC Driver for APL also ADO ADO
An ODBC* Driver for APL * also ADO, ADO. NET and JDBC Morten Kromberg Dyalog Ltd.
Agenda • • Why Would You Want One? Overview of SQAPL Server Architecture Structure of the APL Server Sample Detailed Discussion of 22 Server Functions – – – Login Data Dictionary Calls SELECT statements UPDATE, INSERT, DELETE Transactions SQSRV Intro Dyalog APL 2
What is it? • The SQAPL Server allows an APL Application to appear to be a Relational Database – To clients supporting ODBC, JDBC, ADO[. NET] • Built on the Open. Access Server from Data. Direct • Wraps the OA API in a form which is easy to use for APL Developers • A minimal implementation only requires modifying a handful of simple APL sample fns SQSRV Intro Dyalog APL 3
What Does it Do? • Allows an APL application to appear to be an RDBMS, with a minimum of effort • Provides 100% complete and standard SQL language support without re-implementing SQL in the application • Supports SELECT, INSERT, UPDATE, DELETE, DDL, Stored Procedures and Bulk Inserts • Server-side comms uses TCP/IP sockets (Conga) for easy scalability and a robust architecture
Why Would You Want One? • Any APL application data which can be described as a table can quickly be exposed to almost any client application with minimal effort, enabling: – Reporting Applications using e. g. Crystal Reports, Excel – Writing Custom Front-Ends easily in any Language • You can serve up Live Data, an do not depend on exports having been (planned and) successfully run • Data can be exposed to external applications while maintaining application integrity checks, locks etc • Time for a Demo. . .
Demos • MS Access • MS Excel • Run the Simple QA Suite
SQAPL Server Architecture APL APL Dyalog Components TCP Sockets SQAPL SRV ”IP Provider” (C) OA Server Data. Direct Components ADO / ADO. NET ODBC Driver MS Access Crystal Reports MS Excel Etc. . . JDBC Driver VB/C++/C# . . . Dyalog Java Apps
Components • The Client Application (Crystal, Access, VBA. . . ) makes ODBC calls • ODBC(ADO/JDBC) driver transfers information to the “OA Service” or “Interface Provider” (IP). • Dyalog’s IP (oadamipdyalog) manages connections and data: – One Conga connection per ODBC Connection – Translates SQL into “Parse Trees” or (on request) makes simple selections on each table involved in a query – Buffers data so APL app only deals in arrays • The SQSRV workspace contains code which listens on port 5000 and has fns which map 1: 1 to operations requested by the IP • OA Agent (oaagent) is used to start & stop services via Command Line tool (oacla) but is not involved in sessions. SQSRV Intro Dyalog APL 8
System Requirements • Clients and Server can run under Windows, Linux, AIX (any Dyalog platform except Windows Mobile) • Clients can support ODBC, ADO. NET or JDBC – almost any platform
When? How Much? • At ”Proof of Concept” Stage • Significant OEM Costs if we proceed • Need one or two launch customers • Could be available in 2010
Let’s Look at The Code • The SQSRV workspace contains the code for a server which listens on port 5000 and handles calls from the OA ”IP Server”. • A number of namespace and class scripts are loade by � LX, including the RPCServer namespace • The RPCServer is a standard Conga sample, in this case it serves up instances of the SQSRV class: – An instance of the SQSRV class is created for each connection – Calls from OA are passed to the corresponding SQSRV fns – Each instance maintains state for a connection (like the user ID, once a user logs in) SQSRV Intro Dyalog APL 11
Scripts Loaded by SQSRV Class or Namespace Description SQSRV (Class) ”Sample” containg 22 main interface functions Parse. Tree (Class) Maps OA data to APL Class representation Pass. Through (NS) Tools for handling ”Pass. Through” selections Data. Base (NS) The Active Database RPCServer (Class) Conga Remote Procedure Call Framework ODBC (NS) ODBC-related Utilities Utils (NS) General Utilities SALT (NS) SALT Configuration Namespace SQSRV Intro Dyalog APL 12
The 22 SQSRV Functions LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT PROCCOLS PROCEDURE NEXTSET INSERT SELECTUPDATE SELECTDELETE SQSRV Intro Dyalog APL 13
Session & Schema Fns LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT PROCCOLS PROCEDURE NEXTSET INSERT SELECTUPDATE SELECTDELETE SQSRV Intro Dyalog APL 14
Login Validates logins to a connection – can also receive a database catalog name and Initialize session based on properties specified for the Data Source. ∇ r←LOGIN(DSN User Pwd Catalog IPProps IPCust. Props) : Access Public : If User≡Pwd �// Might want a better check than this : -) r← 0 'ok' User. ID←User Log'Login accepted for user ', User : Else r← 1 'Invalid user/password combination' Log'Login failed' : End. If ∇ SQSRV Intro Dyalog APL 15
Transact Starts, Commits or Rolls back a Transaction. ∇ r←TRANSACT action : Access Public �action: 0=Start, 1=Commit, 2=Rollback, 3=Prepare to Commit Log'Transaction: ', (1+1⊃action)⊃'Start' 'Commit' 'Rollback' 'Prepare to Commit' r← 0 �No problemo! ∇ (The rest is left as an exercise for the reader) SQSRV Intro Dyalog APL 16
Schemas and Catalogs List Catalogs, Schemas and Table Types ∇ r←CATALOGS dummy : Access Public r←DB. catalogs ∇ ∇ r←SCHEMAS dummy : Access Public r←DB. schemas ∇ ∇ r←TABLETYPES dummy : Access Public r←DB. tabletypes ∇ SQSRV Intro (return vars defined in Data. Base script): catalogs← 1 1� ⊂'SCHEMA' schemas← 2 1� 'System' 'DYALOG' tabletypes← 3 1� 'SYSTEM TABLE' 'VIEW' Dyalog APL 17
Tables List Tables ∇ r←TABLES arg : Access Public �arg: Qualifier Owner Name Type r← 0 (arg filter DB. tables) ∇ ∇ mat←arg filter mat; i �Used by TABLES and COLUMNS to filter result : If 0≠� i←(0≠⊃∘� ¨arg)/�� arg← 4↑arg, 4� ⊂'' mat←(mat[; i]∧. ≡arg[i])� mat : End. If ∇ In Database Script: tables← 0 8�� � Catalog schema name type path user tables� ←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'TABLE' '' '' tables� ←'SCHEMA' 'DYALOG' 'SHARES' 'TABLE' '' '' tables� ←'SCHEMA' 'DYALOG' 'PRICES' 'TABLE' '' ''. . . �fnsupport: 2=INSERT, 4=UPDATE, 8=DELETE, 0 x 0 E=2+4+8 SQSRV Intro Dyalog APL fnsupport '0 X 0 E' remark 'Customers' 'Shares Traded' 'Annual Share Prices' 18
Columns List Columns ∇ r←COLUMNS arg : Access Public �arg: Qualifier Owner Name Type r← 0 (arg filter DB. columns) ∇ In Database Script (& see Engine Programmers’ Guide page 205) columns← 0 17�� � Catalog Owner Table. Name Column. Name Data. Type. Name Length Precision columns� ←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTID’ 4 'INTEGER' 4 10 '' 0 0. . . 1 '' columns� ←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTNAME' 12 'VARCHAR' 32 32 '' 0 1. . . 0 '' CUSTOMERS←↑(1 'Jack')(2 'Jill') �. . . Radix Scale (Nullable Scope User. Data Support) Pseudo. Col. Type Remarks �Nullable: 0 = No nulls allowed, 1 = nulls allowed, 2 = unknown �Col. Type: 1 = Row. ID, 2 = Row Version, Null=Normal Column SQSRV Intro Dyalog APL 19
”Statistics” Return Database Statistics ∇ r←STAT arg : Access Public �arg: Qualifier Owner Name r← 0 (arg filter DB. stats) ∇ In Database Script (& see Engine Programmers’ Guide page 210) stats← 0 13�� �Statistics �Table Catalog Owner Name Non. Unique Cat. Ix Name Type Seq Colname Collation stats� ←'SCHEMA' 'DYALOG' 'SHARES' 0 '' 'CODE' 2 1 'CODE' 'A' 3 '' '' stats� ←'SCHEMA' 'DYALOG' 'CUSTOMERS' 0 '' 'CUSTID' 2 1 'CUSTID' 'A' 2 '' '' �. . . Cardinality Pages Filter. Cond �Type: 0="Table Statistic", 1=Cluster, 2=Hashed, 3=Other �Collation: A for Ascending, D for Descending, or NULL �Cardinality: For Stat # of rows else # of unique values �Strictly, Cardinality SHOULD be updated to reflect number of rows in the table NB: Filters on keys defined in stats are used as ”Optimal Filters’, see SELECT function. SQSRV Intro Dyalog APL 20
”Foreign Keys” Return Foreign Keys ∇ r←FKEY arg : Access Public �arg: Primary Qualifier Owner Name, Foreign Qualifier Owner Name : If 0∨. ≠⊃∘� ¨arg ⋄ ∘ ⋄ : End. If �/// Stop on filter r← 0 DB. fkeys ∇ In Database Script (& see Engine Programmers’ Guide page 216) fkeys← 0 13�� �Foreign and Private Keys � PK Cat Schema Tbl. Name Col. Name fkeys� ←'SCHEMA' 'DYALOG' 'SHARES' 'CODE' fkeys� ←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTID' fkeys� ←'SCHEMA' 'DYALOG' 'TRADES' 'CUSTID' � FK Cat Schema Tbl. Name Col. Name '' '' 1 '' '' '' '’ 1 'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTID' 1. . . Sequence Update. Rule Delete. Rule FK '' '' '' '’ Name PK Name First two rows above duplicate primary key information in Statistics table Last row defines a foreign key relationship between TRADES and CUSTOMER on CUSTID When joining tables without PASSTHROUGH, one query is made per unique foreign key value (else one per row!) SQSRV Intro Dyalog APL 21
Selection Functions LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT PROCCOLS PROCEDURE NEXTSET INSERT SELECTUPDATE SELECTDELETE SQSRV Intro Dyalog APL 22
Three Selection Modes • SELECT – A call is made for each table in the expression with simple filter expressions • PARTIAL PASSTHROUGH – We return all the joined columns required to produce the result, but not necessarily completely filtered, grouped or ordered • COMPLETE PASSTHROUGH – We return the EXACT record set to be returned to the client SQSRV Intro Dyalog APL 23
PASSTHROUGH • The SQAPL Server allows ”Selective Passthrough”, which means that the function PASSTHROUGH is always called • It is given a ”Parse. Tree” which describes the statement being executed • After examining the tree, it can declare whether it wants to fall back to SELECT mode • SELECT is very easy; PASSTHROUGH allows optimization, especially for JOINs SQSRV Intro Dyalog APL 24
SELECTQUERY Example SQL: select * from customers where custid > 0 and custname like 'J%' [Select. Query] Highest. Rows: ¯ 1 Tables: Highest. Pct. Rows: 0 customers Value. Expr Search. Expr [Val. Exp. List] [Val. Exp] [Log. Exp] [Value] [Column] CUSTID (# 0 in Table# 0) [Column] CUSTNAME (# 1 in Table# 0) [Log. Exp] [Cond] > And [Log. Exp] [Cond] #. Parse. Tree. Like SQSRV Intro Dyalog APL [Val. Exp] [Value] [Column] custid (# 0 in Table# 0) LIT 4 0 [Column] custname (# 1 in Table# 0) LIT 12 J% 25
Select. Query Class Parse Trees arrive from the IP in the form of a deeply nested array. This array is converted into an instance of the Select. Query class by the function Parse. Tree. New. Properties of a Select. Query are: Set. Quantifier Highest. Rows Highest. Pct. Rows Value. Expr Tables Search. Expr Group. Val. Expr Having. Expr Union. Type Union. Query Order. Expr Whether to remove duplicate rows Number of rows in TOP clause If TRUE, number in Highest. Rows was specified as (TOP n PERCENT) An object describing the values extracted by the query A Table. List – tables involved in the query A Log. Exp object describing the filter expression A Value. Exp. List - Group By expression A Log. Exp – the “Having” expression If “true”, do not check for duplicate records in Union Another Select. Query object if there is a UNION expression An Order. List See the Parse. Tree class for more information about other object types. SQSRV Intro Dyalog APL 26
Pass. Through Decision Pass. Through support is implemented in th Pass. Through namespace. The function Pass. Through. Check contains the following logic: : If ok←#. Parse. Tree. Select. Query= ⊃� CLASS Query �It’s a SELECT Query : And. If ok← 1=� table←Query. Tables. Items. Name �On a single table←#. Utils. ucase ⊃tablecols←(DB. columns[; 3] ∊⊂table)� DB. columns : And. If ok←∧/(Query. Value. Expr. Items). Type ∊⊂'Value' �Only Value Expressions : And. If ok←∧/(cols←Query. Value. Expr. Items. Arg 1). Type ∊⊂'Column' �Which extract Columns : And. If ok←∧/(1↑� tablecols)≥colix←tablecols[; 4] � #. Utils. ucase¨cols. Data. Name Plan←(Query. Tables. Items[1])cols. Data 0 �Tables, Columns, Query : If Query. Search. Expr ≢ 0 �There is a Search Expression ok← 0≢(3⊃Plan)←JITSearch Query. Search. Expr �Compile query and store in 3 rd element of ”plan” : End. If In other words: When handing SELECT statements, the sample code will execute the query in Pass. Through mode if is is simply selecting columns from a single table. SQSRV Intro Dyalog APL 27
FIRSTBLOCK (NEXTBLOCK) If PASSTHROUGH was selected, the next call from the IP will be FIRSTBLOCK. The arguments are: h. Stmt Statement Handle (not really used in POC, possibly useful later) Block. Size Block size (ignored in POC, ALL records are returned by FIRSTBLOCK) Parse. Tree The same type of object passed to PASSTHROUGH but with bind variable values inserted Two types of result are possible: Final The result should be passed unchanged to the ODBC client Base. Cols All columns required to produce the result are included, containing joined data, but OA framework will do final filtering, grouping and ordering if required. SQSRV Intro Dyalog APL 28
FIRSTBLOCK ∇ r←FIRSTBLOCK(h. Stmt Block. Size Parse. Tree); Exec. Plan; Query : Access Public �Called following a PASSTHROUGH result of 1 (else call SELECT) �Returns (Mode Cols Data): � �Mode=1: Final result, ready to go �Mode=2: Base cols only, OA Server will filter, order, group � (Requires 2 extra output cols: Full. Col. Name, Table. Num) �Cols : [; 1] Col. Name, [; 2] Col. Num, [; 3] XOType [; 4] Col. Type � (and in mode 2 also [; 5] Full. Col. Name, [; 6] Table. Num) �Data : One vector for each column of output [more] SQSRV Intro Dyalog APL 29
FIRSTBLOCK ∇ r←FIRSTBLOCK(h. Stmt Block. Size Parse. Tree); Exec. Plan; Query [continued] Query←#. Parse. Tree. New Parse. Tree r Exec. Plan←#. Pass. Through. Check DB Query : If r=0 ⋄ ∘ ⋄ : End. If �Paranoia : Hold 'Data. Base' DB. Prepare 1 Query. Tables. Items. Name Query. Search. Expr �Give DB a chance to prepare data r←#. Pass. Through. Execute DB Exec. Plan �Execute the Query : End. Hold ∇ SQSRV Intro Dyalog APL 30
SELECT If PASSTHROUGH was REJECTED, the IP will call SELECT one or more times: ∇ r←SUBSELECT(h. Stmt Table Columns Opt. Index); tab; data; m; i; j : Access Public �Called if PASSTHROUGH is REJECTED �arg: � Table Name � Columns - 5 column matrix: � 1: Column Name � 2: Column Number � 3: Column Data Type � 4: Column Type (select where) � 5: Restriction List � Optimal Index (Vector of Restriction Lists) � Restrict List is a Vector of Restrictions to be ORed � Column Name, Left. Fn, Left. Val, Right. Fn, Right. Val [more] SQSRV Intro Dyalog APL 31
SELECT If PASSTHROUGH was REJECTED, the IP will call SELECT one or more times: ∇ r←SUBSELECT(h. Stmt Table Columns Opt. Index); tab; data; m; i; j : Access Public �Called if PASSTHROUGH is REJECTED �arg: � Table Name � Columns - 5 column matrix: � 1: Column Name � 2: Column Number � 3: Column Data Type � 4: Column Type (select where) � 5: Restriction List � Optimal Index (Vector of Restriction Lists) � Restrict List is a Vector of Restrictions to be ORed � Column Name, Left. Fn, Left. Val, Right. Fn, Right. Val [more] SQSRV Intro Dyalog APL 32
SELECT ∇ r←SUBSELECT(h. Stmt Table Columns Opt. Index) [continued. . . ] �Returns � 1: Condition Code =0 � 2: nx 4 Column Info � 1: Column Name � 2: Column Number � 3: Column data type � 4: Column Type (select where) � 3: Vector of column data (length=n) � Numbers as simple vectors � Chars as nested vectors � Time, Date. Time as numeric MATRIX � 4: Vector of row indices for use by SELECTUPDATE & SELECTDELETE �In POC, returns all rows at once. Later, "Fetch" will collect more data �(return "success with pending result") [more] SQSRV Intro Dyalog APL 33
SELECT - Continued ∇ r←SUBSELECT(h. Stmt Table Columns Opt. Index) [continued. . . ] tab←DB� ucase Table data←tab[; 1+Columns[; 2]] data←(m←data Exec. Restrictions Opt. Index)� data (m/m)←m 2←data Exec. Restrictions (0≠⊃∘� ¨Columns[; 5])/ Columns[; 5] data←m 2� data rowix←m/�� m data←↓[1]data m←Columns[; 3]∊9 10 11 �Time, Date. Time (m/data)←↑¨m/data r← 0(4↑[2]Columns)data rowix SQSRV Intro Dyalog APL 36
Executing Restrictions ∇ m←Exec. Restriction(Cols Data Restrictions); . . . (cols lfns lvals rfns rvals)←↓[1]↑Restrictions i←Cols[; 1]� ⊂ucase⊃cols m← 0 : For j : In �� lfns fn←� #. Parse. Tree. Cond. Fn j⊃lfns msk←Data[; i] fn lvals[j] : If 0≠j⊃rfns �If there is a ”right fn” (eg Between) fn←� #. Parse. Tree. Cond. Fn j⊃rfns msk←Data[; i] fn rvals[j] : End. If m∨←msk : End. For ∇ #. Parse. Tree. Cond. Fn converts fn numbers into APL code fragments (eg 8 = Like) SQSRV Intro Dyalog APL 37
UPDATE ∇ r←SELECTUPDATE arg : Access Public Selected. Rows← 4⊃r←SUBSELECT arg r← 3↑r ∇ ∇ r←UPDATEROWS(h. Stmt tablename cols rowix data nulls) : Access Public tablename←ucase tablename data←cols[; 3]Cast¨data �Ensure correct APL types � 'DB. ', tablename, '[', (� Selected. Rows[1+rowix]), '; ', (� 1+cols[; 2]), ']←data' r← 0(� rowix) �Return number of rows affected ∇ To. Do: Selected. Rows needs to be stored ”per h. Stmt” Row. Ix is also not thread safe: Need unique row identifiers SQSRV Intro Dyalog APL 38
DELETE ∇ r←SELECTDELETE arg : Access Public Selected. Rows← 4⊃r←SUBSELECT arg r← 3↑r ∇ ∇ r←DELETEROWS(h. Stmt tablename rowix); data; m; t : Access Public tablename←ucase tablename m←~(� 1↑� data←� t←'DB. ', tablename)∊Selected. Rows[1+rowix] � 'DB. ', tablename, '←m� data' �Compress rows out of matrix r← 0(� rowix) ∇ To. Do: See UPDATE SQSRV Intro Dyalog APL 39
INSERT ∇ r←INSERT(h. Stmt tablename cols data nulls); row : Access Public tablename←ucase tablename �Create Empty rows with right types row←((1↑� data), � row)� row← ((DB. columns[; 3]∊⊂tablename)� DB. columns[; 5])Cast¨⊂'’ �Fill with data actually provided row[; 1+cols[; 2]]←((� data)� cols[; 3])Cast¨data � 'DB. ', tablename, '� ←row' r← 0(1↑� data) ∇ To. Do: Nothing SQSRV Intro Dyalog APL 40
Stored Procedures LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT PROCCOLS PROCEDURE NEXTSET INSERT SELECTUPDATE SELECTDELETE SQSRV Intro Dyalog APL 41
Stored Procedure Setup PROC and PROCCOLS return schema information: procs← 0 9�� �Stored Procedures procs� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 1 0 0 'Set Global Interest Rate' '1' '' proccols← 0 14�� �Stored Procedure Columns proccols� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 'Rate' 1 4 'INTEGER' 10 4 ¯ 1 1 proccols� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 'Message' 3 12 'VARCHAR' 255 ¯ 1 1 Tax. Rate← 25 ∇ r←Set. Tax. Rate(Data Nulls) r←, ⊂, ⊂'Tax Rate set to ', (� ⊃Data), ' - was ', � Tax. Rate←⊃Data ∇ SQSRV Intro Dyalog APL 42
Procedure Execution ∇ r←PROCEDURE(h. Stmt Name Cols Data Nulls); i; fn; m; cols : Access Public �Make a Stored Procedure Call i←(ucase¨DB. procs[; 3])� ⊂ucase lastproc←Name : If (1↑� DB. procs)<i : Or. If 3≠DB. � NC fn←⊃DB. procs[i; 3] r← 6 'Unknown stored procedure: ', Name : Else m←DB. proccols[; 3]∊⊂fn cols←m� DB. proccols←(cols[; 5]=3)� cols r← 3 cols ((DB� fn) Data Nulls) : End. If ∇ Result Type 3 means a stored procedure result (vector or vectors) SQSRV Intro Dyalog APL 43
Stored Procedure Setup PROC and PROCCOLS return schema information: procs← 0 9�� �Stored Procedures procs� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 1 0 0 'Set Global Interest Rate' '1' '' proccols← 0 14�� �Stored Procedure Columns proccols� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 'Rate' 1 4 'INTEGER' 10 4 ¯ 1 1 proccols� ←'SCHEMA' 'DYALOG' 'Set. Tax. Rate' 'Message' 3 12 'VARCHAR' 255 ¯ 1 1 Tax. Rate← 25 ∇ r←Set. Tax. Rate(Data Nulls) r←, ⊂, ⊂'Tax Rate set to ', (� ⊃Data), ' - was ', � Tax. Rate←⊃Data ∇ SQSRV Intro Dyalog APL 44
- Slides: 44