- otrs - OTRS mailing lists

[Ticket #1588] hide agent email address from customer view
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ==Ticket History== On Jul 02, 2011 @ 04:37 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:15 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:14 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 04:14 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:09 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:09 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 04:09 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: On 09/14/2010 02:20 PM, Daniel Maher wrote: > On 09/14/2010 01:45 PM, "LQ Marshall" wrote: > >>> I don't suppose anybody has any > ideas on how to go about hiding > >> email addresses from the > customers in customer.pl ? >>> >>> Thank you. >> >> Don't know what you are talking about? Don't have any point where a >> customer can view an agents email address. Only thing available in the >> basic setup is the queue email address. >> >> Where have you configured the agents email causing them to be displayed? > > I haven't purposefully configured that as an option anywhere ; this is a > vanilla installation of OTRS 2.4.7, and nothing beyond the > authentication mechanism (LDAP) and queue name has been configured. > > Please see the following screenshot where i have highlighted each > instance of the agent email address being revealed to the customer. > > http://imgur.com/Ik3Jt.png I apologise for reviving such an old thread, but I am curious to know if anybody has an answer for this question ? To summarize (again) : How can I hide the agent email addresses from view in customer.pl ? Thank you. -- Daniel Maher <dma PLUS otrs AT witbe DOT net> "The Internet is completely over." -- Prince --------------------------------------------------------------------- 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/1588

1 0

[Ticket #1588] hide agent email address from customer view
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, dma+otrs(a)witbe.net wrote: No Comment ==Ticket History== On Jul 02, 2011 @ 04:15 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:14 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 04:14 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:09 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 04:09 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 04:09 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, crythias(a)gmail.com wrote: Edit Kernel/Modules/CustomerTicketZoom.pm my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; and the same in CustomerTicketMessage.m CustomerTicketMessage.pm: my $From = "$Self->{UserFirstname} $Self->{UserLastname} <$Self->{UserEmail}>"; On Wed, Dec 1, 2010 at 11:41 AM, Daniel Maher <dma+otrs(a)witbe.net<dma%2Botrs(a)witbe.net> > wrote: > ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: No Comment ------ On Jul 02, 2011 @ 03:41 pm, dma+otrs(a)witbe.net wrote: On 09/14/2010 02:20 PM, Daniel Maher wrote: > On 09/14/2010 01:45 PM, "LQ Marshall" wrote: > >>> I don't suppose anybody has any > ideas on how to go about hiding > >> email addresses from the > customers in customer.pl ? >>> >>> Thank you. >> >> Don't know what you are talking about? Don't have any point where a >> customer can view an agents email address. Only thing available in the >> basic setup is the queue email address. >> >> Where have you configured the agents email causing them to be displayed? > > I haven't purposefully configured that as an option anywhere ; this is a > vanilla installation of OTRS 2.4.7, and nothing beyond the > authentication mechanism (LDAP) and queue name has been configured. > > Please see the following screenshot where i have highlighted each > instance of the agent email address being revealed to the customer. > > http://imgur.com/Ik3Jt.png I apologise for reviving such an old thread, but I am curious to know if anybody has an answer for this question ? To summarize (again) : How can I hide the agent email addresses from view in customer.pl ? Thank you. -- Daniel Maher <dma PLUS otrs AT witbe DOT net> "The Internet is completely over." -- Prince --------------------------------------------------------------------- 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/1588

1 0

[Ticket #2304] hide agent email address from customer view
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, dma+otrs(a)witbe.net wrote: On 09/14/2010 02:20 PM, Daniel Maher wrote: > On 09/14/2010 01:45 PM, "LQ Marshall" wrote: > >>> I don't suppose anybody has any > ideas on how to go about hiding > >> email addresses from the > customers in customer.pl ? >>> >>> Thank you. >> >> Don't know what you are talking about? Don't have any point where a >> customer can view an agents email address. Only thing available in the >> basic setup is the queue email address. >> >> Where have you configured the agents email causing them to be displayed? > > I haven't purposefully configured that as an option anywhere ; this is a > vanilla installation of OTRS 2.4.7, and nothing beyond the > authentication mechanism (LDAP) and queue name has been configured. > > Please see the following screenshot where i have highlighted each > instance of the agent email address being revealed to the customer. > > http://imgur.com/Ik3Jt.png I apologise for reviving such an old thread, but I am curious to know if anybody has an answer for this question ? To summarize (again) : How can I hide the agent email addresses from view in customer.pl ? Thank you. -- Daniel Maher <dma PLUS otrs AT witbe DOT net> "The Internet is completely over." -- Prince --------------------------------------------------------------------- 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/2304

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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 ==Ticket History== On Jul 02, 2011 @ 04:37 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)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 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, michiel(a)beefreeit.nl wrote: Flavio, ==Ticket History== On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)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 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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 ==Ticket History== On Jul 02, 2011 @ 04:37 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)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 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, michiel.beijen(a)otrs.com wrote: Flavio, ==Ticket History== On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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:37 pm, michiel.beijen(a)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 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, michiel.beijen(a)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 ==Ticket History== On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #1580] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, flavio(a)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(a)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 ==Ticket History== On Jul 02, 2011 @ 04:37 pm, michiel.beijen(a)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 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:14 pm, flavio(a)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(a)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:14 pm, michiel.beijen(a)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 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 04:09 pm, flavio(a)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(a)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:09 pm, michiel.beijen(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 04:08 pm, flavio(a)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(a)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(a)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(a)piramide.ch wrote: Hello people... Anybody has any clues on how to proceed? Thanks, F. > ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)beefreeit.nl wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)otrs.com wrote: Flavio, ------ On Jul 02, 2011 @ 03:41 pm, flavio(a)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(a)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(a)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(a)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@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(a)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

1 0

[Ticket #2296] Troubles viewing output on Customer GUI, MSSQL and charset
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, flavio(a)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@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(a)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/2296

1 0