External customer database text truncated

Moderator: crythias

Post Reply
marc_fauser
Znuny newbie
Posts: 28
Joined: 04 May 2010, 10:27
Znuny Version: 5.0.16

External customer database text truncated

Post by marc_fauser »

We have all customers and customer users in an external SQL Server database.

Code: Select all

    # ---------------------------------------------------- #
    # CustomerCompany configuration
    # ---------------------------------------------------- #
    $Self->{CustomerCompany} = {
        Params => {
            DSN      => 'DBI:ODBC:mssqlotrs',
            User     => ',yuser',
            Password => 'mypwd',
            SourceCharset => 'utf-8',
            DestCharset => 'utf-8',
            Table    => 'VIEW_OTRS_CustomerCompany',
            Type => 'mssql',
            ForeignDB => 1,
            CaseSensitive => 0,
        },
        CacheTTL => 0,
        ReadOnly => 1,

        # customer uniq id
        CustomerCompanyKey             => 'customer_id',
        CustomerCompanyValid           => 'valid_id',
        CustomerCompanyListFields      => [ 'name' ],
        CustomerCompanySearchFields    => [ 'name', 'customer_id' ],
        CustomerCompanySearchPrefix    => '',
        CustomerCompanySearchSuffix    => '*',
        CustomerCompanySearchListLimit => 50,

        Map => [
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'CustomerID',                  'CustomerID',    'customer_id',  0, 1, 'var', '', 1 ],
            [ 'CustomerCompanyName',         'Company',       'name',         1, 1, 'var', '[% Data.CustomerURL | html %]', 1 ],
            [ 'CustomerCompanyStreet',       'Street',        'street',       1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyZIP',          'ZIP',           'zip',          1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyCity',         'City',          'city',         1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyCountry',      'Country',       'country',      1, 1, 'var', '', 1 ],
            [ 'CustomerCompanyURL',          'URL',           'url',          1, 0, 'var', '[% Data.CustomerCompanyURL | html %]', 1 ],
            [ 'ValidID',                     'Valid',         'valid_id',     1, 1, 'int', '', 1 ],
           [ 'CustomerMisc1',          'Info 1',  'info1', 1, 0, 'var', '', 1 ],
           [ 'CustomerMisc2',          'Info 1',  'info2', 1, 0, 'var', '', 1 ],
The view basically is

Code: Select all

    select
        customer_id, street, zip, city, country, url, valid_id
        , N'🛑 ❌' collate Latin1_General_100_CI_AS_SC as info1
        , N'🛑 ❌' as info2
    from
        mycustomers
When I open the customer, I only see 🛑 or ? and not the ❌ character.
I have written a simple perl script to read from the view and write it to a text file.
I have the same problem there so I don't know whats wrong.
I have written a Powershell script on Linux to read from SQL Server and export everything to a textfile and I
also used sqlcmd and isql and everything was working. Only Perl isn't working, so I don't think it's a problem with
ODBC. Most characters are working, but not all and I cannot prevent emojis or other characters in the info field.

Do you have any hints on what could be wrong?
marc_fauser
Znuny newbie
Posts: 28
Joined: 04 May 2010, 10:27
Znuny Version: 5.0.16

Re: External customer database text truncated

Post by marc_fauser »

I fixed it by recompiling DBD::ODBC with
export DBD_ODBC_UNICODE=1
Now everything is working
Post Reply