mariadb cannot use collation with mysql_stmt_prepare
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();
}
}
Using Fedora 27 with latest update (mariadb-devel-10.2.9-3.fc27.x86_64)
add a comment