XRMS error

[ncc@sgcom.com.s]

After installing XRMS, I managed to login as the administrator to create a customer company profile.

But when I click on the activities menu, I encounter the following error in activities/some.php:

Unable to execute your query. Please correct this error.
You have an error in your SQL syntax near '(SELECT 'Current User', '-1') UNION (select username, user_id from users where u' at line 1 
I tried to execute:
SELECT (CASE WHEN (activity_status = 'o') AND (ends_at < '2005-03-01 15:55:59') THEN 'Yes' ELSE '-' END) AS 'Overdue', at.activity_type_pretty_name AS 'Type',CONCAT('<a href="../contacts/one.php?contact_id=',cont.contact_id,'">',cont.first_names,' ',cont.last_name,'</a>') AS 'Contact',CONCAT('<a href="one.php?activity_id=',a.activity_id,'&amp;return_url=/activities/some.php">',activity_title,'</a>') AS 'Title', DATE_FORMAT(a.scheduled_at,'%Y-%m-%d') AS 'Scheduled', DATE_FORMAT(a.ends_at,'%Y-%m-%d') AS 'Due', CONCAT('<a href="../companies/one.php?company_id=',c.company_id,'">',c.company_name,'</a>') AS 'Company', u.username AS 'Owner', 'n/a' AS '%' FROM companies c, activity_types at, addresses addr, activities a LEFT OUTER JOIN contacts cont ON cont.contact_id = a.contact_id LEFT OUTER JOIN users u ON a.user_id = u.user_id WHERE a.company_id = c.company_id AND a.activity_record_status = 'a' AND at.activity_type_id = a.activity_type_id AND c.default_primary_address=addr.address_id and a.activity_status = 'o' and a.ends_at < from_unixtime(unix_timestamp(CURDATE())+(0)*24*3600) order by 'Overdue' asc

Any advise?

Thanks

 

[SBCTEC]

Error in xrms occurs in mysql versions < 4.0.1

I'm glad that we've gotten through the Header error.

The UNION SQL construct is not supported in MySQL 3.23.x, unfortunately. The 'Current User' and 'Not Set' options that are UNION'd into that query are used to make saved searches more generic.

You could patch the code in your installation to not use the UNION:

Change this around line 368:
//get menu for users
$sql2 = "(SELECT " . $con->qstr(_("Current User"),get_magic_quotes_gpc()) . ", '-1')"
. " UNION (select username, user_id from users where user_record_status = 'a' order by username)"
. " UNION (SELECT " . $con->qstr(_("Not Set"),get_magic_quotes_gpc()) . ", '-2')";

to this:
//get menu for users
$sql2 = "select username, user_id from users where user_record_status = 'a' order by username";

This will work fine, but will not have the more generic options for use in saved searches.

It may be possible that in the future we will extend getmenu2 to allow us to add custom fields to a SELECT menu without using SQL UNION clauses, but I can't say that it's really high on our (very long) list of things to do...

Regards,

- Brian

The above references:
https://sourceforge.net/forum/message.php?msg_id=2942854