Working on OTRS with Oracle... fetchrow Problem
Summary: Posting new tickets result in Quotation Errors. When correcting them with a patch, the fetchrow method fails. I did the following: a) Install the Oracle 9 client. b) Install Perl DBI and DBD::Oracle c) Create an Oracle SQL Schema including triggers for the auto increment values, index names hat to be cut off (some where longer than 32 chars). The "session" table has to be renamed to "sessiondb" as "session" is a reserved word - we are not using an sql session database anyhow. d) Create an own initial SQL file for feeding databases (you have to replace "&" with '||chr(38)||') so sqlplus will not complain any more. This also worked. I can put these two files (otrs-schema.oracle and initial_insert.oracle) on my server if there is interest for it. After this I finally had the chance to log on (root@localhost). I was able to create users and groups - it worked. Problem: I can't open a ticket. The result is shown in http://pgienger.de/otrs/oci-error.png Look at the wrong quotation of the content-type. So I did this in the source code of Kernel/System/Ticket/Article.pm (not meant as a contribution, it is more like an instant patch to test): # do db insert $DBParam{ContentType} =~ s/\'//g; After this, there is a new error rising, shown in http://pgienger.de/otrs/oci-fetch-error.png Remarkably, the tickets get created! The proof is shown in http://pgienger.de/otrs/oci-ticket-created.png I am a little bit stuck because of the quotation error and because of the fetchrow error. The database type is "oracle", the communication works. I looked at the DB::Limit-Selector and I saw the Oracle definition which seems to be correct. What's the next step to do? I am happy to contribute if I can. I am using OTRS 1.2.3 on a Linux box. Perl version is 5.8.1. Database is: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Pascal PS: Textual error messages are: a) Quotation error: Traceback: ERROR: OTRS-CGI-42 Perl: 5.8.1 OS: linux Time: Tue May 18 11:27:37 2004 Message: ORA-01756: quoted string not properly terminated (DBD ERROR: OCIStmtPrepare), SQL: 'INSERT INTO article (ticket_id, article_type_id, article_sender_type_id, a_from, a_reply_to, a_to, a_cc, a_subject, a_message_id, a_body, a_content_type, content_path, valid_id, incoming_time, create_time, create_by, change_time, change_by) VALUES (19, 5, 3, 'Pascal.Gienger@testadresse.invalid', '', 'Misc', '', 'This is a test ticket.', '', 'Test Test Test', 'text/plain'; charset=iso-8859-15', '2004/05/18', 1, 1084872457, current_timestamp, 1, current_timestamp, 1)' Traceback (14372): Module: Kernel::System::Ticket::Article::CreateArticle (v1.52) Line: 83 Module: Kernel::Modules::AgentPhone::Run (v1.55.2.2) Line: 514 Module: Apache::ROOT::otrs::index_2epl::handler (v1.66) Line: 687 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149 b) fetchrow error: Software error: Can't call method "fetchrow_array" on an undefined value at /opt/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 438. For help, please send mail to the webmaster (webmaster@linux.local), giving this error message and the time and date of the error.
I managed to solve the issue, but there is a bug in DB.pm regarding Oracle: The quoting characters were not ok. *** DB.pm.dist 2004-05-18 15:47:49.000000000 +0200 --- DB.pm 2004-05-18 15:52:35.000000000 +0200 *************** sub new { *** 138,144 **** $Self->{'DB::DirectBlob'} = 0; $Self->{'DB::QuoteSignle'} = '\''; $Self->{'DB::QuoteBack'} = 0; ! $Self->{'DB::QuoteSemicolon'} = '\''; $Self->{'DB::Attribute'} = { LongTruncOk => 1, LongReadLen => 100*1024, --- 138,144 ---- $Self->{'DB::DirectBlob'} = 0; $Self->{'DB::QuoteSignle'} = '\''; $Self->{'DB::QuoteBack'} = 0; ! $Self->{'DB::QuoteSemicolon'} = ''; $Self->{'DB::Attribute'} = { LongTruncOk => 1, LongReadLen => 100*1024, In Oracle 9, there is no need to escape the semicolon or the backslash: SQL> insert into test (text) values('Eins; zwei'); 1 row created. SQL> insert into test (text) values('Eins ''Zwei'' Drei'); 1 row created. SQL> insert into test (text) values('Eins\Zwei\Drei'); 1 row created. SQL> select text from test; TEXT -------------------------------------------------------------------------------- Eins; zwei Eins 'Zwei' Drei Eins\Zwei\Drei Only the single brackets have to be escaped! The second error was due to a wrong datatype in my schema. You can't search CLOBs without extra software (and an extra sql syntax). So I had to introduce some VARCHAR2(4000) (4000 is the maximum length vor variable char fields) to be able to construct an index on them for faster searches. I think 4000 chars is enough for A_FROM and A_SUBJECT. Now my OTRS with oracle is up and running with one remaining problem: Oracle SQL does not like literals greater than 4000 chars. So an insert of long attachment-mails in the article-tables does not work: Message: ORA-01704: string literal too long (DBD ERROR: error possibly near <*> indicator at char 171 in 'INSERT INTO article_attachment (article_id, filename , content_type, content, create_time, create_by, change_time, change_by) VALUE S (3, 'oci-error.png', 'image/png', [... rest omitted...] There is a special CLOB handling method to upload such bigger data hunks but this would lead to a bigger change of your otrs code especially for Oracle. Pascal
participants (1)
-
Pascal Gienger