Connecting Adobe Flex and Flash to Sybase ASE Using Web Services

1. Connecting Adobe Flex and Flash to Sybase ASE Using Web Services Mark Gearhartmark@mgearhart.com

2. About Mark • Independent Software Developer for last 10 years… • Except 2 years as Sr. Sybase Consultant • Worked with ASE since 1990, Flash since 2001, Flex since 2006 • Based in Raleigh, North Carolina ISUG Articles & Sybase White Paper • Time Translation and Data Tagging Methods in the Sybase Database • Implementing World Time Zones for Sybase ASE using JSQL • Extending the Sybase Enterprise Portal with Realtime Response Capabilities • Connecting Adobe Flash to Sybase ASE Using Web Services

3. Outline • What is Flash and Flex? • Overview of Adobe and ASE Capabilities for Connectivity • Implementation of the ASE Connector for: • Flash 8 • Flash 9 • Flex 2 • Should already be familiar with Web Services • Code used in this presentation is available at www.mgearhart.com

4. Why Connect Flash and Flex to ASE? • Over 1 million Flash Developers Worldwide • Over 700 million Flash Players Installed • As of 2007, only Southern Co. using ASE->WS->Adobe? First to cover and describe a WS implementation. • What is Flash used for? • Movement, Audio/Video, Interactive Sites • ASE-Connected Applications (e.g. Energy Logs, Asset Locator) • What is Flex used for? • Interactive Sites • ASE-Connected Applications (e.g. MDA Viewer)

5. Typical Flash and Flex Sites The Typical Flash Sites The Typical Flex Site ..\..\company\bin\Cp1.html

6. Applications Connected to ASE Energy Logs: Sybase+Flash Asset Tracker: Sybase+(Flash*Flex) MDA Viewer: Sybase+Flex

7. Flash and Flex Product Maturity • Flash is stable. In use for over 10 years. • Flex is new, lots of group participation. First encountered at Sybase in 2005 (SPS Bethesda).

8. Overview of Adobe and ASE Capabilities

9. Choices for Connecting to ASE • JSP’s using JDBC and Tomcat • Requires installation of an HTTP Servlet Server. • OpenAMF or JRun • Data transfer is binary. OpenAMF is free. Future of JRun? • Flex Data Services using Flex Server • Built-in support for Web Services. • Value is "publish" end of the "publish/subscribe" design pattern. • $6000 to $20,000 per CPU. • Web Services • Built-in support in both Flash 8 and Flex 2 and Sybase. • Unlike JSP’s, OpenAMF, and JRun, HTTP Server is provided by Sybase.

10. ASE Development Platforms

11. ASE Development Platforms (cont.)

12. ASE Web Services • Producer ASE Any SOAP client Input: SOAP Input: JDBC Output: SOAP Output: JDBC Web Service Producer Overview • Web Service Producer is a JETTY Server which handles WS traffic • There is also a Web Service Consumer • WSDL URL: • http://mrgearha-PC:8181/services/ase?wsdl • Methods: execute, login, logout • Output: XML, DTD, Schema, Update Count • Web Service Producer is a JETTY Server which handles WS traffic • There is also a Web Service Consumer • WSDL URL: • http://mrgearha-PC:8181/services/ase?wsdl • Methods: execute, login, logout • Output: XML, DTD, Schema, Update Count

13. Adobe interface to Sybase Web Service • var ws:WebService = new WebService(wsdl); • Performs syntax checks • Builds three methods: • ws.execute(service,username,password,sqlxoptions,sql); • ws.login(service,username,password); • ws.logout(); • Builds SOAP header for HTTP message to ASE • Sends URL requests to ASE • Monitors bytesloaded and totalbytes • Converts HTTP return data to ActionScript objects • Dispatches onResult and onFault events to user-supplied handlers

