SAP Adaptive Server Enterprise (Sybase ASE)¶
sqlspec.adapters.sybase provides synchronous support for SAP Adaptive Server Enterprise (SAP ASE) 16.0+ via pymssql (>= 2.4.4) over FreeTDS TDS 5.0 wire protocol, featuring thread-local connection pooling (SybaseConfig), custom SQLGlot sybase dialect compilation, system catalog introspection, and Litestar, Events, and Google ADK session/memory store extensions.
Overview & Capabilities¶
| Capability | Details |
|---|---|
| Adapter Config | SybaseConfig (sqlspec.adapters.sybase) |
| Driver Extra | sqlspec[sybase] (pymssql >= 2.4.4 with FreeTDS TDS 5.0) |
| Execution Mode | Synchronous |
| Pool Architecture | SybaseConnectionPool (thread-local manager with pool_recycle_seconds, health_check_interval; inside an event loop each asyncio task holds its own connection) |
| Dialect & Extensions | Custom SQLGlot sybase dialect, Litestar store, Events queue store, Google ADK session & memory stores |
Installation & Quickstart¶
Connection Setup and Protocol Negotiation¶
Sybase ASE requires TDS 5.0 protocol negotiation. FreeTDS negotiates TDS 5.0 either when TDSVER=5.0 is set in the environment before process start or when a FreeTDS server section alias is defined in freetds.conf:
An alias supplies the host and TDS version; always set port explicitly as well, because pymssql overrides the port from freetds.conf and a missing or zero port raises ImproperConfigurationError.
Connection & Pool Configuration¶
Connection and Pool Parameters¶
Pass SybaseConnectionParams and SybasePoolParams keys inside connection_config={...} on SybaseConfig:
| Key | Type | Default | Description |
|---|---|---|---|
server |
str |
- | Database server host, IP, or FreeTDS server definition name. |
host |
str |
- | Alias for server. |
freetds_server |
str |
- | FreeTDS server configuration section name; requires an explicit port. |
server_alias |
str |
- | FreeTDS server configuration section name; requires an explicit port. |
is_server_alias |
bool |
- | Treat server as a FreeTDS configuration section name; requires an explicit port. |
user |
str |
- | Login user name. |
username |
str |
- | Alias for user. |
password |
str |
- | Authentication password. |
database |
str |
- | Initial database name to connect to. |
db |
str |
- | Alias for database. |
port |
int or str |
- | TCP port number; required for section aliases, otherwise defaults to 5000. |
timeout |
SybaseTimeout |
- | Query execution timeout in seconds. |
login_timeout |
SybaseTimeout |
- | Login and connection timeout in seconds. |
charset |
str |
- | Client character set encoding. |
as_dict |
bool |
- | Return query result rows as dictionaries. |
appname |
str |
- | Application name sent to the server for connection identification. |
conn_properties |
str |
- | Session initialization SQL; replaces the default connection properties when supplied. |
autocommit |
bool |
- | Enable autocommit mode for transactions. |
tds_version |
str |
- | TDS protocol version (such as '5.0' or '7.4'). |
encryption |
Literal['off', 'request', 'require'] |
- | TDS connection encryption level. |
arraysize |
int |
- | Default cursor fetch array size. |
conv |
Mapping[int or type[Any], Callable[..., Any]] |
- | Custom datatype conversion dictionary mapping TDS types to python types. |
read_only |
bool |
- | Native pymssql login intent; does not enforce ASE read-only permissions. |
pool_recycle_seconds |
int |
- | Maximum age in seconds before a pooled connection is recycled. |
health_check_interval |
float |
- | Idle duration in seconds before a connection is checked on checkout. |
extra |
dict[str, Any] |
- | Additional driver connection arguments. |
Driver Features¶
Pass SybaseDriverFeatures keys inside driver_features={...} on SybaseConfig:
| Key | Type | Default | Description |
|---|---|---|---|
json_serializer |
Callable[[Any], str] |
- | Custom callable for serializing objects to JSON strings. |
json_deserializer |
Callable[[str], Any] |
- | Custom callable for deserializing JSON strings. |
on_connection_create |
Callable[[SybaseConnection], None] |
- | Callback invoked immediately when a new connection is established. |
enable_events |
bool |
- | Enable database event listening. |
events_backend |
Literal['poll_queue'] |
- | Backend mechanism used for dispatching events. |
Type Mappings & Dialect Notes¶
| Database Type | Python Type | Notes / Feature Flag |
|---|---|---|
BIT |
bool / int |
In SAP ASE, BIT columns are non-nullable by default (BIT DEFAULT 0 NOT NULL) unless explicitly altered. |
MONEY / SMALLMONEY |
decimal.Decimal |
Fixed-scale currency types mapped to exact Decimal values. |
DATETIME / SMALLDATETIME / BIGDATETIME |
datetime.datetime |
CreateTable.from_arrow_schema() maps Arrow timestamps to BIGDATETIME (ASE's TIMESTAMP keyword denotes a binary row-version column). Normalize timezone-aware values to UTC beforehand. |
IMAGE / TEXT / UNITEXT |
bytes / str |
Large object (LOB) columns governed by SET TEXTSIZE 2147483647 in default conn_properties. |
VARCHAR(36) / VARBINARY(16) |
uuid.UUID / str |
UUID parameters serialize cleanly to character or binary representations. |
TEXT / VARCHAR (XML / JSON) |
dict / list / str |
JSON payloads serialize via json_serializer and json_deserializer. |
- Autocommit and DDL in Transactions: In Sybase ASE, statements like
CREATE TABLEandCREATE INDEXraise error2762inside multi-statement transactions unless database optionddl in tranis enabled.SybaseConfigdefaultsautocommit=Trueand begins explicit transactions on demand viasession.begin(). - Pagination (
SELECT TOP nvsROWS LIMIT): Zero-offset bounded queries (including ADK session, event, and memory queries) emitSELECT TOP nwithout anOFFSETclause. Positive offsets emitROWS LIMIT n OFFSET mon ASE 16.0+ modes that support it. - Arrow Schema Inference and Staged Storage Loads:
CreateTable.from_arrow_schema()maps string primary keys toVARCHAR(255)and raisesSQLBuilderErrorfor decimal precision above38. During staged storage loads (load_from_arrow/load_from_storage) withconflict_keys, SQLSpec updates matching rows first and inserts new rows in a second statement; coordinate concurrent loads that target the same keys. - System Catalog Introspection: Schema discovery queries
dbo.sysobjects,dbo.syscolumns,dbo.sysindexes,dbo.sysreferences, anddbo.systypes. - Migration Schemas:
-- schema:directives,default_schema, andversion_table_schemaraiseMigrationError. Qualify objects in migration SQL, and setversion_table_nameto"<schema>.ddl_migrations"to place the tracking table.