Leveraging SQL Server 2000 in Cold Fusion Applications

Leveraging SQL Server 2000 in Cold. Fusion Applications December 9, 2003 Chris Lomvardias SRA International Christopher_Lomvardias@sra. com 5/24/01

Presentation Agenda • User-Defined Functions • XML Support • Code Examples • Resources 5/24/01

Functions • Built-In Functions in SQL Server 2000 – Built-in subroutines that encapsulate frequently performed logic – Cannot be modified • Rowset Functions – Return object that can be used as table reference – Open. XML (to be discussed later) • Aggregate Functions – Take a collection of values and return a single value – Max(), Min(), Avg(), Sum(), Count() • Scalar Functions – Take a single value and return a single value – Configuration functions (@@version) – Cursor functions – Date and Time functions (getdate) – Mathematical functions (round) – Metadata functions – Security functions – String functions (substring, upper) – System functions (@@identity, case, isnull, nullif) – System statistical functions – Text and image functions 5/24/01

User-Defined Functions • New in SQL Server 2000 – User-defined functions introduced in SQL Server 2000, enabling programmers to create custom functions (user-defined functions introduced in Cold. Fusion 5!) • Advantages – – Code simplification Readability Code reuse Maintainability • Three Types of User-Defined. Functions – scalar – inline table-valued – multistatement table-valued • Syntax – CREATE FUNCTION – ALTER FUNCTION – DROP FUNCTION 5/24/01

UDF Types • Scalar – Operate on a single value and then return a single value • Inline Table-Valued – Return clause specifies TABLE (e. g. , parameterized view) CREATE FUNCTION fn_Authors. In. State (@state varchar(5) RETURNS TABLE AS RETURN (SELECT au_lname from authors where state=@state) • Multistatement Table-Valued – Return clause specifies a TABLE type with columns and their data types 5/24/01 CREATE FUNCTION fn_All. Authors () RETURNS @Authors. Table TABLE ( au_id int, au_lname varchar(40), au_fname varchar(20)) AS BEGIN INSERT @Authors. Table SELECT au_id, au_lname, au_fname FROM authors RETURN END

UDF Limitations • Using built-in functions in UDFs – Cannot use nondeterministic functions inside a UDF (e. g. , getdate() cannot be used, though there are some workarounds) • Performance considerations – In some situations UDFs can be slower than using TSQL; if you can do it using TSQL, you are probably better off not writing a UDF • System (Global) UDFs – Not recommended but appear to work; make sure you research implications before implementing 5/24/01

FOR XML • FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE 64] • XML mode – Specifies the XML mode determines the shape of the resulting XML. Mode can be RAW, AUTO, or EXPLICIT. • XMLDATA – Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema. • ELEMENTS – If the ELEMENTS option is specified, the columns are returned as sub -elements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only. • BINARY BASE 64 – If the BINARY Base 64 option is specified, any binary data returned by the query is represented in base 64 -encoded format. 5/24/01

FOR XML Mode • Raw – Returns an element for each row in the query • Auto – Returns rows as nested elements based on table(s) • Explicit – Provides granular control of shape of generated XML • Cold. Fusion Column = XML_F 52 E 2 B 61 -18 A 1 -11 d 1 -B 105 -00805 F 49916 B <cfquery name="xml. Query" datasource="pubs"> SELECT au_id, au_lname, au_fname FROM authors FOR XML AUTO </cfquery> <cfset xml. String=""> <cfloop index="i" from="1" to="#xml. Query. Record. Count#"> <cfset xml. String=xml. String & xml. Query[xml. Query. Column. List][i]> </cfloop> 5/24/01 <cfoutput>#htmleditformat(xml. String)#</cfoutput>

FOR XML Limitations • Limitations – – – Can’t use in a View Can’t use with Cursors Doesn’t support Compute By Can’t use in a subselect, nested Select or Select Into Can’t use in a User Defined Function • Other Considerations – Doesn’t generate well-formed XML (just fragments) – NULLS do not appear – Watch out for low-order, non-printable ASCII characters (CDATA is an option) 5/24/01

FOR XML EXPLICIT • FOR XML EXPLICIT – Universal Table Element. Name!Tag. Number!Attribute. Name!Directive • Directives – – – xml hide cdata xmltext element SELECT 1 as Tag, null as Parent, au_fname as [Author!1!First. Name!Hide], au_lname as [Author!1!Last. Name!Element], au_id as [Author!1!ID] FROM authors FOR XML EXPLICIT 5/24/01

OPENXML • Open. XML provides a relational view of XML data – – SELECT INSERT UPDATE DELETE • Advantages – – – Reduce database calls Act on multiple tables in one step Act on multiple rows in one step Join with existing tables Store open content • Disadvantages – Performance issues with large documents 5/24/01

OPENXML • Usage in stored procedures – – – Declare handle Declare incoming packet Obtain document handle Execute statement against packet (Xpath) Release from memory OPENXML(i. Doc, rowpattern, flags) [WITH (rowsetschema [colpatterns] | tablename)] 0 1 2 8 Default value. Attribute centric mapping Use element centric mapping Only unconsumed data should be copied to the overflow property @mp; xmltext 5/24/01

Additional Options • • • IIS Virtual Directory (template, URL queries) Updategrams XML Bulk Load SQLXML Managed Classes -. Net Web Services Support 5/24/01

Resources • • http: //www. sqlxml. org http: //www. perfectxml. com/SQLXML. asp http: //msdn. microsoft. com/sqlxml http: //www. topxml. com/sql/default. asp 5/24/01