| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect for AS/400 |
![[-]](/dwicons/i_colpse.gif)
| Chapter 13 Retrieving information with System Procedures |
Chapter 13
This chapter describes the system procedures that an access service supports. It covers the following topics:
Each of the preceding systems procedures are described in sections of this chapter.
System procedures are Sybase-supplied stored procedures that return information about the access service and the target database. If the access service cannot support a procedure, that procedure returns a correctly formatted result set containing zero rows.
A client application initiates a system procedure in the same way that it initiates a CSP. For instructions to code both CSP and system procedures, see "Coding instructions" .
Returns the SQL capabilities of an access service.
sp_capabilities
The result set contains information that allows applications to successfully interact with an access service during normal query processing.
This procedure is used by ASE/CIS to determine the capabilities of a data source such as DirectConnect, enabling ASE/CIS to send only SQL that can be handled by DirectConnect.
The following table shows the result set:
Column | Datatype | Description |
ID | int | Capability ID |
CAPABILITY_NAME | char(30) | Capability name |
VALUE | int | Capability value |
DESCRIPTION | char(128) | Capability description |
The following table shows the ID and values for several access service functional capabilities:
ID | Capability | Value Description |
101 | SQL syntax | 1=SybaseT-SQL supported 2=DB2 SQL supported |
102 | Join handling | 0=Unsupported 1=No outer join supported 2=T-SQL support 3=Oracle supported |
103 | Aggregate handling | 0=Unsupported 1=ANSI supported 2=All functions |
104 | AND predicates | 0=Unsupported 1=Supported |
105 | OR predicates | 0=Unsupported 1=Supported |
106 | LIKE predicates | 0=Unsupported 1=ANSI-style supported 2=T-SQL supported |
107 | Bulk insert handling | 0=Unsupported 1=Supported |
108 | Text and image handling | 0=Unsupported 1=Text, no textptr 2=Text and textptr |
109 | Transaction handling | 0=Unsupported 1=Local supported 2=Two-phase commit supported |
110 | Text pattern handling | 0=Unsupported 1=Pattern (text) supported |
111 | order by | 0=Unsupported 1=Supported |
112 | group by | 0=Unsupported 1=ANSI supported 2=T-SQL supported |
113 | Net password encryption | 0=Unsupported 1=Supported |
114 | Object case sensitivity | 0=Case insensitive 1=Case sensitive |
115 | distinct | 0=Unsupported 1=Supported |
116 | Wild card escape | 0=Unsupported Non-zero=Escape_char(s) |
117 | Union handling | 0=Unsupported 1=Supported |
118 | String functions | 0=Unsupported 1=Substring supported 2=Oracle subset supported 3=T-SQL supported |
119 | Expression handling | 0=Unsupported 1=ANSI supported 2=T-SQL supported |
120 | Character truncation | 0=Fixed length character parameters may contain trailing blanks 1=Fixed length character parameters will not contain trailing blanks |
121 | Language events | 0=Unsupported 1=T-SQL DML without datetime in the where clause supported 2=T-SQL DML supported |
122 | Date functions | 0=Unsupported 1=T-SQL date functions supported |
123 | Math functions | 0=Unsupported 1=Oracle functions supported 2=T-SQL math functions supported |
124 | T-SQL convert functions | 0=Unsupported 1=Supported |
125 | T-SQL delete/update | 0=Sybase extensions not supported 1=Sybase extensions supported |
126 | Insert/select handling | 0=Unsupported 1=Supported |
127 | Subquery handling | 0=Unsupported 1=Supported |
128 | IN/NOT IN support | 0=Unsupported 1=Supported |
129 | CASE support | 0=Unsupported 1=Supported |
Provides the user with a report containing the following rows of information:
Row 1: the version of Open Server currently in use
Row 2: the version of DirectConnect Server currently in use
Row 3: the version of DirectConnect Access Service Library currently in use
Row 4: the version of the DBMS with which an access service is associated
sp_helpserver
The following table shows the sp_helpserver result set.
Column Name | Datatype | Description |
SERVER_PROPERTY | varchar(32) | Server property name |
PROPERTY_VALUE | varchar(32) | Server property value |
Provides information about SQL parameters that the target database supports for the specified attribute.
sp_sqlgetinfo [attribute_name]
is the name of the attribute about which information is requested. Wildcard characters are supported.
If the attribute is not found in the internal table, the access service returns an empty result set.
If a parameter is not provided, the access service returns a result set of all supported attributes.
This stored procedure provides SQL grammar, syntax, and capabilities that are supported on the target database.
The access service stores this information in a file that you can modify if necessary. The SQLInformationFile property specifies the path and file name. For more information, see "SQLInformationFile" .
The following table shows the sp_sqlgetinfo result set.
Column Name | Datatype | Description |
ATTRIBUTE_ID | intnot NULL | The numeric identifier for the attribute |
ATTRIBUTE_NAME | varchar(30) not NULL | The name of the attribute |
ATTRIBUTE_VALUE | varchar(255) NULL | The value of the attribute |
|
|