14. Format of input/output parameters • <?xml version="1.0" encoding="UTF-8"?> • <wsdl:types> • <complexType name="DataReturn"> • <sequence> • <element name="XML" nillable="true" type="xsd:string"/> • <element name="updateCount" type="xsd:int"/> • <element name="DTD" nillable="true" type="xsd:string"/> • <element name="schema" nillable="true" type="xsd:string"/> • </sequence> • </complexType> • <complexType name="ArrayOf_tns2_DataReturn"> • <complexContent> • <restriction base="soapenc:Array"> • <attribute ref="soapenc:arrayType“ • wsdl:arrayType="tns1:DataReturn[]"/> • </restriction> • </complexContent> • </complexType> • </wsdl:types> • <wsdl:message name="executeRequest"> • <wsdl:part name="service" type="xsd:string"/> • <wsdl:part name="userName" type="xsd:string"/> • <wsdl:part name="password" type="xsd:string"/> • <wsdl:part name="sqlxOptions" type="xsd:string"/> • <wsdl:part name="sql" type="xsd:string"/> • </wsdl:message> • <wsdl:message name="executeResponse"> • <wsdl:part name="executeReturn“ • type="impl:ArrayOf_tns2_DataReturn"/> • </wsdl:message> • <wsdl:binding name="aseSoapBinding" type="impl:ExecuteStoredProc"> • <wsdl:operation name="execute"> • <wsdl:input name="executeRequest"/> • <wsdl:output name="executeResponse"/> • </wsdl:operation> • </wsdl:binding> • </wsdl:definitions>

15. Wsdl specification for return data • select title,price,pubdate from pubs2..titles exec sp_who • Result[0].XML = • <row> • <title>Net Etiquette</title> • <price xsi:nil="true"/> • <pubdate>2007-05-29 07:52:18</pubdate> • </row> • <row> • <title>The Psychology of Computer Cooking</title> • <price xsi:nil="true"/> • <pubdate>2007-05-29 07:52:18</pubdate> • </row> • <row> • <title>Computer Behavior Variations</title> • <price>21.5900</price> • <pubdate>1990-10-21 00:00:00</pubdate> • </row> • . . . . • We will revisit this in detail

16. Message Bus file system Putting it all together Full Text Search Web Service Applications External Web Services SOAP/HTTP ASE - Web Services ODBC ASE ORACLE, MSFT, IBM-DB2 ODBC, Mainframe ASE Ct-Lib Client-Server Applications Enterprise Connect JDBC

17. Web Service Producer Experiences • WSDL’s for 12.X and 15.X are identical • Ran across some issues: • Logout method will abort. Null pointer exception. • Persistent connections do not timeout when the application exits. • Must issue “set nocount on” to get correct data structures on return • Sybase Tech Support is working on it.

18. Adobe Overview • A fast-moving target in 2007: • Flash 8 Professional ActionScript 2. Runs on Flash Player 8 • Flash 9 CS3 Professional ActionScript 3. Runs on Flash Player 9 • Flash Lite 2.x ActionScript 2. Runs on Flash Player 7 • Flex 2 ActionScript 3. Runs on Flash Player 9 • Flex 3 Currently in beta test • Web Service Support: • Flash 8 mx.services.WebService() class • Flash 8 WebServiceConnector visual component • Flash 9 No Web Service support in the March 27, 2007 release • Flex 2 mx.rpc.soap.WebService() class

19. Flash 8, Flash 9 and Flex 2 Examples

20. Flash 8 Implementation

21. Building the Flash 8 Connector • We’ve been using this in production for the last 3 years • Two implementation choices: • A simple stateless connection • A persistent session-oriented connection • Uses ActionScript 2 WebService() class • Example issues sql batch for select and exec sp_who • Update, Insert, Delete also works • Can issue any number of DML, DDL and sp requests • Watch out for 30 second timeout in Flash Player

22. The Simplest Operational Code • import mx.services.*; • sybase = function(wsdl:String, service:String, user:String, passwd:String, sql:String) { • var ws = new WebService(wsdl); • ws.onFault = function(fault:Object) { • trace("new WebService() error: " + fault.detail + ". " + fault.faultstring + ". "); • } • var sqlx:String = ""; • var callback = ws.execute(service, user, passwd, sqlx, sql); • callback.onResult = function(result) { • trace(result[0].XML); • trace(result[1].XML); • } • callback.onFault = function(obj:Object):Void { • trace("ws.execute() error: " + obj.fault.detail + ". " + obj.fault.faultstring + ". "); • } • } • sybase ("http://mrgearha-PC:8181/services/ase?wsdl", "MRGEARHAPC", "sa", "sybase", • "set nocount on set rowcount 2 select title, price, pubdate from pubs2..titles exec sp_who") ; • [cut/paste code] • [flash ide] • ..\FlashCS3\Flash8Start\Untitled-1.html

23. XML Results • From the WebService Class, we can access two .XML strings: • samples\outputxml.txt • But the goal is to produce a data structure for displayable results:

