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

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