From 5f45d2ae803911712b11f1219dba7a29ace4d8f2 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Sat, 9 Sep 2006 13:51:40 +0000 Subject: [PATCH] New function sql_order_by_text() to automatically build custom expresions to being used in ORDER BY clauses with TEXT columns. --- lib/dmllib.php | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) diff --git a/lib/dmllib.php b/lib/dmllib.php index f8443e253b..3d24f3a408 100644 --- a/lib/dmllib.php +++ b/lib/dmllib.php @@ -1312,6 +1312,31 @@ function sql_as() { } } +/** + * Returns the SQL text to be used to order by one TEXT (clob) column, because + * some RDBMS doesn't support direct ordering of such fields. + * Note that the use or queries being ordered by TEXT columns must be minimised, + * because it's really slooooooow. + * @param string fieldname the name of the TEXT field we need to order by + * @param string number of chars to use for the ordering (defaults to 32) + * @return string the piece of SQL code to be used in your statement. + */ +function sql_order_by_text($fieldname, $numchars=32) { + + global $CFG; + + switch ($CFG->dbtype) { + case 'mssql': + return 'CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')'; + break; + case 'oci8po': + return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)'; + break; + default: + return $fieldname; + } +} + /** * Prepare a SQL WHERE clause to select records where the given fields match the given values. * -- 2.39.5