[otrs] [Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset

KIRAN IT kiran1432u at gmail.com
Sat Jul 2 10:42:15 GMT 2011


This is a notification from the Help Desk.




On Jul 02, 2011 @ 04:09 pm, michiel.beijen at otrs.com wrote:
Flavio,


==Ticket History==

On Jul 02, 2011 @ 04:08 pm, flavio at piramide.ch wrote:
Hello Mike and thanks for your quick feedback.

> If your remote database is unicode, add this to your mapping.
> 
>            SourceCharset => 'utf-8',
>            DestCharset => 'utf-8',
> 
> Actually, I'm not quite sure if DBD::Sybase would also return 
> Unicode, you might want to experiment with trying either 
> 'utf-8' and 'iso-8859-1' as values for SourceCharset.

I've tried inserting the lines you suggested *before* the "Params"
section, like:

        Name   => 'Database clienti',
        Module => 'Kernel::System::CustomerUser::DB',
        SourceCharset => 'utf-8',
        DestCharset => 'utf-8',
        Params => {

but it didn't change my output.

You say "if your remote database is unicode", what do you mean by this?

Also tried with:

        SourceCharset => 'Latin1_General',
        DestCharset => 'utf-8',

but still no success.

Where may the problem be?

Kind regards,
Flavio Boniforti

PIRAMIDE INFORMATICA SAGL
Via Ballerini 21
6600 Locarno
Switzerland
Phone: +41 91 751 68 81
Fax: +41 91 751 69 14
URL: http://www.piramide.ch
E-mail: flavio at piramide.ch 
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

------
On Jul 02, 2011 @ 04:08 pm, michiel.beijen at otrs.com wrote:
If your remote database is unicode, add this to your mapping.

           SourceCharset => 'utf-8',
           DestCharset => 'utf-8',

Actually, I'm not quite sure if DBD::Sybase would also return Unicode,
you might want to experiment with trying either 'utf-8' and
'iso-8859-1' as values for SourceCharset.

--
Mike

------
On Jul 02, 2011 @ 03:50 pm, flavio at piramide.ch wrote:
Hello people... 
Anybody has any clues on how to proceed?

Thanks,
F. 

>

------
On Jul 02, 2011 @ 03:41 pm, flavio at piramide.ch wrote:
Hello again Mike...

> There's one thing to pay attention here: OTRS can cache the 
> results from the database query for a couple of minutes. This 
> means that if you search for 'jim', change your config and 
> again search for 'jim', you'll probably see cached results. 
> In that case, the configuration change did not affect your 
> results. Please remove the cache and/or change TTL to make 
> sure this does not apply to you.

I don't know where to remove cache or change TTL, but for sure it's not
"caching" what is disturbing: as I'm running OTRS on a Virtual Machine,
and I reboot it when I reboot my PC, I feel it's no caching involved.

> > I understand you know a bit about SQL, therefore I'm bothering a bit
> > further: would it change if I'd use ODBC connection instead 
> of Sybase 
> > (like I'm doing now)?
> > Would it be worth a try? If yes, I'd need some help because 
> I already 
> > tried to connect via ODBC but keep getting failures (when 
> testing with 
> > "isql" command). Instead, tsql command works pretty well!
> 
> Sure, that could help. I remember I used DBD::Sybase once for 
> a customer database from Linux >> MS SQL Server successfully. 

Could you eventually provide that configuration excerpt?

> But DBD::ODBC plus an ODBC driver manager should also work. 
> If you have issues, you can certainly try that.

Well, I'm having issues with ODBC connection using FreeTDS, would it
help if I'd be posting my configs here?

Kind regards,
Flavio Boniforti

PIRAMIDE INFORMATICA SAGL
Via Ballerini 21
6600 Locarno
Switzerland
Phone: +41 91 751 68 81
Fax: +41 91 751 69 14
URL: http://www.piramide.ch
E-mail: flavio at piramide.ch 
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

------
On Jul 02, 2011 @ 03:41 pm, michiel at beefreeit.nl wrote:
Flavio,

------
On Jul 02, 2011 @ 03:41 pm, flavio at piramide.ch wrote:
Hello Mike,

> No, you should actually include them in the Params section, 
> otherwise they're not used.

OK, put them under the "Params", nothing changed...

> Latin1_General is not a character set name, or at least not 
> one recognized by Perl's Encode, it's a MS SQL Server Collation name.
> Please try iso-8859-1 instead.

Tried with both of them:

        Params => {
                SourceCharset => 'iso-8859-1',
                DestCharset => 'utf-8',

and

        Params => {
                SourceCharset => 'utf-8',
                DestCharset => 'utf-8',

No way to get some *written* output (you took a look at the output shown
in my first post).

I understand you know a bit about SQL, therefore I'm bothering a bit
further: would it change if I'd use ODBC connection instead of Sybase
(like I'm doing now)?
Would it be worth a try? If yes, I'd need some help because I already
tried to connect via ODBC but keep getting failures (when testing with
"isql" command). Instead, tsql command works pretty well!

Kind regards and thanks again.

Flavio Boniforti

PIRAMIDE INFORMATICA SAGL
Via Ballerini 21
6600 Locarno
Switzerland
Phone: +41 91 751 68 81
Fax: +41 91 751 69 14
URL: http://www.piramide.ch
E-mail: flavio at piramide.ch 
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

------
On Jul 02, 2011 @ 03:41 pm, michiel.beijen at otrs.com wrote:
Flavio,

------
On Jul 02, 2011 @ 03:41 pm, flavio at piramide.ch wrote:
Hello Mike and thanks for your quick feedback.

> If your remote database is unicode, add this to your mapping.
> 
>            SourceCharset => 'utf-8',
>            DestCharset => 'utf-8',
> 
> Actually, I'm not quite sure if DBD::Sybase would also return 
> Unicode, you might want to experiment with trying either 
> 'utf-8' and 'iso-8859-1' as values for SourceCharset.

I've tried inserting the lines you suggested *before* the "Params"
section, like:

        Name   => 'Database clienti',
        Module => 'Kernel::System::CustomerUser::DB',
        SourceCharset => 'utf-8',
        DestCharset => 'utf-8',
        Params => {

but it didn't change my output.

You say "if your remote database is unicode", what do you mean by this?

Also tried with:

        SourceCharset => 'Latin1_General',
        DestCharset => 'utf-8',

but still no success.

Where may the problem be?

Kind regards,
Flavio Boniforti

PIRAMIDE INFORMATICA SAGL
Via Ballerini 21
6600 Locarno
Switzerland
Phone: +41 91 751 68 81
Fax: +41 91 751 69 14
URL: http://www.piramide.ch
E-mail: flavio at piramide.ch 
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

------
On Jul 02, 2011 @ 03:41 pm, michiel.beijen at otrs.com wrote:
If your remote database is unicode, add this to your mapping.

           SourceCharset => 'utf-8',
           DestCharset => 'utf-8',

Actually, I'm not quite sure if DBD::Sybase would also return Unicode,
you might want to experiment with trying either 'utf-8' and
'iso-8859-1' as values for SourceCharset.

--
Mike

------
On Jul 02, 2011 @ 03:41 pm, flavio at piramide.ch wrote:
Hello everybody.

I'll be starting from the latest problem I'm encountering, which is as
follows: when I do a search in my Customer Database, I get the correct
number of records, but they print out as blank/empty lines (look here:
http://tinyurl.com/2cx44r2 ).

Here the background and the setup:

Debian Sid - OTRS 2.4.9

I have my customers already stored in an MSSQL db (SQL Express 2005 on
Windows 2003 Server), therefore I tried to connect that db to my OTRS
installation. I seem to have managed it using FreeTDS, because I can
connect to MSSQL by hand with tsql.

My Config.pm for my CustomerDB looks like this:

    # CustomerUser
    # (customer user database backend and settings)
    $Self->{CustomerUser} = {
        Name   => 'Database clienti',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
            DSN => 'DBI:Sybase:',
            DSN =>
'DBI:Sybase:server=sqlexpress;database=Piramide_Informatica',
            User => 'sa',
            Password => 'password',
            Type => 'mssql',
            Table => 'v_customer',
            Attribute => {},
        },

        # customer uniq id
        CustomerKey => 'CUST_ID',

        # customer #
        CustomerID             => 'CUST_ID',
#        CustomerValid          => 'valid_id',
        CustomerUserListFields => [ 'COMPANY', 'LASTNAME', 'FIRSTNAME',
'EMAIL' ],

#        CustomerUserListFields => ['login', 'first_name', 'last_name',
'customer_id', 'email' ],
        CustomerUserSearchFields           => [ 'COMPANY', 'LASTNAME',
'FIRSTNAME' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 50,
        CustomerUserPostMasterSearchFields => ['EMAIL'],
        CustomerUserNameFields     => [ 'COMPANY', 'LASTNAME',
'FIRSTNAME' ],
        CustomerUserEmailUniqCheck => 1,

        Map => [

            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required,
storage-type, http-link, readonly, http-link-target
#            [ 'UserSalutation', 'Salutation', 'salutation', 1, 0,
'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'COMPANY', 1, 1, 'var',
'', 0 ],
            [ 'UserLastname',   'Lastname',   'LASTNAME',  1, 1, 'var',
'', 0 ],
            [ 'UserEmail',      'Email',      'EMAIL',      1, 1, 'var',
'', 0 ],

#            [ 'UserEmail',      'Email', 'email',           1, 1,
'var',
'$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data
{"TicketID"}&ArticleID=$Data{"ArticleID"}', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'CUST_ID', 0, 1, 'var',
'', 0 ],

#            [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0,
'var', '', 0 ],
#            [ 'UserPhone',        'Phone',       'phone',        1, 0,
'var', '', 0 ],
#            [ 'UserFax',          'Fax',         'fax',          1, 0,
'var', '', 0 ],
#            [ 'UserMobile',       'Mobile',      'mobile',       1, 0,
'var', '', 0 ],
#            [ 'UserStreet',       'Street',      'street',       1, 0,
'var', '', 0 ],
#            [ 'UserZip',          'Zip',         'zip',          1, 0,
'var', '', 0 ],
#            [ 'UserCity',         'City',        'city',         1, 0,
'var', '', 0 ],
#            [ 'UserCountry',      'Country',     'country',      1, 0,
'var', '', 0 ],
#            [ 'UserComment',      'Comment',     'comments',     1, 0,
'var', '', 0 ],
#            [ 'ValidID',          'Valid',       'valid_id',     0, 1,
'int', '', 0 ],
        ],

    };

My question now is: why does it happen, that my output is *blank* when I
seek for customers? While googling around, I came across references
talking about "charset conversion", so I did my search on my dbs:

Using "sp_helpsort" on my MSSQL db, I get:

Server default collation
Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive
(1 row affected)
(return status = 0)

>From Linux, when connecting via tsql, I get:

root at ticket:~# tsql -S sqlexpress -U sa
locale is "it_CH.UTF-8"
locale charset is "UTF-8"

Therefore I was thinking about some kind of "charset mismatch".

Is anybody out there who can help?
Thanks in advance!

Flavio Boniforti

PIRAMIDE INFORMATICA SAGL
Via Ballerini 21
6600 Locarno
Switzerland
Phone: +41 91 751 68 81
Fax: +41 91 751 69 14
URL: http://www.piramide.ch
E-mail: flavio at piramide.ch 
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

------
This is an automated response.  Your issue has been noted.  We'll be in touch soon.

Please reply to this email or visit the URL below with any additional details.

http://itsupport:1111/portal/view-help-request/1580


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.otrs.org/pipermail/otrs/attachments/20110702/d692b807/attachment-0001.html>


More information about the otrs mailing list