Elxis CMS Forum

Support => Database => Topic started by: forgetms on December 23, 2007, 16:55:25

Title: Large number of error-prone SQL statements
Post by: forgetms on December 23, 2007, 16:55:25

Hi!

The older versions of PostgreSQL (and other databased, too?) is "forgiving" to not-so-strict SQL statements. Newer PostgreSQL like 8.3 requires more strict SQL standard compliant statements. As a result, a huge number of SQL statements are no longer accepted by PostgreSQL. An example is when

http://localhost/administrator/index2.php?option=com_frontpage

yields this error

Code: [Select]

ERROR:  operator does not exist: integer = character varying at character 256
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  SELECT c.*, g.name AS groupname, cc.name, cc.language AS category_lang, s.name AS sect_name, u.name AS editor, f.ordering AS fpordering, v.name AS author
FROM content AS c
INNER JOIN categories AS cc ON cc.id = c.catid
INNER JOIN sections AS s ON s.id = cc.section AND s.scope='content'
INNER JOIN content_frontpage AS f ON f.content_id = c.id
INNER JOIN core_acl_aro_groups AS g ON g.group_id = c.access
LEFT JOIN users AS u ON u.id = c.checked_out
LEFT JOIN users AS v ON v.id = c.created_by
WHERE c.state >= 0
ORDER BY f.ordering LIMIT 10 OFFSET 0
This error comes from

Code: [Select]

s.id = cc.sectionTo fix it for PostgreSQL, the Elxis PHP codes must be be modified as

Code: [Select]

s.id::VARCHAR = cc.section or

Code: [Select]

CAST(s.id AS VARCHAR) = cc.sectionSimilar loose (not so SQL compliant?) SQL statements exist in many *.php files. IIRC, there are about 15! I feel such problems must be seriously dealt with.

Title: Re: Large number of error-prone SQL statements
Post by: forgetms on December 23, 2007, 17:16:28

OK. Please refer to the 3 or 4

Code: [Select]

CAST( in the attached file revised from
~/administrator/components/com_frontpage/admin.frontpage.php

[old attachment deleted by admin]

Title: Re: Large number of error-prone SQL statements
Post by: datahell on December 23, 2007, 18:40:04

Thank you very much forgetms, I checked your sql statements and made the necessary changes to admin.frontpage.php. I use elxis with postgre 8.1 and existing queries work fine so it is hard to determine the wrong queries. I am not a postgre specialist. If you found more sql statements that needs to be modified please report them to us.

Thanks again!