July 2003 - dev - OTRS mailing lists

Re: [otrs] Problem installing on Oracle
by Martin Edenhofer 01 Aug '03

01 Aug '03

Hi Graziano, On Wed, Jul 23, 2003 at 03:20:30PM +0200, Ruffoni Graziano wrote: > I would like to install OTRS using Oracle DB and I've got some problems. > The first error has been during the generation of SQL script for schema > cration using Torque 3.0.2 due to an error in otrs-schema.sql with the > <app-data> TAG, I've solved removing it. > After this I've got errors creating tables because some tables names are > Oracle keywords: > comment That means it's not possible to is a column named comment? > session This is the session table, you can drop it (it's just used for the database session backend. default is the IPC (RAM) session backend). > and some index names are too long: > index_customer_preferences_user_id > index_ticket_loop_protection_sent_to > index_ticket_loop_protection_sent_date You can cut the index names... but what is the max. length? PS: It would be better to move on the dev-mailing list to discuss this kind of topic. :) > Thanks > graziano -Martin -- Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/ --

2 1

single sign on - pre-authentication using apache
by Martin Edenhofer 13 Jul '03

13 Jul '03

Hi, I added the "pre-authentication using apache" option from Phil Davis (http://lists.otrs.org/pipermail/dev/2003-April/000142.html) to the CVS HEAD. The idea is the same. But I put it to a new auth-backend-module: Kernel/System/Auth/HTTPBasicAuth.pm (agent interface) and Kernel/System/CustomerAuth/HTTPBasicAuth.pm (customer interface) So it's also possible to write pre-authentication modules for other mechanism not just http-basic-auth. It's really nice and I like it. :) Now it's easy to integrate OTRS into a "single sign on" environment. ;) Thanks Phil! PS: I also added the documentation of the new HTTPBasicAuth modules to the CVS documentation (http://doc.otrs.org/cvs/html/). Martin -- Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/ -- Old programmers never die. They just branch to a new address.

1 0

OTRS and MS SQL 2000 again
by Holger.Struck＠sqs.de 12 Jul '03

12 Jul '03

Hi, It looks like, as if otrs runs now with the SQL server. A few data types and a table name in the database had to be however adapted. the table name system_user cannot be used, in MS SQL this name is reserved of the system. Therefore I renamed the table in sys_user. The data base schema I use is attached on this mail. A (hopefully) last problem exists however still: the field a_body in the table article is in the origin of the data type text. The data type text can only with IS ZERO or LIKE be compared or sorted within MS SQL, not with GROUP BY. ( ntext and image too) Therefore I use varchar with a length of 8000 for as data type for a_body. But I think 8000 indications could be too few, for a very big incoming email? The error message in the logfiles, if the data type stands on text: This error messages will appear in the logfiles if I want to Zoom a ticket (a_body data type = text) otrs log: [Thu Jul 10 11:48:32 2003][Error][Kernel::Modules::AgentZoom::Run][164] [Microsoft][ODBC SQL Server Driver][SQL Server]Die Datentypen text, ntext und image können nur mithilfe des Operators IS NULL oder LIKE verglichen oder sortiert werden. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000)(DBD: st_execute/SQLExecute err=-1), SQL: 'SELECT sa.id, st.tn, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_body, st.create_time_unix, st.tn, st.user_id, st.ticket_state_id, st.ticket_priority_id, sa.create_time, stt.name as sender_type, at.name as article_type, su.login, sl.name as lock_type, sp.name as priority, sp.id as priority_id, tsd.name as state, sa.content_path, sq.name as queue, st.create_time as ticket_create_time, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.freekey1, st.freekey2, st.freetext1, st.freetext2, st.customer_id, st.customer_user_id, sq.group_id, st.ticket_answered, sq.escalation_time, sa.a_content_type, sa.incoming_time, st.until_time FROM article sa, ticket st, article_sender_type stt, article_type at, sys_user su, ticket_lock_type sl, ticket_priority sp, ticket_state tsd, queue sq WHERE sa.ticket_id = st.id AND sq.id = st.queue_id AND stt.id = sa.article_sender_type_id AND at.id = sa.article_type_id AND sp.id = st.ticket_priority_id AND sl.id = st.ticket_lock_id AND tsd.id = st.ticket_state_id AND sa.ticket_id = 1 AND su.id = st.user_id GROUP BY sa.id, st.tn, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_body, st.create_time_unix, st.tn, st.user_id, st.ticket_state_id, st.ticket_priority_id, sa.create_time, stt.name, at.name, su.login, sl.name, sp.name, sp.id, tsd.name, sa.content_path, sq.name, st.create_time, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.freekey1, st.freekey2, st.freetext1, st.freetext2, st.customer_id, st.customer_user_id, sq.group_id, st.ticket_answered, sq.escalation_time, sa.a_content_type, sa.incoming_time, st.until_time ' apache log: [Thu Jul 10 12:20:28 2003] nul: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Die Datentypen text, ntext und image können nur mithilfe des Operators IS NULL oder LIKE verglichen oder sortiert werden. (SQL-42000) [Thu Jul 10 12:20:28 2003] nul: [Microsoft][ODBC SQL Server Driver][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at d:/otrs_ms//Kernel/System/DB.pm line 265. ERROR: OTRS-CGI-10 Perl: 5.6.1 OS: MSWin32 Time: Thu Jul 10 12:20:28 2003 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Die Datentypen text, ntext und image können nur mithilfe des Operators IS NULL oder LIKE verglichen oder sortiert werden. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000)(DBD: st_execute/SQLExecute err=-1), SQL: 'SELECT sa.id, st.tn, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_body, st.create_time_unix, st.tn, st.user_id, st.ticket_state_id, st.ticket_priority_id, sa.create_time, stt.name as sender_type, at.name as article_type, su.login, sl.name as lock_type, sp.name as priority, sp.id as priority_id, tsd.name as state, sa.content_path, sq.name as queue, st.create_time as ticket_create_time, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.freekey1, st.freekey2, st.freetext1, st.freetext2, st.customer_id, st.customer_user_id, sq.group_id, st.ticket_answered, sq.escalation_time, sa.a_content_type, sa.incoming_time, st.until_time FROM article sa, ticket st, article_sender_type stt, article_type at, sys_user su, ticket_lock_type sl, ticket_priority sp, ticket_state tsd, queue sq WHERE sa.ticket_id = st.id AND sq.id = st.queue_id AND stt.id = sa.article_sender_type_id AND at.id = sa.article_type_id AND sp.id = st.ticket_priority_id AND sl.id = st.ticket_lock_id AND tsd.id = st.ticket_state_id AND sa.ticket_id = 1 AND su.id = st.user_id GROUP BY sa.id, st.tn, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_body, st.create_time_unix, st.tn, st.user_id, st.ticket_state_id, st.ticket_priority_id, sa.create_time, stt.name, at.name, su.login, sl.name, sp.name, sp.id, tsd.name, sa.content_path, sq.name, st.create_time, sa.a_freekey1, sa.a_freetext1, sa.a_freekey2, sa.a_freetext2, sa.a_freekey3, sa.a_freetext3, st.freekey1, st.freekey2, st.freetext1, st.freetext2, st.customer_id, st.customer_user_id, sq.group_id, st.ticket_answered, sq.escalation_time, sa.a_content_type, sa.incoming_time, st.until_time ' Traceback (1352): Module: Kernel::Modules::AgentZoom::Run (v1.34.2.1) Line: 164 Module: Apache::ROOT::otrs::index_2epl::handler (v1.58) Line: 608 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149 [Thu Jul 10 12:20:28 2003] nul: DBD::ODBC::st fetchrow_hashref failed: (DBD: no select statement currently executing err=-1) at d:/otrs_ms//Kernel/System/DB.pm line 302. [Thu Jul 10 12:20:28 2003] nul: Use of uninitialized value in pattern match (m//) at d:/otrs_ms//Kernel/Output/HTML/Agent.pm line 391. [Thu Jul 10 12:20:28 2003] nul: Argument "?" isn't numeric in numeric gt (>) at d:/otrs_ms//Kernel/Output/HTML/Generic.pm line 737. [Thu Jul 10 12:20:28 2003] [error] Can't use an undefined value as a HASH reference at d:/otrs_ms//Kernel/Output/HTML/Agent.pm line 549. any idea somebody? Regards Holger

2 1

RE: [dev] OTRS with MS SQL Server
by Holger.Struck＠sqs.de 09 Jul '03

09 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 09, 2003 12:33 PM > To: dev(a)otrs.org > Subject: Re: [dev] OTRS with MS SQL Server > So a \ should be quoted with '\ or with '\' ? if you write a single \ you have to write '\' if you want to write something like \blabla\bla it have to look like this '\blabla\bla' > > maybe i schould switch back to mysql ;-) > > Hmmm... not yet! The nice work! .-) :-) > I think it's my mistake. .-( > > Could you try it with an additional config option: > > $Self->{'Database::Limit'} = 0; > > (I added limit support to the mssql spec) > > And the "Zeile 1: Falsche Syntax in der Nähe von 'LIMIT'" > error should go > away. that did it :-) you are great, thank you. now i can search for other errors *g* Holger

1 0

RE: [dev] OTRS with MS SQL Server
by Holger.Struck＠sqs.de 09 Jul '03

09 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 09, 2003 11:12 AM > To: dev(a)otrs.org > Subject: Re: [dev] OTRS with MS SQL Server Hi Martin, > > yes, normally '\' > > That means you don't need to quote \? ups, sorry, i meant you have to quote \ > I improved Kernel/System/DB.pm with mssql specs. Use the 1.29 version > from cvs (http://cvs.otrs.org/cvsweb.cgi/otrs/Kernel/System/DB.pm). > > -=> With ODBC is no auto database type detection possible, so you > need to set the database type in your Kernel/Config.pm > > $Self->{'Database::Type'} = 'mssql'; > > And? :) maybe i should describe, what i have done at first to connect to the SQL Server. my Config.pm looked like this: $Self->{Server} = 'SQL Server'; # DatabaseDSN $Self->{DatabaseDSN} ="DBI:ODBC:driver=$Self->{Server};Server=$Self->{DatabaseHost};database=$Sel f->{Database};uid=$Self->{DatabaseUser};pwd=$Self->{DatabasePw};"; with this i can connect to the SQL Server, and most things are working as far as i can see. if i try this: # DatabaseDSN $Self->{DatabaseDSN} = "DBI:ODBC:driver=$Self->{Database}"; $Self->{'Database::Type'} = 'mssql'; i get this error: (sorry, only german error messages ;-) Fehler: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben. (SQL-IM002)(DBD: db_login/SQLConnect err=-1) so next i tried was this: $Self->{Server} = 'SQL Server'; # DatabaseDSN $Self->{DatabaseDSN} ="DBI:ODBC:driver=$Self->{Server};Server=$Self->{DatabaseHost};database=$Sel f->{Database};uid=$Self->{DatabaseUser};pwd=$Self->{DatabasePw};"; $Self->{'Database::Type'} = 'mssql'; this worked for me, i can connect now. an now my last error is gone :-) but now i have a new one :-/ in the queue view no more tickets can be viewed. this error message appears in the log: Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Zeile 1: Falsche Syntax in der Nähe von 'LIMIT'. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000)(DBD: st_execute/SQLExecute err=-1), SQL: 'SELECT st.id, st.queue_id FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id in ( 1, 4, 6, 7, 8 ) AND st.ticket_lock_id in ( 3, 1 ) AND st.queue_id in ( 5 ) AND sq.group_id IN ( 1, 2, 3, 24, 4, 10, 5, 11, 6, 12, 20, 7, 13, 21, 8, 14, 22, 23, 15, 9, 16, 17, 18, 19 ) ORDER BY st.ticket_priority_id DESC, st.create_time_unix ASC LIMIT 16' Traceback (2012): Module: Kernel::Modules::AgentQueueView::Run (v1.36) Line: 173 Module: Apache::ROOT::otrs::index_2epl::handler (v1.58) Line: 608 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149 maybe i schould switch back to mysql ;-) Holger

2 1

RE: [dev] OTRS with MS SQL Server
by Holger.Struck＠sqs.de 09 Jul '03

09 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 09, 2003 10:46 AM > To: dev(a)otrs.org > Subject: Re: [dev] OTRS with MS SQL Server > Just one more question and I send you a working Kernel/System/DB.pm > for mssql. .-) :-) > Do you also need to quote \ on mssql? yes, normally '\' Holger

2 1

RE: [dev] OTRS with MS SQL Server
by Holger.Struck＠sqs.de 09 Jul '03

09 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 09, 2003 10:29 AM > To: dev(a)otrs.org > Subject: Re: [dev] OTRS with MS SQL Server > > > Hi Holger, > The normal string is: > > Old: 'open' New: 'closed successful' > > But a ' would brake the SQL syntax so it will be quoted with \'. > > On SAPDB you need to quote with ' instead of \ -=> '' instand of \'. > > How is the quoteing on mssql? Also ''? Hi Martin; yepp, its also ''. so, where do i have to change this? Holger

2 1

OTRS with MS SQL Server
by Holger.Struck＠sqs.de 09 Jul '03

09 Jul '03

Hi, im trying to use otrs with sql server 2000. most things working, but i got this error in my error.log if i try to fetch mail via pop3. same error by creating a phone ticket. [Error][Kernel::System::Ticket::History::AddHistoryRow][105] [Microsoft][ODBC SQL Server Driver][SQL Server]Falsche Syntax in der Nähe des open-Schlüsselwortes. (SQL-42000)(DBD: Execute immediate failed err=-1), SQL: 'INSERT INTO ticket_history (name, history_type_id, ticket_id, article_id, valid_id, create_time, create_by, change_time, change_by) VALUES ('Old: \'open\' New: \'closed successful\'', 30, 452, 482, 1, current_timestamp, 1, current_timestamp, 1)' the problem are the ' in this part: \'open\' New: \'closed successful\' where do they come from? or which module is responsible for it? regards holger

