1 / 44

An ODBC* Driver for APL * also ADO, ADO.NET and JDBC

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

xuxa
Download Presentation

An ODBC* Driver for APL * also ADO, ADO.NET and JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. An ODBC* Driverfor APL* also ADO, ADO.NET and JDBC Morten Kromberg Dyalog Ltd.

  2. 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 Dyalog APL

  3. 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 OpenAccess Server from DataDirect • 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 Dyalog APL

  4. 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

  5. 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...

  6. Demos • MS Access • MS Excel • Run the Simple QA Suite

  7. SQAPL Server Architecture APL APL APL APL Dyalog Components TCP Sockets SQAPL SRV”IP Provider” (C) OA Server DataDirect Components ODBC Driver ADO / ADO.NET JDBC Driver MS Access MS Excel VB/C++/C# ... Java Apps Crystal Reports Etc... Dyalog

  8. 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. Dyalog APL

  9. System Requirements • Clients and Server can run under Windows, Linux, AIX (any Dyalog platform except Windows Mobile) • Clients can support ODBC, ADO, ADO.NET or JDBC – almost any platform

  10. 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

  11. 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) Dyalog APL

  12. Scripts Loaded by SQSRV Dyalog APL

  13. The 22 SQSRV Functions LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT INSERT SELECTUPDATE UPDATE SELECTDELETE DELETE PROC PROCCOLS PROCEDURE NEXTSET Dyalog APL

  14. Session & Schema Fns LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT INSERT SELECTUPDATE UPDATE SELECTDELETE DELETE PROC PROCCOLS PROCEDURE NEXTSET Dyalog APL

  15. 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 IPCustProps) :Access Public :If User≡Pwd ⍝ // Might want a better check than this :-) r←0 'ok' UserID←User Log'Login accepted for user ',User :Else r←1 'Invalid user/password combination' Log'Login failed' :EndIf ∇ Dyalog APL

  16. 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) Dyalog APL

  17. Schemas and Catalogs (return vars defined in DataBase script): catalogs←1 1⍴⊂'SCHEMA' schemas←2 1⍴'System' 'DYALOG' tabletypes←3 1⍴'SYSTEM TABLE' 'TABLE' 'VIEW' 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 ∇ Dyalog APL

  18. 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 :EndIf ∇ In Database Script: tables←0 8⍴⍬ ⍝ Catalog schema name type path user fnsupport remark tables⍪←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'TABLE' '' '' '0X0E' 'Customers' tables⍪←'SCHEMA' 'DYALOG' 'SHARES' 'TABLE' '' '' '0X0E' 'Shares Traded' tables⍪←'SCHEMA' 'DYALOG' 'PRICES' 'TABLE' '' '' '0X0E' 'Annual Share Prices' ... ⍝ fnsupport: 2=INSERT, 4=UPDATE, 8=DELETE, 0x0E=2+4+8 Dyalog APL

  19. 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 TableName ColumnName DataType TypeName 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 UserData Support) PseudoCol ColType Remarks ⍝ Nullable: 0 = No nulls allowed, 1 = nulls allowed, 2 = unknown ⍝ ColType: 1 = RowID, 2 = Row Version, Null=Normal Column Dyalog APL

  20. ”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 NonUnique CatIx 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 FilterCond ⍝ 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. Dyalog APL

  21. ”Foreign Keys” Return Foreign Keys ∇ r←FKEY arg :Access Public ⍝ arg: Primary Qualifier Owner Name, Foreign Qualifier Owner Name :If 0∨.≠⊃∘⍴¨arg ⋄ ∘ ⋄ :EndIf ⍝ /// 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 TblName ColName FK Cat Schema TblName ColName fkeys⍪←'SCHEMA' 'DYALOG' 'SHARES' 'CODE' '' '' '' '' 1 '' '' '' '' fkeys⍪←'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTID' '' '' '' '’ 1 '' '' '' '' fkeys⍪←'SCHEMA' 'DYALOG' 'TRADES' 'CUSTID' 'SCHEMA' 'DYALOG' 'CUSTOMERS' 'CUSTID' 1 '' '' '' '’ ⍝ ... Sequence UpdateRule DeleteRule 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!) Dyalog APL

  22. Selection Functions LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT INSERT SELECTUPDATE UPDATE SELECTDELETE DELETE PROC PROCCOLS PROCEDURE NEXTSET Dyalog APL

  23. 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 Dyalog APL

  24. PASSTHROUGH • The SQAPL Server allows ”Selective Passthrough”, which means that the function PASSTHROUGH is always called • It is given a ”ParseTree” 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 Dyalog APL

  25. SELECTQUERY Example SQL: select * from customers where custid > 0 and custname like 'J%' [SelectQuery] HighestRows: ¯1 HighestPctRows: 0 Tables: customers ValueExpr [ValExpList] [ValExp] [Value] [Column] CUSTID (# 0 in Table# 0) [ValExp] [Value] [Column] CUSTNAME (# 1 in Table# 0) SearchExpr [LogExp] [LogExp] [Cond] [ValExp] [Value] [Column] custid (# 0 in Table# 0) > [ValExp] [Value] LIT 4 0 And [LogExp] [Cond] [ValExp] [Value] [Column] custname (# 1 in Table# 0) #.ParseTree.Like [ValExp] [Value] LIT 12 J% Dyalog APL

  26. SelectQuery Class Parse Trees arrive from the IP in the form of a deeply nested array. This array is converted into an instance of the SelectQuery class by the function ParseTree.New. Properties of a SelectQuery are: SetQuantifier Whether to remove duplicate rows HighestRows Number of rows in TOP clause HighestPctRows If TRUE, number in HighestRows was specified as (TOP n PERCENT) ValueExpr An object describing the values extracted by the query Tables A TableList – tables involved in the query SearchExpr A LogExp object describing the filter expression GroupValExpr A ValueExpList - Group By expression HavingExpr A LogExp – the “Having” expression UnionType If “true”, do not check for duplicate records in Union UnionQuery Another SelectQuery object if there is a UNION expression OrderExpr An OrderList See the ParseTree class for more information about other object types. Dyalog APL

  27. PassThrough Decision PassThrough support is implemented in th PassThrough namespace. The function PassThrough.Check contains the following logic: :If ok←#.ParseTree.SelectQuery=⊃⎕CLASS Query ⍝ It’s a SELECT Query :AndIf ok←1=⍴table←Query.Tables.Items.Name ⍝ On a single table table←#.Utils.ucase⊃table tablecols←(DB.columns[;3]∊⊂table)⌿DB.columns :AndIf ok←∧/(Query.ValueExpr.Items).Type∊⊂'Value' ⍝ Only Value Expressions :AndIf ok←∧/(cols←Query.ValueExpr.Items.Arg1).Type∊⊂'Column' ⍝ Which extract Columns :AndIf ok←∧/(1↑⍴tablecols)≥colix←tablecols[;4]⍳#.Utils.ucase¨cols.Data.Name Plan←(Query.Tables.Items[1])cols.Data 0 ⍝ Tables, Columns, Query :If Query.SearchExpr≢0 ⍝ There is a Search Expression ok←0≢(3⊃Plan)←JITSearch Query.SearchExpr ⍝ Compile query and store in 3rd element of ”plan” :EndIf :EndIf In other words: When handing SELECT statements, the sample code will execute the query in PassThrough mode if is is simply selecting columns from a single table. Dyalog APL

  28. FIRSTBLOCK (NEXTBLOCK) If PASSTHROUGH was selected, the next call from the IP will be FIRSTBLOCK. The arguments are: hStmt Statement Handle (not really used in POC, possibly useful later) BlockSize Block size (ignored in POC, ALL records are returned by FIRSTBLOCK) ParseTree 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 BaseCols All columns required to produce the result are included, containing joined data, but OA framework will do final filtering, grouping and ordering if required. Dyalog APL

  29. FIRSTBLOCK ∇ r←FIRSTBLOCK(hStmt BlockSize ParseTree);ExecPlan;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: FullColName, TableNum) ⍝ Cols : [;1] ColName, [;2] ColNum, [;3] XOType [;4] ColType ⍝ (and in mode 2 also [;5] FullColName, [;6] TableNum) ⍝ Data : One vector for each column of output [more] Dyalog APL

  30. FIRSTBLOCK ∇ r←FIRSTBLOCK(hStmt BlockSize ParseTree);ExecPlan;Query [continued] Query←#.ParseTree.New ParseTree r ExecPlan←#.PassThrough.Check DB Query :If r=0 ⋄ ∘ ⋄ :EndIf ⍝ Paranoia :Hold 'DataBase' DB.Prepare 1 Query.Tables.Items.Name Query.SearchExpr ⍝ Give DB a chance to prepare data r←#.PassThrough.Execute DB ExecPlan ⍝ Execute the Query :EndHold ∇ Dyalog APL

  31. SELECT If PASSTHROUGH was REJECTED, the IP will call SELECT one or more times: ∇ r←SUBSELECT(hStmt Table Columns OptIndex);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, LeftFn, LeftVal, RightFn, RightVal [more] Dyalog APL

  32. SELECT If PASSTHROUGH was REJECTED, the IP will call SELECT one or more times: ∇ r←SUBSELECT(hStmt Table Columns OptIndex);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, LeftFn, LeftVal, RightFn, RightVal [more] Dyalog APL

  33. SELECT ∇ r←SUBSELECT(hStmt Table Columns OptIndex) [continued...] ⍝ Returns ⍝ 1: Condition Code =0 ⍝ 2: nx4 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, DateTime 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] Dyalog APL

  34. SELECT Example: Input select * from customers where custid > 0 and custname like 'J%' OptIndex: ┌→─────────────────────────┐ │ ┌→─────────────────────┐ │ │ │ ┌→─────────────────┐ │ │ │ │ │ ┌→─────┐ │ │ │ │ │ │ │custid│ 4 0 0 0 │ │ │ │ │ │ └──────┘ │ │ │ │ │ └∊─────────────────┘ │ │ │ └∊─────────────────────┘ │ └∊─────────────────────────┘ Columns: ┌→────────────────────────────────────────────────┐ ↓ ┌→─────┐ ┌⊖┐ │ │ │CUSTID│ 0 4 3 │0│ │ │ └──────┘ └~┘ │ │ ┌→───────┐ ┌→──────────────────────────┐ │ │ │CUSTNAME│ 1 12 3 │ ┌→──────────────────────┐ │ │ │ └────────┘ │ │ ┌→───────┐ ┌→─┐ │ │ │ │ │ │ │custname│ 8 │J%│ 0 0 │ │ │ │ │ │ └────────┘ └──┘ │ │ │ │ │ └∊──────────────────────┘ │ │ │ └∊──────────────────────────┘ │ └∊────────────────────────────────────────────────┘ Dyalog APL

  35. SELECT Example: Output select * from customers where custid>0 and custname like ''J%'‘ Data: ┌→────────────────────────┐ │ ┌→──┐ ┌→──────────────┐ │ │ │1 2│ │ ┌→───┐ ┌→───┐ │ │ │ └~──┘ │ │Jack│ │Jill│ │ │ │ │ └────┘ └────┘ │ │ │ └∊──────────────┘ │ └∊────────────────────────┘ RowIx: ┌→──┐ │1 2│ └~──┘ RowIx is used by UPDATE and DELETE Dyalog APL

  36. SELECT - Continued ∇ r←SUBSELECT(hStmt Table Columns OptIndex) [continued...] tab←DB⍎ucase Table data←tab[;1+Columns[;2]] data←(m←data ExecRestrictions OptIndex)⌿data (m/m)←m2←data ExecRestrictions (0≠⊃∘⍴¨Columns[;5])/ Columns[;5] data←m2⌿data rowix←m/⍳⍴m data←↓[1]data m←Columns[;3]∊9 10 11 ⍝ Time, Date, DateTime (m/data)←↑¨m/data r←0(4↑[2]Columns)data rowix Dyalog APL

  37. Executing Restrictions ∇ m←ExecRestriction(Cols Data Restrictions);... (cols lfns lvals rfns rvals)←↓[1]↑Restrictions i←Cols[;1]⍳⊂ucase⊃cols m←0 :For j :In ⍳⍴lfns fn←⍎#.ParseTree.CondFn j⊃lfns msk←Data[;i] fn lvals[j] :If 0≠j⊃rfns ⍝ If there is a ”right fn” (eg Between) fn←⍎#.ParseTree.CondFn j⊃rfns msk←Data[;i] fn rvals[j] :EndIf m∨←msk :EndFor ∇ #.ParseTree.CondFn converts fn numbers into APL code fragments (eg 8 = Like) Dyalog APL

  38. UPDATE ∇ r←SELECTUPDATE arg :Access Public SelectedRows←4⊃r←SUBSELECT arg r←3↑r ∇ ∇ r←UPDATEROWS(hStmt tablename cols rowix data nulls) :Access Public tablename←ucase tablename data←cols[;3]Cast¨data ⍝ Ensure correct APL types ⍎'DB.',tablename,'[',(⍕SelectedRows[1+rowix]), ';',(⍕1+cols[;2]),']←data' r←0(⍴rowix) ⍝ Return number of rows affected ∇ ToDo: SelectedRows needs to be stored ”per hStmt” RowIx is also not thread safe: Need unique row identifiers Dyalog APL

  39. DELETE ∇ r←SELECTDELETE arg :Access Public SelectedRows←4⊃r←SUBSELECT arg r←3↑r ∇ ∇ r←DELETEROWS(hStmt tablename rowix);data;m;t :Access Public tablename←ucase tablename m←~(⍳1↑⍴data←⍎t←'DB.',tablename)∊SelectedRows[1+rowix] ⍎'DB.',tablename,'←m⌿data' ⍝ Compress rows out of matrix r←0(⍴rowix) ∇ ToDo: See UPDATE Dyalog APL

  40. INSERT ∇ r←INSERT(hStmt 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) ∇ ToDo: Nothing  Dyalog APL

  41. Stored Procedures LOGIN TRANSACT CATALOGS SCHEMAS TABLETYPES TABLES COLUMNS STAT FKEY PASSTHROUGH FIRSTBLOCK NEXTBLOCK SELECT INSERT SELECTUPDATE UPDATE SELECTDELETE DELETE PROC PROCCOLS PROCEDURE NEXTSET Dyalog APL

  42. Stored Procedure Setup PROC and PROCCOLS return schema information: procs←0 9⍴⍬ ⍝ Stored Procedures procs⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 1 0 0 'Set Global Interest Rate' '1' '' proccols←0 14⍴⍬ ⍝ Stored Procedure Columns proccols⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 'Rate' 1 4 'INTEGER' 10 4 ¯1 ¯1 1 proccols⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 'Message' 3 12 'VARCHAR' 255 255 ¯1 ¯1 1 TaxRate←25 ∇ r←SetTaxRate(Data Nulls) r←,⊂,⊂'Tax Rate set to ',(⍕⊃Data),' - was ',⍕TaxRate TaxRate←⊃Data ∇ Dyalog APL

  43. Procedure Execution ∇ r←PROCEDURE(hStmt 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 :OrIf 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←(cols[;5]=3)⌿cols r←3 cols ((DB⍎fn) Data Nulls) :EndIf ∇ Result Type 3 means a stored procedure result (vector or vectors) Dyalog APL

  44. Stored Procedure Setup PROC and PROCCOLS return schema information: procs←0 9⍴⍬ ⍝ Stored Procedures procs⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 1 0 0 'Set Global Interest Rate' '1' '' proccols←0 14⍴⍬ ⍝ Stored Procedure Columns proccols⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 'Rate' 1 4 'INTEGER' 10 4 ¯1 ¯1 1 proccols⍪←'SCHEMA' 'DYALOG' 'SetTaxRate' 'Message' 3 12 'VARCHAR' 255 255 ¯1 ¯1 1 TaxRate←25 ∇ r←SetTaxRate(Data Nulls) r←,⊂,⊂'Tax Rate set to ',(⍕⊃Data),' - was ',⍕TaxRate TaxRate←⊃Data ∇ Dyalog APL

More Related