July 2011 - otrs - OTRS mailing lists

[Ticket #1571] Customer Company
by KIRAN IT 02 Jul '11

02 Jul '11

This is a notification from the Help Desk. On Jul 02, 2011 @ 04:37 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - This helped tremendously - thank you very much. I now have a dropdown for "CustomerID" (which lists available Companies, as expected) when I create new Customers. That said, I noticed that the "CustomerID" field when creating a new ticket is still a free text field, not a dropdown. Is there any way I can enable all CustomerID fields to become Company dropdowns? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, November 18, 2010 3:56 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ==Ticket History== On Jul 02, 2011 @ 04:36 pm, en(a)otrs.com wrote: Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 04:36 pm, en(a)otrs.com wrote: In the admin area go to Customer Company and add a new one (remember the CustomerID), after that you can modify your existing customers or create new ones, and associate them with that company by having the same entry in the CustomerID. Regards, Elva ------ On Jul 02, 2011 @ 04:36 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Is there an option that must be set to have this dropdown displayed if I'm running v3.0.1? From Admin --> Customer Companies, I set up a few unique companies. If I then go to Admin --> Customers --> Add Customer, the "CustomerID" field is just a normal text entry field... not a dropdown. Thoughts? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Jober Mijares Sent: Thursday, November 18, 2010 2:39 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Thanks I work with the version 2.4.7 But how i make the link between the company and the client. Sorry but i don't find the option --- El mié, 11/17/10, Elva Novoa <en(a)otrs.com> escribió: De: Elva Novoa <en(a)otrs.com> Asunto: Re: [otrs] Customer Company A: "User questions and discussions about OTRS." <otrs(a)otrs.org> Fecha: miércoles, 17 de noviembre de 2010, 01:15 pm ------ On Jul 02, 2011 @ 04:15 pm, mark.shepard(a)canoe-ventures.com wrote: Attachment: ------ On Jul 02, 2011 @ 04:15 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Hmm, so that brings up another question then. (Thanks again for your help on this.) How can I give my agents access to update customer and company information in case they are logging a ticket for a customer that has not yet been entered into the 'Customers' tab of OTRS (we're not connecting to an external customer DB)? It appears that by default, the Customers and Companies screens are only available to those with an 'admin' role. I tried to dig around, and it looks like I just need to open up AdminCustomerUser, so from Admin --> Sysconfig --> Framework --> Frontend::Admin::ModuleRegistration, I took a look at Frontend::Module###AdminCustomerUser, but the 'Group' already has both 'admin' and 'users'. Yet, those with 'user' permissions cannot access the Customers screen (they get an "Insufficient Rights: Message - No Permission to use this frontend module!" if they try to navigate to <host>/otrs/index.pl?Action=AdminCustomerUser;Nav=Agent). Below is a screen capture of my settings for AdminCustomerUser... your help is much appreciated! -Mark [cid:image002.jpg@01CB92D7.2409D840] From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, December 02, 2010 12:45 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 04:15 pm, en(a)otrs.com wrote: Attachment: ------ On Jul 02, 2011 @ 04:15 pm, en(a)otrs.com wrote: Mark, The problem is that your "users" group only has ro (read only) permission. If you want your agents defined in the users group to be able to register new customers and companies, just delete the entry "users" from the "Group ro". I attached a part of your same screen shot, highlighting the part your should remove. Regards, Elva ------ On Jul 02, 2011 @ 04:14 pm, en(a)otrs.com wrote: Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 04:14 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - This helped tremendously - thank you very much. I now have a dropdown for "CustomerID" (which lists available Companies, as expected) when I create new Customers. That said, I noticed that the "CustomerID" field when creating a new ticket is still a free text field, not a dropdown. Is there any way I can enable all CustomerID fields to become Company dropdowns? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, November 18, 2010 3:56 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 04:14 pm, en(a)otrs.com wrote: Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 04:13 pm, en(a)otrs.com wrote: In the admin area go to Customer Company and add a new one (remember the CustomerID), after that you can modify your existing customers or create new ones, and associate them with that company by having the same entry in the CustomerID. Regards, Elva ------ On Jul 02, 2011 @ 04:13 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Is there an option that must be set to have this dropdown displayed if I'm running v3.0.1? From Admin --> Customer Companies, I set up a few unique companies. If I then go to Admin --> Customers --> Add Customer, the "CustomerID" field is just a normal text entry field... not a dropdown. Thoughts? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Jober Mijares Sent: Thursday, November 18, 2010 2:39 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Thanks I work with the version 2.4.7 But how i make the link between the company and the client. Sorry but i don't find the option --- El mié, 11/17/10, Elva Novoa <en(a)otrs.com> escribió: De: Elva Novoa <en(a)otrs.com> Asunto: Re: [otrs] Customer Company A: "User questions and discussions about OTRS." <otrs(a)otrs.org> Fecha: miércoles, 17 de noviembre de 2010, 01:15 pm ------ On Jul 02, 2011 @ 04:10 pm, mark.shepard(a)canoe-ventures.com wrote: Attachment: ------ On Jul 02, 2011 @ 04:10 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Hmm, so that brings up another question then. (Thanks again for your help on this.) How can I give my agents access to update customer and company information in case they are logging a ticket for a customer that has not yet been entered into the 'Customers' tab of OTRS (we're not connecting to an external customer DB)? It appears that by default, the Customers and Companies screens are only available to those with an 'admin' role. I tried to dig around, and it looks like I just need to open up AdminCustomerUser, so from Admin --> Sysconfig --> Framework --> Frontend::Admin::ModuleRegistration, I took a look at Frontend::Module###AdminCustomerUser, but the 'Group' already has both 'admin' and 'users'. Yet, those with 'user' permissions cannot access the Customers screen (they get an "Insufficient Rights: Message - No Permission to use this frontend module!" if they try to navigate to <host>/otrs/index.pl?Action=AdminCustomerUser;Nav=Agent). Below is a screen capture of my settings for AdminCustomerUser... your help is much appreciated! -Mark [cid:image002.jpg@01CB92D7.2409D840] From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, December 02, 2010 12:45 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 04:09 pm, en(a)otrs.com wrote: Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 04:09 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - This helped tremendously - thank you very much. I now have a dropdown for "CustomerID" (which lists available Companies, as expected) when I create new Customers. That said, I noticed that the "CustomerID" field when creating a new ticket is still a free text field, not a dropdown. Is there any way I can enable all CustomerID fields to become Company dropdowns? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, November 18, 2010 3:56 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 04:08 pm, en(a)otrs.com wrote: Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 04:08 pm, en(a)otrs.com wrote: In the admin area go to Customer Company and add a new one (remember the CustomerID), after that you can modify your existing customers or create new ones, and associate them with that company by having the same entry in the CustomerID. Regards, Elva ------ On Jul 02, 2011 @ 04:08 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Is there an option that must be set to have this dropdown displayed if I'm running v3.0.1? From Admin --> Customer Companies, I set up a few unique companies. If I then go to Admin --> Customers --> Add Customer, the "CustomerID" field is just a normal text entry field... not a dropdown. Thoughts? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Jober Mijares Sent: Thursday, November 18, 2010 2:39 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Thanks I work with the version 2.4.7 But how i make the link between the company and the client. Sorry but i don't find the option --- El mié, 11/17/10, Elva Novoa <en(a)otrs.com> escribió: De: Elva Novoa <en(a)otrs.com> Asunto: Re: [otrs] Customer Company A: "User questions and discussions about OTRS." <otrs(a)otrs.org> Fecha: miércoles, 17 de noviembre de 2010, 01:15 pm ------ On Jul 02, 2011 @ 03:46 pm, en(a)otrs.com wrote: Attachment: ------ On Jul 02, 2011 @ 03:46 pm, en(a)otrs.com wrote: Mark, The problem is that your "users" group only has ro (read only) permission. If you want your agents defined in the users group to be able to register new customers and companies, just delete the entry "users" from the "Group ro". I attached a part of your same screen shot, highlighting the part your should remove. Regards, Elva ------ On Jul 02, 2011 @ 03:42 pm, mark.shepard(a)canoe-ventures.com wrote: Attachment: ------ On Jul 02, 2011 @ 03:42 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Hmm, so that brings up another question then. (Thanks again for your help on this.) How can I give my agents access to update customer and company information in case they are logging a ticket for a customer that has not yet been entered into the 'Customers' tab of OTRS (we're not connecting to an external customer DB)? It appears that by default, the Customers and Companies screens are only available to those with an 'admin' role. I tried to dig around, and it looks like I just need to open up AdminCustomerUser, so from Admin --> Sysconfig --> Framework --> Frontend::Admin::ModuleRegistration, I took a look at Frontend::Module###AdminCustomerUser, but the 'Group' already has both 'admin' and 'users'. Yet, those with 'user' permissions cannot access the Customers screen (they get an "Insufficient Rights: Message - No Permission to use this frontend module!" if they try to navigate to <host>/otrs/index.pl?Action=AdminCustomerUser;Nav=Agent). Below is a screen capture of my settings for AdminCustomerUser... your help is much appreciated! -Mark [cid:image002.jpg@01CB92D7.2409D840] From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, December 02, 2010 12:45 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 03:41 pm, en(a)otrs.com wrote: Mark, I don't think that you can make the CustomerID a drop-down in the new ticket screen, but this field is automatically filled when you select a customer, just as the customer information and history. Regards, Elva ------ On Jul 02, 2011 @ 03:41 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - This helped tremendously - thank you very much. I now have a dropdown for "CustomerID" (which lists available Companies, as expected) when I create new Customers. That said, I noticed that the "CustomerID" field when creating a new ticket is still a free text field, not a dropdown. Is there any way I can enable all CustomerID fields to become Company dropdowns? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Elva Novoa Sent: Thursday, November 18, 2010 3:56 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 03:41 pm, en(a)otrs.com wrote: Hi Mark, Yes, in the you have to open the Defaults.pm and copy the sections that correspond to $Self->{CustomerUser} and $Self->{CustomerCompany}, paste them into the Config.pm and uncomment the line for customer company support (CustomerCompanySupport => 1,) in Config.pm and you are done! I hope this helps :-) Regards, Elva ------ On Jul 02, 2011 @ 03:41 pm, en(a)otrs.com wrote: In the admin area go to Customer Company and add a new one (remember the CustomerID), after that you can modify your existing customers or create new ones, and associate them with that company by having the same entry in the CustomerID. Regards, Elva ------ On Jul 02, 2011 @ 03:41 pm, mark.shepard(a)canoe-ventures.com wrote: Elva - Is there an option that must be set to have this dropdown displayed if I'm running v3.0.1? From Admin --> Customer Companies, I set up a few unique companies. If I then go to Admin --> Customers --> Add Customer, the "CustomerID" field is just a normal text entry field... not a dropdown. Thoughts? Thanks, Mark From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Jober Mijares Sent: Thursday, November 18, 2010 2:39 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Customer Company Thanks I work with the version 2.4.7 But how i make the link between the company and the client. Sorry but i don't find the option --- El mié, 11/17/10, Elva Novoa <en(a)otrs.com> escribió: De: Elva Novoa <en(a)otrs.com> Asunto: Re: [otrs] Customer Company A: "User questions and discussions about OTRS." <otrs(a)otrs.org> Fecha: miércoles, 17 de noviembre de 2010, 01:15 pm ------ On Jul 02, 2011 @ 03:40 pm, ronnamijares(a)yahoo.com wrote: Thanks I work with the version 2.4.7 But how i make the link between the company and the client. Sorry but i don't find the option --- El mié, 11/17/10, Elva Novoa <en(a)otrs.com> escribió: De: Elva Novoa <en(a)otrs.com> Asunto: Re: [otrs] Customer Company A: "User questions and discussions about OTRS." <otrs(a)otrs.org> Fecha: miércoles, 17 de noviembre de 2010, 01:15 pm On 11/16/2010 1:51 PM, Jober Mijares wrote: > Then, maybe i'm confused but I thought that the function of the company client, is link several customers in just one company. Hi Jober, Actually you are right in the definition of Customer Company. You should be able to create more than one customer with the same company, but you need to add customer companies first. What version of OTRS are you using? In OTRS 3.0.1, this field (CustomerID) was changed to be a drop-down when you are adding a new customer, so that you can select a company from the registered ones. I hope this helps! Regards, Elva --------------------------------------------------------------------- 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/1571

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, 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