I'm trying to add to OTRS5 such that it can read a customer database residing on a MS SQL 2008 server. I have installed the ODBC Driver 11 on the OTRS-system (centos7) and can connect to the database using the sqlcmd test. This is a new installation with a new mysql database, and im trying to use code from our old Config.PM custom settings to implement the mssql customer db backend.
When testing by making a phone ticket and entering something to look up, I see from httpd error_log the following:
[Fri Nov 13 09:27:41 2015] -e: DBD::ODBC::st execute failed: [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification (SQL-22018) at /opt/otrs//Kernel/System/DB.pm line 666.
ERROR: OTRS-CGI-64 Perl: 5.16.3 OS: linux Time: Fri Nov 13 09:27:41 2015
Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification (SQL-22018), SQL: 'SELECT TOP 250 ObjectID , Name, Email FROM View_Object_Support WHERE ((ObjectID LIKE ? OR Name LIKE ? OR FirmName LIKE ? ) ) AND Active IN (1) '
I have pasted the relevant settings we used on an earlier version (3.2 Windows) Config.pm. Below is the configurations. Please, if anyone has an idea how I can fix this, I would appreciate it.
Content of /etc/odbc.ini: (Redacted actual IP and db-name)
Code: Select all
[mssqldynamics]
Server = tcp:<mssqlserverip>,1433
#Driver = SQL Server Native Client 11.0
Driver = ODBC Driver 11 for SQL Server
Database = <dbname>
Code: Select all
# --
# Copyright (C) 2001-2015 xxx, http://otrs.com/
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --
# Note:
#
# -->> Most OTRS configuration should be done via the OTRS web interface
# and the SysConfig. Only for some configuration, such as database
# credentials and customer data source changes, you should edit this
# file. For changes do customer data sources you can copy the definitions
# from Kernel/Config/Defaults.pm and paste them in this file.
# Config.pm will not be overwritten when updating OTRS.
# --
package Kernel::Config;
use strict;
use warnings;
use utf8;
sub Load {
my $Self = shift;
# ---------------------------------------------------- #
# database settings #
# ---------------------------------------------------- #
# The database host
$Self->{'DatabaseHost'} = '127.0.0.1';
# The database name
$Self->{'Database'} = "otrs";
# The database user
$Self->{'DatabaseUser'} = "otrs";
# The password of database user. You also can use bin/otrs.Console.pl Maint::Database::PasswordCrypt
# for crypted passwords
$Self->{'DatabasePw'} = 'XXXXXXXXXXXXXXXX';
# The database DSN for MySQL ==> more: "perldoc DBD::mysql"
$Self->{'DatabaseDSN'} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost}";
# The database DSN for PostgreSQL ==> more: "perldoc DBD::Pg"
# if you want to use a local socket connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};";
# if you want to use a TCP/IP connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";
# The database DSN for Microsoft SQL Server - only supported if OTRS is
# installed on Windows as well
# $Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Database=$Self->{Database};Server=$Self->{DatabaseHost},1433";
# The database DSN for Oracle ==> more: "perldoc DBD::oracle"
# $Self->{DatabaseDSN} = "DBI:Oracle://$Self->{DatabaseHost}:1521/$Self->{Database}";
#
# $ENV{ORACLE_HOME} = '/path/to/your/oracle';
# $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
# $ENV{NLS_LANG} = 'AMERICAN_AMERICA.AL32UTF8';
# ---------------------------------------------------- #
# fs root directory
# ---------------------------------------------------- #
$Self->{Home} = '/opt/otrs';
# ---------------------------------------------------- #
# insert your own config settings "here" #
$Self->{'LogModule'} = 'Kernel::System::Log::File';
$Self->{'LogModule::LogFile'} = "$Self->{Home}/var/log/otrs.log";
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser} = {
Name => 'Database Datasource',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
# DSN => 'DBI:ODBC:Driver={SQL Server}',
# Type => 'mssql', # only for ODBC connections
# DSN => 'DBI:ODBC:Driver=mssqldynamics;Server=<serverip>;database=<dbname>',
DSN => 'DBI:ODBC:mssqldynamics',
User => 'otrs_readonly',
Password => 'XXXXXXXXXXXXXXXX',
Table => 'View_Object_Support',
# if your frontend is unicode and the charset of your
# customer database server is iso-8859-1, use these options.
# SourceCharset => 'iso-8859-1',
# DestCharset => 'utf-8',
# CaseSensitive will control if the SQL statements need LOWER()
# function calls to work case insensitively. Setting this to
# 1 will improve performance dramatically on large databases.
CaseSensitive => 0,
},
# customer unique id
CustomerKey => 'ObjectID',
# customer #
CustomerID => 'FirmName',
CustomerValid => 'Active',
CustomerUserListFields => ['Name', 'Email'],
CustomerUserSearchFields => ['ObjectID', 'Name', 'FirmName'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['Email'],
CustomerUserNameFields => ['Name'],
CustomerUserEmailUniqCheck => 0,
# # show not own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# # generate auto logins
# AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',
# # admin can change customer preferences
# AdminSetPreferences => 1,
# # cache time to live in sec. - cache any database queries
# CacheTTL => 0,
# # just a read only source
ReadOnly => 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
# [ 'UserTitle', 'Title', 'title', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', 'Firstname', 'Name', 1, 1, 'var', '', 0 ],
# [ 'UserLastname', 'Lastname', 'Name', 1, 1, 'var', '', 0 ],
[ 'UserLogin', 'Username', 'ObjectID', 1, 1, 'var', '', 0 ],
[ 'UserPassword', 'Password', 'ObjectID', 0, 0, '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', 'FirmName', 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', 'Active', 0, 1, 'int', '', 0 ],
],
};
$Self->{CustomerCompany} = {
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:ODBC:mssqldynamics;Server=<serverip>;database=<dbname>',
# User => 'otrs_readonly',
# Password => 'XXXXXXXXXXXXXXXX',
DSN => 'DBI:ODBC:mssqldynamics',
User => 'otrs_readonly',
Password => 'XXXXXXXXXXXXXXXX',
Table => 'View_Firm_Support',
ForeignDB => 1, # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
},
# company unique id
CustomerCompanyKey => 'FirmID',
CustomerCompanyValid => 'Active',
CustomerCompanyListFields => [ 'Name', 'name' ],
CustomerCompanySearchFields => ['Name', 'name'],
CustomerCompanySearchPrefix => '',
CustomerCompanySearchSuffix => '*',
CustomerCompanySearchListLimit => 250,
CacheTTL => 0, #60 * 60 * 24, # use 0 to turn off cache
Map => [
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
[ 'CustomerID', 'CustomerID', 'Name', 0, 1, 'var', '', 0 ],
[ 'CustomerCompanyName', 'Company', 'name', 1, 1, 'var', '', 0 ],
# [ 'CustomerCompanyStreet', 'Street', 'street', 1, 0, 'var', '', 0 ],
# [ 'CustomerCompanyZIP', 'Zip', 'zip', 1, 0, 'var', '', 0 ],
# [ 'CustomerCompanyCity', 'City', 'city', 1, 0, 'var', '', 0 ],
# [ 'CustomerCompanyCountry', 'Country', 'country', 1, 0, 'var', '', 0 ],
# [ 'CustomerCompanyURL', 'URL', 'url', 1, 0, 'var', '$Data{"CustomerCompanyURL"}', 0 ],
# [ 'CustomerCompanyComment', 'Comment', 'comments', 1, 0, 'var', '', 0 ],
[ 'ValidID', 'Valid', 'Active', 0, 1, 'bit', '', 0 ],
],
};
# config settings taken from Kernel/Config/Defaults.pm #
# ---------------------------------------------------- #
# $Self->{SessionUseCookie} = 0;
# $Self->{CheckMXRecord} = 0;
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# data inserted by installer #
# ---------------------------------------------------- #
# $DIBI$
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# #
# end of your own config options!!! #
# #
# ---------------------------------------------------- #
# ---------------------------------------------------- #
}
# ---------------------------------------------------- #
# needed system stuff (don't edit this) #
# ---------------------------------------------------- #
use base qw(Kernel::Config::Defaults);
# -----------------------------------------------------#
1;