mariadb cannot use collation with mysql_stmt_prepare

asked 2018-01-20 11:19:04 -0500

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)

edit retag flag offensive close merge delete