Using runexecute

The runexecute script executes a stored procedure or Transact-SQL statement on Adaptive Server Enterprise through ASE Web Services. This sample application invokes the execute Web method.

Syntax

runexecute “web_service_URLaseServerName user_ID passwordSQLX_optionoutput_class countsql_statement

Parameters

Example 1

This example checks the version number for Adaptive Server Enterprise using a select statement.

runexecute "http://johndoe-sun:8183/services/ase" johndoe-sun sa nopasswordspecified "tablename=ws" all 1 "select @@version"

ASE Web Services returns an XML schema, a DTD, and a result set containing the result of the executed statement.

Example 2

This example executes a stored procedure called booksales on the pubs2 database. The stored procedure returns the number of copies sold for a specified book title ID.

runexecute "http://johndoe-sun:8183/services/ase" johndoe-sun sa nopasswordspecified "columnstyle=attribute,format=no,rowname=wsrow,prefix=Unnamedcol,nullstyle=attribute,header=yes" all 1 "execute booksales MC2222"

ASE Web Services returns an XML schema, a DTD, and a result set containing the result of the executed statement.

This is the result set returned:

<?xml version="1.0" ?>
<resultset
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<wsrow title="Silicon Valley Gastronomic Treats"
total_sales="2032" Unnamedcol1="Books sold"/>
</resultset>

This is the DTD returned:

<!DOCTYPE ws [ 
<!ELEMENT resultset (row*)>
<!ELEMENT row (title, total_sales, Unnamedcol1)>
<!ELEMENT title (#PCDATA)>
<!ELEMENT total_sales (#PCDATA)>
<!ELEMENT Unnamedcol1 (#PCDATA)>
]>

This is the schema returned:

<?xml version="1.0" ?>
<xsd:schema
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:sqlxml="http://www.iso-standards.org/mra/9075/
sqlx">
 <xsd:import
  namespace="http://www.w3.org/2001/XMLSchema"
  schemaLocation="http://www.iso-standards.org/mra/
9075/sqlx.xsd" />
 <xsd:complexType
  name="RowType.resultset">
  <xsd:attribute name="title"
   type="VARCHAR_80" use="required"/>
  <xsd:attribute name="total_sales" type="INTEGER"
   use="optional"/>
  <xsd:attribute name="Unnamedcol1"
   type="VARCHAR_24" use="optional"/>
 </xsd:complexType>
 <xsd:complexType
  name="TableType.resultset">
  <xsd:sequence>
   <xsd:element name="wsrow"
    type="RowType.resultset"  minOccurs="0"
    maxOccurs="unbounded"/>
  </xsd:sequence>
 </xsd:complexType>
 <xsd:simpleType name="VARCHAR_80">
  <xsd:restriction base="xsd:string">
  <xsd:maxLength value="80"/>
  </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="INTEGER">
  <xsd:restriction base="xsd:integer">
   <xsd:maxInclusive value="2147483647"/>
   <xsd:minInclusive value="-2147483648"/>
  </xsd:restriction>
 </xsd:simpleType>
 <xsd:simpleType name="VARCHAR_24">
  <xsd:restriction base="xsd:string">
   <xsd:maxLength value="24"/>
  </xsd:restriction>
   </xsd:simpleType>
 <xsd:element name="resultset"   type="TableType.resultset"/> 
</xsd:schema>

Example 3

This example executes a SQL query on the pubs2 database. The query returns the last names and cities of residence for authors who do not live in the same city as their publisher.

runexecute "http://johndoe-sun:8183/services/ase" johndoe-sun sa nopasswordspecified "tablename=ws,header=yes,schemaloc='http://www-edm/remote/svr/xmltestdir/resultset.xsd',targetns='http://www-edm/remote/svr/xmltestdir/'" data 1 "select distinct au_lname, authors.city from publishers, authors where authors.city not in (select city from publishers where authors.city = publishers.city)"

ASE Web Services returns a result set containing the result of the executed statement.