Topic
  • 4 replies
  • Latest Post - ‏2005-12-28T14:29:11Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic SQL Function to Convert Binary Text (CCSID 65535) to Client Code Page (37) ?

‏2005-12-20T17:45:45Z |
It would be SO COOL if there was a simple CAST or CONVERT that would return normal readable text from a binary text field. It has to be there, I just don't know what it is yet. Any ideas?

Thanks!

James
Updated on 2005-12-28T14:29:11Z at 2005-12-28T14:29:11Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: SQL Function to Convert Binary Text (CCSID 65535) to Client Code Page (37) ?

    ‏2005-12-20T21:39:41Z  
    <jamesw@daddario.com> wrote:

    > It would be SO COOL if there was a simple CAST or CONVERT that would
    > return normal readable text from a binary text field. It has to be
    > there, I just don't know what it is yet. Any ideas?

    How are you accessing the database? Most of the drivers IBM provides
    have a simple boolean setting that do this automatically. With ODBC it
    is a setting in the driver, with JDBC you set the translate binary=true
    property on the JDBC URL.

    I am sure their other drivers have similar settings.

    Mark
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: SQL Function to Convert Binary Text (CCSID 65535) to Client Code Page (37) ?

    ‏2005-12-20T21:59:05Z  
    <jamesw@daddario.com> wrote:

    > It would be SO COOL if there was a simple CAST or CONVERT that would
    > return normal readable text from a binary text field. It has to be
    > there, I just don't know what it is yet. Any ideas?

    How are you accessing the database? Most of the drivers IBM provides
    have a simple boolean setting that do this automatically. With ODBC it
    is a setting in the driver, with JDBC you set the translate binary=true
    property on the JDBC URL.

    I am sure their other drivers have similar settings.

    Mark
    Sigh.. Yeah, you would hope. I'm using the new .NET 2.0 Managed provider, which doesn't include that flag. I've fallen back on

    CAST(mybintext AS VARCHAR(1000) CCSID 37)

    But it feels just a bit heavy (the 1000 is so I can do it in a function called 'AsText' and accept any text field of 1000 or less characters)
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: SQL Function to Convert Binary Text (CCSID 65535) to Client Code Page (37) ?

    ‏2005-12-20T23:44:48Z  
    Why are you storing text data in a binary 65535 field? That's not
    recommended.

    jamesw@daddario.com wrote:
    > It would be SO COOL if there was a simple CAST or CONVERT that would return normal readable text from a binary text field. It has to be there, I just don't know what it is yet. Any ideas?
    >
    > Thanks!
    >
    > James

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: SQL Function to Convert Binary Text (CCSID 65535) to Client Code Page (37) ?

    ‏2005-12-28T14:29:11Z  
    Why are you storing text data in a binary 65535 field? That's not
    recommended.

    jamesw@daddario.com wrote:
    > It would be SO COOL if there was a simple CAST or CONVERT that would return normal readable text from a binary text field. It has to be there, I just don't know what it is yet. Any ideas?
    >
    > Thanks!
    >
    > James

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)
    Kent Milligan wrote:
    > Why are you storing text data in a binary 65535 field? That's not
    > recommended.
    >

    That's what I'd like to ask the vendor that supplies this large
    manufacturing package that we use. Just a WAG, but perhaps they didn't
    know how else to handle international requirements decades ago.

    -Stevers