24. The “Array of Objects” Data Provider • Used in Flash and Flex for list-based components: • Examples: • var arr:Array = [{ title:"Book 1", price:"22.50", pubdate:"8/1/07" }, { title:"Book 2", price:"15.99", pubdate:"6/3/05" }]var dp:DataProvider = new DataProvider();dp.addItems(arr);grid_dg.dataProvider = dp; • var dp:DataProvider = new DataProvider();dp.addItem( { title:"Book 1", price:"22.50", pubdate:"8/1/07" } );dp.addItem( { title:"Book 2", price:"15.99", pubdate:"6/3/05" } );grid_dg.dataProvider = dp; • var arr:Array = new Array();arr.push( { title:"Book 1", price:"22.50", pubdate:"8/1/07" } );arr.push( { title:"Book 2", price:"15.99", pubdate:"6/3/05" } );grid_dg.dataProvider = dp;

25. The Generic Solution [result set on next slide]

26. Our Custom Structure for Flash 8

27. main • import SybManager; • import mx.utils.Delegate; • var wsdl:String = "http://mrgearha-PC:8181/services/ase?wsdl"; • var service:String = "MRGEARHAPC"; • var user:String = "sa"; • var passwd:String = "sybase"; • var sql:String = "set nocount on select title,price,pubdate from pubs2..titles order by pubdate desc exec • sp_who"; • onResult = function(evt:Object):Void { • status_txt.text = evt.data.status; • rows1_txt.text = evt.data.rowcount[0]; • rows2_txt.text = evt.data.rowcount[1]; • grid1_dg.dataProvider = evt.data.results[0]; • grid2_dg.dataProvider = evt.data.results[1]; • } • onFault = function(evt:Object):Void { • status_txt.htmlText = evt.data.status; • } • var db:SybManager = new SybManager(wsdl,service,user,passwd); • db.addEventListener ("onResult", Delegate.create(this,onResult)); • db.addEventListener ("onFault", Delegate.create(this,onFault)); • db.runService(sql);

