NAME
DBIO::Sybase::Storage::ASE - Sybase ASE SQL Server support for DBIO
VERSION
version 0.900007
SYNOPSIS
# Recommended connect_info settings for Sybase ASE:
on_connect_call => [['datetime_setup'], ['blob_setup', log_on_update => 0]]
DESCRIPTION
Storage driver for Sybase ASE (Adaptive Server Enterprise) databases via DBD::Sybase.
A schema that declares the driver (the DBIO::Sybase component or the -syb shortcut) moves its storage here at the first use, without asking the server what it is ("_adapt_to_access_path" in DBIO::Sybase::Storage). The decision about placeholders follows: if your combination of DBD::Sybase and client library does not support placeholders, the storage is reblessed to DBIO::Sybase::Storage::ASE::NoBindVars, and if it supports them only with a type, auto_cast is turned on. A storage pinned to this class by hand (storage_type) takes that decision too, at its first use and on the same connection ("_adapt_to_access_path" in DBIO::Sybase::Storage); it ends in the class, and has the auto_cast, the declared base has on that connection. One pinned to a subclass of this class is left as it is.
FreeTDS connections are supported but TEXT/IMAGE columns will not work; use the native Sybase OpenClient libraries for full functionality.
Autoincrement retrieval is done via SELECT MAX(col) inside a locked transaction, as Sybase ASE provides no single-statement equivalent of SCOPE_IDENTITY(). TEXT/IMAGE (blob) columns require a separate write operation on a dedicated connection and are handled transparently.
Bulk inserts use the DBD::Sybase bulk API when available.
METHODS
_mask_error_values
my $text = $storage->_mask_error_values($text, \@values, $statement);
The seam of "_mask_error_values" in DBIO::Storage::DBI, for the texts ASE and its client library write. "mask_error_details" in DBIO::Sybase::Util replaces the value in the sentences that quote one (conversion, duplicate key, cs_convert) and every bound value of four characters or more found elsewhere in the text; then core takes out the bound values it knows. The message number, type, column, constraint, table and the with N bind values of the exception stay. Text that is undef or a reference is returned as it is, and the method never throws.
It is declared on this class and so serves the NoBindVars class, the FreeTDS class, the writer storage and the bulk storage as well. The base DBIO::Sybase::Storage does not have it: that class is shared with DBIO::MSSQL::Storage::Sybase, whose server writes other sentences.
The warning of the client library for a conversion it could not do (cs_convert failed (to_money(x))) is no error and carries no errstr; _execute_sth hands it on to the __WARN__ handler of the caller masked the same way. The errstr of the handle stays as the engine wrote it.
connect_call_blob_setup
Used as:
on_connect_call => [ [ 'blob_setup', log_on_update => 0 ] ]
Does $dbh->{syb_binary_images} = 1; to return IMAGE data as raw binary instead of as a hex string.
Recommended.
Also sets the log_on_update value for blob write operations. The default is 1, but 0 is better if your database is configured for it.
See "Handling IMAGE/TEXT data with syb_ct_get_data()/syb_ct_send_data()" in DBD::Sybase.
connect_call_datetime_setup
Used as:
on_connect_call => 'datetime_setup'
In connect_info to set:
$dbh->syb_date_fmt('ISO_strict'); # output fmt: 2004-08-21T14:36:48.080Z
Only a DBD::Sybase too old to have syb_date_fmt gets, with a warning,
$dbh->do('SET DATEFORMAT mdy'); # input fmt: 08/13/1979 18:08:55.080
instead; the output format is then not set.
This works for both DATETIME and SMALLDATETIME columns, note that SMALLDATETIME columns only have minute precision.
EXISTS QUERIES
This storage declares the supports_exists_query capability (ADR 0037), so "results_exist" in DBIO::ResultSet, "results_exist_async" in DBIO::ResultSet and "results_exist_as_query" in DBIO::ResultSet are available on Sybase ASE. It does not override "_exists_value_sql" in DBIO::Storage::DBI: core's base rendering, SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END, is grammatical ASE Transact-SQL as written -- a FROM-less select is legal (the from_clause is optional in ASE's select production), case "can be used anywhere a value expression can be used", and exists (<subquery>) is one of the search conditions its when accepts. What ASE does not document is EXISTS as a bare value expression, which is what core emitted before ADR 0037.
The declaration lives on this class rather than on DBIO::Sybase::Storage because that class is only the shared base: DBIO::MSSQL::Storage::Sybase inherits from it too, and reaches Microsoft SQL Server rather than ASE, so an ASE engine fact declared there would be an answer about the wrong engine. See "SET OPERATIONS".
Note that reaching this class at all costs a connection: the storage connects to learn the client library and whether the connection takes placeholders ("_adapt_to_access_path" in DBIO::Sybase::Storage); the server is not asked what it is. So $rs->results_exist_as_query connects on this driver even though ADR 0037 removed core's own connect-at-query-build-time. That is this driver's own dispatch cost, not core's. Only a storage pinned to this class by hand is here without one.
SET OPERATIONS
Of DBIO::ResultSet's six set operations, Sybase ASE has four: union, union_all, intersect and except, all spelled the ANSI way and inherited from core unchanged. (ASE accepts minus as an interchangeable synonym for except, so there is no spelling for this driver to contribute -- only refusals.)
intersect_all and except_all throw (ADR 0039). ASE's intersect and except always return distinct rows and take no ALL, while union on the same engine is documented as union [all]. That refusal is unconditional, because the gap is: no ASE release has ever had an ALL form of either. $rs->intersect and $rs->except are the nearest available operations, but they are not substitutes -- they collapse duplicate rows, which is a different question.
Servers older than 16.0 SP03
intersect and except are new in ASE 16.0 SP03. Every older release answers them with Incorrect syntax near the keyword ..., and SAP note 2687392 reports ASE Cluster Edition as lacking them in every version.
"_set_operation_keyword" in DBIO::Storage::DBI must answer as a static class fact -- DBIO::ResultSet bakes the keyword into the query while it is being built, so probing the server version there would open a connection during query build, purely in order to throw. This driver therefore assumes 16.0 SP03 or newer and offers a class-level opt-out for deployments that know better:
# somewhere in your application setup, for an ASE older than 16.0 SP03
DBIO::Sybase::Storage::ASE->_use_intersect_except(0);
# or for a single connection
$schema->storage->_use_intersect_except(0);
With that set, $rs->intersect and $rs->except throw while the query is being built, with a message naming the version boundary, instead of reaching the server and coming back as a syntax error. Left at its default of 1, this driver behaves exactly as it always has.
The default deliberately points the other way from DBIO::Oracle::Storage's comparable flag, because the ASE version boundary does: Oracle's missing operators exist only on its newest releases, ASE's missing operators are missing only on its oldest ones.
The keyword is resolved before any operand SQL is assembled, so a refused operation throws while the query is being built and never yields a fragment that could be embedded in a larger query.
RANDOM ORDERING
"rand" in DBIO::ResultSet drops "_random_function" in DBIO::Storage::DBI into an order_by literal. This storage answers NEWID().
Core's inherited RANDOM() is not a function Sybase ASE has -- the SAP ASE 16.0 Transact-SQL Functions list carries rand and newid and no random at all -- so $rs->rand died on the inherited default, loudly, naming the function the server could not find.
The obvious replacement is the wrong one, which is what this driver has to record. ASE's rand is not computed per row: the reference entry for its sibling rand2 defines itself by exactly that contrast -- "Unlike rand, it is computed for each returned row when it is used in the select list" -- and gives the worked example "If there are n rows in table t, the following select statement returns n different random values, not just one: select rand2() from t". So ORDER BY rand() hands every row the same sort key, the ordering is not random, and nothing anywhere says so: a silent wrong answer, strictly worse than the loud RANDOM() it would replace. rand2() has the per-row behaviour and is documented out of this position instead -- "The behavior of rand2 in places other than the select list is currently undefined" -- and an ORDER BY is such a place.
newid() is the function whose own entry carries the per-row guarantee: "By default newid returns new values for every filtered row." It yields a 32-byte human-readable GUID (varchar, no dashes) by default, so ordering by it shuffles.
That DBIO::MSSQL::Storage answers NEWID() too is a coincidence of two independently documented engine facts rather than shared Transact-SQL heritage. The declaration still lives on this class rather than on DBIO::Sybase::Storage, for the reason given under "EXISTS QUERIES".
$rs->rand($n)->as_query throws here, for a reason that has nothing to do with the function: ASE paginates through the GenericSubQ limit dialect, which refuses an ORDER BY that is not over a column of the main source. The executed path does not go that way -- _prep_for_execute lifts a rows-without-offset limit out of the query and wraps it in SET ROWCOUNT instead -- so $rs->rand($n)->all works, and emits ORDER BY NEWID() between the two SET ROWCOUNT lines.
What is verified. The four function facts above are quoted from the SAP ASE 16.0 Reference Manual: Building Blocks entries for rand, rand2 and newid, and from its Transact-SQL Functions list. No live ASE was run. What that leaves unchecked is the quality of the shuffle: that newid() produces a new value per row is documented, but whether a lexicographic sort over the leading bytes of an ASE GUID is well distributed is not.
QUERY PLANS
"explain" in DBIO::ResultSet works on Sybase ASE through the server-message seam core's ADR 0040 section 7 defines: this storage overrides "_explain_query" in DBIO::Storage::DBI and "_execute_sth" in DBIO::Storage::DBI. Obtaining a plan on ASE is a session setting, as it is on Microsoft SQL Server, but it is neither the same setting nor the same shape.
The spelling differs, and so do the semantics.
ASE has
set showplan on, notSET SHOWPLAN_ALL ON, and it does not suppress execution. SAP's own "Usingset showplanwithnoexec" says the plain form "prints the query plan but also executes the queries, which might be time consuming", and that "if you include set noexec, you can view the query plan without running the query". So the operation is five statements rather than three: the explained query has to be bracketed byset noexec on/set noexec offas well.The plan is not a result set.
ASE emits showplan as server messages -- the same channel Transact-SQL
printanddbccoutput travel on -- and DBD::Sybase surfaces them only through itssyb_err_handlercallback, documented there as "useful for handling PRINT statements in Transact-SQL, for handling messages from the Backup Server, showplan output, dbcc output", with a worked example that traps the showplan message numbers 3612-3615, 6201-6299 and 10201-10299. Nothing reaches$sth->fetchall_arrayref, so_explain_querycollects the messages through the "_execute_sth" in DBIO::Storage::DBI seam instead: the handler is installed on the handle the statement runs on, the query executes, and the messages are read back from "_server_messages" in DBIO::Storage::DBI and wrapped as a single-column rowset.
The handler is installed on the handle the statement runs on -- the one _dbh_execute received, never $self->dbh -- so tracing and therefore "redact_bind_value" in DBIO::Storage::DBI (ADR 0025), dbh_do's reconnect retry, and the read handler of a DBIO::Replicated::Storage all survive. It fires only for the showplan message numbers and returns 0 for them (showplan output is not an error); every other message is chained to the handler the borrow displaced, so a caller's own syb_err_handler (Transact-SQL print and dbcc output processing) keeps seeing its messages and keeps its verdict while the slot is taken (the restore pattern is ADR 0040 section 7). The two OFF statements run from a Scope::Guard, created only after both ONs succeeded: if the explained query throws, an unguarded OFF is skipped and the session is left with showplan on -- every later query on that connection returns a plan instead of its rows, and with noexec still on, does not even run.
No live ASE was run. The facts above are quoted from SAP's set showplan / noexec page and from DBD::Sybase's own POD. What is unverified is whether ASE 15+'s XML plan routes (set plan for show_exec_xml, show_final_plan_xml, showplan_in_xml()) answer with a rowset rather than with messages; if one of them does, this section is wrong and the driver should switch to the rowset route.
SEE ALSO
DBIO::Sybase - Sybase schema component
DBIO::Sybase::Storage - Sybase base storage
DBIO::Sybase::Storage::FreeTDS - FreeTDS connection layer
DBIO::Storage::DBI - Base DBI storage class
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.