Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Question 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?

    Looks forward for any helps available. TQ...

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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

    Neither are particularly great
    Thanks,

    Matt

  3. #3
    Join Date
    Jul 2004
    Posts
    15
    Default that argument to NULL in SP definition . If you don't want to pass that value do not include that argument in the VB calling code. Sybase will automatically use the NULL value.

    ~Ratheesh

  4. #4
    Join Date
    Apr 2004
    Posts
    3
    Refering to
    http://manuals.sybase.com/onlinebook...190;pt=16190#X

    It saids that...

    "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 ?

    Thank you...

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You execute:
    1> SET STRING_RTRUNCATION ON
    2> GO

    However if you go to the SET section of the REFERENCE MANUAL you see the following:
    string_rtruncation
    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.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •