Index: ChangeLog =================================================================== --- ChangeLog (revision 490) +++ ChangeLog (working copy) @@ -5,8 +5,10 @@ but must be enabled through configuration files or the DSN. (Bug #12918) Bugs fixed: + * If there was more than one unique key on a table, the correct fields + were not used in handling SQLSetPos(). (Bug #10563) * "Out of sync error" or "MySQL Server has gone away" after executing a - stored procedure (Bug #27544) + stored procedure (Bug #27544) * SQL_C_TYPE_DATE, SQL_C_TYPE_TIME, and SQL_C_TYPE_TIMESTAMP were formatted without seperators, which could cause them to get interpreted incorrectly in some cases due to server bugs. (Bug #15773) Index: driver/cursor.c =================================================================== --- driver/cursor.c (revision 490) +++ driver/cursor.c (working copy) @@ -185,61 +185,113 @@ } -/* - @type : myodbc3 internal - @purpose : checks whether the Primary Key column exists in the table - if it exists, returns the PK column name +/** + Check if a field exists in a result set. + + @param[in] name Name of the field + @param[in] result Result set to check */ +static my_bool have_field_in_result(const char *name, MYSQL_RES *result) +{ + MYSQL_FIELD *field; + unsigned int ncol; -static SQLRETURN check_if_pk_exists(STMT FAR *stmt) + for (ncol= 0; ncol < result->field_count; ncol++) + { + field= result->fields + ncol; + if (myodbc_strcasecmp(name, +#if MYSQL_VERSION_ID >= 40100 + field->org_name +#else + field->name +#endif + ) == 0) + return TRUE; + } + + return FALSE; +} + + +/** + Check if a primary or unique key exists in the table referred to by + the statement for which all of the component fields are in the result + set. If such a key exists, the field names are stored in the cursor. + + @param[in] stmt Statement + + @return Whether a usable unique keys exists +*/ +static my_bool check_if_usable_unique_key_exists(STMT *stmt) { - char buff[NAME_LEN+18]; - MYSQL_ROW row; - MYSQL_RES *presult; + char buff[NAME_LEN * 2 + 18], /* Possibly escaped name, plus text for query */ + *pos, *table; + MYSQL_RES *res; + MYSQL_ROW row; + int seq_in_index= 0; - if ( stmt->cursor.pk_validated ) - return(stmt->cursor.pk_count); + if (stmt->cursor.pk_validated) + return stmt->cursor.pk_count; - /* - Check for the existence of keys in the table - We quote the table name to allow weird table names. - TODO: Write a table-name-quote function and use this instead. - */ #if MYSQL_VERSION_ID >= 40100 - strxmov(buff,"show keys from `",stmt->result->fields->org_table,"`",NullS); -#else - strxmov(buff,"show keys from `",stmt->result->fields->table,"`",NullS); + if (stmt->result->fields->org_table) + table= stmt->result->fields->org_table; + else #endif - MYLOG_QUERY(stmt, buff); - pthread_mutex_lock(&stmt->dbc->lock); - if ( mysql_query(&stmt->dbc->mysql,buff) || - !(presult= mysql_store_result(&stmt->dbc->mysql)) ) + table= stmt->result->fields->table; + + /* Use SHOW KEYS FROM table to check for keys. */ + pos= strmov(buff, "SHOW KEYS FROM `"); + pos+= mysql_real_escape_string(&stmt->dbc->mysql, pos, table, strlen(table)); + pos= strmov(pos, "`"); + + MYLOG_QUERY(stmt, buff); + + pthread_mutex_lock(&stmt->dbc->lock); + if (mysql_query(&stmt->dbc->mysql, buff) || + !(res= mysql_store_result(&stmt->dbc->mysql))) + { + set_error(stmt, MYERR_S1000, mysql_error(&stmt->dbc->mysql), + mysql_errno(&stmt->dbc->mysql)); + pthread_mutex_unlock(&stmt->dbc->lock); + return FALSE; + } + + while ((row= mysql_fetch_row(res)) && + stmt->cursor.pk_count < MY_MAX_PK_PARTS) + { + int seq= atoi(row[3]); + + /* If this is a new key, we're done! */ + if (seq <= seq_in_index) + break; + + /* Unless it is non_unique, it does us no good. */ + if (row[1][0] == '1') + continue; + + /* If this isn't the next part, this key is no good. */ + if (seq != seq_in_index + 1) + continue; + + /* Check that we have the key field in our result set. */ + if (have_field_in_result(row[4], stmt->result)) { - set_error(stmt,MYERR_S1000,mysql_error(&stmt->dbc->mysql), - mysql_errno(&stmt->dbc->mysql)); - pthread_mutex_unlock(&stmt->dbc->lock); - return(0); + /* We have a unique key field -- copy it, and increment our count. */ + strmov(stmt->cursor.pkcol[stmt->cursor.pk_count++].name, row[4]); + seq_in_index= seq; } + else + /* Forget about any key we had in progress, we didn't have it all. */ + stmt->cursor.pk_count= seq_in_index= 0; + } + mysql_free_result(res); + pthread_mutex_unlock(&stmt->dbc->lock); - /* - TODO: Fix this loop to only return columns that are part of the - primary key. - */ - while ( (row= mysql_fetch_row(presult)) && - (stmt->cursor.pk_count < MY_MAX_PK_PARTS) ) - { - /* - Collect all keys, it may be - - PRIMARY or - - UNIQUE NOT NULL - TODO: Fix this seperately and use the priority.. - */ - strmov(stmt->cursor.pkcol[stmt->cursor.pk_count++].name,row[4]); - } - mysql_free_result(presult); - pthread_mutex_unlock(&stmt->dbc->lock); - stmt->cursor.pk_validated= 1; - return(stmt->cursor.pk_count); + /* Remember that we've figured this out already. */ + stmt->cursor.pk_validated= 1; + + return stmt->cursor.pk_count > 0; } @@ -681,8 +733,11 @@ /* simply append WHERE to our statement */ dynstr_append_mem( dynQuery, " WHERE ", 7 ); - /* IF pk exists THEN use pk cols for where ELSE use all cols */ - if (check_if_pk_exists(pStmt)) + /* + If a suitable key exists, then we'll use those columns, otherwise + we'll try to use all of the columns. + */ + if (check_if_usable_unique_key_exists(pStmt)) { if (insert_pk_fields(pStmt, dynQuery) != SQL_SUCCESS) return SQL_ERROR; Index: test/my_cursor.c =================================================================== --- test/my_cursor.c (revision 490) +++ test/my_cursor.c (working copy) @@ -2460,10 +2460,10 @@ ok_stmt(hstmt, SQLSetCursorName(hstmt, (SQLCHAR *)"bug", SQL_NTS)); - ok_sql(hstmt, "SELECT a FROM t_bug10563 WHERE b > 3"); + ok_sql(hstmt, "SELECT b FROM t_bug10563 WHERE b > 3"); ok_stmt(hstmt, SQLFetch(hstmt)); - is_num(my_fetch_int(hstmt, 1), 1); + is_num(my_fetch_int(hstmt, 1), 4); ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE)); ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE));