Sql scripting with Advantage

1. Sql scripting with Advantage Chris Franz Systems consultant / advantage evangelist May 2011

2. agenda • SQL script overview • Syntax • Cache • System Variables • Parameters • Stored Procedures • User Defined Functions (UDFs)

3. Sql script overview • Benefits • Stored procedures without third-party compiler • Same stored procedure runs on all operating systems • No additional files deployed • ADS has greater controls over script-based procedures and triggers • Drawbacks • not as versatile as non-script stored procedures

4. Sql script overview • Subset of the ANSI SQL 2003 PSM (Persistent Stored Module) • includes variables, cursors, branches, loops and exception handling. • Structure • Consists of one or more declare statements or a statement block. • Script Statement Block • Consists of an assignment or if, while, cursor, try, raise, return, cache or sql statement. • script_statement ::= assignment_statement | if_statement | while_statement |cursor_statement | try_statement | raise_statement | return_statement | cache_statement | sql_statement

5. syntax: declare • DECLARE statement is used to declare variables and cursors. • variables must be declared • variables are initialized to NULL • A cursor may be defined with a SELECT statement or an EXECUTE PROCEDURE statement. • the statement is not executed until the cursor is opened using the OPEN statement

6. Declaring variables • Variable and cursor names are case insensitive • Supports same data types as SQL engine except autoinc. • Also support string data type DECLAREstrLocal String; DECLAREiLocal Integer, jLocal Integer; DECLAREcLocal Char(20), Cursor1 Cursor; DECLARE Cursor2 CURSOR as SELECT * FROM employees; OPEN Cursor2; WHILE FETCH Cursor2 DO iLocal = Cursor2.empid; END WHILE;

7. assignment • Assigns a new value to a variable DECLAREstrLocal String; DECLARE str2 String; DECLARE acVar3 Char(20), dtValDate; SETstrLocal= 'abc'; SET str2 = strLocal + 'def'; acVar3 = str2 + strLocal; dtVal = (SELECTdob FROM employees WHEREempid = 1);

8. Conditional statements • Syntax • IF condition_expr THEN statement_block [elseif_clause_list] [ELSE statement_block] END IF | END | ENDIF; DECLAREdValDouble; dVal = 3 * Rand(); IFdVal < 1 THEN SELECT* FROM employees; ELSEIFdVal < 2 THEN dVal= dVal + 3; SELECT* FROM employees WHEREempid > dVal; ELSE UPDATEemployees SETempid = empid + dVal; dVal= dVal * dVal; ENDIF; DECLAREcursor1 CURSORAS SELECT * FROM employees; OPEN cursor1; IFFETCH Cursor1 THEN INSERTINTOErrorLog ( msg ) VALUES('employees is not empty'); ENDIF; CLOSE cursor1;

9. While loops • Syntax • WHILE condition_expr DO loop_statement_blockEND WHILE | END | ENDWHILE; • Additional Commands • LEAVE: the execution of the loop_statement_block is terminated and the execution continues on the next statement after the WHILE statement. • CONTINUE: the current iteration of the loop is terminated and the next iteration of the loop is started by evaluating the WHILE condition.

10. While example DECLAREiInteger; DECLARE cursor1 CURSORASSELECT * FROM test1; OPEN cursor1; WHILEFETCH cursor1 DO IF ( cursor1.val = 0 ) OR ( cursor1.val > 50 ) THEN CONTINUE; // Do not include zero in results i = 1; WHILEi <= 50 DO IF cursor1.val * i > 50 THEN LEAVE; INSERTINTO results VALUES( cursor1.val, i, cursor1.val * i ); i = i + 1; ENDWHILE; ENDWHILE; • Example: WHILE

11. Working with tables • OPEN • cursor_statement can be specified in OPEN statement, previous OPEN statement or declaration • on open, the record pointer is positioned before the first row • opening a cursor with a SELECT statement re-executes the statement • opening a cursor with an EXECUTE PROCEDURE statement will re-execute the stored procedure and open the output table of the stored procedure • record pointer gets positioned before the first valid row. • CLOSE • Closes a cursor and allows it to be reopened • FETCH • Scrolls the cursor forward one row. NOTE: does not throw an error after last row

12. Execute immediate CREATE PROCEDURE AddAutoInc( tblNamecichar(20), colNamecichar(20)) BEGIN DECLAREcol cursor, __input CURSOR as SELECT * FROM __input; DECLARE stmt string; OPEN __input; FETCH __input; // Check to see if the column already exists OPENcol as SELECT * FROMsystem.columns WHERE parent = __input.tblName AND name = __input.colName; IF FETCH colTHEN // already exists ELSE // column does not exists, add it // Construct the ALTER TABLE statement stmt = 'ALTER TABLE ' + __input.tblName + ' ADD ' + __input.colName + ' autoinc '; EXECUTE IMMEDIATE stmt; END IF; CLOSEcol; CLOSE __input; END;

13. Error handling • Syntax • TRY statement_block [catch_clauses] [catchall_clause] [finally_clause] END TRY | END | ENDTRY; • TRY construct for handling exceptions raised intentionally or not intentionally • If no exception in statement block, execution moves to finally • If exception, execution moves to CATCH or CATCHALL clause

14. Exceptions • Handling Exceptions • if exception, __errcode and __errclass and __errtext are set. • execution moves to catch_clause • CATCH error_class clause is examined for match • execution moves to finally_clause (if exists) • catch_all clause can be used to handle all exceptions.

15. Exception handling example TRY CREATETABLE #Test( id integer, name char( 20 ) ); CATCH ADS_SCRIPT_EXCEPTION // Only do something if the error code indicates // table already exists IF __errcode = 2010 THEN DROPTABLE #Test; CREATETABLE #Test( id integer, name char( 20 ) ); ELSE RAISE; // re-raise the exception ENDIF; ENDTRY;

16. Raise • Syntax • RAISE [ exception ];exception ::= identifier ( integer_expr, char_expr ) • Behavior • RAISE statement raises an exception and execution jumps to catch or catch_all clause. • A RAISE statement without the optional exception specification re-raises an existing exception and it is only valid in the CATCH clause. • Identifier, integer_expr and char_expr values in the exception specification will be assigned to the __errclass, __errcode, and __errtext

17. Return • RETURN • The RETURN statement terminates the execution of the current script. • Example DECLARE cursor1 ASSELECT * FROM #Inpupt; TRY OPEN cursor1; CATCHALL RETURN; ENDTRY;

18. Merge statement • Syntax • MERGE [INTO] <tableref> [USING <tableref>] ON <search-condition> <WHEN MATCHED THEN <update specification> | <WHEN NOT MATCHED THEN <insert specification> • Behavior • Attempts to update matched records using the <update specification> and inserts unmatched records using the <insert specification> • For best performance ensure search condition is fully optimized

19. Merge example MERGEEmp e1 USING Employees e2ON (e1.ID = e2.ID)WHENMATCHEDTHENUPDATESET e1.SSN = e2.SSN, e1.LastName = e2.LastName, e1.FirstName = e2.FirstNameWHENNOTMATCHEDTHENINSERT (ID, SSN, LastName, FirstName) VALUES (e2.ID, e2.SSN, e2.LastName, e2.FirstName)

20. caching • Syntax • CACHE PREPARE ON | OFF | DEFAULT; • Sets Caching for semantic information • improves performance if statements are repeatedly executed (WHILE loop) • drawbacks • tables opened by cached statements are not closed • The cached semantic information about string variables may cause string concatenation errors if the size of the string variable increases. strVal = 'abc';CACHEPREPAREON;WHILEstrVal <>'abcddd' DO StrVal = strVal + 'd';ENDWHILE;CACHEPREPAREDEFAULT;

21. Cache behavior • Behavior • ON – The semantic information of the script statements is always cached. • OFF – The semantic information of the script statements is never cached. After executing a script statement, the semantic information of the statement is freed immediately. • DEFAULT– This is the default cache setting of the script engine. The semantic information of the script statements is cached only if the semantic information does not hold any table open and if no string variable is used in the script statement. • Exceptions • CACHE PREPARE ON–If a cursor is re-opened with a different cursor statement, the semantic information of the previous open will be discarded. • Demo

22. System variables • __errclass String • __errorcode Integer • __errtext String • Properties • Cannot be assigned directly • Initialized with RAISE statement • Runtime errors initialize _errclass with “ADS_SCRIPT_EXCEPTION”

23. System variables • Connection (::conn) • Name: current user connection unique name • Collation: default collation for the statements • Transactioncount: nesting level of the transaction • Statement (::stmt) • UpdateCount: the number of rows affected since the beginning of the current execution • TrigRecNo: record number of the row that fired the trigger • Collation default collation for the statement • Example syntax • ::conn.Name • ::stmt.TrigRecNo

24. Parameters • Usage • using parameters in an SQL script is only supported when the parameters are used in regular SQL DDL or DML statements. DECLAREiInteger; i = ( SELECT id FROM #input ); IFi = 1 THEN INSERTINTO table1 Values( :val1, :val2 ); ELSE UPDATE table1 SET name = :val3 WHERE id = :val4; END;

25. Parameters • Unsupported usage of a parameter in a script DECLARE strName String; strName = (SELECT name FROM table1 WHERE id=:id ); • Getting parameter value in the variables DECLARE strName String; CREATE TABLE #temp ( name memo ); TRY INSERT INTO #temp SELECT name FROM table1 WHERE id = :id; StrName = (SELECT name FROM #temp); FINALLY DROP TABLE #temp; END TRY;

26. parameters • Support for Unnamed Parameters • INSERT INTO #temp SELECT name FROM table1 WHERE id = ?; • Parameters Are Not Allowed In • Cursor Definitions • Text of EXECUTE IMMEDIATE statements

27. Stored procedures • Input parameters are accessed through __input • Output parameters are returned through __output CREATEPROCEDUREtestproc( strchar( 20 ), leninteger, strResultmemoOUTPUT ) BEGIN DECLAREstrVal String, lenInteger; strVal = ( SELECT trim( str ) FROM __input ); len = ( SELECTlenFROM __input ); WHILE length( strVal ) < lenDO strVal = strVal + strVal; ENDWHILE; INSERTINTO __output VALUES ( strVal ); END

28. User defined functions • Requirements • Defined within a data dictionary • Must return one variable • Written as an SQL script • Limitations • Only available when connected to dictionary • Can only return a single value • Cannot define aggrigate functions

29. Creating a UDF • GUI Available in ARC • Use of the CREATE FUNCTION SQL Statement • Can be Defined Inside a Package • Allows for logical grouping of functions • Function will be called with dot notation (i.e. package.function)

30. Examples • Simple Function to Concatenate a Name CREATE FUNCTIONFullName( Last CHAR(25), First CHAR(25))RETURNS CHAR(80)BEGINRETURN Trim(Last) + ', ' + Trim(First);END; • Supports Recursion CREATEFUNCTIONMyMath.Factorial( num integer ) RETURNSIntegerBEGINIF num = 0 THENRETURN 1;ELSERETURN num * MyMath.Factorial( num - 1 );ENDIF;END;

31. Bitwise operators • Supported Operators • AND ( & ) • OR ( | ) • XOR ( ^ ) • NOT ( ~ ) • SHIFT LEFT ( << ) • SHIFT RIGHT ( >> ) DECLARE @Test INTEGER; SET @Test = 12; IF ( @Test & 8 ) = 8 THEN RETURN “Third bit is set”; ENDIF;