Product Documentation
ADC
14.1-Current Release 13.1
View PDF

Advanced policy expressions: DataStream

August 1, 2023
Contributed by: 
S

The policy infrastructure on the NetScaler appliance includes expressions that you can use to evaluate and process database server traffic when the appliance is deployed between a farm of application servers and their associated database servers.

This topic includes the following sections:

  • Expressions for the MySQL Protocol
  • Expressions for Evaluating Microsoft SQL Server Connections

Expressions for the MySQL protocol

The following expressions evaluate traffic associated with MySQL database servers. You can use the request-based expressions (expressions that begin with MYSQL.CLIENT and MYSQL.REQ) in policies to make request switching decisions at the content switching virtual server bind point and the response-based expressions (expressions that begin with MYSQL.RES) to evaluate server responses to user-configured health monitors.

  • MYSQL.CLIENT. Operates on the client properties of a MySQL connection.

  • MYSQL.CLIENT.CAPABILITIES. Returns the set of flags that the client has set in the capabilities field of the handshake initialization packet during authentication. Examples of the flags that are set are CLIENT_FOUND_ROWS, CLIENT_COMPRESS, and CLIENT_SSL.

  • MYSQL.CLIENT.CHAR_SET. Returns the enumeration constant assigned to the character set that the client uses. The EQ(<m>) and NE(<m>) operators, which return Boolean values to indicate the result of a comparison, are used with this prefix. Following are the character set enumeration constants:

    • LATIN2_CZECH_CS
    • DEC8_SWEDISH_CI
    • CP850_GENERAL_CI
    • GREEK_GENERAL_CI
    • LATIN1_GERMAN1_CI
    • HP8_ENGLISH_CI
    • KOI8R_GENERAL_CI
    • LATIN1_SWEDISH_CI
    • LATIN2_GENERAL_CI
    • SWE7_SWEDISH_CI
    • ASCII_GENERAL_CI
    • CP1251_BULGARIAN_CI
    • LATIN1_DANISH_CI
    • HEBREW_GENERAL_CI
    • LATIN7_ESTONIAN_CS
    • LATIN2_HUNGARIAN_CI
    • KOI8U_GENERAL_CI
    • CP1251_UKRAINIAN_CI
    • CP1250_GENERAL_CI
    • LATIN2_CROATIAN_CI
    • CP1257_LITHUANIAN_CI
    • LATIN5_TURKISH_CI
    • LATIN1_GERMAN2_CI
    • ARMSCII8_GENERAL_CI
    • UTF8_GENERAL_CI
    • CP1250_CZECH_CS
    • CP866_GENERAL_CI
    • KEYBCS2_GENERAL_CI
    • MACCE_GENERAL_CI
    • MACROMAN_GENERAL_CI
    • CP852_GENERAL_CI
    • LATIN7_GENERAL_CI
    • LATIN7_GENERAL_CS
    • MACCE_BIN
    • CP1250_CROATIAN_CI
    • LATIN1_BIN
    • LATIN1_GENERAL_CI
    • LATIN1_GENERAL_CS
    • CP1251_BIN
    • CP1251_GENERAL_CI
    • CP1251_GENERAL_CS
    • MACROMAN_BIN
    • CP1256_GENERAL_CI
    • CP1257_BIN
    • CP1257_GENERAL_CI
    • ARMSCII8_BIN
    • ASCII_BIN
    • CP1250_BIN
    • CP1256_BIN
    • CP866_BIN
    • DEC8_BIN
    • GREEK_BIN
    • HEBREW_BIN
    • HP8_BIN
    • KEYBCS2_BIN
    • KOI8R_BIN
    • KOI8U_BIN
    • LATIN2_BIN
    • LATIN5_BIN
    • LATIN7_BIN
    • CP850_BIN
    • CP852_BIN
    • SWE7_BIN
    • UTF8_BIN
    • GEOSTD8_GENERAL_CI
    • GEOSTD8_BIN
    • LATIN1_SPANISH_CI
    • UTF8_UNICODE_CI
    • UTF8_ICELANDIC_CI
    • UTF8_LATVIAN_CI
    • UTF8_ROMANIAN_CI
    • UTF8_SLOVENIAN_CI
    • UTF8_POLISH_CI
    • UTF8_ESTONIAN_CI
    • UTF8_SPANISH_CI
    • UTF8_SWEDISH_CI
    • UTF8_TURKISH_CI
    • UTF8_CZECH_CI
    • UTF8_DANISH_CI
    • UTF8_LITHUANIAN_CI
    • UTF8_SLOVAK_CI
    • UTF8_SPANISH2_CI
    • UTF8_ROMAN_CI
    • UTF8_PERSIAN_CI
    • UTF8_ESPERANTO_CI
    • UTF8_HUNGARIAN_CI
    • INVAL_CHARSET

  • MYSQL.CLIENT.DATABASE. Returns the name of the database specified in the authentication packet that the client sends to the database server. This is the databasename attribute.

  • MYSQL.CLIENT.USER. Returns the user name (in the authentication packet) with which the client is attempting to connect to the database. This is the user attribute.

  • MYSQL.REQ. Operates on a MySQL request.

  • MYSQL.REQ.COMMAND. Identifies the enumeration constant assigned to the type of command in the request. The EQ(<m>) and NE(<m>) operators, which return Boolean values to indicate the result of a comparison, are used with this prefix. Following are the enumeration constant values:

    • SLEEP
    • QUIT
    • INIT_DB
    • QUERY
    • FIELD_LIST
    • CREATE_DB
    • DROP_DB
    • REFRESH
    • SHUTDOWN
    • STATISTICS
    • PROCESS_INFO
    • CONNECT
    • PROCESS_KILL
    • DEBUG
    • PING
    • TIME
    • DELAYED_INSERT
    • CHANGE_USER
    • BINLOG_DUMP
    • TABLE_DUMP
    • CONNECT_OUT
    • REGISTER_SLAVE
    • STMT_PREPARE
    • STMT_EXECUTE
    • STMT_SEND_LONG_DATA
    • STMT_CLOSE
    • STMT_RESET
    • SET_OPTION
    • STMT_FETCH

  • MYSQL.REQ.QUERY. Identifies the query in the MySQL request.

  • MYSQL.REQ.QUERY.COMMAND. Returns the first keyword in the MySQL query.

  • MYSQL.REQ.QUERY.SIZE. Returns the size of the request query in integer format. The SIZE method is similar to the CONTENT_LENGTH method that returns the length of an HTTP request or response.

  • MYSQL.REQ.QUERY.TEXT. Returns a string covering the entire query.

  • MYSQL.REQ.QUERY.TEXT(<n>). Returns the first n bytes of the MySQL query as a string. This is similar to HTTP.BODY(<n>).

    Parameters:

    n - Number of bytes to be returned

  • MYSQL.RES. Operates on a MySQL response.

  • MYSQL.RES.ATLEAST_ROWS_COUNT(<i>). Checks whether the response has at least i number of rows and returns a Boolean TRUE or FALSE to indicate the result.

    Parameters:

    i - Number of rows

  • MYSQL.RES.ERROR. Identifies the MySQL error object. The error object includes the error number and the error message.

  • MYSQL.RES.ERROR.MESSAGE. Returns the error message that is retrieved from the server’s error response.

  • MYSQL.RES.ERROR.NUM. Returns the error number that is retrieved from the server’s error response.

  • MYSQL.RES.ERROR.SQLSTATE. Returns the value of the SQLSTATE field in the server’s error response. The MySQL server translates error number values to SQLSTATE values.

  • MYSQL.RES.FIELD(<i>). Identifies the packet that corresponds to the i<sup>th</sup> individual field in the server’s response. Each field packet describes the properties of the associated column. The packet count (i) begins at 0.

    Parameters:

    i - Packet number

  • MYSQL.RES.FIELD(<i>).CATALOG. Returns the catalog property of the field packet.

  • MYSQL.RES.FIELD(<i>).CHAR_SET. Returns the character set of the column. The EQ(<m>) and NE(<m>) operators, which return Boolean values to indicate the result of a comparison, are used with this prefix.

  • MYSQL.RES.FIELD(<i>).DATATYPE. Returns an enumeration constant that represents the data type of the column. This is the type (also called enum_field_type) attribute of the column. The EQ(<m>) and NE(<m>) operators, which return Boolean values to indicate the result of a comparison, are used with this prefix. The possible values for the various data types are:

    • DECIMAL
    • TINY
    • SHORT
    • LONG
    • FLOAT
    • DOUBLE
    • NULL
    • TIMESTAMP
    • LONGLONG
    • INT24
    • DATE
    • TIME
    • DATETIME
    • YEAR
    • NEWDATE
    • VARCHAR (new in MySQL 5.0)
    • BIT (new in MySQL 5.0)
    • NEWDECIMAL (new in MySQL 5.0)
    • ENUM
    • SET
    • TINY_BLOB
    • MEDIUM_BLOB
    • LONG_BLOB
    • BLOB
    • VAR_STRING
    • STRING
    • GEOMETRY

  • MYSQL.RES.FIELD(<i>).DB. Returns the database identifier (db) attribute of the field packet.

  • MYSQL.RES.FIELD(<i>).DECIMALS. Returns the number of positions after the decimal point if the type is DECIMAL or NUMERIC. This is the decimals attribute of the field packet.

  • MYSQL.RES.FIELD(<i>).FLAGS. Returns the flags property of the field packet. Following are the possible hexadecimal flag values:

    • 0001: NOT_NULL_FLAG
    • 0002: PRI_KEY_FLAG
    • 0004: UNIQUE_KEY_FLAG
    • 0008: MULTIPLE_KEY_FLAG
    • 0010: BLOB_FLAG
    • 0020: UNSIGNED_FLAG
    • 0040: ZEROFILL_FLAG
    • 0080: BINARY_FLAG
    • 0100: ENUM_FLAG
    • 0200: AUTO_INCREMENT_FLAG
    • 0400: TIMESTAMP_FLAG
    • 0800: SET_FLAG

  • MYSQL.RES.FIELD(<i>).LENGTH. Returns the length of the column. This is the value of the length attribute of the field packet. The value that is returned might be larger than the actual value. For example, an instance of a VARCHAR(2) column might return a value of 2 even when it contains only one character.

  • MYSQL.RES.FIELD(<i>).NAME. Returns the column identifier (the name after the AS clause, if any). This is the name attribute of the field packet.

  • MYSQL.RES.FIELD(<i>).ORIGINAL_NAME. Returns the original column identifier (before the AS clause, if any). This is the org_name attribute of the field packet.

  • MYSQL.RES.FIELD(<i>).ORIGINAL_TABLE. Returns the original table identifier of the column (before the AS clause, if any). This is the org_table attribute of the field packet.

  • MYSQL.RES.FIELD(<i>).TABLE. Returns the table identifier of the column (after the AS clause, if any). This is the table attribute of the field packet.

  • MYSQL.RES.FIELDS_COUNT. Returns the number of field packets in the response (the field_count attribute of the OK packet).

  • MYSQL.RES.OK. Identifies the OK packet sent by the database server.

  • MYSQL.RES.OK.AFFECTED_ROWS. Returns the number of rows affected by an INSERT, UPDATE, or DELETE query. This is the value of the affected_rows attribute of the OK packet.

  • MYSQL.RES.OK.INSERT_ID. Identifies the unique_id attribute of the OK packet. If an auto-increment identity is not generated by the current MySQL statement or query, the value of unique_id, and hence the value returned by the expression, is 0.

  • MYSQL.RES.OK.MESSAGE. Returns the message property of the OK packet.

  • MYSQL.RES.OK.STATUS. Identifies the bit string in the server_status attribute of the OK packet. Clients can use the server status to check whether the current command is a part of a running transaction. The bits in the server_status bit string correspond to the following fields (in the given order):

    • IN TRANSACTION
    • AUTO_COMMIT
    • MORE RESULTS
    • MULTI QUERY
    • BAD INDEX USED
    • NO INDEX USED
    • CURSOR EXISTS
    • LAST ROW SEEN
    • DATABASE DROPPED
    • NO BACKSLASH ESCAPES

  • MYSQL.RES.OK.WARNING_COUNT. Returns the warning_count attribute of the OK packet.

  • MYSQL.RES.ROW(<i>). Identifies the packet that corresponds to the i<sup>th</sup> individual row in the database server’s response.

    Parameters:

    i - Row number

  • MYSQL.RES.ROW(<i>).DOUBLE_ELEM(<j>). Checks whether the j<sup>th</sup> column of the i<sup>th</sup> row of the table is NULL. Following C conventions, both indexes i and j start from 0. Therefore, row i and column j are actually the (i+1)<sup>th</sup> row and the (j+1)<sup>th</sup> column, respectively.

    Parameters:

    i - Row number

    j - Column number

  • MYSQL.RES.ROW(<i>).IS_NULL_ELEM(j). Checks whether the j<sup>th</sup> column of the i<sup>th</sup> row of the table is NULL. Following C conventions, both indexes i and j start from 0. Therefore, row i and column j are actually the (i+1)<sup>th</sup> row and the (j+1)<sup>th</sup> column, respectively.

    Parameters:

    i - Row number

    j - Column number

  • MYSQL.RES.ROW(<i>).NUM_ELEM(<j>). Returns an integer value from the j<sup>th</sup> column of the i<sup>th</sup> row of the table. Following C conventions, both indexes i and j start from 0. Therefore, row i and column j are actually the (i+1)<sup>th</sup> row and the (j+1)<sup>th</sup> column, respectively.

    Parameters:

    i - Row number

    j - Column number

  • MYSQL.RES.ROW(<i>).TEXT_ELEM(j). Returns a string from the j<sup>th</sup> column of the i<sup>th</sup> row of the table. Following C conventions, both indexes i and j start from 0. Therefore, row i and column j are actually the (i+1)<sup>th</sup> row and the (j+1)<sup>th</sup> column, respectively.

    Parameters:

    i - Row number

    j - Column number

  • MYSQL.RES.TYPE. Returns an enumeration constant for the response type. Its values can be ERROR, OK, and RESULT_SET. The EQ(<m>) and NE(<m>) operators, which return Boolean values to indicate the result of a comparison, are used with this prefix.

