SQL Error bei der suche im Kundenbenutzer-Adressbuch

[ChristianHuhn]

Hallo in die Runde,

ich habe an ein Znuny 7.0.14 ein externes Datenbank Backend angebunden.
Es handelt sich dabei um einen MSSQL Server 2022. Wenn ich in dem Kundenbenutzer-Adressbuch beim erstellen eines neuen Ticket suche, gibt es einen SQL Fehler.

Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]OWER wird nicht als Name einer integrierten Funktion erkannt. (SQL-42000)
[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000), SQL: 'SELECT DISTINCT TOP 10000(GUID) FROM dbo.vkundenbenutzer WHERE ( LOWER(first_name) LIKE OWER('Müll%') ) AND ( valid_id IN (1) ) ORDER BY login ASC'

Hier muss die Abfrage angepasst werden. Das Feld, welches für das ORDER BY genutzt wird, muss mit in das SELECT.
SELECT DISTINCT TOP 10000(GUID), login FROM ......

Ich bin auf die Datei: /opt/znuny/Kernel/System/CustomerUser/DB.pm gestoßen. Leider bekomme ich die Anfrage nicht angepasst.
Hat jemand keinen Tipp für mich ?

[root]

Hallo,

Die DB.pm musste ich für sowas noch nie anpassen. ich vermutet mal eher das die CustomerUser-Konfiguration nicht 100%ig passt, wie sieh die denn aus?

- Roy

[ChristianHuhn]

Hallo Roy,

vielen Dank für die schnelle Antwort.
Ich bin mir nicht sicher, was du genau meinst.
Geht es um die Anbindung an die Datenbank ?

[ChristianHuhn]

Hallo Roy,

ich habe zum testen die letzte Version installiert (7.0.14.24.13).
Nun geht die Suche ohne Probleme.

Ein Problem habe ich aber noch:
Der Name wird in der KUNDENBENUTZER-VERWALTUNG nicht angezeigt.
Fehler:
[Sun Jan 14 09:07:46 2024] -e: Use of uninitialized value $Field in hash element at /opt/znuny/Kernel/System/CustomerUser/DB.pm line 1276.
Hast du dazu noch nen Tipp ?

[root]

Hast du dazu noch nen Tipp ?

Hallo,

Und da hat sich IMHO nichts an der Suche geändert. Ich vermute immer noch eine fehlerhafte CustomerUser-Konfiguration.

- Roy

[ChristianHuhn]

Hallo Roy,

wo kann ich gucken, bzw was meinst du mit der CustomerUser-Konfiguration ?

[root]

Hallo Roy,

wo kann ich gucken, bzw was meinst du mit der CustomerUser-Konfiguration ?

Wo und wie hast Du denn das MS SQL backend konfiguriert?

- Roy

P.S.: Bitte Passwörter, Usernamen und Hostnamen unkenntlich machen.

[ChristianHuhn]

Hallo Roy,
in der Config_PM.

Anbei ein Auszug davon:

