create service

Description

Wraps the supplied SQL statement in a stored procedure with the specified name and parameters.

Syntax

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])

Parameters

service-name: is 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: clear – indicates that HTTP is used to access this Web service.

ssl – indicates HTTPS is used to access this Web service.

path: is a character-string literal specifying the user-defined path to be appended to the URL accessing the Web service. By default, path is null.

alias-name: is a character-string-literal specifying the user-defined Web service alias.

parameter_name: is 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: are the actions the user-defined Web service is to take. Any number and type of SQL statements can be included, with the exception of create view, create default, create rule, create procedure, create trigger, and use.

type: 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 Adaptive Server stored procedures and SOAP user-defined Web services, see the Web Services Users Guide.

Examples

Example 1

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 is then deployed:

1> sp_webservices 'deploy', 'all'
2> go

The Web Service Definition Language for the newly created user-defined Web service is at http://myhost:8181/services/pubs2?wsdl.

The newly created user-defined Web service is available at the following URL, where bob and bob123 are the user ID and password of the creator of the user-defined Web service:

http://myhost:8181/services/pubs2?method=rawservice&username=bob&password=bob123

The output, an Adaptive Server Enterprise version string, appears in an HTML <h1> tag in the browser window.

Example 2

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 is then deployed:

1> sp_webservices 'deploy', 'xmlservice'
2> go

For details on the deploy option, see sp_webservices in Reference Manual: Procedures.

The WSDL for user-defined Web service is at:

http://myhost:8181/services/pubs2/testing?wsdl

You can invoke the user-defined Web service from a browser at the following URL, where bob and bob123 are the user ID and password of the creator of the user-defined Web service:

http://myhost:8181/services/pubs2/testing?method=xmlervice&
username=bob&password=bob123

The output appears as XML in the browser window.

Example 3

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:

http://localhost:8181/pubs2/myservices/args/sp_who_service

The WSDL for the service is available at:

http://localhost:8181/pubs2/myservices/args?wsdl

The signature for the Web method, described in the WSDL file, is:

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

Usage

Except for the following differences, the resulting stored procedure behaves the same as a stored procedure created with the create procedure command, following 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 re-create the create service command.
The specified service name cannot create a stored procedure group.

To make a user-defined Web service available through the Adaptive Server 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create service permission defaults to the database owner, who can transfer it to other users.

Permission to use a Web service must be granted explicitly with the grant command and may be revoked with the revoke command.

Permissions on objects at service creation When you create a Web service, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the service. Therefore, you can successfully create a Web service even though you do not have access to its objects. All permission checks occur when a user executes the Web service.

Permissions on objects at Web service execution When the Web service is executed, permission checks on objects depend on whether the Web service and all referenced objects are owned by the same user.

If the Web service’s objects are owned by different users, the invoker must have been granted direct access to the objects. For example, if the Web service performs a select from a table that the user cannot access, the Web service execution fails.
If a Web service and its objects are owned by the same user, however, special rules apply. The invoker automatically has “implicit permission” to access the Web service’s objects even though the invoker could not access them directly. Without having to grant users direct access to your tables and views, you can give them restricted access with a stored procedure. In this way, a stored procedure can be a security mechanism. For example, invokers of the Web service might be able to access only certain rows and columns of your table.

A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
11	create	create services	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect