1 / 14

Utilizing SQL Server 2000 for ColdFusion: User-Defined Functions and XML Support

Learn about leveraging SQL Server 2000 in ColdFusion applications through user-defined functions, XML support, code examples, and resources. Understand function types, limitations, FOR XML mode, ColdFusion integration, and performance considerations.

rachella
Télécharger la présentation

Utilizing SQL Server 2000 for ColdFusion: User-Defined Functions and XML Support

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. Leveraging SQL Server 2000in ColdFusion ApplicationsDecember 9, 2003Chris LomvardiasSRA InternationalChristopher_Lomvardias@sra.com

  2. Presentation Agenda • User-Defined Functions • XML Support • Code Examples • Resources

  3. 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 • OpenXML (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

  4. 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 ColdFusion 5!) • Advantages • Code simplification • Readability • Code reuse • Maintainability • Three Types of User-DefinedFunctions • scalar • inline table-valued • multistatement table-valued • Syntax • CREATE FUNCTION • ALTER FUNCTION • DROP FUNCTION

  5. 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) • Multistatement Table-Valued • Return clause specifies a TABLE type with columns and their data types • CREATE FUNCTION fn_AuthorsInState (@state varchar(5) • RETURNS TABLE • AS • RETURN (SELECT au_lname from authors where state=@state) • CREATE FUNCTION fn_AllAuthors () • RETURNS @AuthorsTable TABLE • ( au_id int, au_lname varchar(40), au_fname varchar(20)) • AS • BEGIN • INSERT @AuthorsTable • SELECT au_id, au_lname, au_fname FROM authors • RETURN • END

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

  7. FOR XML • FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] • XML mode • Specifies the XML mode. 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 BASE64 • If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format.

  8. 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 • ColdFusion Column = XML_F52E2B61-18A1-11d1-B105-00805F49916B • <cfquery name="xmlQuery" datasource="pubs"> • SELECT au_id, au_lname, au_fname FROM authors • FOR XML AUTO • </cfquery> • <cfset xmlString=""> • <cfloop index="i" from="1" to="#xmlQuery.RecordCount#"> • <cfset xmlString=xmlString & xmlQuery[xmlQuery.ColumnList][i]> • </cfloop> • <cfoutput>#htmleditformat(xmlString)#</cfoutput>

  9. 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)

  10. FOR XML EXPLICIT • FOR XML EXPLICIT – Universal Table ElementName!TagNumber!AttributeName!Directive • Directives • xml • hide • cdata • xmltext • element SELECT 1 as Tag, null as Parent, au_fname as [Author!1!FirstName!Hide], au_lname as [Author!1!LastName!Element], au_id as [Author!1!ID] FROM authors FOR XML EXPLICIT

  11. OPENXML • OpenXML 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

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

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

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

More Related