Sybase Guide

SQLAPI++ allows to work with a number of SQL database servers. It provides common mechanisms to access database, and as a general rule they work for any database server. But each server has some specific features which a developer has to know in order to leverage server's unique features and avoid potential errors.

This page collects all specific information that concerns working with Sybase server using SQLAPI++ Library. Full information about using SQLAPI++ see in How To and Documentation.

Available topics:

Connecting to a database

To connect to a database you should create a connection object and then connect it. A connection object is represented by SAConnection class. After the connection is created you need to connect it to Sybase server using SAConnection::Connect method

void Connect(
    const SAString &sDBString,
    const SAString &sUserID,
    const SAString &sPassword,
    SAClient_t eSAClient = SA_Client_NotSpecified);

with the following parameters:

sDBStringOne of the following formats:
  • "" or "@" - empty string or '@' character, connects to a default database on a default server
  • <database name> or @<database name> - connects to a database with the specified name on your default server
  • <server name>@ - connects to a default database on the specified server
  • <server name>@<database name> - connects to a database with the specified name on the specified server
sUserIDA string containing a user name to use when establishing the connection.
sPasswordA string containing a password to use when establishing the connection.
eSAClientOptional. One of the following values from SAClient_t enum:
  • SA_Sybase_Client Sybase client
  • SA_Client_NotSpecified Used by default if eSAClient parameter is omitted. You can use this default value only if you have SAConnection::setAPI method with SAPI object initialized with SA_Sybase_Client constant before

The SQLAPI++ Library requires Open Client version 10.0 or higher.

For more details see How To - Connect to database, SAConnection object, SAConnection::Connect.

Transaction isolation levels

SQL-92 defines four isolation levels, all of which are supported by SQLAPI++:

  • Read uncommitted (the lowest level where transactions are isolated just enough to ensure that physically corrupt data is not read)
  • Read committed
  • Repeatable read
  • Serializable (the highest level, where transactions are completely isolated from one another)

SQLAPI++ maps different isolation levels on Sybase server in the following way:

SA_ReadUncommitted = CS_OPT_LEVEL0 ('read uncommitted')
SA_ReadCommitted = CS_OPT_LEVEL1 ('read committed')
SA_RepeatableRead = CS_OPT_LEVEL2 ('repeatable read')
SA_Serializable = CS_OPT_LEVEL3 ('serializable')

Also 'snapshot' isolation level supported, SA_Snapshot = 'transaction snapshot'.

For more details see SAConnection::setIsolationLevel.

Working with Long or Lob (CLob, BLob) data

SQLAPI++ supports four types for working with Long or Lob (CLob, BLob) data:

NameC++ Enum
LongBinarySA_dtLongBinary
LongCharSA_dtLongChar
BLob (Binary Large object)SA_dtBLob
CLob (Character Large object)SA_dtCLob

The table below shows how SQLAPI++ data types correspond with servers original data types:

SA_dtLongBinary <=> IMAGE
SA_dtLongChar <=> TEXT
SA_dtBLob => IMAGE
SA_dtCLob => TEXT

For more details see How To - Working with Long or Lob (CLob, BLob) data.

Returning output parameters

Sybase ASE server does not provide information about parameter's direction type, that's why SQLAPI++ Library defines all parameters (except status result code) as input (SA_ParamInput). If you have input-output parameters in the procedure you have to call SAParam::setParamDirType method for these parameters before command execution and set parameter's direction type explicitly.

You shouldn't call this method for procedure status result code because it is detected correctly (as SA_ParamReturn) by the Library.

There is no need to call this method for Sybase ASA because all parameters are detected correctly automatically (including the direction type).

For more details see SAParam::setParamDirType.

In Sybase server stored procedures can have integer return codes and output parameters. The return codes and output parameters are sent in the last packet from the server and are therefore not available to the application until all result sets from stored procedure (if any) are completely processed using SACommand::FetchNext method.

SQLAPI++ Library automatically creates SAParam object to represent function return value. You can refer to this SAParam object using SQLAPI++ predefined name "RETURN_VALUE".

For more details see SACommand::Execute, SAParam object, How To - Get output parameters.

Cancelling queries

Using SACommand::Cancel method you can cancel the following types of processing on a statement:

  • A function running asynchronously on the statement.
  • A function running on the statement on another thread.

SQLAPI++ calls ct_cancel function to cancel a query with CS_CANCEL_ATTN parameter. To get more details see ct_cancel function description in Sybase documentation.

For more details see SACommand::Cancel.

Connection, command, parameter and field options

A server specific option can relate to a connection, command, parameter or field. We recommend you specify each option in an appropriate object, although it is possible to specify them in the parental object as well. In that case the option affects all the child objects.

An API related option must be specified in SAPI object. If an internal SAPI object used for the DBMS API initialization (implicit DBMS API initialization, see SAConnection::Connect method) the related DBMS specific options taken from initial connection object.

