cannot use collation with mysql_stmt_prepare

If I want to select with a case insensitive collation

SELECT * FROM test WHERE value = ?  COLLATE utf8_general_ci</code>

and in a prepare function an error is returned:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'

A normal query works without errors.

What am I doing wrong?


here's my test program:

// test: $ gcc mysql.c `mysql_config --libs` && ./a.out
#include <stdio.h>
#include <string.h>
#include <mysql/mysql.h>

MYSQL *mysql;

#define ERROR() do { fprintf(stderr,"! %s.\n",mysql_error(mysql)); return 1; } while(0)
#define QUERY(sql) if( mysql_query(mysql,sql) ) ERROR()
#define CHARSET "utf8"
#define COLL " COLLATE " CHARSET "_general_ci"

int main()  {
	mysql = mysql_init(0);
	mysql_options( mysql, MYSQL_SET_CHARSET_NAME, CHARSET);
	if( mysql_real_connect(mysql,"localhost","gasi",0,
			"gasi",0,0,0) ) {
		fprintf(stderr,"# connected %s\n",mysql_character_set_name(mysql));
		// QUERY("SET NAMES '" CHARSET "'");
		QUERY("DROP TABLE IF EXISTS test");
		QUERY("CREATE TABLE test ( "
				"id INTEGER, "
				"value TEXT CHARSET " CHARSET
				")");
		QUERY("INSERT test VALUES(556,'áêíöǘ')");
		if( 0 ) {
			fprintf(stderr,"# query:\n");
			QUERY(    "SELECT * FROM test WHERE value='x'" COLL);
		}
		else {
			char *s = "SELECT * FROM test WHERE value= ? " COLL;
			fprintf(stderr,"# prepare:\n");
			MYSQL_STMT *stmt = mysql_stmt_init(mysql);
			if( mysql_stmt_prepare(stmt,s,strlen(s)) ) {
				ERROR();
			}
		}
		mysql_close(mysql);
		fprintf(stderr,"# done\n");
		return 0;
	}
	else {
		ERROR();
	}
}

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.