Ticket History

On Jul 02, 2011 @ 04:37 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-mai l: 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@beefreeit.nl wrote:

Flavio,

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

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:

On Jul 02, 2011 @ 04:14 pm, michiel@beefreeit.nl wrote:

Flavio,

On Jul 02, 2011 @ 04:14 pm, flavio@piramide.ch wrote:

On Jul 02, 2011 @ 04:14 pm, michiel.beijen@otrs.com wrote:

Flavio,

On Jul 02, 2011 @ 04:14 pm, flavio@piramide.ch wrote:

On Jul 02, 2011 @ 04:14 pm, michiel.beijen@otrs.com wrote:

On Jul 02, 2011 @ 04:09 pm, flavio@piramide.ch wrote:

On Jul 02, 2011 @ 04:09 pm, michiel@beefreeit.nl wrote:

Flavio,

On Jul 02, 2011 @ 04:09 pm, flavio@piramide.ch wrote:

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

Flavio,

On Jul 02, 2011 @ 04:08 pm, flavio@piramide.ch wrote:

On Jul 02, 2011 @ 04:08 pm, michiel.beijen@otrs.com wrote:

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:

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

Flavio,

On Jul 02, 2011 @ 03:41 pm, flavio@piramide.ch wrote:

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

Flavio,

On Jul 02, 2011 @ 03:41 pm, flavio@piramide.ch wrote:

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

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 yo u 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' ],
CustomerUserSear chPrefix => '*',
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"
loc ale 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