Applies to
- Liquibase Secure (Pro)
- Liquibase Community (Open Source)
Conditions
- Sybase ASE as the target database engine
- Deploying stored procedures or other SQL that contains double-quoted string literals
Issue Summary
A stored procedure (or other SQL) deploys successfully through a native client, such as isql, but fails with an "Invalid column name" error when the same SQL is deployed through Liquibase over a JDBC connection. Switching JDBC drivers does not resolve the problem, and the error points to a value that is not an actual column in any table.
Error Message
ERROR: Exception Primary Reason: Invalid column name '0'.(The specific value in quotes will vary depending on the double-quoted literal in your SQL, e.g., 'CU', 'PA', '0', etc.)
Root Cause
The error originates from Sybase ASE itself, not from Liquibase or the JDBC driver. The fact that multiple drivers (e.g., jConnect and jTDS) produce the identical error confirms this is a SQL parsing behavior on the database side.
The trigger is the QUOTED_IDENTIFIER setting:
- JDBC connections run with QUOTED_IDENTIFIER ON. In this mode, anything in double quotes is treated as an identifier (such as a column name) rather than a string literal.
- Native clients such as isql run with QUOTED_IDENTIFIER OFF by default. In this mode, double-quoted values are treated as string literals.
When SQL contains double-quoted string literals (for example, "0", "CU", "PA"), the database under QUOTED_IDENTIFIER ON interprets each double-quoted value as a column name. Because no such column exists, Sybase ASE returns "Invalid column name '<value>'".
This is also why the same SQL succeeds in isql but fails through Liquibase/JDBC: the two connection types use different default QUOTED_IDENTIFIER settings.
Note: Double-quoted column aliases (for example, "ID" or "COMPANY NAME") are valid identifiers and do not need to be changed. Only double-quoted string literals cause the error.
Resolution
Replace all double-quoted string literals in the SQL with single-quoted string literals. This conforms to the ANSI/ISO SQL standard, in which double quotes denote identifiers and single quotes denote string literals.
Examples:
Before:
Country = isnull(@variable, "0")
when col_type = "CU" then 'VALUE_A'
when col_type = "PA" then 'VALUE_B'After:
Country = isnull(@variable, '0')
when col_type = 'CU' then 'VALUE_A'
when col_type = 'PA' then 'VALUE_B'After making these changes, redeploy through Liquibase.
Recommended Best Practice
Keep QUOTED_IDENTIFIER ON and standardize on single-quoted string literals throughout your SQL. This is the recommended approach because:
- SQL standard compliance: The ANSI/ISO SQL standard reserves double quotes for identifiers and single quotes for string literals.
- JDBC compatibility: Most modern JDBC drivers default to QUOTED_IDENTIFIER ON. Aligning the server-side behavior with JDBC expectations avoids surprises when deploying with any JDBC-based tool.
- Portability: Standard-compliant quoting works consistently across Sybase ASE, SQL Server, and other TDS-compatible databases, so procedures transfer cleanly between systems.
- Predictability: A single consistent setting eliminates the mismatch between native-client sessions and JDBC connections that produces this error.
Teams migrating legacy scripts can adopt this convention progressively, auditing existing procedures for double-quoted string literals as they promote changes to higher environments.
Additional Best Practices for Stored Procedures
When deploying stored procedures through JDBC, include splitStatements:false in the changeset header. Stored procedures are large, multi-statement blocks; without this setting, Liquibase may parse and split the SQL on delimiters before sending it, which can submit the procedure in fragments. Setting splitStatements:false hands the entire procedure body to the driver as a single unit.
Example changeset header:
--changeset author:procedure_name runOnChange:true stripComments:false splitStatements:false endDelimiter:#
Troubleshooting Tip
If the error persists after these changes, capture the Liquibase output with --log-level=FINE and save it as a text file. The FINE log shows the exact SQL being submitted to the database, which helps pinpoint any remaining double-quoted literals.
Comments
0 comments
Article is closed for comments.