A connection related option may be specified in either SAPI object or SAConnection object. If it is specified in SAPI object it affects all the connections on that API.

A command related option may be specified in SAPI object, SAConnection object or SACommand object. If it is specified in a parent object it affects all the commands on that SAPI, SAConnection objects.

A parameter related option may be specified in SAPI object, SAConnection object, SACommand object or SAParam object. If it is specified in a parent object it affects all the parameters on that SAPI, SAConnection, SACommand objects.

A field related option may be specified in SAPI object, SAConnection object, SACommand object or SAField object. If it is specified in a parent object it affects all the fields on that on that SAPI, SAConnection, SACommand objects.

Specific options for Sybase:

Option name / ScopeDescription

SYBINTL.LIBS

API related. Should be specified before the API is initialized.

Linux/Unix only. Forces SQLAPI++ Library to use specified Sybase INTL library.

Valid values: Any valid library name list. Names separated by ':'

Default value: "libsybintl.so:libintl.so"

SYBCOMN.LIBS

API related. Should be specified before the API is initialized.

Linux/Unix only. Forces SQLAPI++ Library to use specified Sybase COMN library.

Valid values: Any valid library name list. Names separated by ':'

Default value: "libsybcomn.so:libcomn.so"

SYBTCL.LIBS

API related. Should be specified before the API is initialized.

Linux/Unix only. Forces SQLAPI++ Library to use specified Sybase TCL library.

Valid values: Any valid library name list. Names separated by ':'

Default value: "libsybtcl.so:libtcl.so"

SYBCT.LIBS

API related. Should be specified before the API is initialized.

Forces SQLAPI++ Library to use specified Sybase CT-library.

Valid values: Any valid CT-Lib library name list. Names separated by ';' on Windows or ':' on other operation systems

Default value:

  • Windows - "libsybct.dll;libct.dll"
  • Linux - "libsybct.so:libct.so"

Special value: "STATIC", forces using the linked Sybase API functions when the Library compiled with SA_STATIC_SYBASE build option

SYBCS.LIBS

API related. Should be specified before the API is initialized.

Forces SQLAPI++ Library to use specified Sybase CT-library.

Valid values: Any valid CS-Lib library name list. Names separated by ';' on Windows or ':' on other operating systems

Default value:

  • Windows - "libsybcs.dll;libcs.dll"
  • Linux - "libsybcs.so:libcs.so"

CS_VERSION

API related. Should be specified before the API is initialized.

Describes the version of Client-Library behavior that the application expects. For more information see Sybase documentation.

Valid values: "CS_VERSION_155" (15.5 behavior), "CS_VERSION_150" (15.0 behavior), "CS_VERSION_125" (12.5 behavior), "CS_VERSION_110" (11.0 behavior), "CS_VERSION_100" (10.0 behavior), "Detect" (SQLAPI++ automatically detects the maximum available version of behavior via the version of Client-Library), "Default" (SQLAPI++ tries to set 15.5 behavior; if it fails, tries to set 15.0 behavior; if it fails, SQLAPI++ tries to set 12.5 behavior; if it fails, SQLAPI++ tries to set 11.0 behavior and so on from the highest version to the lowest one until it succeeds)

Default value: "Default"

CS_LC_ALL or CS_LOCALE

API related. Should be specified before the API is initialized.

Allows to change the client side connection locale. Should be suppoorted by the server side. For more information see Sybase documentation.

Valid values: Any valid Sybase locale name

Default value: none

CS_SYB_CHARSET

API related. Should be specified before the API is initialized.

Allows to change the client side character set (actual for the non-Unicode Library version). Should be suppoorted by the server side. For more information see Sybase documentation.

Valid values: Any valid Sybase character set name

Default value: none

CS_PACKETSIZE

Connection related. Should be specified before actual connection is made.

Determines the packet size that Client-Library uses when sending Tabular Data Stream (TDS) packets. For more information see Sybase documentation.

Valid values: String containing packet size, see Sybase documentation

Default value: See Sybase documentation

CS_APPNAME or APPNAME

Connection related. Should be specified before actual connection is made.

Defines the application name that a connection will use when connecting to a server. For more information see Sybase documentation.

Valid values: See Sybase documentation

Default value: See Sybase documentation

CS_HOSTNAME or WSID

Connection related. Should be specified before actual connection is made.

Declares the name of the host machine, used when logging in to a server. For more information see Sybase documentation.

Valid values: See Sybase documentation

Default value: See Sybase documentation

CS_BULK_LOGIN

Connection related. Should be specified before actual connection is made.

Describes whether or not a connection can perform bulk copy operations into a database. For information on Bulk Copy, see the Sybase Common Libraries Reference Manual.

Valid values: "CS_TRUE", "CS_FALSE"

Default value: See Sybase documentation

CS_SEC_ENCRYPTION
CS_SEC_EXTENDED_ENCRYPTION
CS_SEC_NON_ENCRYPTION_RETRY

