Unable to execute the specified SQL query

[Joshua T M]

Bump on executing queries @Victor. 

 

[Victor]

@Joshua T M that's different than what is being discussed here in this thread... You won't be able to execute anything else other than SELECT statements using Database Direct.

"Enforced checks for Database Direct to only allow SELECTs for production instances"

 

[Joshua T M]

On 1/8/2020 at 11:57 AM, Victor said:

@Joshua T M that's different than what is being discussed here in this thread... You won't be able to execute anything else other than SELECT statements using Database Direct.

"Enforced checks for Database Direct to only allow SELECTs for production instances"

 

Victor,

 

This is now going to cause us major issues. One script is used to migrate emails to mailboxes which is a feature that is not avaliable and was actually something agreed when we implemented the Hornbill solution, it saves us huge amounts of time clearing these mails. Can you suggest a workaround for this? Please feel free to message me to keep this topic clean.

 

Sample -

 

-- Move Sent and Deleted emails

UPDATE h_msg_messages msg
, h_sys_organizations organisation
SET msg.h_folder_id = organisation.h_custom_11
, h_msg_status = 2 -- mark as read
WHERE
(
msg.h_folder_id IN (113, 114) -- STRUMIS Support -> Sent & Deleted Folders
)
AND
(
(
LOCATE(REPLACE(CONCAT('- ', organisation.h_organization_name, ' -'), ' ', ''), REPLACE(msg.h_msg_subject, ' ', '')) > 0 -- Look for '- Name -'' only in subject
AND organisation.h_archived = 0 -- Ignore archived organisations
AND IFNULL(organisation.h_custom_11, -1) > 1 -- Must have a valid folder
AND IFNULL(organisation.h_custom_11, -1) <> 172 -- Ignore STRUMIS organisation
)
OR
(
organisation.h_organization_name = 'STRUMIS'
AND
(
msg.h_msg_subject LIKE '%- STRUMIS (Australia) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Belgium) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Canada) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (China) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (France) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (India) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Malaysia) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Middle East) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Netherlands) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Romania) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (South Africa) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (Training) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (United Kingdom) -%'
OR msg.h_msg_subject LIKE '%- STRUMIS (United States) -%'
)
)
)

[Victor]

@Joshua T M you mentioned a script. I assume/hope it does more than just the plain SQL update. Amend the script to retrieve the emails you need to move with a SELECT statement  to retrieve the IDs of the messages that needs to be moved and the destination folder. Then make use of the https://api.hornbill.com/mail/?op=moveMessage API to move these messages.

EDIT: Probably you have noticed, I have created a separate thread for this: https://community.hornbill.com/topic/17518-unable-to-execute-the-specified-sql-query/

 

[Joshua T M]

On 1/10/2020 at 2:07 PM, Victor said:

@Joshua T M you mentioned a script. I assume/hope it does more than just the plain SQL update. Amend the script to retrieve the emails you need to move with a SELECT statement  to retrieve the IDs of the messages that needs to be moved and the destination folder. Then make use of the https://api.hornbill.com/mail/?op=moveMessage API to move these messages.

EDIT: Probably you have noticed, I have created a separate thread for this: https://community.hornbill.com/topic/17518-unable-to-execute-the-specified-sql-query/

 

Thanks Victor.