Support default column values in BCP with ASE #685
Description
Background
- Database tables in ASE and SQL Server may be defined with a
DEFAULTvalue that can either be a constant or a built-in function such asGETDATE() - When inserting a row specifying a NULL value for a field which has a default value defined, two behaviours are possible:
- insert NULL
- insert the default value
- MS bcp.exe and ASE bcp commandline utility both offer commandline switches to toggle these two behaviours. the switches are
-kor--ignoredefaultsrespectively, and in both cases the default value is inserted unless that switch is given. - ASE dblib bcp inserts the default value if a NULL is specified.
- In any of the above cases where the default value is to be generated, the insert should succeed even if the column is defined as non-nullable.
- When inserting a row but a column does not have a value specified at all (either by bcp commandline utility with a format file that omits the column, or ASE DBlib bcp API and the column is not given a value), the behaviour should be the same as if a NULL value was specified to insert.
- MSSQL does not allow omitting columns marked as NOT NULL.
- MSSQL
bcp.exe -fdoes not allow omitting columns at all. - ASE
bcp -fallows omitting columns , so long as the column is either nullable or has a default value. It gives an error otherwise.
Problem
- Using FreeTDS freebcp.exe or DBlib bcp, to ASE, the default value is never inserted, it always inserts NULL (and rejects the insert if the column was non-nullable).
Notes
- Using FreeTDS to MSSQL:
- the default value is correctly inserted.
- Although
freebcp.exedoes not support-k; the option-hKEEP_NULLSis a viable workaround as it has the same effect as-k.
- There are some other bugs in
freebcp.exe's handling of NULLs with MSSQL, see Handling of NULL values in FreeBCP native file format with MSSQL #686 - MSSQL's own dblib API seems to have been discontinued decades ago so I'm not testing that now -- only their commandline bcp.
Analysis
It turns out that MSSQL and ASE implement the insertion of the default value via BCP differently:
- In MSSQL on the wire, the default behaviour is to insert the default value. This is actioned by the server; if the server receives an INSERT request with a NULL or missing field, the server will fill it in with the default value.
- to turn it off , the
insert bulkSQL statement includes the clausewith(KEEP_NULLS)which tells the server to not fill in default values for this statement.
- to turn it off , the
- In ASE BCP, the server never generates the default value. Instead, the response to
insert bulkincludes a column property specifying the default value for that column. The client code is responsible for replacing NULL in the user's insertion request with the default value for the column.
Items to do on this ticket
- Support
-kand--ignoredefaultsswitches infreebcp. - When talking to ASE, correctly instantiate the default value when requested. This will involve adding code to the processing of the
insert bulkresponse to store the default value; and also applying that default value to the column data of each row being inserted.- This should be the default behaviour (to match the functionality of ASE's own tools), but we will need to provide a way for the user to request
--ignoredefaultsbehaviour when using DBlib bcp with ASE. For simplicilty I propose the use ofbcp_optionsto setKEEP_NULLS, so that the same client code works with both MSSQL and ASE.
- This should be the default behaviour (to match the functionality of ASE's own tools), but we will need to provide a way for the user to request
My testing for this issue was done with ASE 16.0 SP04 Evaluation, and SQL Server 2022 x64 16.0.1150.1.