OTRS Generic Interface fails when using Ticket::TicketSearch

[eandrex]

Hi,

As title says, when using Ticket::TicketSearch for a soap webservice and CustomerUserLogin as authentication method, it fails

Code: Select all

[Tue Oct 22 14:50:34 2013][Error][Kernel::System::Group::GroupGroupMemberList][855] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000), SQL: 'SELECT g.id, g.name, gu.permission_key, gu.permission_value, gu.user_id
        FROM groups g, group_user gu, users u
        WHERE g.valid_id IN (1)
            AND g.id = gu.group_id
            AND gu.permission_value = 1
            AND gu.permission_key IN ( 'ro','rw' )
            AND gu.user_id = u.id
            AND u.valid_id IN ( 1 )
            AND gu.user_id = '

i read it was fixed time ago.. https://github.com/OTRS/otrs/pull/86 but im experiencing it on a fresh install of otrs 3.2.10 running on windows server 2008 and SQL Server 2008

i tested with Ticket::TicketGet and it works(havent tested it with Ticket::TicketCreate tho)..

have anyone fixed it? thank you in advanced

[eandrex]

hmm..weird stuff...

i took a look into Kernel\GenericInterface\Operation\Ticket\TicketSearch.pm

and it was way different from "its fix" (https://github.com/OTRS/otrs/pull/86/files)

so i added the missing line and edited "UserID => $UserID," to "$UserType => $UserID,"

and it worked

[eandrex]

I just teste with Ticket::TicketCreate using CustomerUserLogin as authentication
and..error:

Code: Select all

[Tue Oct 22 16:04:09 2013][Error][Kernel::System::Ticket::TicketCreate][500] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018), SQL: '
            INSERT INTO ticket (tn, title, create_time_unix, type_id, queue_id, ticket_lock_id,
                user_id, responsible_user_id, ticket_priority_id, ticket_state_id,
                escalation_time, escalation_update_time, escalation_response_time,
                escalation_solution_time, timeout, service_id, sla_id, until_time,
                valid_id, archive_flag, create_time, create_by, change_time, change_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, ?, ?, 0, ?, ?,
                
        '2013-10-22 16:04:09'
    , ?, 
        '2013-10-22 16:04:09'
    , ?)'

it works if i use UserLogin

[eandrex]

update:

it seems to fail as well with mysql as db engine

Code: Select all

[Tue Oct 22 17:05:19 2013][Error][Kernel::System::Ticket::TicketCreate][500] Cannot add or update a child row: a foreign key constraint fails (`<yourdatabasename>`.`ticket`, CONSTRAINT `FK_ticket_create_by_id` FOREIGN KEY (`create_by`) REFERENCES `users` (`id`)), SQL: '
            INSERT INTO ticket (tn, title, create_time_unix, type_id, queue_id, ticket_lock_id,
                user_id, responsible_user_id, ticket_priority_id, ticket_state_id,
                escalation_time, escalation_update_time, escalation_response_time,
                escalation_solution_time, timeout, service_id, sla_id, until_time,
                valid_id, archive_flag, create_time, create_by, change_time, change_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, ?, ?, 0, ?, ?,
                
        '2013-10-22 17:05:19'
    , ?, 
        '2013-10-22 17:05:19'
    , ?)'

[eandrex]

update #2:

i also tried creating a SessionID using Session::SessionCreate and passing it to Ticket::TicketCreate ( <SessionID>?</SessionID> ) but same error as i were using CustomerUserLogin for authentication