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

is 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

is 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

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

NoteFor datatype mappings between ASE stored procedures and SOAP user-defined Web services, see the Web Services User’s 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 WSDL 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, is displayed 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

NoteFor details on the deploy option, see sp_webservices on page 719.

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 displays 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, follows existing stored procedure rules for execution, replication, sp_helptext, and recompilation, and is executable from isql:

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

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 create a Web service successfully 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 upon whether the Web service and all referenced objects are owned by the same user.

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 in effect

See also