gilles.massen | 2 Jul 10:55 2003
Picon

Initiate Ticket and send email

Hi,

I assembled a funtion to enable agents to create ticket and send emails. 
Feel free to do with it whatever you want.

A few comments: this is basically patchwork with pieces from AgentPhone 
and AgentCompose. It is in no way clean and reliable code. It works (for 
me...) and that's it. Feel free to comment though....

Best,
Gilles

--
RESTENA - DNS-LU
6, rue Coudenhove-Kalergi
L-1359 Luxembourg
tel: (+352) 424409
fax: (+352) 422473
Attachment (AgentEmail.tgz): application/octet-stream, 13 KiB
Holger.Struck | 2 Jul 11:42 2003
Picon

SQL Server 2000 with otrs

Hi,

how i can use the MS SQL Server 2000 as database server for otrs?
I have imported the tables into SQL Server.
Im trying to connect via DBI:ODBC.
It seems to work, but if i try to login into otrs, i get this error:
"SessionID invalid! Need user data!"
And in the Apache error.log i found this:
"(long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) at d:/otrs/Kernel/System/DB.pm line 249"

Any Idea how to solve this?


Regards,

Holger Struck

Martin Edenhofer | 2 Jul 23:01 2003
Picon

Re: SQL Server 2000 with otrs

Hi Holger,

On Wed, Jul 02, 2003 at 11:42:26AM +0200, Holger.Struck <at> sqs.de wrote:

> how i can use the MS SQL Server 2000 as database server for otrs?
> I have imported the tables into SQL Server.

Cool! :-) Cool Holger!

> Im trying to connect via DBI:ODBC.
> It seems to work, but if i try to login into otrs, i get this error:
> "SessionID invalid! Need user data!"
> And in the Apache error.log i found this:
> "(long truncated DBI attribute LongTruncOk not set and/or LongReadLen too
> small) at d:/otrs/Kernel/System/DB.pm line 249"
> Any Idea how to solve this?

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;
    }
[...]

> Holger Struck

  -Martin

--
Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/
--

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

Holger.Struck | 3 Jul 09:09 2003
Picon

RE: SQL Server 2000 with otrs



> -----Original Message-----
> From: Martin Edenhofer [mailto:martin <at> edenhofer.de]
> Sent: Wednesday, July 02, 2003 11:02 PM
> To: dev <at> 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

Holger.Struck | 3 Jul 10:08 2003
Picon

RE: SQL Server 2000 with otrs



> -----Original Message-----
> From: Martin Edenhofer [mailto:martin <at> edenhofer.de]
> Sent: Wednesday, July 02, 2003 11:02 PM
> To: dev <at> 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

Holger.Struck | 9 Jul 10:22 2003
Picon

OTRS with MS SQL Server

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
 

Martin Edenhofer | 9 Jul 10:29 2003
Picon

Re: OTRS with MS SQL Server

Hi Holger,

On Wed, Jul 09, 2003 at 10:22:54AM +0200, Holger.Struck <at> sqs.de wrote:
> 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.

Cool! :)

> 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?

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 ''?

> regards
> holger

  Martin

--
Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/
--
Noch 67 Tage bis zum Gäubodenvolksfest! ;-)
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

Holger.Struck | 9 Jul 10:35 2003
Picon

RE: OTRS with MS SQL Server



> -----Original Message-----
> From: Martin Edenhofer [mailto:martin <at> edenhofer.de]
> Sent: Wednesday, July 09, 2003 10:29 AM
> To: dev <at> 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

Martin Edenhofer | 9 Jul 10:45 2003
Picon

Re: OTRS with MS SQL Server

Hi Holger,

On Wed, Jul 09, 2003 at 10:35:56AM +0200, Holger.Struck <at> sqs.de wrote:
> yepp, its also ''.
> 
> so, where do i have to change this?

I improved the Kernel/System/DB.pm to detect different database types and
use the preferences of the database.

Just one more question and I send you a working Kernel/System/DB.pm 
for mssql. .-) 

Do you also need to quote \ on mssql?

> Holger

  Martin

--
Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/
--
"The number of Unix installations has grown to 10, with more expected."
                      The Unix Programmer's Manual, 2nd Edition, June 1972

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

Holger.Struck | 9 Jul 10:59 2003
Picon

RE: OTRS with MS SQL Server



> -----Original Message-----
> From: Martin Edenhofer [mailto:martin <at> edenhofer.de]
> Sent: Wednesday, July 09, 2003 10:46 AM
> To: dev <at> 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


Gmane