The create service command wraps the supplied SQL statement in a stored procedure with the specified name and parameters. Except for the following differences, the resulting stored procedure behaves the same as a stored procedure created with the create procedure command, follows existing stored procedure rules for execution, replication, sp_helptext, and recompilation, and is executable from isql:
The resulting stored procedure can be dropped only with the drop service command, not the drop procedure command.
The syscomments table is populated with DDL necessary to recreate the create service command.
The specified service name may not create a stored procedure group.
To make a user-defined Web service available through
the ASE Web Services Engine, you must use the deploy option
of sp_webservices. However, the stored
procedure for a user-defined Web service is accessible from isql, even
if it has not been deployed. For information on the deploy option
of sp_webservices, see “deploy” in “Using sp_webservices.”
create service service-name [secure security_options ] [, userpath path]
[, alias alias-name]
type { xml | raw | soap }
[[(@parameter_name datatype [(length ) | (precision [, scale ])]
[= default][output]
[, @parameter_name datatype [(length ) | (precision [, scale ])]
[= default][output]]...[)]]
as SQL_statements
security_options ::= (security_option_item [security_option_item])
service-name – the name for the user-defined Web service. This name can be any name that is valid for a stored procedure. When the drop service command is invoked with this service name, the corresponding stored procedure is dropped. If you specify the name of an existing service, an exception results.
security_option_item – either clear or ssl:
clear indicates that HTTP is used to access this Web service.
ssl indicates HTTPS is used to access this Web service
path – a character-string literal specifying the user-defined path to be appended to the URL accessing the Web service. This path is null by default.
alias-name – a character-string-literal specifying the user-defined Web service alias.
parameter_name – the name of an argument to the user-defined Web service. The value of this parameter is supplied when the Web service is executed. Parameter names must be preceded by the @ sign and conform to the rules for identifiers. These conditions are the same as for the parameter_name parameter of the create procedure command.
SQL_statements – the actions the user-defined Web service is to take. Any number and kind of SQL statements can be included, with the exception of create view, create default, create rule, create procedure, create trigger, and use. These conditions are the same as for the SQL_statements parameter of the create procedure command.
type – can be soap, raw, or xml:
soap implies an HTTP POST request and must be compliant with all the SOAP rules. The data is returned in SQL/XML format.
raw indicates that the output is to be sent without any alteration or reformatting. This implies an HTTP GET request. The invoked stored procedure can specify the exact output.
xml indicates that the result set output is returned in SQL/XML format. This implies an HTTP GET request.
For datatype mappings between ASE stored procedures
and SOAP user-defined Web services, see “ASE-to-SOAP datatype mappings for the create service command” in Appendix C, “SOAP and Adaptive Server Enterprise Datatype Mapping.”
In this example, a user-defined Web service, rawservice, of type raw is created to return the version of the current database. The create service command is entered from the isql command line for the pubs2 database:
1> use pubs2 2> go 1> create service rawservice type raw as select '<html><h1>' + @@version + '</h1></html>' 2> go
The newly created user-defined Web service must then be deployed:
1> sp_webservices 'deploy', 'all' 2> go
For example, if the sample file is named testraw.html and it is copied to $SYBASE/WS-15_0/producer (%SYBASE%\WS-15_0\producer on Windows), you can access the page https://myhost:8182/testraw.html, where the username, password, and database are bob, bob123, and pubs2, respectively. Click “Access rawservice” to display the result.
For details on the deploy option
for sp_webservices, see “Using sp_webservices with user-defined Web services”.
The WSDL for the newly created user-defined Web service can be found at the following URL:
https://myhost:8182/services/pubs2?wsdl
The output, an Adaptive Server Enterprise version string, is displayed in an HTML <h1> tag in the browser window:
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Inovke version</title> <link rel="stylesheet" type="text/css" href="ws.css"> </head> <body> <form method="POST" action=services> <p>Username: <input type="text" name="username"</p> <p>Password: <input type="text" name="password"</p> <p>Database: <input type="text" name="dboralias"</p> <p><input type="hidden" value="rawservice" name="method"</p> <p><input type="submit" value="Access rawservice" name="B2"> </form> </body> </html>
In this example, a user-defined Web service, xmlservice, of type xml is created to return the version of the current database. The create service command is entered from the isql command line for the pubs2 database:
1> use pubs2 2> go 1> create service xmlservice userpath "testing" type xml as select @@version 2> go
The newly created user-defined Web service must then be deployed:
1> sp_webservices 'deploy', 'xmlservice' 2> go
For details on the deploy option
for sp_webservices, see “Using sp_webservices with user-defined Web services”.
The WSDL for user-defined Web service can be found at the following URL:
https://localhost:8182/services/pubs2/testing?wsdl
For example, if the HTML page is named testxml.html, and you copy the the HTML file to $SYBASE/WS-15_0/producer (%SYBASE%\WS-15_0\producer on Windows). Access the pagehttps://myhost:8182/testxml.html and input these parameters:
bob – the user ID
bob123 – the password
pubs2/testing – the database
Click “Retrieve Version” to display the result.
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Inovke version</title> <link rel="stylesheet" type="text/css" href="ws.css"> </head> <body> <form method="POST" action=services> <p>Username: <input type="text" name="username"</p> <p>Password: <input type="text" name="password"</p> <p>Database: <input type="text" name="dboralias"</p> <p><input type="hidden" value="xmlservice" name="method"</p> <p><input type="submit" value="Retrieve Version" name="B1"> </form> </body> </html>
In this example, a user-defined Web service is made available to a SOAP client to execute the stored procedure sp_who. One argument is supplied, and the optional userpath token is specified:
create service sp_who_service userpath 'myservices/args' type soap @loginname varchar(30) as exec sp_who @loginname
The Web service is created as sp_who_service in the pubs2 database and, after being deployed, it is accessible at the following URL:
http://localhost:8181/pubs2/myservices/args/sp_who_service
The WSDL for the service is available at the following URL:
http://localhost:8181/pubs2/myservices/args?wsdl
The signature for the Web method, described in the WSDL file, is as follows:
DataReturn[] sp_who_service (xsd:string username, xsd:string password, xsd:string loginname)
The new service is invoked by a SOAP client with one parameter, loginname, of type varchar(30).