# ---------------------------------------------------- #
# data inserted by installer #
# ---------------------------------------------------- #
# $DIBI$
$Self->{CustomerCompany} = {
Name => Translatable('Database Backend'),
Module => 'Kernel::System::CustomerCompany::DB',
Params => {
# if you want to use an external database, add the
# required settings
# DSN => 'DBI:odbc:yourdsn',
# Type => 'mssql', # only for ODBC connections
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
# User => '',
# Password => '',
#DSN => 'DBI:ODBC:MSSQLSERVER:database=taifunexport;host=192.168.230.78',
DSN => 'DBI:ODBC:MSSQLSERVER',
Type => 'mssql', # only for ODBC connections
User => 'znunysql',
Password => 'XXX',
Database=> 'taifunexport',
Table => 'dbo.vkunden',
# ForeignDB => 0, # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
ForeignDB => 1,
# CaseSensitive defines if the data storage of your DBMS is case sensitive and will be
# preconfigured within the database driver by default.
# If the collation of your data storage differs from the default settings,
# you can set the current behavior ( either 1 = CaseSensitive or 0 = CaseINSensitive )
# to fit your environment.
#
# CaseSensitive => 0,
# SearchCaseSensitive will control if the searches within the data storage are performed
# case sensitively (if possible) or not. Change this option to 1, if you want to search case sensitive.
# This can improve the performance dramatically on large databases.
SearchCaseSensitive => 0,
},

# company unique id
CustomerCompanyKey => 'customer_id',
CustomerCompanyValid => 'valid_id',
CustomerCompanyListFields => [ 'customer_id', 'name', 'street', 'zip', 'city' ],
CustomerCompanySearchFields => [ 'customer_id', 'name', 'street', 'zip', 'city' ],
CustomerCompanySearchPrefix => '*',
CustomerCompanySearchSuffix => '*',
CustomerCompanySearchListLimit => 250,
CacheTTL => 60 * 60 * 24, # use 0 to turn off cache
ReadOnly => 1,
Map => [
# Info about dynamic fields:
#
# Dynamic Fields of type CustomerCompany can be used within the mapping (see example below).
# The given storage (third column) then can also be used within the following configurations (see above):
# CustomerCompanySearchFields, CustomerCompanyListFields
#
# Note that the columns 'frontend' and 'readonly' will be ignored for dynamic fields.

# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
[ 'CustomerID', Translatable('CustomerID'), 'customer_id', 1, 1, 'var', '', 1 ],
[ 'CustomerCompanyName', Translatable('Customer'), 'name', 1, 1, 'var', '', 1 ],
[ 'CustomerCompanyStreet', Translatable('Street'), 'street', 1, 1, 'var', '', 1 ],
[ 'CustomerCompanyZIP', Translatable('Zip'), 'zip', 1, 1, 'var', '', 1 ],
[ 'CustomerCompanyCity', Translatable('City'), 'city', 1, 1, 'var', '', 1 ],
# [ 'CustomerCompanyCountry', Translatable('Country'), 'country', 1, 1, 'var', '', 1 ],
[ 'CustomerCompanyURL', Translatable('URL'), 'url', 1, 1, 'var', '[% Data.CustomerCompanyURL | html %]', 1 ],
[ 'CustomerCompanyComment', Translatable('Comment'), 'comments', 1, 1, 'var', '', 1 ],
[ 'ValidID', Translatable('Valid'), 'valid_id', 0, 1, 'int', '', 1 ],
# Dynamic field example
# [ 'DynamicField_Name_Y', undef, 'Name_Y', 0, 0, 'dynamic_field', undef, 0 ],
],
};

$Self->{CustomerUser} = {
Name => Translatable('Database Backend'),
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the
# required settings
# DSN => 'DBI:odbc:yourdsn',
# Type => 'mssql', # only for ODBC connections
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
# User => '',
# Password => '',
DSN => 'DBI:ODBC:MSSQLSERVER',
Type => 'mssql', # only for ODBC connections
User => 'znunysql',
Password => 'XXX',
Database=> 'taifunexport',
Table => 'dbo.vkundenbenutzer',
# ForeignDB => 0, # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
ForeignDB => 1,
# CaseSensitive defines if the data storage of your DBMS is case sensitive and will be
# preconfigured within the database driver by default.
# If the collation of your data storage differs from the default settings,
# you can set the current behavior ( either 1 = CaseSensitive or 0 = CaseINSensitive )
# to fit your environment.
#
# CaseSensitive => 0,
# SearchCaseSensitive will control if the searches within the data storage are performed
# case sensitively (if possible) or not. Change this option to 1, if you want to search case sensitive.
# This can improve the performance dramatically on large databases.
SearchCaseSensitive => 0,
},

# customer unique id
CustomerKey => 'GUID',
# customer #
CustomerID => 'customer_id',
CustomerValid => 'valid_id',

# The last field must always be the email address so that a valid
# email address like "John Doe" <john.doe@domain.com> can be constructed from the fields.
# CustomerUserListFields => [ 'first_name', 'last_name', 'email' ],
CustomerUserListFields => [' login', 'first_name', 'last_name', 'customer_id', 'email', 'city' ],
CustomerUserSearchFields => [ 'login', 'first_name', 'last_name', 'customer_id', 'email', 'city' ],
CustomerUserSearchPrefix => '*',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => [ 'email' ],
CustomerUserNameFields => [ 'title', 'first_name', 'last_name' ],
CustomerUserEmailUniqCheck => 1,

