sql_compare_text()

sql_compare_text()

Mark Johnson -
回帖数：12
Core developers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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 微笑

平均分： -
回复Mark Johnson

Re: sql_compare_text()

Howard Miller -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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.
平均分： -
回复Howard Miller

Re: sql_compare_text()

Mark Johnson -
Core developers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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?

平均分： -
回复Mark Johnson

Re: sql_compare_text()

Howard Miller -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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.
平均分： -
回复Howard Miller

Re: sql_compare_text()

Mark Johnson -
Core developers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 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.

平均分： -
回复Mark Johnson

Re: sql_compare_text()

Howard Miller -
Core developers的头像 Documentation writers的头像 Particularly helpful Moodlers的头像 Peer reviewers的头像 Plugin developers的头像
I think those who are more knowledgeable (I resisted the temptation to say "those that caused all this trouble" 大笑 ) will say that you *have* to use the _select variant for this type of query !
平均分： -
回复Mark Johnson

Re: sql_compare_text()

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.

平均分： -
回复Howard Miller

Re: sql_compare_text()

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));

平均分：Useful (1)
回复Mark Johnson

Re: sql_compare_text()

Mark Sharp -
Core developers的头像 Particularly helpful Moodlers的头像 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

平均分：Useful (1)
回复Mark Sharp

Re: sql_compare_text()

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?

平均分： -