Index: ChangeLog =================================================================== --- ChangeLog (revision 416) +++ ChangeLog (working copy) @@ -3,6 +3,8 @@ Functionality added or changed: Bugs fixed: + * SQLSetPos could update or delete the wrong rows when the original result + set did not contain all columns of a multi-part primary key. (Bug #28255) ---- Index: driver/cursor.c =================================================================== --- driver/cursor.c (revision 416) +++ driver/cursor.c (working copy) @@ -513,8 +513,7 @@ /* @type : myodbc3 internal @purpose : checks for the existance of pk columns in the resultset, - if it is, copy that data to query, else get the data by - building a temporary resultset + if it is, copy that data to query, else we can't find the right row */ static SQLRETURN insert_pk_fields(STMT FAR *stmt, DYNAMIC_STRING *dynQuery) @@ -526,98 +525,36 @@ MYCURSOR *cursor= &stmt->cursor; SQLUINTEGER pk_count= 0; - /* - Look for primary key columns in the current result set, - if it exists, take that data else query new resultset - */ - for ( ncol= 0; ncol < result->field_count; ncol++ ) + /* Look for primary key columns in the current result set, */ + for (ncol= 0; ncol < result->field_count; ncol++) { - field= result->fields+ncol; - for ( index= 0; index < cursor->pk_count; index++ ) + field= result->fields+ncol; + for (index= 0; index < cursor->pk_count; index++) + { + if (!myodbc_strcasecmp(cursor->pkcol[index].name, field->org_name)) { - if ( !myodbc_strcasecmp(cursor->pkcol[index].name,field->name) ) - { - /* PK data exists...*/ - dynstr_append_quoted_name(dynQuery,field->name); - dynstr_append_mem(dynQuery,"=",1); - if ( insert_field(stmt,result,dynQuery,ncol) ) - return(SQL_ERROR); - cursor->pkcol[index].bind_done= TRUE; - pk_count++; - break; - } + /* PK data exists...*/ + dynstr_append_quoted_name(dynQuery, field->org_name); + dynstr_append_mem(dynQuery, "=", 1); + if (insert_field(stmt, result, dynQuery, ncol)) + return SQL_ERROR; + cursor->pkcol[index].bind_done= TRUE; + pk_count++; + break; } + } } - if ( pk_count != cursor->pk_count ) - { - /* - Primary key column doesn't exists in the opened rs, so - get the data by executing a query - */ - DYNAMIC_STRING query; - MYSQL_RES *presult; - SQLUSMALLINT field_count= 0; - if ( init_dynamic_string(&query, "SELECT ", 1024,1024) ) - return set_error(stmt,MYERR_S1001,NULL,4001); + /* + If we didn't have data for all the components of the primary key, + we can't build a correct WHERE clause. + */ + if (pk_count != cursor->pk_count) + return set_stmt_error(stmt, "HY000", + "Not all components of primary key are available, " + "so row to modify cannot be identified", 0); - for ( index= 0; index < cursor->pk_count; index++ ) - { - if ( !cursor->pkcol[index].bind_done ) - { - dynstr_append_quoted_name(&query,stmt->cursor.pkcol[index].name); - dynstr_append_mem(&query,",",1); - } - } - query.length-= 1; - dynstr_append_mem(&query," FROM ",6); - - if ( !find_used_table(stmt) ) - { - dynstr_free(&query); - return(SQL_ERROR); - } - - dynstr_append_quoted_name(&query,stmt->table_name); - MYLOG_QUERY(stmt, query.str); - pthread_mutex_lock(&stmt->dbc->lock); - if ( mysql_query(&stmt->dbc->mysql,query.str) || - !(presult= 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); - dynstr_free(&query); - return(SQL_ERROR); - } - pthread_mutex_unlock(&stmt->dbc->lock); - - for ( index= 0;index< (uint) stmt->current_row;index++ ) - presult->data_cursor= presult->data_cursor->next; - - for ( index= 0; index < cursor->pk_count; index++ ) - { - if ( !cursor->pkcol[index].bind_done ) - { - dynstr_append_quoted_name(dynQuery,cursor->pkcol[index].name); - dynstr_append_mem(dynQuery,"=",1); - - /* - Might have multiple pk fields in the missing list .. - so avoid the wrong query by having internal field_count.. - */ - if ( insert_field(stmt,presult,dynQuery,field_count++) ) - { - mysql_free_result(presult); - dynstr_free(&query); - return(SQL_ERROR); - } - } - } - mysql_free_result(presult); - dynstr_free(&query); - } - return(SQL_SUCCESS); + return SQL_SUCCESS; } @@ -745,15 +682,17 @@ 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 (check_if_pk_exists(pStmt)) { - if ( insert_pk_fields( pStmt, dynQuery ) != SQL_SUCCESS ) - return set_stmt_error( pStmt, "HY000", "Build WHERE -> insert_pk_fields() failed.", 0 ); + if (insert_pk_fields(pStmt, dynQuery) != SQL_SUCCESS) + return SQL_ERROR; } else { - if ( append_all_fields( pStmt, dynQuery ) != SQL_SUCCESS ) - return set_stmt_error( pStmt, "HY000", "Build WHERE -> insert_fields() failed.", 0 ); + if (append_all_fields(pStmt, dynQuery) != SQL_SUCCESS) + return set_stmt_error(pStmt, "HY000", + "Build WHERE -> insert_fields() failed.", + 0); } /* Remove the trailing ' AND ' */ dynQuery->length -= 5; Index: test/my_cursor.c =================================================================== --- test/my_cursor.c (revision 416) +++ test/my_cursor.c (working copy) @@ -1478,68 +1478,6 @@ } -DECLARE_TEST(tmysql_pos_update_ex2) -{ - SQLHSTMT hstmt1; - SQLROWSETSIZE pcrow; - SQLLEN nlen= SQL_NTS; - SQLUSMALLINT rgfRowStatus; - SQLCHAR cursor[30], sql[255], data[]= "updated"; - - ok_sql(hstmt, "DROP TABLE IF EXISTS t_pos_updex2"); - ok_sql(hstmt, "CREATE TABLE t_pos_updex2 (a INT NOT NULL, b VARCHAR(30)," - "c INT NOT NULL, PRIMARY KEY(a,c))"); - ok_sql(hstmt, "INSERT INTO t_pos_updex2 VALUES (10,'venu',1),(20,'MySQL',2)"); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_sql(hstmt, "SELECT a, b FROM t_pos_updex2"); - - ok_stmt(hstmt, SQLExtendedFetch(hstmt, SQL_FETCH_ABSOLUTE, 2, &pcrow, - &rgfRowStatus)); - ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE)); - - ok_stmt(hstmt, SQLGetCursorName(hstmt, cursor, sizeof(cursor), NULL)); - - ok_con(hdbc, SQLAllocStmt(hdbc, &hstmt1)); - - ok_stmt(hstmt1, SQLBindParameter(hstmt1, 1, SQL_PARAM_INPUT, SQL_C_CHAR, - SQL_CHAR, 0, 0, data, sizeof(data), NULL)); - - sprintf((char *)sql, - "UPDATE t_pos_updex2 SET a = 999, b = ? WHERE CURRENT OF %s", cursor); - - ok_stmt(hstmt1, SQLExecDirect(hstmt1, sql, SQL_NTS)); - - ok_stmt(hstmt1, SQLRowCount(hstmt1, &nlen)); - is_num(nlen, 1); - - ok_stmt(hstmt1, SQLFreeStmt(hstmt1, SQL_DROP)); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_sql(hstmt, "SELECT * FROM t_pos_updex2"); - - ok_stmt(hstmt, SQLFetch(hstmt)); - is_num(my_fetch_int(hstmt, 1), 10); - is_str(my_fetch_str(hstmt, sql, 2), "venu", 4); - is_num(my_fetch_int(hstmt, 3), 1); - - ok_stmt(hstmt, SQLFetch(hstmt)); - is_num(my_fetch_int(hstmt, 1), 999); - is_str(my_fetch_str(hstmt, sql, 2), "updated", 7); - is_num(my_fetch_int(hstmt, 3), 2); - - expect_stmt(hstmt, SQLFetch(hstmt), SQL_NO_DATA_FOUND); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_sql(hstmt, "DROP TABLE IF EXISTS t_pos_updex2"); - - return OK; -} - - DECLARE_TEST(tmysql_pos_update_ex3) { SQLHSTMT hstmt1; @@ -1951,86 +1889,6 @@ } -DECLARE_TEST(tmysql_setpos_pkdel1) -{ - SQLRETURN rc; - SQLINTEGER nData= 500; - SQLLEN nlen; - SQLCHAR szData[255]={0}; - SQLUINTEGER pcrow; - SQLUSMALLINT rgfRowStatus; - - tmysql_exec(hstmt,"drop table tmysql_setpos1"); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = tmysql_exec(hstmt,"create table tmysql_setpos1(col1 int primary key, col3 int,col2 varchar(30))"); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(100,10,'MySQL1')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(200,20,'MySQL2')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(300,20,'MySQL3')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(400,20,'MySQL4')"); - mystmt(hstmt,rc); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLSetCursorName(hstmt,"venu",SQL_NTS); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"select col2,col3 from tmysql_setpos1"); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,1,SQL_C_LONG,&nData,100,NULL); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,2,SQL_C_CHAR,szData,100,&nlen); - mystmt(hstmt,rc); - - rc = SQLExtendedFetch(hstmt,SQL_FETCH_ABSOLUTE,4,&pcrow,&rgfRowStatus); - mystmt(hstmt,rc); - - printMessage(" pcrow:%d\n",pcrow); - - printMessage(" row1:%d,%s\n",nData,szData); - - rc = SQLSetPos(hstmt,1,SQL_POSITION,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLSetPos(hstmt,1,SQL_DELETE,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLRowCount(hstmt,&nlen); - mystmt(hstmt,rc); - - printMessage(" rows affected:%d\n",nlen); - - rc = SQLFreeStmt(hstmt,SQL_UNBIND); - mystmt(hstmt,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"select * from tmysql_setpos1"); - mystmt(hstmt,rc); - - my_assert( 3 == myresult(hstmt)); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - return OK; -} - - DECLARE_TEST(tmysql_setpos_pkdel2) { SQLRETURN rc; @@ -2107,80 +1965,6 @@ } -DECLARE_TEST(tmysql_setpos_pkdel3) -{ - SQLRETURN rc; - SQLINTEGER nData= 500; - SQLLEN nlen; - SQLCHAR szData[255]={0}; - SQLUINTEGER pcrow; - SQLUSMALLINT rgfRowStatus; - - tmysql_exec(hstmt,"drop table tmysql_setpos1"); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = tmysql_exec(hstmt,"create table tmysql_setpos1(col1 int, col3 int,col2 varchar(30) primary key)"); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(100,10,'MySQL1')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(200,20,'MySQL2')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(300,20,'MySQL3')"); - mystmt(hstmt,rc); - rc = tmysql_exec(hstmt,"insert into tmysql_setpos1 values(400,20,'MySQL4')"); - mystmt(hstmt,rc); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLSetCursorName(hstmt,"venu",SQL_NTS); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"select col1 from tmysql_setpos1"); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,1,SQL_C_LONG,&nData,100,NULL); - mystmt(hstmt,rc); - - rc = SQLExtendedFetch(hstmt,SQL_FETCH_ABSOLUTE,4,&pcrow,&rgfRowStatus); - mystmt(hstmt,rc); - - printMessage(" pcrow:%d\n",pcrow); - - printMessage(" row1:%d,%s\n",nData,szData); - - rc = SQLSetPos(hstmt,1,SQL_DELETE,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLRowCount(hstmt,&nlen); - mystmt(hstmt,rc); - - printMessage(" rows affected:%d\n",nlen); - - rc = SQLFreeStmt(hstmt,SQL_UNBIND); - mystmt(hstmt,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = tmysql_exec(hstmt,"select * from tmysql_setpos1"); - mystmt(hstmt,rc); - - my_assert( 3 == myresult(hstmt)); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - return OK; -} - - DECLARE_TEST(t_setpos_upd_bug1) { SQLRETURN rc; @@ -2606,13 +2390,17 @@ } +/** + Bug #28255: Cursor operations on result sets containing only part of a key + are incorrect +*/ DECLARE_TEST(t_bug28255) { SQLLEN nlen; ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug28255"); ok_sql(hstmt, "CREATE TABLE t_bug28255 (a INT, b INT, PRIMARY KEY (a,b))"); - ok_sql(hstmt, "INSERT INTO t_bug28255 VALUES (1,3),(1,4)"); + ok_sql(hstmt, "INSERT INTO t_bug28255 VALUES (1,3),(1,4),(1,5)"); ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); @@ -2624,11 +2412,9 @@ is_num(my_fetch_int(hstmt, 1), 1); ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE)); - ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE)); + expect_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE), + SQL_ERROR); - ok_stmt(hstmt, SQLRowCount(hstmt, &nlen)); - is_num(nlen, 1); - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND)); ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); @@ -2638,6 +2424,14 @@ is_num(my_fetch_int(hstmt, 1), 1); is_num(my_fetch_int(hstmt, 2), 3); + ok_stmt(hstmt, SQLFetch(hstmt)); + is_num(my_fetch_int(hstmt, 1), 1); + is_num(my_fetch_int(hstmt, 2), 4); + + ok_stmt(hstmt, SQLFetch(hstmt)); + is_num(my_fetch_int(hstmt, 1), 1); + is_num(my_fetch_int(hstmt, 2), 5); + ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug28255"); @@ -2667,15 +2461,12 @@ ADD_TEST(t_pos_update) ADD_TEST(tmysql_pos_update_ex) ADD_TEST(tmysql_pos_update_ex1) - ADD_TEST(tmysql_pos_update_ex2) ADD_TEST(tmysql_pos_update_ex3) ADD_TEST(tmysql_pos_update_ex4) ADD_TEST(tmysql_pos_dyncursor) ADD_TEST(tmysql_mtab_setpos_del) ADD_TEST(tmysql_setpos_pkdel) - ADD_TEST(tmysql_setpos_pkdel1) ADD_TEST(tmysql_setpos_pkdel2) - ADD_TEST(tmysql_setpos_pkdel3) ADD_TEST(t_alias_setpos_pkdel) ADD_TEST(t_alias_setpos_del) ADD_TEST(t_setpos_upd_bug1) @@ -2685,7 +2476,7 @@ ADD_TEST(tmy_cursor2) ADD_TEST(tmy_cursor3) ADD_TEST(tmysql_pcbvalue) - ADD_TODO(t_bug28255) + ADD_TEST(t_bug28255) END_TESTS Index: test/my_bulk.c =================================================================== --- test/my_bulk.c (revision 416) +++ test/my_bulk.c (working copy) @@ -130,52 +130,6 @@ /** - @todo This test demonstrates an egregious bug. There is logic in - insert_pk_fields() that is just wrong. -*/ -DECLARE_TEST(t_mul_pkdel1) -{ - SQLINTEGER nData; - SQLLEN nlen; - SQLROWSETSIZE pcrow; - - ok_sql(hstmt, "DROP TABLE IF EXISTS t_mul_pkdel1"); - ok_sql(hstmt, "CREATE TABLE t_mul_pkdel1 (a INT NOT NULL, b INT," - "c VARCHAR(30) NOT NULL, PRIMARY KEY(a, c))"); - ok_sql(hstmt, "INSERT INTO t_mul_pkdel1 VALUES (100,10,'MySQL1')," - "(200,20,'MySQL2'),(300,20,'MySQL3'),(400,20,'MySQL4')"); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_stmt(hstmt, SQLSetCursorName(hstmt, (SQLCHAR *)"venu", SQL_NTS)); - - ok_sql(hstmt, "SELECT a FROM t_mul_pkdel1"); - - ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_LONG, &nData, 0, NULL)); - - ok_stmt(hstmt, SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &pcrow, NULL)); - ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE)); - - ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE)); - ok_stmt(hstmt, SQLRowCount(hstmt, &nlen)); - is_num(nlen, 1); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND)); - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_sql(hstmt, "SELECT * FROM t_mul_pkdel1"); - - is_num(myrowcount(hstmt), 3); - - ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE)); - - ok_sql(hstmt, "DROP TABLE IF EXISTS t_mul_pkdel1"); - - return OK; -} - - -/** Bug #24306: SQLBulkOperations always uses indicator varables' values from the first record */ @@ -337,7 +291,6 @@ BEGIN_TESTS ADD_TEST(t_bulk_insert) ADD_TEST(t_mul_pkdel) - ADD_TEST(t_mul_pkdel1) ADD_TEST(t_bulk_insert_indicator) ADD_TEST(t_bulk_insert_rows) END_TESTS Index: test/my_keys.c =================================================================== --- test/my_keys.c (revision 416) +++ test/my_keys.c (working copy) @@ -22,309 +22,7 @@ #include "odbctap.h" -/* UPDATE with primary keys ... */ -DECLARE_TEST(my_primary_keys) -{ - SQLRETURN rc; - SQLROWCOUNT rowcount; - SQLINTEGER nData; - SQLExecDirect(hstmt,"drop table my_primary_keys",SQL_NTS); - rc = SQLExecDirect(hstmt,"create table my_primary_keys(col1 int not null,\ - col2 varchar(30) unique,\ - col3 int unique not null,\ - col4 int not null,\ - primary key(col1))",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"insert into my_primary_keys values(100,'MySQL1',1,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_primary_keys values(200,'MySQL2',2,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_primary_keys values(300,'MySQL3',3,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_primary_keys values(400,'MySQL4',4,3000)",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY ,(SQLPOINTER)SQL_CONCUR_ROWVER , 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE ,(SQLPOINTER)1 , 0); - mystmt(hstmt, rc); - - rc = SQLExecDirect(hstmt,"select col4 from my_primary_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,1,SQL_C_LONG,&nData,100,NULL); - mystmt(hstmt,rc); - - rc = SQLExtendedFetch(hstmt,SQL_FETCH_ABSOLUTE,2,NULL,NULL); - mystmt(hstmt,rc); - - nData = 999; - - rc = SQLSetPos(hstmt,0,SQL_UPDATE,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLRowCount(hstmt,&rowcount); - mystmt(hstmt,rc); - - printMessage(" rows affected:%d\n",rowcount); - /* myassert(rowcount == 1); */ - - rc = SQLFreeStmt(hstmt,SQL_UNBIND); - mystmt(hstmt,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_primary_keys",SQL_NTS); - mystmt(hstmt,rc); - - myassert(4 == myresult(hstmt)); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_primary_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(999 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc); - - SQLFreeStmt(hstmt,SQL_UNBIND); - SQLFreeStmt(hstmt,SQL_CLOSE); - - return OK; -} - -/* UPDATE with unique and notnull keys ... */ -DECLARE_TEST(my_unique_notnull_keys) -{ - SQLRETURN rc; - SQLROWCOUNT rowcount; - SQLINTEGER nData; - - SQLExecDirect(hstmt,"drop table my_unique_notnull_keys",SQL_NTS); - rc = SQLExecDirect(hstmt,"create table my_unique_notnull_keys(col1 int not null,\ - col2 varchar(30) unique,\ - col3 int unique not null,\ - col4 int not null)",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"insert into my_unique_notnull_keys values(100,'MySQL1',1,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_notnull_keys values(200,'MySQL2',2,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_notnull_keys values(300,'MySQL3',3,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_notnull_keys values(400,'MySQL4',4,3000)",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY ,(SQLPOINTER)SQL_CONCUR_ROWVER , 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE ,(SQLPOINTER)1 , 0); - mystmt(hstmt, rc); - - rc = SQLExecDirect(hstmt,"select col4 from my_unique_notnull_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,1,SQL_C_LONG,&nData,100,NULL); - mystmt(hstmt,rc); - - rc = SQLExtendedFetch(hstmt,SQL_FETCH_ABSOLUTE,2,NULL,NULL); - mystmt(hstmt,rc); - - nData = 999; - - rc = SQLSetPos(hstmt,0,SQL_UPDATE,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLRowCount(hstmt,&rowcount); - mystmt(hstmt,rc); - - printMessage(" rows affected:%d\n",rowcount); - myassert(rowcount == 1); - - rc = SQLFreeStmt(hstmt,SQL_UNBIND); - mystmt(hstmt,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_unique_notnull_keys",SQL_NTS); - mystmt(hstmt,rc); - - myassert(4 == myresult(hstmt)); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_unique_notnull_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(999 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc); - - SQLFreeStmt(hstmt,SQL_UNBIND); - SQLFreeStmt(hstmt,SQL_CLOSE); - - return OK; -} - - -/* UPDATE with unique keys ... */ -DECLARE_TEST(my_unique_keys) -{ - SQLRETURN rc; - SQLROWCOUNT rowcount; - SQLINTEGER nData; - - SQLExecDirect(hstmt,"drop table my_unique_keys",SQL_NTS); - rc = SQLExecDirect(hstmt,"create table my_unique_keys(col1 int not null,\ - col2 varchar(30) unique,\ - col3 int unique,\ - col4 int not null)",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"insert into my_unique_keys values(100,'MySQL1',1,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_keys values(200,'MySQL2',2,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_keys values(300,'MySQL3',3,3000)",SQL_NTS); - mystmt(hstmt,rc); - rc = SQLExecDirect(hstmt,"insert into my_unique_keys values(400,'MySQL4',4,3000)",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLTransact(NULL,hdbc,SQL_COMMIT); - mycon(hdbc,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY ,(SQLPOINTER)SQL_CONCUR_ROWVER , 0); - mystmt(hstmt, rc); - - rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE ,(SQLPOINTER)1 , 0); - mystmt(hstmt, rc); - - rc = SQLExecDirect(hstmt,"select col4 from my_unique_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLBindCol(hstmt,1,SQL_C_LONG,&nData,100,NULL); - mystmt(hstmt,rc); - - rc = SQLExtendedFetch(hstmt,SQL_FETCH_ABSOLUTE,2,NULL,NULL); - mystmt(hstmt,rc); - - nData = 999; - - rc = SQLSetPos(hstmt,0,SQL_UPDATE,SQL_LOCK_NO_CHANGE); - mystmt(hstmt,rc); - - rc = SQLRowCount(hstmt,&rowcount); - mystmt(hstmt,rc); - - printMessage(" rows affected:%d\n",rowcount); - myassert(rowcount == 1); - - rc = SQLFreeStmt(hstmt,SQL_UNBIND); - mystmt(hstmt,rc); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_unique_keys",SQL_NTS); - mystmt(hstmt,rc); - - myassert(4 == myresult(hstmt)); - - rc = SQLFreeStmt(hstmt,SQL_CLOSE); - mystmt(hstmt,rc); - - rc = SQLExecDirect(hstmt,"select * from my_unique_keys",SQL_NTS); - mystmt(hstmt,rc); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(999 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt(hstmt,rc); - myassert(3000 == my_fetch_int(hstmt,4)); - - rc = SQLFetch(hstmt); - mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc); - - SQLFreeStmt(hstmt,SQL_UNBIND); - SQLFreeStmt(hstmt,SQL_CLOSE); - - return OK; -} - - /* UPDATE with no keys ... */ DECLARE_TEST(my_no_keys) { @@ -992,9 +690,6 @@ BEGIN_TESTS - ADD_TEST(my_primary_keys) - ADD_TEST(my_unique_notnull_keys) - ADD_TEST(my_unique_keys) ADD_TEST(my_no_keys) ADD_TEST(my_foreign_keys) ADD_TEST(my_tables)