sql_compare_text()

sql_compare_text()

by Mark Johnson -
Number of replies: 12
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I've just gotten this error from a get_record query:

Comparisons of text column conditions are not allowed. Please use sql_compare_text() in your query.

This is a new one on me, and I don't know what I should be doing to alleviate it.  sql_compare_text() doesn't appear to be documented anywhere, and the code itself doesn't really help.  The comments talk about comparing two columns, which isn't what I'm trying to do (I'm not even sure this is possible with get_record?), and the function just appears to call sql_order_by_text().

Any pointers would, as always, be greatly appreciated smile

Average of ratings: -
In reply to Mark Johnson

Re: sql_compare_text()

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I wasn't sure either, but the answer seems to simply be that..


$sql = "select * from {user_info_data} where data = ? ";

causes the error, and...


$sql = "select * from {user_info_data} where " . $DB->sql_compare_text('data') . " = ? ";

doesn't (from an example I found quickly). In this case 'data' is a text field. You can't compare text fields without wrapping them in this. I *think* it's digs into the db library depths and modifies to suit different database types. For MySQL and Postgres it appears to do nothing at all though.
Average of ratings: -
In reply to Howard Miller

Re: sql_compare_text()

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

That's fair enough, but I'm not creating an sql string, I'm just using $DB->get_record(), which I would assume should do the necessary for me.  Should I file this as a bug?

Average of ratings: -
In reply to Mark Johnson

Re: sql_compare_text()

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I'm struggling to find an example, but if you take the view that get_record is just a special case of an sql call then something like...


$DB->get_record( 'some_table', array('textfield' => 'fred') )

isn't going to work either, and should be...


$DB->get_record( 'some_table', array(sql_compare_text('textfield') => 'fred'))

...which I think is a syntax error (?), but you get my point I hope.
Average of ratings: -
In reply to Howard Miller

Re: sql_compare_text()

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Thanks for that Howard, I tried exactly as you suggest there and I got the same error.

What I would expect to happen in the first case above, is for the parameters to be passed to where_clause(), and where_clause() to say "that's a text field, I need to run it through sql_compare_text()" rather than one having to do so before it's passed to get_record() (after all, where_clause() would have no way of knowing this had already happened).  Instead, it just seems to say "that's a text field, forget it".  Unless that's the intended behaviour, in which case the error message needs to be changed to make that clear and suggest a solution.

In the meantime, I'm using get_record_select() with sql_compare_text() around the field name in the WHERE clause, which seems to work around the issue.

Average of ratings: -
In reply to Mark Johnson

Re: sql_compare_text()

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I think those who are more knowledgeable (I resisted the temptation to say "those that caused all this trouble" big grin ) will say that you *have* to use the _select variant for this type of query !
Average of ratings: -
In reply to Howard Miller

Re: sql_compare_text()

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't mind that being the case, as long as they *actually* say that in the error message smile

I've created MDL-27629 to track this.

Average of ratings: -
In reply to Mark Johnson

Re: sql_compare_text()

by A B -

Apology for reviving a couple of years old post, but I am facing the same error here. And to my knowledge, I think I pin pointed the problem:

In line 3234:

'eventtype' => 'facetofacesession' is causing the error (I guess)...

Any idea to resolve this!

Thank you.

Average of ratings: -
In reply to A B

Re: sql_compare_text()

by Ramchadnra Pujari -

@Avas B : I am also facing the same issue now you have solved this?

 

If solved please give me solution

 

Thank you

Average of ratings: -
In reply to Howard Miller

Re: sql_compare_text()

by hemalatha arun -

 example code for the sql_compare_text()

  $compare_scale_clause = $DB->sql_compare_text('shortname')  . ' = 

   ' . $DB->sql_compare_text(':short');

  compare text first  and  then append the variable to your query

  $DB->get_records_sql("select * from {table name} where $compare_scale_clause",      array('short' => $sh));

Average of ratings: Useful (1)
In reply to Mark Johnson

Re: sql_compare_text()

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

I know this is really really really old, but since I arrived here because I had the same error, I thought I'd explain why the error happens. 

It's because the field name you (we) are using are MySql reserved words - in my case "code" and in the examples above "name" and "data". Usually, if you do an ordinary select command, you'll wrap those field names in backticks ``, but using the get_record(s) method doesn't add those for you.

The work around is to either use the get_records_sql and put in your own backticks, or change the field name in the table, if that's within your power.

Field names to avoid: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

pip pip

Average of ratings: Useful (1)
In reply to Mark Sharp

Re: sql_compare_text()

by v k -

Hello,

Maybe your version is correct, but it isn't applicable to my issue:

I face today the 'textconditionsnotallowed' with $DB->get_field function (though I used it many times, don't know what's wrong now...) and my DB is MSSQL, so I also checked for not using it's reserved words..

My code is:
 

$table='mytable';
$params = ['userid'=>2, 'mynote'=>'some note'];
$id=$DB->get_field($table,'id', $params]); 


In the 'mytable', the field 'userid' is type of bigint and 'mynote' is varchar(MAX), so can't get the point of the error...

Also I tried changing single quotes to double quotes - no effect.

How to resolve this, does anybody know, please?

Average of ratings: -