an odbc* driver for apl * also ado, ado.net and jdbc morten kromberg dyalog ltd
Post on 27-Dec-2015
225 Views
Preview:
TRANSCRIPT
An ODBC* Driverfor APL
* also ADO, ADO.NET and JDBC
Morten KrombergDyalog 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 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
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
ODBC Driver
OA Server
SQAPL SRV”IP Provider”
(C)
MS Access MS Excel
Crystal Reports
VB/C++/C# ...Dyalog
ADO / ADO.NET
JDBC Driver
Java Apps
Etc...
APLAPL APL APL
DataDirect Components
Dyalog ComponentsTCP Sockets
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, 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
SQSRV Intro Dyalog APL 11
• 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)
Scripts Loaded by SQSRV
SQSRV Intro Dyalog APL 12
Class or Namespace
Description
SQSRV (Class) ”Sample” containg 22 main interface functions
ParseTree (Class) Maps OA data to APL Class representation
PassThrough (NS) Tools for handling ”PassThrough” selections
DataBase (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
The 22 SQSRV Functions
SQSRV Intro Dyalog APL 13
PROCPROCCOLS
PROCEDURENEXTSET
PASSTHROUGHFIRSTBLOCKNEXTBLOCKSELECT
INSERT
SELECTUPDATEUPDATE
SELECTDELETEDELETE
LOGIN
TRANSACT
CATALOGSSCHEMASTABLETYPES
TABLESCOLUMNSSTATFKEY
Session & Schema Fns
SQSRV Intro Dyalog APL 14
PROCPROCCOLS
PROCEDURENEXTSET
PASSTHROUGHFIRSTBLOCKNEXTBLOCKSELECT
INSERT
SELECTUPDATEUPDATE
SELECTDELETEDELETE
LOGIN
TRANSACT
CATALOGSSCHEMASTABLETYPES
TABLESCOLUMNSSTATFKEY
Login
SQSRV Intro Dyalog APL 15
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 ∇
Transact
SQSRV Intro Dyalog APL 16
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)
Schemas and Catalogs
SQSRV Intro Dyalog APL 17
(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 ∇
Tables
SQSRV Intro Dyalog APL 18
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
Columns
SQSRV Intro Dyalog APL 19
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
”Statistics”
SQSRV Intro Dyalog APL 20
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.
”Foreign Keys”
SQSRV Intro Dyalog APL 21
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 CUSTIDWhen joining tables without PASSTHROUGH, one query is made per unique foreign key value (else one per row!)
Selection Functions
SQSRV Intro Dyalog APL 22
PROCPROCCOLS
PROCEDURENEXTSET
PASSTHROUGHFIRSTBLOCKNEXTBLOCKSELECT
INSERT
SELECTUPDATEUPDATE
SELECTDELETEDELETE
LOGIN
TRANSACT
CATALOGSSCHEMASTABLETYPES
TABLESCOLUMNSSTATFKEY
Three Selection Modes
SQSRV Intro Dyalog APL 23
• 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
PASSTHROUGH
SQSRV Intro Dyalog APL 24
• 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
SELECTQUERY Example
SQSRV Intro Dyalog APL 25
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%
SelectQuery Class
SQSRV Intro Dyalog APL 26
Parse Trees arrive from the IP in the form of a deeply nested array. This array is converted intoan 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.
PassThrough Decision
SQSRV Intro Dyalog APL 27
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.
FIRSTBLOCK (NEXTBLOCK)
SQSRV Intro Dyalog APL 28
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 PASSTHROUGHbut 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.
FIRSTBLOCK
SQSRV Intro Dyalog APL 29
∇ 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]
FIRSTBLOCK
SQSRV Intro Dyalog APL 30
∇ 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 ∇
SELECT
SQSRV Intro Dyalog APL 31
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]
SELECT
SQSRV Intro Dyalog APL 32
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]
SELECT
SQSRV Intro Dyalog APL 33
∇ 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]
SELECT Example: Input
SQSRV Intro Dyalog APL 34
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 │ │ │ │ │ │ └────────┘ └──┘ │ │ │ │ │ └∊──────────────────────┘ │ │ │ └∊──────────────────────────┘ │
└∊────────────────────────────────────────────────┘
SELECT Example: Output
SQSRV Intro Dyalog APL 35
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
SELECT - Continued
SQSRV Intro Dyalog APL 36
∇ 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
Executing Restrictions
SQSRV Intro Dyalog APL 37
∇ 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)
UPDATE
SQSRV Intro Dyalog APL 38
∇ 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
DELETE
SQSRV Intro Dyalog APL 39
∇ 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
INSERT
SQSRV Intro Dyalog APL 40
∇ 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
Stored Procedures
SQSRV Intro Dyalog APL 41
PROCPROCCOLS
PROCEDURENEXTSET
PASSTHROUGHFIRSTBLOCKNEXTBLOCKSELECT
INSERT
SELECTUPDATEUPDATE
SELECTDELETEDELETE
LOGIN
TRANSACT
CATALOGSSCHEMASTABLETYPES
TABLESCOLUMNSSTATFKEY
Stored Procedure Setup
SQSRV Intro Dyalog APL 42
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 ∇
Procedure Execution
SQSRV Intro Dyalog APL 43
∇ 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)
Stored Procedure Setup
SQSRV Intro Dyalog APL 44
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 ∇
top related