# Configures the character for joining customer user name parts. Join single space if it is not defined.
# CustomerUserNameFieldsJoin => '',
# show now own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# generate auto logins
# AutoLoginCreation => 0,
# generate auto login prefix
# AutoLoginCreationPrefix => 'auto',
# admin can change customer preferences
# AdminSetPreferences => 1,
# use customer company support (reference to company, See CustomerCompany settings)
# CustomerCompanySupport => 1,
# cache time to live in sec. - cache any database queries
CacheTTL => 60 * 60 * 24,
# Consider this source read only.
# ReadOnly => 1,
ReadOnly => 1,
Map => [
# Info about dynamic fields:
#
# Dynamic Fields of type CustomerUser can be used within the mapping (see example below).
# The given storage (third column) then can also be used within the following configurations (see above):
# CustomerUserSearchFields, CustomerUserPostMasterSearchFields, CustomerUserListFields, CustomerUserNameFields
#
# Note that the columns 'frontend' and 'readonly' will be ignored for dynamic fields.

# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target, link class(es)
[ 'UserTitle', Translatable('Title or salutation'), 'title', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserFirstname', Translatable('Firstname'), 'first_name', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserLastname', Translatable('Lastname'), 'last_name', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserLogin', Translatable('Username'), 'login', 1, 1, 'var', '', 1, undef, undef ],
# [ 'UserPassword', Translatable('Password'), 'pw', 0, 0, 'var', '', 0, undef, undef ],
[ 'UserEmail', Translatable('Email'), 'email', 1, 1, 'var', '', 1, undef, undef ],
# [ 'UserEmail', Translatable('Email'), 'email', 1, 1, 'var', '[% Env("CGIHandle") %]?Action=AgentTicketCompose;ResponseID=1;TicketID=[% Data.TicketID | uri %];ArticleID=[% Data.ArticleID | uri %]', 0, '', 'AsPopup OTRSPopup_TicketAction' ],
[ 'UserCustomerID', Translatable('CustomerID'), 'customer_id', 1, 1, 'var', '', 1, undef, undef ],
# [ 'UserCustomerIDs', Translatable('CustomerIDs'), 'customer_ids', 1, 0, 'var', '', 0, undef, undef ],
[ 'UserPhone', Translatable('Phone'), 'phone', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserFax', Translatable('Fax'), 'fax', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserMobile', Translatable('Mobile'), 'mobile', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserStreet', Translatable('Street'), 'street', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserZip', Translatable('Zip'), 'zip', 1, 1, 'var', '', 1, undef, undef ],
[ 'UserCity', Translatable('City'), 'city', 1, 1, 'var', '', 1, undef, undef ],
# [ 'UserCountry', Translatable('Country'), 'country', 1, 0, 'var', '', 0, undef, undef ],
[ 'UserComment', Translatable('Comment'), 'comments', 1, 1, 'var', '', 1, undef, undef ],
[ 'ValidID', Translatable('Valid'), 'valid_id', 1, 1, 'int', '', 1, undef, undef ],
# Dynamic field example
# [ 'DynamicField_Name_X', undef, 'Name_X', 0, 0, 'dynamic_field', undef, 0, undef, undef ],
],

# default selections
Selections => {

# UserTitle => {
# 'Mr.' => Translatable('Mr.'),
# 'Mrs.' => Translatable('Mrs.'),
# },
},
};
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# #
# end of your own config options!!! #
# #
# ---------------------------------------------------- #
# ---------------------------------------------------- #

[root]

Hallo,

mach doch mal aus

CustomerKey => 'GUID',
einfach
CustomerKey => 'login',

Lösche den Cache und probiere es nochmal.

- Roy

[ChristianHuhn]

Hallo Roy,

das hat geklappt.
Viele Dank für deine Hilfe !