2 1

RE: [dev] SQL Server 2000 with otrs
by Holger.Struck＠sqs.de 03 Jul '03

03 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 02, 2003 11:02 PM > To: dev(a)otrs.org > Subject: Re: [dev] SQL Server 2000 with otrs > > > Im not sure, but try to change the "DBI->connect(" in > Kernel/System/DB.pm [...] > [...] > # -- > # db connect > # -- > if (!($Self->{dbh} = DBI->connect("$Self->{DSN}", > $Self->{USER}, $Self->{PW}, { LongTruncOk => 1 }))) { > $Self->{LogObject}->Log( > Caller => 1, > Priority => 'Error', > Message => $DBI::errstr, > ); > return; > } > [...] i think, i have it now. instead of: $Self->{USER}, $Self->{PW}, { LongTruncOk => 1 }))) { i tried: $Self->{USER}, $Self->{PW}, { LongReadLen => 200000 }))) { now i can connect without any failure :-) Holger

1 0

RE: [dev] SQL Server 2000 with otrs
by Holger.Struck＠sqs.de 03 Jul '03

03 Jul '03

> -----Original Message----- > From: Martin Edenhofer [mailto:martin@edenhofer.de] > Sent: Wednesday, July 02, 2003 11:02 PM > To: dev(a)otrs.org > Subject: Re: [dev] SQL Server 2000 with otrs > Yepp. But I can't say it at this moment (I need to have access to an > other machine - I get it on sunday). I had the same problem > with SAPDB. > Im not sure, but try to change the "DBI->connect(" in > Kernel/System/DB.pm > from: > > [...] > # -- > # db connect > # -- > if (!($Self->{dbh} = DBI->connect("$Self->{DSN}", > $Self->{USER}, $Self->{PW}))) { > $Self->{LogObject}->Log( > Caller => 1, > Priority => 'Error', > Message => $DBI::errstr, > ); > return; > } > [...] > > to: > > [...] > # -- > # db connect > # -- > if (!($Self->{dbh} = DBI->connect("$Self->{DSN}", > $Self->{USER}, $Self->{PW}, { LongTruncOk => 1 }))) { > $Self->{LogObject}->Log( > Caller => 1, > Priority => 'Error', > Message => $DBI::errstr, > ); > return; > } > [...] > > -Martin thanks, i tried it and it doesnt work. same error message as before. maybe i really have to wait until sunday? ;-) Holger

1 0