Connection related. Should be specified before actual connection is made.

Describe the connection password encryption details. For information on connection password encryption, see the Sybase Common Libraries Reference Manual.

Valid values: "CS_TRUE", "CS_FALSE"

Default value: See Sybase documentation

CS_HAFAILOVER

Connection related. Should be specified before actual connection is made.

Describes whether or not a connection uses HA failover. For information on CS_HAFAILOVER, see the Sybase Common Libraries Reference Manual.

Valid values: "CS_TRUE", "CS_FALSE"

Default value: none

CS_LOGIN_TIMEOUT

Connection related. Should be specified before actual connection is made.

For more information see Sybase documentation.

Default value: none

CS_TIMEOUT

Connection related. Should be specified before actual connection is made.

For more information see Sybase documentation.

Default value: none

PreFetchRows

Command related. Should be specified before command execution.

Forces SQLAPI++ Library to fetch rows in bulk, rather than retrieving records one by one.

Valid values: String containing number of rows in the fetch buffer

Default value: "1"

UseDynamicCursor or
Scrollable

Command related. Should be specified before describing parameters or command execution.

Forces SQLAPI++ to declare and open Sybase insensitive scrollable cursor.

Valid values: "True", "1"

Default value: "False"

ct_cursor

Command related. Should be specified before describing parameters or command execution.

Instructs SQLAPI++ to use "ct_cursor" for current statement execution (as opposed to defaulting to "ct_command"). See Sybase documentation for more information on using cursors and their advantages and limitations.

Valid values: A cursor name. See Sybase documentation on what constitutes a valid cursor name

Default value: none

SybaseResultType

Command related, read-only.

Stores the current result type.

Possible values: "", "CS_ROW_RESULT", "CS_STATUS_RESULT", "CS_PARAM_RESULT", "CS_COMPUTE_RESULT", "CS_CURSOR_RESULT"

SybaseResultCount

Command related, read-only.

Counts the output results, zero based.

Possible values: "0", "1", ...

For more details see SAConnection::setOption, SACommand::setOption, SAField::setOption, SAParam::setOption.

Getting native Sybase API

You can call client specific API functions which are not directly supported by SQLAPI++ Library. SAConnection::NativeAPI method returns a pointer to the set of native API functions available for Sybase. To use the database API directly you have to downcast this IsaAPI pointer to the appropriate type and use its implementation-specific members. The following example shows what type cast you have to make and what additional header file you have to include to work with Sybase API.

Cast the result to class sybAPI:

#include "sybAPI.h"
 
IsaAPI *pResult = con.NativeAPI();
sybAPI *p_sybAPI = (sybAPI *)pResult;

To get more information about DBMS API functions see this DBMS specific documentation.

For more details see SAConnection::NativeAPI.

Getting native Sybase connection related handles

You have to use native API handles when you want to call specific Sybase API functions which are not directly supported by the Library. API functions usually need to receive one or more active handles as a parameter(s). SAConnection::NativeHandles method returns a pointer to the set of native API connection related handles. To use API handles directly you have to downcast saConnectionHandles pointer to the appropriate type and use its implementation-specific members. The following example shows what type cast you have to make and what additional header file you have to include to work with specific Sybase API.

Cast the result to class sybConnectionHandles:

#include "sybAPI.h"
 
saConnectionHandles *pResult = con.NativeHandles();
sybConnectionHandles *p_sybCH = (sybConnectionHandles*)pResult;

To get more information about DBMS API functions and handles see this DBMS specific documentation.

For more details see SAConnection::NativeHandles.

Getting native Sybase command related handles

You have to use native API handles when you want to call specific Sybase API functions which are not directly supported by the Library. API functions usually need to receive one or more active handles as a parameter(s). SACommand::NativeHandles method returns a pointer to the set of native API command related handles. To use API handles directly you have to downcast saCommandHandles pointer to the appropriate type and use its implementation-specific members. The following example shows what type cast you have to make and what additional header file you have to include to work with specific Sybase API.

Cast the result to class sybCommandHandles:

#include "sybAPI.h"
 
saCommandHandles *pResult = cmd.NativeHandles();
sybCommandHandles *p_sybCH = (sybCommandHandles*)pResult;

To get more information about DBMS API functions and handles see this DBMS specific documentation.

For more details see SACommand::NativeHandles.

Error handling

When an error occurs inside SQLAPI++ Library it throws an exception of type SAException.

SAException::ErrPos method gets an error position in SQL statement. In Sybase server SAException::ErrPos method returns the number of line within SQL statement where error occurred.

For more details see How To - Error handling, SAException object.

Special header files - Compile time

The header files are in the include subdirectory of SQLAPI++ distributions:

  • #include "SQLAPI.h" - main header, should be used whenever SQLAPI++ is used
  • #include "sybAPI.h" - Sybase API, should be included if direct Sybase calls are required

For more details see Documentation - Instructions for Compiling and Linking Applications with SQLAPI++