Index: driver/catalog.c =================================================================== --- driver/catalog.c (revision 614) +++ driver/catalog.c (working copy) @@ -1860,9 +1860,90 @@ CLEAR_STMT_ERROR(hstmt); my_SQLFreeStmt(hstmt,MYSQL_RESET); - if ( is_minimum_version(stmt->dbc->mysql.server_version,"3.23",4) ) + /* For 5.0 and later, use INFORMATION_SCHEMA. */ + if (is_minimum_version(stmt->dbc->mysql.server_version, "5.0", 3)) { - STMT FAR *stmt=(STMT FAR*) hstmt; + MYSQL *mysql= &stmt->dbc->mysql; + char query[2048], *buff; /* This should be big enough. */ + SQLRETURN rc; + + /* This is a big, ugly query. But it works! */ + buff= strmov(query, + "SELECT REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," + "NULL AS PKTABLE_SCHEM," + "REFERENCED_TABLE_NAME AS PKTABLE_NAME," + "REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," + "TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM," + "TABLE_NAME AS FKTABLE_NAME, COLUMN_NAME AS FKCOLUMN_NAME," + "ORDINAL_POSITION AS KEY_SEQ, 0 AS UPDATE_RULE," + "0 AS DELETE_RULE, CONSTRAINT_NAME AS FK_NAME," + "(SELECT CONSTRAINT_NAME FROM" + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND" + " TABLE_NAME = REFERENCED_TABLE_NAME AND" + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" + " AS PK_NAME," + "0 AS DEFERRABILITY" + " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" + " JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " USING (CONSTRAINT_SCHEMA,CONSTRAINT_NAME)" + " WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' "); + + if (szPkTableName && szPkTableName[0]) + { + buff= strmov(buff, "AND REFERENCED_TABLE_SCHEMA = "); + if (szPkCatalogName && szPkCatalogName[0]) + { + if (cbPkCatalogName == SQL_NTS) + cbPkCatalogName= strlen((char *)szPkCatalogName); + buff= strmov(buff, "'"); + buff+= mysql_real_escape_string(mysql, buff, (char *)szPkCatalogName, + cbPkCatalogName); + buff= strmov(buff, "' "); + } + else + buff= strmov(buff, "DATABASE() "); + buff= strmov(buff, "AND REFERENCED_TABLE_NAME = '"); + if (cbPkTableName == SQL_NTS) + cbPkTableName= strlen((char *)szPkTableName); + buff+= mysql_real_escape_string(mysql, buff, (char *)szPkTableName, + cbPkTableName); + buff= strmov(buff, "' "); + } + + if (szFkTableName && szFkTableName[0]) + { + buff= strmov(buff, "AND TABLE_SCHEMA = "); + if (szFkCatalogName && szFkCatalogName[0]) + { + if (cbFkCatalogName == SQL_NTS) + cbFkCatalogName= strlen((char *)szFkCatalogName); + buff= strmov(buff, "'"); + buff+= mysql_real_escape_string(mysql, buff, (char *)szFkCatalogName, + cbFkCatalogName); + buff= strmov(buff, "' "); + } + else + buff= strmov(buff, "DATABASE() "); + buff= strmov(buff, "AND TABLE_NAME = '"); + if (cbFkTableName == SQL_NTS) + cbFkTableName= strlen((char *)szFkTableName); + buff+= mysql_real_escape_string(mysql, buff, (char *)szFkTableName, + cbFkTableName); + buff= strmov(buff, "' "); + } + + strmov(buff, "ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ"); + + rc= my_SQLPrepare(hstmt, (SQLCHAR *)query, SQL_NTS); + if (!SQL_SUCCEEDED(rc)) + return rc; + + return my_SQLExecute(hstmt); + } + /* For 3.23 and later, use comment in SHOW TABLE STATUS (yuck). */ + else if (is_minimum_version(stmt->dbc->mysql.server_version,"3.23",4)) + { MEM_ROOT *alloc; MYSQL_ROW row; char **data; @@ -1905,8 +1986,8 @@ /* Convert mysql fields to data that odbc wants */ alloc= &stmt->result->field_alloc; - data= tempdata; - comment_id= stmt->result->field_count-1; + data= tempdata; + comment_id= stmt->result->field_count - 1; while ( (row= mysql_fetch_row(stmt->result)) ) { @@ -1920,11 +2001,12 @@ if ( !(comment_token= strchr(row[comment_id],';')) ) continue; /* MySQL 4.1 and above, the comment field is '15' */ - do + do { - /* - Found reference information in comment field from InnoDB type, - and parse the same to get the FK information .. + /* + Found reference information in comment field from + InnoDB type, and parse the same to get the FK + information .. */ key_seq= 1; @@ -1938,7 +2020,7 @@ if ( !(token= my_next_token(token+8,&comment_token,ref_token,'/')) ) continue; - + data[0]= strdup_root(alloc,ref_token); /* PKTABLE_CAT */ if (!(token= my_next_token(token, &comment_token, @@ -1956,7 +2038,7 @@ continue; pk_length= (uint)((token-2)-pk_cols_start); - data[1]= ""; /* PKTABLE_SCHEM */ + data[1]= NULL; /* PKTABLE_SCHEM */ /** @todo clean this up when current database tracking is @@ -1969,26 +2051,25 @@ data[4]= (szFkCatalogName ? strdup_root(alloc, (char *)szFkCatalogName) : strdup_root(alloc, stmt->dbc->database)); - data[5]= ""; /* FKTABLE_SCHEM */ + data[5]= NULL; /* FKTABLE_SCHEM */ data[6]= row[0]; /* FKTABLE_TABLE */ - /* - TODO : FIX both UPDATE_RULE and DELETE_RULE after - 3.23.52 is released, which supports this feature in - server by updating the 'comment' field as well as + /* + TODO : FIX both UPDATE_RULE and DELETE_RULE after + 3.23.52 is released, which supports this feature in + server by updating the 'comment' field as well as from SHOW CREATE TABLE defination.. - - right now return only SQL_CASCADE as the DELETE/UPDATE - rule - */ + right now return only SQL_CASCADE as the + DELETE/UPDATE rule + */ - data[9]= "1"; /*SQL_CASCADE*/ /* UPDATE_RULE */ - data[10]= "1"; /*SQL_CASCADE*/ /* DELETE_RULE */ + data[9]= "1"; /*SQL_CASCADE*/ /* UPDATE_RULE */ + data[10]= "1"; /*SQL_CASCADE*/ /* DELETE_RULE */ data[11]= "NULL"; /* FK_NAME */ data[12]= "NULL"; /* PK_NAME */ data[13]= "7"; /*SQL_NOT_DEFERRABLE*/ /* DEFERRABILITY */ - token = fkcomment = (char *)fk_cols_start; + token = fkcomment = (char *)fk_cols_start; pktoken = pkcomment = (char *)pk_cols_start; fkcomment[fk_length]= '\0'; pkcomment[pk_length]= '\0'; @@ -2006,8 +2087,8 @@ row_count++; for ( fk_length= SQLFORE_KEYS_FIELDS; fk_length--; ) data[fk_length]= prev_data[fk_length]; - } - data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */ + } + data[7]= strdup_root(alloc,fkcomment); /* FKTABLE_COLUMN */ data[3]= strdup_root(alloc,pkcomment); /* PKTABLE_COLUMN */ sprintf(ref_token,"%d",key_seq); data[8]= strdup_root(alloc,ref_token); /* KEY_SEQ */ @@ -2017,12 +2098,12 @@ } while ( (comment_token = strchr(comment_token,';')) );/* multi table ref */ } - } - + } + /* Copy only the elements that contain fk names */ stmt->result_array= (MYSQL_ROW) my_memdup((gptr) tempdata, - sizeof(char*)*SQLFORE_KEYS_FIELDS*row_count, - MYF(0)); + sizeof(char*)*SQLFORE_KEYS_FIELDS*row_count, + MYF(0)); my_free((gptr)tempdata, MYF(0)); if (!stmt->result_array) { @@ -2030,10 +2111,11 @@ return handle_connection_error(stmt); } } - else /* NO FOREIGN KEY support from SERVER */ + /* Versions older than 3.23 don't support foreign keys at all. */ + else { goto empty_set; - } + } stmt->result->row_count= row_count; mysql_link_fields(stmt,SQLFORE_KEYS_fields,SQLFORE_KEYS_FIELDS); return SQL_SUCCESS; Index: test/my_catalog.c =================================================================== --- test/my_catalog.c (revision 614) +++ test/my_catalog.c (working copy) @@ -789,12 +789,13 @@ Bug #4518: SQLForeignKeys returns too many foreign key Bug #27723: SQLForeignKeys does not escape _ and % in the table name arguments - The original test case was extended to have a table that would inadvertantly + The original test case was extended to have a table that would inadvertently get included because of the poor escaping. */ DECLARE_TEST(t_bug4518) { SQLCHAR buff[255]; + SQLLEN len; /** @todo re-enable this test when I_S based SQLForeignKeys is done. */ if (mysql_min_version(hdbc, "5.1", 3)) @@ -831,6 +832,12 @@ is_str(my_fetch_str(hstmt, buff, 7), "t_bug4518_c", 11); is_str(my_fetch_str(hstmt, buff, 8), "parent_id", 9); + /* For Bug #19923: Test that schema columns are NULL. */ + ok_stmt(hstmt, SQLGetData(hstmt, 2, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + ok_stmt(hstmt, SQLGetData(hstmt, 6, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); ok_stmt(hstmt, SQLFreeStmt(hstmt,SQL_CLOSE)); @@ -1098,6 +1105,50 @@ } +/** + Bug #19923: MyODBC Foreign key retrieval broken in multiple ways + Two issues to test: schema columns should be NULL, not just an empty string, + and more than one constraint should be returned. +*/ +DECLARE_TEST(t_bug19923) +{ + SQLCHAR buff[255]; + SQLLEN len; + + ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19923c, t_bug19923b, t_bug19923a"); + ok_sql(hstmt, "CREATE TABLE t_bug19923a (a INT PRIMARY KEY) ENGINE=InnoDB"); + ok_sql(hstmt, "CREATE TABLE t_bug19923b (b INT PRIMARY KEY) ENGINE=InnoDB"); + ok_sql(hstmt, "CREATE TABLE t_bug19923c (a INT, b INT, UNIQUE(a), UNIQUE(b), CONSTRAINT `first_constraint` FOREIGN KEY (`b`) REFERENCES `t_bug19923b` (`b`), CONSTRAINT `second_constraint` FOREIGN KEY (`a`) REFERENCES `t_bug19923a` (`a`)) ENGINE=InnoDB"); + + + ok_stmt(hstmt, SQLForeignKeys(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0, + NULL, 0, (SQLCHAR *)"t_bug19923c", SQL_NTS)); + + ok_stmt(hstmt, SQLFetch(hstmt)); + is_str(my_fetch_str(hstmt, buff, 3), "t_bug19923b", 10); + is_str(my_fetch_str(hstmt, buff, 4), "b", 1); + is_str(my_fetch_str(hstmt, buff, 7), "t_bug19923c", 10); + is_str(my_fetch_str(hstmt, buff, 8), "b", 1); + is_str(my_fetch_str(hstmt, buff, 12), "first_constraint", 16); + ok_stmt(hstmt, SQLGetData(hstmt, 2, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + ok_stmt(hstmt, SQLGetData(hstmt, 6, SQL_C_CHAR, buff, sizeof(buff), &len)); + is_num(len, SQL_NULL_DATA); + + ok_stmt(hstmt, SQLFetch(hstmt)); + is_str(my_fetch_str(hstmt, buff, 3), "t_bug19923a", 10); + is_str(my_fetch_str(hstmt, buff, 4), "a", 1); + is_str(my_fetch_str(hstmt, buff, 7), "t_bug19923c", 10); + is_str(my_fetch_str(hstmt, buff, 8), "a", 1); + is_str(my_fetch_str(hstmt, buff, 12), "second_constraint", 17); + + expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); + + ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19923c, t_bug19923b, t_bug19923a"); + return OK; +} + + BEGIN_TESTS ADD_TEST(my_columns_null) ADD_TEST(my_drop_table) @@ -1119,6 +1170,7 @@ ADD_TEST(bug8860) ADD_TEST(t_bug26934) ADD_TEST(t_bug29888) + ADD_TEST(t_bug19923) END_TESTS Index: test/my_keys.c =================================================================== --- test/my_keys.c (revision 614) +++ test/my_keys.c (working copy) @@ -321,7 +321,7 @@ NULL, SQL_NTS, /*FK SCHEMA*/ NULL, SQL_NTS); /*FK TABLE*/ mystmt(hstmt,rc); -/* myassert(9 == myresult(hstmt)); */ + myassert(9 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -343,7 +343,7 @@ NULL, SQL_NTS, "test_fkey_c1", SQL_NTS); mystmt(hstmt,rc); - /* myassert(15 == myresult(hstmt)); */ + myassert(15 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -354,7 +354,7 @@ NULL, SQL_NTS, "test_fkey2", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -365,7 +365,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(11 == myresult(hstmt)); */ + myassert(11 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -387,7 +387,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(2 == myresult(hstmt)); */ + myassert(2 == myresult(hstmt)); printMessage("\n WITH ONLY PK OPTION"); rc = SQLForeignKeys(hstmt, @@ -398,7 +398,7 @@ NULL, SQL_NTS, NULL, SQL_NTS); mystmt(hstmt,rc); - /* myassert(9 == myresult(hstmt)); */ + myassert(9 == myresult(hstmt)); printMessage("\n WITH ONLY FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -409,7 +409,7 @@ NULL, SQL_NTS, "test_fkey3", SQL_NTS); mystmt(hstmt,rc); - /* myassert(4 == myresult(hstmt)); */ + myassert(4 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -420,7 +420,7 @@ NULL, SQL_NTS, "test_fkey3", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -431,7 +431,7 @@ NULL, SQL_NTS, "test_fkey_c1", SQL_NTS); mystmt(hstmt,rc); - /* myassert(11 == myresult(hstmt)); */ + myassert(11 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -442,7 +442,7 @@ NULL, SQL_NTS, "test_fkey2", SQL_NTS); mystmt(hstmt,rc); - /* myassert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); printMessage("\n WITH BOTH PK and FK OPTION"); rc = SQLForeignKeys(hstmt, @@ -487,7 +487,7 @@ NULL, SQL_NTS, "test_fkey2",10); mystmt(hstmt,rc); - /* assert(3 == myresult(hstmt)); */ + myassert(3 == myresult(hstmt)); SQLFreeStmt(hstmt,SQL_CLOSE); printMessage("\n WITH NON-EXISTANT TABLES"); @@ -499,7 +499,7 @@ NULL, SQL_NTS, "test_fkey_junk", SQL_NTS); mystmt(hstmt,rc); - myassert(0 == myresult(hstmt)); + myassert(0 == myresult(hstmt)); SQLFreeStmt(hstmt,SQL_CLOSE); printMessage("\n WITH COMMENT FIELD"); @@ -511,7 +511,7 @@ NULL, SQL_NTS, "test_fkey_comment_f", SQL_NTS); mystmt(hstmt,rc); - /* myassert(1 == myresult(hstmt)); */ + myassert(1 == myresult(hstmt)); { char buff[255]; Index: ChangeLog =================================================================== --- ChangeLog (revision 614) +++ ChangeLog (working copy) @@ -1,12 +1,16 @@ 3.51.18 Functionality added or changed: + * SQLForeignKeys uses INFORMATION_SCHEMA when it is available on the server, + which allows more complete information to be returned. * Added FLAG_MULTI_STATEMENTS to allow issuing queries that contain multiple statements. Also added to the setup GUI. (Bug #7445) * Removed support for the TRACE and TRACEFILE DSN options. The standard ODBC logging should be used. Bugs fixed: + * SQLForeignKeys returned an empty string for the schema columns instead of + a NULL. (Bug #19923) * SQLSpecialColumns() returned all TIMESTAMP fields when queried for SQL_ROWVER, not just an auto-updating TIMESTAMP field. (Bug #9927, still limited by Bug #30081 in the server.)