SQL Anywhere connection attributes

The SQL Anywhere ODBC driver supports some extended connection attributes.

  • SA_REGISTER_MESSAGE_CALLBACK   Messages can be sent to the client application from the server using the SQL MESSAGE statement. A message handler routine can created to intercept these messages. The message handler callback prototype is as follows:

    void SQL_CALLBACK message_handler(
    SQLHDBC sqlany_dbc,
    unsigned char  msg_type,
    long code,
    unsigned short  length,
    char * message
    );

    The following possible values for msg_type are defined in sqldef.h.

    • MESSAGE_TYPE_INFO   The message type was INFO.

    • MESSAGE_TYPE_WARNING   The message type was WARNING.

    • MESSAGE_TYPE_ACTION   The message type was ACTION.

    • MESSAGE_TYPE_STATUS   The message type was STATUS.

    A SQLCODE associated with the message may be provided incode. When not available, thecode parameter value is 0.

    The length of the message is contained in length.

    A pointer to the message is contained in message. Note that message is not null-terminated. Your application must be designed to handle this. The following is an example.

    memcpy( mybuff, msg, len );
    mybuff[ len ] = '\0';

    To register the message handler in ODBC, call the SQLSetConnectAttr function as follows:

    rc = SQLSetConnectAttr(
       hdbc,
       SA_REGISTER_MESSAGE_CALLBACK,
       (SQLPOINTER) &message_handler, SQL_IS_POINTER );

    To unregister the message handler in ODBC, call the SQLSetConnectAttr function as follows:

    rc = SQLSetConnectAttr(
       hdbc,
       SA_REGISTER_MESSAGE_CALLBACK,
       NULL, SQL_IS_POINTER );

  • SA_GET_MESSAGE_CALLBACK_PARM   To retrieve the value of the SQLHDBC connection handle that will be passed to message handler callback routine, use SQLGetConnectAttr with the SA_GET_MESSAGE_CALLBACK_PARM parameter.
    SQLHDBC callback_hdbc = NULL;
    rc = SQLGetConnectAttr( 
       hdbc,
       SA_GET_MESSAGE_CALLBACK_PARM,
       (SQLPOINTER) &callback_hdbc, 0, 0 );

    The returned value will be the same as the parameter value that is passed to the message handler callback routine.

  • SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK   This is used to register a file transfer validation callback function. Before allowing any transfer to take place, the ODBC driver will invoke the validation callback, if it exists. If the client data transfer is being requested during the execution of indirect statements such as from within a stored procedure, the ODBC driver will not allow a transfer unless the client application has registered a validation callback. The conditions under which a validation call is made are described more fully below.

    The callback prototype is as follows:

    int SQL_CALLBACK file_transfer_callback(
    void * sqlca,
    char * file_name,
    int is_write
    );

    The file_name parameter is the name of the file to be read or written. The is_write parameter is 0 if a read is requested (transfer from the client to the server), and non-zero for a write. The callback function should return 0 if the file transfer is not allowed, non-zero otherwise.

    For data security, the server tracks the origin of statements requesting a file transfer. The server determines if the statement was received directly from the client application. When initiating the transfer of data from the client, the server sends the information about the origin of the statement to the client software. On its part, the embedded SQL client library allows unconditional transfer of data only if the data transfer is being requested due to the execution of a statement sent directly by the client application. Otherwise, the application must have registered the validation callback described above, in the absence of which the transfer is denied and the statement fails with an error. Note that if the client statement invokes a stored procedure already existing in the database, then the execution of the stored procedure itself is considered not to have been for a client initiated statement. However, if the client application explicitly creates a temporary stored procedure then the execution of the stored procedure results in the server treating the procedure as having been client initiated. Similarly, if the client application executes a batch statement, then the execution of the batch statement is considered as being done directly by the client application.

  • SA_SQL_ATTR_TXN_ISOLATION   This is used to set an extended transaction isolation level. The following example sets a snapshot isolation level:
    SQLAllocHandle( SQL_HANDLE_DBC, env, &dbc );
    SQLSetConnectAttr( dbc, SQL_ATTR_TXN_ISOLATION,
          SA_SQL_TXN_SNAPSHOT, SQL_IS_UINTEGER );

    For more information, see Choosing ODBC transaction isolation level.