Expressions for evaluating Microsoft SQL server connections

The following expressions evaluate traffic associated with Microsoft SQL Server database servers. You can use the request-based expressions (expressions that begin with MSSQL.CLIENT and MSSQL.REQ) in policies to make request switching decisions at the content switching virtual server bind point and the response-based expressions (expressions that begin with MSSQL.RES) to evaluate server responses to user-configured health monitors.

Expression Description
MSSQL.CLIENT.CAPABILITIES Returns the OptionFlags1, OptionFlags2, OptionFlags3, and TypeFlags fields of the LOGIN7authentication packet, in that order, as a 4-byte integer. Each field is 1 byte long and specifies a set of client capabilities.
MSSQL.CLIENT.DATABASE Returns the name of the client database. The value returned is of type text.
MSSQL.CLIENT.USER Returns the user name with which the client authenticated. The value returned is of type text.
MSSQL.REQ.COMMAND Returns an enumeration constant that identifies the type of command in the request sent to a Microsoft SQL Server database server. The value returned is of type text. Examples of the values of the enumeration constant are QUERY, RESPONSE, RPC, and ATTENTION. The EQ() and NE() operators, which return Boolean values to indicate the result of a comparison, are used with this expression.
MSSQL.REQ.QUERY.COMMAND Returns the first keyword in the SQL query. The value returned is of type text.
MSSQL.REQ.QUERY.SIZE Returns the size of the SQL query in the request. The value returned is a number.
MSSQL.REQ.QUERY.TEXT Returns the entire SQL query as a string. The value returned is of type text.
MSSQL.REQ.QUERY.TEXT() Returns the first n bytes of the SQL query. The value returned is of type text. Parameters: n - Number of bytes
MSSQL.REQ.RPC.NAME Returns the name of the procedure that is being called in a remote procedure call (RPC) request. The name is returned as a string.
MSSQL.REQ.RPC.IS_PROCID Returns a Boolean value that indicates whether the remote procedure call (RPC) request contains a procedure ID or an RPC name. A return value of TRUEindicates that the request contains a procedure ID and a return value of FALSE indicates that the request contains an RPC name.
MSSQL.REQ.RPC.PROCID Returns the procedure ID of the remote procedure call (RPC) request as an integer.
MSSQL.REQ.RPC.BODY Note: Not available for releases before 10.1. Returns the body of the SQL request as a string in the form of parameters represented as “a=b” clauses separated by commas, where “a” is the RPC parameter name and “b” is its value.
MSSQL.REQ.RPC.BODY(n) Note: Not available for releases before 10.1. Returns part of the body of the SQL request as a string in the form of parameters represented as “a=b” clauses separated by commas, where “a” is the RPC parameter name and “b” is its value. Parameters are returned from only the first “n” bytes of the request, skipping the SQL header. Only complete name-value pairs are returned.
MSSQL.RES.ATLEAST_ROWS_COUNT(i) Checks whether the response has at least i number of rows. The value returned is a Boolean TRUE or FALSEvalue. Parameters: i - Number of rows
MSSQL.RES.DONE.ROWCOUNT Returns a count of the number of rows affected by an INSERT, UPDATE, or DELETE query. The value returned is of type unsigned long.
MSSQL.RES.DONE.STATUS Returns the status field from the DONE token sent by a Microsoft SQL Server database server. The value returned is a number.
MSSQL.RES.ERROR.MESSAGE Returns the error message from the ERROR token sent by a Microsoft SQL Server database server. This is the value of the MsgText field in the ERROR token. The value returned is of type text.
MSSQL.RES.ERROR.NUM Returns the error number from the ERROR token sent by a Microsoft SQL Server database server. This is the value of the Number field in the ERROR token. The value returned is a number.
MSSQL.RES.ERROR.STATE Returns the error state from the ERROR token sent by a Microsoft SQL Server database server. This is the value of the State field in the ERROR token. The value returned is a number.
MSSQL.RES.FIELD().DATATYPE Returns the data type of the ith field in the server response. The EQ() and NE() functions, which return Boolean values to indicate the result of a comparison, are used with this prefix. For example, the following expression returns a Boolean TRUE if the DATATYPE function returns a value of datetime for the third field in the response: MSSQL.RES.FIELD(<2>).DATATYPE.EQ(datetime) Parameters: i - Row number
MSSQL.RES.FIELD().LENGTH Returns the maximum possible length of the ith field in the server response. The value returned is a number. Parameters: i - Row number
MSSQL.RES.FIELD().NAME Returns the name of the ith field in the server response. The value returned is of type text. Parameters: i - Row number
MSSQL.RES.ROW().DOUBLE_ELEM() Returns a value of type double from the jth column of the ith row of the table. If the value is not a double value, an UNDEF condition is raised. Following C conventions, both indexes i and j start from 0 (zero). Therefore, row i and column j are actually the (i + 1)th row and the (j + 1)th column, respectively. Parameters: i - Row number j - Column number
MSSQL.RES.ROW().NUM_ELEM(j) Returns an integer value from the jth column of ith row of the table. If the value is not an integer value, an UNDEF condition is raised. Following C conventions, both indexes i and j start from 0 (zero). Therefore, row i and column j are actually the (i + 1)th row and the (j + 1)th column, respectively. Parameters: i - Row number j - Column number
MSSQL.RES.ROW().IS_NULL_ELEM(j) Checks whether the jth column of the ith row of the table is NULL and returns a Boolean TRUE or FALSE to indicate the result. Following C conventions, both indexes i and j start from 0 (zero). Therefore, row iand column j are actually the (i + 1)th row and the (j + 1)th column, respectively. Parameters: i - Row number j - Column number
MSSQL.RES.ROW().TEXT_ELEM(j) Returns a text string from the jth column of ith row of the table. Following C conventions, both indexes i and j start from 0 (zero). Therefore, row i and column j are actually the (i + 1)th row and the (j + 1)th column, respectively. Parameters: i - Row number j - Column number
MSSQL.RES.TYPE Returns an enumeration constant that identifies the response type. Following are the possible return values: ERROR, OK, and RESULT_SET. The EQ() and NE() operators, which return Boolean values to indicate the result of a comparison, are used with this expression.
Advanced policy expressions: DataStream
August 1, 2023
Contributed by: 
S
August 1, 2023
Contributed by: 
S

In this article

Site feedback | Your privacy choices footer linkYour Privacy Choices | Privacy and legal terms | Cookie preferences | Consent settings | docs.cloud.com
© 1999- Cloud Software Group, Inc. All rights reserved.