Unanswered: Empty string to be converted to a single space
I'm using Sybase ASE 12.5.2 (both windows and unix environment) for my development.
Encountered one situation that in need of expert's advises on this... Here's the situation.
When I pass a empty string from my VB program to perform INSERT or EDIT stmt, Sybase will automatically convert it to a single space, something like " " and to be stored in that particular column...
My doubts were:
1. Is this a norm for Sybase?
2. If I want to set the Sybase to convert it to NULL instead of " ", how can I do it?
Yes this is the case, and this is silly. From what I recall this is because ASE stores NULL strings as an empty string (!) "" instead of an actual NULL, so in order to differentiate between really NULL and just an empty string, it pads an empty string with a space.
Two things you could do:
have your application explicitly insert NULL (which is kind of what it should do since the empty string is not equivalent to NULL)
put an insert/update trigger on that table (or tables) which intercepts the empty string and converts it to NULL
"Adaptive Server truncates entries to the specified column length without warning or error, unless you set string_rtruncation on. See the set command in the Reference Manual for more information. The empty string, ""or '', is stored as a single space rather than as NULL. Thus, "abc" + "" + "def" is equivalent to "abc def", not to "abcdef"."
Will this setting able to solve my problem? If so, how and where to perform this SET STRING_RTRUNCTION ON ?
However if you go to the SET section of the REFERENCE MANUAL you see the following:
determines whether Adaptive Server raises a SQLSTATE exception when an insert or update command truncates a char, unichar, varchar or univarchar string. If the truncated characters consist only of spaces, no exception is raised. The default setting, off, does not raise the SQLSTATE exception, and the character string is silently truncated.
So I'm not sure that solves your problem. If you set the attribute to be "NULL"-able and then insert a NULL instead of a single space you might be OK.
I really hate this behavior of ASE but I don't know if anyone at Sybase has really looked into "fixing" it.