We were using 5.2 for a long time, but recently upgraded the k1000 to the most current version (at the time of this questions, v5.4.70402)

 

when we were on the old version we had a custom rule on the ticketing system to add url's to the tickets for the file attachments so we could view the screenshots, excel files etc from a click in the email to the technician. This seems to be broken now in 5.4.

 

the select query we had was this:

 

select 

C.ID 

FROM

HD_TICKET 

JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID> and

DESCRIPTION LIKE '%Added Attachment%'

WHERE

1=1

The update query we had was this:

 

UPDATE 

HD_TICKET_CHANGE C JOIN

(select C.ID,

GROUP_CONCAT( CONCAT('Added Attachment http://k1000.comapnyhidden.org/packages/hd_attachments/',C.ID,'/',REPLACE(FILE_NAME,' ','%20')) SEPARATOR '\n') GROUPURLFIX,GROUP_CONCAT( CONCAT('Added Attachment \'',FILE_NAME,'\'') SEPARATOR '\n') GROUPURLOLD

FROM

HD_TICKET_CHANGE C 

JOIN HD_ATTACHMENT A ON A.HD_TICKET_CHANGE_ID=C.ID

WHERE C.ID=<TICKET_IDS> and  DESCRIPTION LIKE '%Added attachment%'

GROUP BY C.ID) REPVALUES

ON REPVALUES.ID=C.ID and DESCRIPTION LIKE '%Added attachment%' 

SET

DESCRIPTION=CONCAT(

SUBSTRING(DESCRIPTION FROM 1 FOR LOCATE(REPVALUES.GROUPURLOLD,DESCRIPTION)-1),

REPVALUES.GROUPURLFIX,

SUBSTRING(DESCRIPTION FROM (LOCATE(REPVALUES.GROUPURLOLD,DESCRIPTION)+LENGTH(REPVALUES.GROUPURLOLD)) FOR LENGTH(DESCRIPTION))

)

 

When I try to manually run the query, I get this error

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= and DESCRIPTION LIKE '%Added Attachment%' WHERE 1=1 and HD_TICKE' at line 3] in EXECUTE("SELECT COUNT(*) FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID= and DESCRIPTION LIKE '%Added Attachment%' WHERE 1=1 and HD_TICKET.HD_QUEUE_ID = '1' LIMIT 1")

 

I'm not any sort of sql person so I don’t know what this means. Any help would be appreciated. Thanks!

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

The description field is no longer in the 5.4 database. Look in the friendly name field for your field descriptor.

Answered 01/21/2013 by: jdornan
Red Belt

  • jdornan, Thank you for your reply. I however, do not seem to be able to find where I would find the friendly name for the field I am looking for. We have a pretty basic standard instlal of the KBOX I do not believe we have made any custom changes to field names. If you could point me in the right direction I could get the info back to you for some help recreating the select statement and the update query.

    thanks!
  • Does anyone else have any suggestions? I cant seem to find the field jdornan referenced.
Please log in to comment
Answer this question or Comment on this question for clarity