28. The SybManager Class • class SybManager { private static var ERRMSG:String = "(SQLX mappings)"; private var ws:WebService; private var service:String; private var user:String; private var passwd:String; public function SybManager(wsdl:String, service:String, user:String, passwd:String) { mx.events.EventDispatcher.initialize(this); this.service = service; this.user = user; this.passwd = passwd; • var sybObj:SybManager = this; ws = new WebService(wsdl); ws.onFault = function(fault:Object) { • var data:Object = new Object(); • data.status = "new WebService() error: " + fault.detail + ". " + fault.faultstring + "."; • sybObj.dispatchEvent({type:"onFault", data:data}); } ws.onLoad = function(wsdlDocument) { } • } public function runService(sql:String):Void { var results:Array = new Array(); var rowcount:Array = new Array(); var xml:Array = new Array(); var schema:Array = new Array(); var sybObj:SybManager = this; var sqlx:String = “nullstyle=attribute"; var callback = ws.execute(service, user, passwd, sqlx, sql);

29. The SybManager Class (cont.) callback.onResult = function(result) { // Process each result set for (var set:Number = 0; set < result.length; set++) { var xmlResults:Array = new Array(); var document:XML = new XML(); document.ignoreWhite = true; document.parseXML(result[set].XML); for (var xmlRow:XMLNode = document.firstChild.firstChild; xmlRow != null; xmlRow = xmlRow.nextSibling) { var row:Object = new Object(); for (var xmlCol:XMLNode = xmlRow.firstChild; xmlCol != null; xmlCol = xmlCol.nextSibling) { row[xmlCol.nodeName] = (xmlCol.firstChild.nodeValue == undefined) ? “” : xmlCol.firstChild.nodeValue; } xmlResults.push(row); } // Push on to return structures. results.push(xmlResults); rowcount.push(result[set].updateCount); xml.push(result[set].XML); schema.push(result[set].schema); } var data:Object = new Object({results:results,rowcount:rowcount,status:"Success",xml:xml, schema:schema}); sybObj.dispatchEvent({type:"onResult", data:data}); • } • } • } #30. XML Parsing in ActionScript 2

30. The SybManager Class (cont.) • callback.onFault = function(fault:Object):Void { • var data:Object = new Object(); • data.status = fault.faultstring; • if (fault.faultstring.indexOf(ERRMSG) < 0) { • data.status = fault.faultstring; • } else { • data.status = fault.faultstring.substr( • fault.faultstring.indexOf(ERRMSG)+ERRMSG.length+1,fault.faultstring.length); • } • sybObj.dispatchEvent({type:"onFault", data:data}); • } • } • }

31. XML Parsing in ActionScript 2 • <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> • <row> • <title>Net Etiquette</title> • <price 15.95</price> • <pubdate>2007-05-29 07:52:18</pubdate> • </row> • <row> • <title>The Psychology of Computer Cooking</title> • <price 18.20</price> • <pubdate>2007-05-29 07:52:18</pubdate> • </row> • <row> • <title>Computer Phobic and Non-Phobic Individuals: Behavior Variations</title> • <price>21.59</price> • <pubdate>1990-10-21 00:00:00</pubdate> • </row> • </resultset> • XML Language elements used: • XML.parseXML() Method • XML.firstChild property • XML.nextSibling property #28. The SybManager Class (cont.)

32. Further Refinements • Persistent Connection uses Singleton per (Service,User,Password). • Not a huge performance gain. Stateless is pretty fast. • Code is quite involved. Can download from www.mgearhart.com • Bug in Sybase Web Services. Logout method not working. • Another bug also. Session Timeout not working.

33. Flash 9 Implementation

34. Issues with Flash 9 • Things are missing from AS3 and Flash IDE. • We have to reinvent Adobe’s mx.services.WebService() class using an HTTP Service. • However, AS3 adds ECMAScript (E4X) for XML. Very useful for operating on XML strings. • Goal is to produce a Flash 9 result which is similar to Flash 8:

35. Using E4X (great for XML Queries!!) • E4X defines a package of loop-free operators on XML structures • Minimally used in our Web Service Classes • Very useful if you want to operate directly on XML (demo):

36. Back to Web Services, the SybManager Class • package { • public class SybManager extends EventDispatcher { private var ws:MyWebService; private var wsdl:String; private var service:String; private var user:String; private var passwd:String; public function SybManager(wsdl:String, service:String, user:String, passwd:String):void { this.wsdl = wsdl; this.service = service; this.user = user; this.passwd = passwd; ws = new SybWebService(); } public function runService(sql:String):void { var sqlx:String = "nullstyle=attribute"; ws.addEventListener("onResult", onResult); ws.addEventListener("onFault", onFault); ws.execute(wsdl, service, user, passwd, sqlx, sql); } private function onResult(event:WebServiceResultEvent):void { • … Build return structure just like Flash 8 implementation • } private function onFault(event:WebServiceStatusEvent):void { • var data:Object = new Object(); • data.status = event.status; • var e:SybManagerEvent = new SybManagerEvent("onFault",data); • dispatchEvent(e); } • } • }

37. The SybWebService Class • package { • public class SybWebService extends EventDispatcher { private var loader:URLLoader; private var request:URLRequest; public function MyWebService() { } public function execute(wsdl:String, service:String, user:String, passwd:String, sqlx:String, sql:String): void { var loader:URLLoader = new URLLoader(); loader.dataFormat = URLLoaderDataFormat.TEXT; loader.addEventListener(Event.COMPLETE, onLoaded); loader.addEventListener(IOErrorEvent.IO_ERROR, onFailed); loader.addEventListener(SecurityErrorEvent.SECURITY_ERROR, onFailed); var request:URLRequest = new URLRequest(wsdl); request.method=URLRequestMethod.POST; request.requestHeaders.push(new URLRequestHeader("Content-Type", "text/xml; charset=utf-8")); request.requestHeaders.push(new URLRequestHeader("Accept", "application/soap+xml, application/dime, multipart/related, text/*")); request.requestHeaders.push(new URLRequestHeader("Cache-Control","no-cache")); request.requestHeaders.push(new URLRequestHeader("Pragma","no-cache")); request.requestHeaders.push(new URLRequestHeader("SOAPAction",""));

38. The SybWebService Class (cont.) var soapMsg:String = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> " + "<soapenv:Envelope " + "xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" " + "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" " + "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"> " + " <soapenv:Body> " + " <ns1:execute " + " soapenv:encodingStyle=\"http://schemas.xmlsoap.org/soap/encoding/\" " + " xmlns:ns1=\"urn:genwsdl.ws.ase.sybase.com\"> " + " <service xsi:type=\"xsd:string\">" + service + "</service> " + " <userName xsi:type=\"xsd:string\">" + user + "</userName> " + " <password xsi:type=\"xsd:string\">" + passwd + "</password> " + " <sqlxOptions xsi:type=\"xsd:string\">" + sqlx + "</sqlxOptions> " + " <sql xsi:type=\"xsd:string\">" + sql + "</sql> " + " </ns1:execute> " + " </soapenv:Body> " + "</soapenv:Envelope>"; request.data = soapMsg; • loader.load(request); • } • private function onFailed(event:IOErrorEvent):void { • dispatchEvent(new WebServiceStatusEvent("onFault",event.text)); • }

39. The SybWebService Class (cont.) • private function onLoaded(event:Event):void { • var result:Array = new Array(); • var sdoc:String = event.target.data; // load string for conversion • sdoc = sdoc.replace(/&lt;/g,"<"); // convert &lt; to < • sdoc = sdoc.replace(/&gt;/g,">"); // convert &gt; to > • sdoc = sdoc.replace(/&quot;/g,"\""); // convert &quot; to " • var xdoc:XML = new XML(sdoc); // convert string to XML for parseXML • var document:XMLDocument = new XMLDocument(); • document.ignoreWhite = true; • document.parseXML(xdoc..resultset); • for (var d1:XMLNode = document.firstChild; d1 != null; d1=d1.nextSibling) { • var data:Object = new Object(); • data.resultsetXML = d1; • data.xml = d1.toString(); • result.push(data); • } • document.parseXML(xdoc..schema); • for (var s1:XMLNode = document.firstChild, i:Number = 0; s1 != null; s1=s1.nextSibling, i++) { • result[i].schema = s1.toString(); • } • document.parseXML(xdoc..updateCount); • for (var u1:XMLNode = document.firstChild, j:Number = 0; u1 != null; u1=u1.nextSibling, j++) { • result[j].updateCount = u1.toString(); • } • var e:WebServiceResultEvent = new WebServiceResultEvent("onResult",result); • dispatchEvent(e); • } • } • } [click for rawoutput]

40. Flex 2 Implementation

41. Flex 2 is simple • Data Provider can now be an Array of XML Nodes. • Flex Application looks like this:

42. Our Custom Structure for Flex 2

43. The SybManager Class • package managers { • public class SybManager extends EventDispatcher { private static var ERRMSG:String = "(SQLX mappings)"; private var ws:WebService; private var service:String; private var user:String; private var passwd:String; public function SybManager(wsdl:String, service:String, user:String, passwd:String):void { this.service = service; this.user = user; this.passwd = passwd; ws = new WebService(); ws.wsdl = wsdl; ws.loadWSDL(); } public function runService(sql:String):void { var sqlx:String="tablename=ws,header=no,columnstyle=attribute,nullstyle=attribute,root=yes, format=no"; var op:mx.rpc.AbstractOperation = ws["execute"]; ws.addEventListener("result", onResult); ws.addEventListener("fault", onFault); op.arguments = {service:service,userName:user,password:passwd,sqlxOptions:sqlx,sql:sql}; var token:AsyncToken = op.send(); }

44. The SybManager Class (cont.) private function onResult(event:ResultEvent):void{ • var results:Array = new Array(); var rowcount:Array = new Array(); var xml:Array = new Array(); • var schema:Array = new Array(); // Process each result set for (var set:Number=0; set < event.result.length; set++) { var document:XML = new XML(event.result[set].XML); var rows:XMLList = document..row; var rowsCollection:XMLListCollection = new XMLListCollection(rows); // Push on to return structures. results.push(rowsCollection); rowcount.push(event.result[set].updateCount); • xml.push(event.result[set].XML); • schema.push(event.result[set].schema); } • var data:Object = {results:results, rowcount:rowcount, xml:xml, schema:schema, status:"Success"}; dispatchEvent(new SybManagerEvent("onResult",data)); } private function onFault(event:FaultEvent):void { var data:Object = new Object(); data.status = event.fault.faultString.substr( event.fault.faultString.indexOf(ERRMSG)+ERRMSG.length+1, event.fault.faultString.length); dispatchEvent(new SybManagerEvent("onFault",data)); } } • }

45. Final Results

46. Summary • Flash 8 with ActionScript 2 stable and mature. • Can use Flash 9 IDE to do Flash 8 development. • ActionScript 2 will be around, especially for Flash Lite apps. • Flash 9 missing key features, so homemade WS is one approach. • Can also embed Flash inside Flex 2 and provide data via Flex. • Use free SDK available from Adobe or use remote connection methods. • Flex 2 is straightfoward and works well with XML stream from ASE. • ASE 15, Flash 8 and Flex 9 Web Services are included in product. • Can take advantage of WS Consumer also. • Good for small results sets. For large sets, use paging. • Can find no real advantage in $6K to $20K/CPU for FDS.

47. Questions? That’s all… Thanks mark@mgearhart.com www.mgearhart.com