int main(int argc, char *argv[]) { SQLSMALLINT num_params, cols; SQLLEN count; SQLINTEGER id; odbc_use_version3 = 1; odbc_connect(); odbc_command("create table #tester (id int not null, name varchar(20) not null)"); odbc_command("insert into #tester(id, name) values(1, 'abc')"); odbc_command("insert into #tester(id, name) values(2, 'duck')"); CHKPrepare(T("SELECT * FROM #tester WHERE id = ?"), SQL_NTS, "S"); CHKR(SQLNumParams, (odbc_stmt, &num_params), "S"); assert(num_params == 1); id = 1; CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &id, sizeof(id), NULL, "S"); CHKExecute("S"); CHKR(SQLFreeStmt, (odbc_stmt, SQL_RESET_PARAMS), "S"); CHKRowCount(&count, "S"); CHKNumResultCols(&cols, "S"); assert(cols == 2); odbc_disconnect(); return 0; }
static void Test(const char *query) { CHKPrepare(T(query), SQL_NTS, "S"); CHKExecute("S"); CHKFetch("SI"); CHKFetch("No"); /* * Microsoft SQL Server 2000 provides a diagnostic record * associated with the second SQLFetch (which returns * SQL_NO_DATA) saying "Warning: Null value is eliminated by * an aggregate or other SET operation." * We check for "NO DM" cause unixODBC till 2.2.11 do not read * errors on SQL_NO_DATA */ if (odbc_db_is_microsoft() && tds_no_dm) { SQLTCHAR output[256]; CHKGetDiagRec(SQL_HANDLE_STMT, odbc_stmt, 1, NULL, NULL, output, ODBC_VECTOR_SIZE(output), NULL, "SI"); printf("Message: %s\n", C(output)); } odbc_reset_statement(); }
static void insert_test_man(void) { SQLLEN sql_nts = SQL_NTS; SQLINTEGER commit_off = SQL_AUTOCOMMIT_OFF; SQLINTEGER commit_on = SQL_AUTOCOMMIT_ON; SQLINTEGER id = 0; char string[64]; CHKSetConnectAttr(SQL_ATTR_AUTOCOMMIT, int2ptr(commit_off), SQL_IS_INTEGER, "SI"); odbc_reset_statement(); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts, "SI"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(string), 0, string, 0, &sql_nts, "SI"); CHKPrepare(T("insert into test values (?, ?)"), SQL_NTS, "SI"); for (id = 0; id < 20; id++) { sprintf(string, "This is a test (%d)", (int) id); CHKExecute("SI"); } SQLEndTran(SQL_HANDLE_DBC, odbc_conn, SQL_COMMIT); SQLSetConnectAttr(odbc_conn, SQL_ATTR_AUTOCOMMIT, int2ptr(commit_on), SQL_IS_INTEGER); odbc_reset_statement(); }
static int test_insert(void *buf, SQLLEN buflen) { SQLHSTMT odbc_stmt = SQL_NULL_HSTMT; SQLLEN strlen_or_ind; const char *qstr = "insert into " TEST_TABLE_NAME " values (?)"; assert(odbc_conn); assert(odbc_env); /* allocate new statement handle */ CHKAllocHandle(SQL_HANDLE_STMT, odbc_conn, &odbc_stmt, "SI"); /* execute query */ CHKPrepare(T(qstr), SQL_NTS, "SI"); strlen_or_ind = buflen; CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, (SQLUINTEGER) (-1), 0, buf, buflen, &strlen_or_ind, "SI"); CHKExecute("SI"); /* this command shouldn't fail */ odbc_command("DECLARE @i INT"); SQLFreeHandle(SQL_HANDLE_STMT, odbc_stmt); return 0; }
static int Test(int bind_before) { SQLSMALLINT ReturnCode = 0; SQLSMALLINT InParam = 5; SQLSMALLINT OutParam = 1; char OutString[OUTSTRING_LEN]; SQLLEN cbReturnCode = 0, cbInParam = 0, cbOutParam = 0; SQLLEN cbOutString = SQL_NTS; odbc_connect(); /* drop proc */ odbc_command("IF OBJECT_ID('spTestProc') IS NOT NULL DROP PROC spTestProc"); /* create proc */ odbc_command(sp_define); if (!bind_before) CHKPrepare(T(SP_TEXT), strlen(SP_TEXT), "S"); CHKBindParameter(1, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &ReturnCode, 0, &cbReturnCode, "S"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &InParam, 0, &cbInParam, "S"); CHKBindParameter(3, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &OutParam, 0, &cbOutParam, "S"); OutString[0] = '\0'; strcpy(OutString, "Test"); /* Comment this line and we get an error! Why? */ CHKBindParameter(4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, OUTSTRING_LEN, 0, OutString, OUTSTRING_LEN, &cbOutString, "S"); if (bind_before) CHKPrepare(T(SP_TEXT), strlen(SP_TEXT), "S"); CHKExecute("S"); odbc_command("DROP PROC spTestProc"); printf("Output:\n"); printf(" Return Code = %d\n", (int) ReturnCode); printf(" InParam = %d\n", (int) InParam); printf(" OutParam = %d\n", (int) OutParam); printf(" OutString = %s\n", OutString); if (InParam != OutParam) { fprintf(stderr, "InParam != OutParam\n"); return 1; } if (strcmp(OutString, "This is cool!") != 0) { fprintf(stderr, "Bad string returned\n"); return 1; } odbc_disconnect(); ODBC_FREE(); return 0; }
static void DoTest(int prepared) { odbc_command("create table #odbctestdata (i int)"); /* test that 2 empty result set are returned correctly */ if (!prepared) { odbc_command("select * from #odbctestdata select * from #odbctestdata"); } else { CHKPrepare(T("select * from #odbctestdata select * from #odbctestdata"), SQL_NTS, "S"); CHKExecute("S"); } CHKFetch("No"); CHKMoreResults("S"); printf("Getting next recordset\n"); CHKFetch("No"); CHKMoreResults("No"); /* test that skipping a no empty result go to other result set */ odbc_command("insert into #odbctestdata values(123)"); if (!prepared) { odbc_command("select * from #odbctestdata select * from #odbctestdata"); } else { CHKPrepare(T("select * from #odbctestdata select * from #odbctestdata"), SQL_NTS, "S"); CHKExecute("S"); } CHKMoreResults("S"); printf("Getting next recordset\n"); CHKFetch("S"); CHKFetch("No"); CHKMoreResults("No"); odbc_command("drop table #odbctestdata"); ODBC_FREE(); }
int main(int argc, char **argv) { #define ARRAY_SIZE 10 SQLCHAR v_dec[ARRAY_SIZE][21]; SQLLEN v_ind[ARRAY_SIZE]; SQLULEN nrows; odbc_use_version3 = 1; odbc_connect(); odbc_check_cursor(); odbc_command("IF OBJECT_ID('mytab1') IS NOT NULL DROP TABLE mytab1"); odbc_command("CREATE TABLE mytab1 ( k INT, d DECIMAL(10,2))"); odbc_command("INSERT INTO mytab1 VALUES ( 201, 111.11 )"); /*SQLExecDirect(m_hstmt, (SQLCHAR *) "insert into mytab1 values ( 202, 222.22 )", SQL_NTS); */ odbc_command("INSERT INTO mytab1 VALUES ( 202, null )"); odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER) SQL_BIND_BY_COLUMN, SQL_IS_UINTEGER, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ARRAY_SIZE, SQL_IS_UINTEGER, "S"); CHKSetStmtAttr(SQL_ATTR_ROWS_FETCHED_PTR, (SQLPOINTER) & (nrows), SQL_IS_UINTEGER, "S"); CHKPrepare(T("SELECT SUM(d) FROM mytab1"), SQL_NTS, "S"); CHKExecute("I"); #if 0 CHKMoreResults("S"); /* skip warning*/ #endif CHKBindCol(1, SQL_C_CHAR, v_dec, 21, v_ind, "S"); CHKFetch("S"); printf("fetch 1: rows fetched = %d\n", (int) nrows); printf("fetch 1: value = [%s]\n", v_dec[0]); CHKFetch("No"); CHKMoreResults("No"); odbc_command("drop table mytab1"); odbc_disconnect(); return 0; }
int main(int argc, char **argv) { char buff[64]; SQLLEN ind; odbc_use_version3 = 1; odbc_connect(); odbc_check_cursor(); exec_direct("CREATE TABLE #t1 ( k INT, c VARCHAR(20))"); exec_direct("INSERT INTO #t1 VALUES (1, 'aaa')"); odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER, "S"); CHKSetCursorName(T("c112"), SQL_NTS, "S"); CHKSetConnectAttr(SQL_ATTR_AUTOCOMMIT, int2ptr(SQL_AUTOCOMMIT_OFF), 0, "S"); CHKPrepare(T("SELECT * FROM #t1 FOR UPDATE"), SQL_NTS, "S"); CHKExecute("S"); CHKFetch("S"); exec_direct("UPDATE #t1 SET c = 'xxx' WHERE CURRENT OF c112"); CHKCloseCursor("SI"); CHKEndTran(SQL_HANDLE_DBC, odbc_conn, SQL_COMMIT, "S"); CHKSetConnectAttr(SQL_ATTR_AUTOCOMMIT, int2ptr(SQL_AUTOCOMMIT_ON), 0, "S"); CHKExecDirect(T("SELECT c FROM #t1 WHERE k = 1"), SQL_NTS, "S"); CHKFetch("S"); CHKGetData(1, SQL_C_CHAR, buff, sizeof(buff), &ind, "S"); printf(">> New value after update = [%s] (should be [xxx]) \n", buff); CHKFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) odbc_stmt, "S"); odbc_stmt = SQL_NULL_HSTMT; odbc_disconnect(); return 0; }
/* stripped down version of TestInput for NULLs */ static void NullInput(SQLSMALLINT out_c_type, SQLSMALLINT out_sql_type, const char *param_type) { char sbuf[1024]; SQLLEN out_len = SQL_NULL_DATA; odbc_reset_statement(); /* create a table with a column of that type */ odbc_reset_statement(); sprintf(sbuf, "CREATE TABLE #tmp_insert (col %s NULL)", param_type); odbc_command(sbuf); if (use_cursors) { odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_SCROLLABLE, 0, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_DYNAMIC, 0, "S"); } /* insert data using prepared statements */ sprintf(sbuf, "INSERT INTO #tmp_insert VALUES(?)"); if (exec_direct) { CHKBindParameter(1, SQL_PARAM_INPUT, out_c_type, out_sql_type, 20, 0, NULL, 1, &out_len, "S"); CHKExecDirect(T(sbuf), SQL_NTS, "SNo"); } else { if (prepare_before) CHKPrepare(T(sbuf), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, out_c_type, out_sql_type, 20, 0, NULL, 1, &out_len, "S"); if (!prepare_before) CHKPrepare(T(sbuf), SQL_NTS, "S"); CHKExecute("SNo"); } /* check if row is present */ odbc_reset_statement(); if (!odbc_db_is_microsoft() && strcmp(param_type, "TEXT") == 0) odbc_command("SELECT * FROM #tmp_insert WHERE col LIKE ''"); else odbc_command("SELECT * FROM #tmp_insert WHERE col IS NULL"); CHKFetch("S"); CHKFetch("No"); CHKMoreResults("No"); odbc_command("DROP TABLE #tmp_insert"); ODBC_FREE(); }
static void TestBinding(int minimun) { const char * const*p; SQLINTEGER n; SQLLEN n_len; sprintf(tmp, "DELETE FROM %s", table_name); odbc_command(tmp); /* insert with SQLPrepare/SQLBindParameter/SQLExecute */ sprintf(tmp, "INSERT INTO %s VALUES(?,?,?)", table_name); CHKPrepare(T(tmp), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &n, 0, &n_len, "S"); n_len = sizeof(n); for (n = 1, p = strings; p[0] && p[1]; p += 2, ++n) { SQLLEN s1_len, s2_len; unsigned int len; len = minimun ? (strlen(strings_hex[p-strings]) - 2) /4 : 40; CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WCHAR, len, 0, (void *) p[0], 0, &s1_len, "S"); len = minimun ? (strlen(strings_hex[p+1-strings]) - 2) /4 : 40; /* FIXME this with SQL_VARCHAR produce wrong protocol data */ CHKBindParameter(3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WVARCHAR, len, 0, (void *) p[1], 0, &s2_len, "S"); s1_len = strlen(p[0]); s2_len = strlen(p[1]); printf("insert #%d\n", (int) n); CHKExecute("S"); } /* check rows */ for (n = 1, p = strings_hex; p[0] && p[1]; p += 2, ++n) { sprintf(tmp, "IF NOT EXISTS(SELECT * FROM %s WHERE k = %d AND c = %s AND vc = %s) SELECT 1", table_name, (int) n, p[0], p[1]); CheckNoRow(tmp); } odbc_reset_statement(); }
static void insert_test_auto(void) { SQLLEN sql_nts = SQL_NTS; SQLINTEGER id = 0; char string[64]; odbc_reset_statement(); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts, "SI"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(string), 0, string, 0, &sql_nts, "SI"); CHKPrepare(T("insert into test values (?, ?)"), SQL_NTS, "SI"); for (id = 0; id < 20; id++) { sprintf(string, "This is a test (%d)", (int) id); CHKExecute("SI"); } odbc_reset_statement(); }
static void TestInsert(char *buf) { SQLLEN ind; int l = strlen(buf); char sql[200]; /* insert some data and test success */ CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, l, 0, buf, l, &ind, "S"); ind = l; CHKExecute("S"); SWAP_STMT(stmt); sprintf(sql, "SELECT 1 FROM #tmp1 WHERE c = '%s'", buf); odbc_command(sql); CHKFetch("S"); CHKFetch("No"); CHKMoreResults("No"); SWAP_STMT(stmt); }
int main(int argc, char *argv[]) { int i; odbc_connect(); odbc_command("create table #timeout(i int)"); odbc_command("insert into #timeout values(1)"); for (i = 0; i < 2; ++i) { printf("Loop %d\n", i); CHKSetStmtAttr(SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER) 10, SQL_IS_UINTEGER, "S"); CHKPrepare(T("select * from #timeout"), SQL_NTS, "S"); CHKExecute("S"); do { while (CHKFetch("SNo") == SQL_SUCCESS) ; } while (CHKMoreResults("SNo") == SQL_SUCCESS); if (i == 0) { printf("Sleep 15 seconds to test if timeout occurs\n"); tds_sleep_s(15); } SQLFreeStmt(odbc_stmt, SQL_CLOSE); SQLFreeStmt(odbc_stmt, SQL_UNBIND); SQLFreeStmt(odbc_stmt, SQL_RESET_PARAMS); SQLCloseCursor(odbc_stmt); } odbc_disconnect(); ODBC_FREE(); return 0; }
static void test_with_conversions(void) { SQLLEN ind, ind2, ind3; char out2[30]; /* * test from Bower, Wayne * Cfr ML 2012-03-02 "[freetds] [unixODBC][Driver Manager]Function sequence error (SQL-HY010)" */ odbc_use_version3 = 1; odbc_connect(); odbc_command("IF OBJECT_ID('TMP_SP_Test_ODBC') IS NOT NULL DROP PROC TMP_SP_Test_ODBC"); odbc_command("create proc TMP_SP_Test_ODBC @i int,\n@o int output\nas\nset nocount on\nselect @o = 55\nreturn 9\n"); odbc_reset_statement(); CHKPrepare(T("{ ? = call TMP_SP_Test_ODBC (?, ?) }"), SQL_NTS, "S"); ind2 = 2; CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 80, 0, "10", 2, &ind2, "S"); ind3 = SQL_NULL_DATA; strcpy(out2, " "); CHKBindParameter(3, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 1, out2, 29, &ind3, "S"); ind = 1; CHKBindParameter(1, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 1, out2, 29, &ind, "S"); /* mssql returns SUCCESS */ CHKExecute("No"); ODBC_CHECK_COLS(0); odbc_reset_statement(); odbc_command("drop proc TMP_SP_Test_ODBC"); odbc_disconnect(); }
static void Test(const char *bind1, SQLSMALLINT type1, const char *bind2, SQLSMALLINT type2) { char sql[512]; char *val = "test"; SQLLEN ind = 4; int id = 1; ODBC_BUF *odbc_buf = NULL; SQLFreeStmt(odbc_stmt, SQL_RESET_PARAMS); ++test_num; sprintf(sql, "insert into #test_output values (%s, %s)", bind1, bind2); CHKPrepare(T(sql), strlen(sql), "S"); if (bind1[0] == '?') CHKBindParameter(id++, SQL_PARAM_INPUT, SQL_C_LONG, type1, 3, 0, &test_num, 0, &ind, "S"); if (bind2[0] == '?') CHKBindParameter(id++, SQL_PARAM_INPUT, SQL_C_CHAR, type2, strlen(val) + 1, 0, (SQLCHAR *) val, 0, &ind, "S"); CHKExecute("S"); ODBC_FREE(); }
int main(int argc, char *argv[]) { SQLLEN ind; int len = strlen(test_text), n, i; const char *p; char *pp; SQLPOINTER ptr; unsigned char buf[256], *pb; SQLRETURN RetCode; int type, lc, sql_type; odbc_connect(); /* create table to hold data */ odbc_command("CREATE TABLE #putdata (c TEXT NULL, b IMAGE NULL)"); sql_type = SQL_LONGVARCHAR; type = SQL_C_CHAR; lc = 1; for (;;) { CHKBindParameter(1, SQL_PARAM_INPUT, type, sql_type, 0, 0, (SQLPOINTER) 123, 0, &ind, "S"); /* length required */ ind = SQL_LEN_DATA_AT_EXEC(len * lc); /* * test for char */ CHKPrepare(T("INSERT INTO #putdata(c) VALUES(?)"), SQL_NTS, "S"); CHKExecute("Ne"); p = test_text; n = 5; CHKParamData(&ptr, "Ne"); if (ptr != (SQLPOINTER) 123) ODBC_REPORT_ERROR("Wrong pointer from SQLParamData"); while (*p) { int l = strlen(p); if (l < n) n = l; if (type == SQL_C_CHAR) { CHKPutData((char *) p, n, "S"); } else { SQLWCHAR buf[256]; CHKPutData((char *) buf, to_sqlwchar(buf, p, n), "S"); } p += n; n *= 2; } CHKParamData(&ptr, "S"); CHKParamData(&ptr, "E"); /* check state and reset some possible buffers */ odbc_command("DECLARE @i INT"); /* use server ntext if available */ if (sql_type == SQL_LONGVARCHAR && odbc_db_is_microsoft() && odbc_db_version_int() >= 0x08000000u) { sql_type = SQL_WLONGVARCHAR; continue; } if (type != SQL_C_CHAR) break; sql_type = SQL_LONGVARCHAR; type = SQL_C_WCHAR; lc = sizeof(SQLWCHAR); } /* update row setting binary field */ for (i = 0; i < 255; ++i) buf[i] = BYTE_AT(i); /* * test for binary */ CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) 4567, 0, &ind, "S"); ind = SQL_LEN_DATA_AT_EXEC(254); CHKPrepare(T("UPDATE #putdata SET b = ?"), SQL_NTS, "S"); CHKExecute("Ne"); pb = buf; n = 7; CHKParamData(&ptr, "Ne"); if (ptr != (SQLPOINTER) 4567) ODBC_REPORT_ERROR("Wrong pointer from SQLParamData"); while (pb != (buf + 254)) { int l = buf + 254 - pb; if (l < n) n = l; CHKPutData((char *) pb, n, "S"); pb += n; n *= 2; } CHKParamData(&ptr, "S"); CHKParamData(&ptr, "E"); /* check state and reset some possible buffers */ odbc_command("DECLARE @i2 INT"); CHKFreeStmt(SQL_RESET_PARAMS, "S"); /* check inserts ... there should be all equal rows */ strcpy(sql, "IF EXISTS(SELECT * FROM #putdata WHERE CONVERT(VARBINARY(255),b) <> 0x"); /* append binary */ for (i = 0; i < 254; ++i) sprintf(strchr(sql, 0), "%02x", buf[i]); strcat(sql, " OR CONVERT(VARCHAR(255),c) <> '"); /* append string */ pp = strchr(sql, 0); p = test_text; do { *pp++ = *p; if (*p == '\'') *pp++ = *p; } while(*p++); strcat(sql, "') SELECT 1"); odbc_check_no_row(sql); odbc_command("DELETE FROM #putdata"); /* test len == 0 case from ML */ type = SQL_C_CHAR; for (;;) { CHKPrepare(T("INSERT INTO #putdata(c) VALUES(?)"), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, type, SQL_LONGVARCHAR, 0, 0, (PTR) 2, 0, &ind, "S"); ind = SQL_LEN_DATA_AT_EXEC(0); RetCode = CHKExecute("Ne"); while (RetCode == SQL_NEED_DATA) { RetCode = SQLParamData(odbc_stmt, &ptr); if (RetCode == SQL_NEED_DATA) { if (type == SQL_C_CHAR) { CHKPutData("abc", 3, "S"); } else { SQLWCHAR buf[10]; CHKPutData(buf, to_sqlwchar(buf, "abc", 3), "S"); } } } if (type != SQL_C_CHAR) break; type = SQL_C_WCHAR; odbc_reset_statement(); } /* check inserts ... */ odbc_check_no_row("IF EXISTS(SELECT * FROM #putdata WHERE c NOT LIKE 'abc') SELECT 1"); odbc_command("DELETE FROM #putdata"); /* test putting 0 bytes from Sebastien Flaesch */ if (odbc_db_is_microsoft()) { type = SQL_C_CHAR; for (;;) { CHKPrepare(T("INSERT INTO #putdata(c) VALUES(?)"), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, type, SQL_LONGVARCHAR, 10, 0, (PTR) 2, 10, &ind, "S"); ind = SQL_DATA_AT_EXEC; RetCode = CHKExecute("Ne"); while (RetCode == SQL_NEED_DATA) { RetCode = SQLParamData(odbc_stmt, &ptr); if (RetCode == SQL_NEED_DATA) CHKPutData("", 0, "S"); } if (type != SQL_C_CHAR) break; type = SQL_C_WCHAR; odbc_reset_statement(); } /* check inserts ... */ odbc_check_no_row("IF EXISTS(SELECT * FROM #putdata WHERE c NOT LIKE '') SELECT 1"); } /* TODO test cancel inside SQLExecute */ odbc_disconnect(); printf("Done.\n"); return 0; }
static void Test0(int use_sql, const char *create_sql, const char *insert_sql, const char *select_sql) { #define ROWS 4 #define C_LEN 10 SQLUINTEGER n[ROWS]; char c[ROWS][C_LEN]; SQLLEN c_len[ROWS], n_len[ROWS]; SQLUSMALLINT statuses[ROWS]; SQLUSMALLINT i; SQLULEN num_row; SQLHSTMT stmt2; /* create test table */ odbc_command("IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test"); odbc_command(create_sql); for (i = 1; i <= 6; ++i) { char sql_buf[80], data[10]; memset(data, 'a' + (i - 1), sizeof(data)); data[i] = 0; sprintf(sql_buf, insert_sql, data, i); odbc_command(sql_buf); } /* set cursor options */ odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_ROWVER, 0, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_DYNAMIC, 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROWS, 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER) statuses, 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROWS_FETCHED_PTR, &num_row, 0, "S"); CHKSetCursorName(T("C1"), SQL_NTS, "S"); /* */ CHKExecDirect(T(select_sql), SQL_NTS, "S"); /* bind some rows at a time */ CHKBindCol(1, SQL_C_ULONG, n, 0, n_len, "S"); CHKBindCol(2, SQL_C_CHAR, c, C_LEN, c_len, "S"); /* allocate an additional statement */ CHKAllocStmt(&stmt2, "S"); while (CHKFetchScroll(SQL_FETCH_NEXT, 0, "SNo") == SQL_SUCCESS) { /* print, just for debug */ for (i = 0; i < num_row; ++i) printf("row %d i %d c %s\n", (int) (i + 1), (int) n[i], c[i]); printf("---\n"); /* delete a row */ i = 1; if (i > 0 && i <= num_row) { if (mssql2005) CHKSetPos(i, use_sql ? SQL_POSITION : SQL_DELETE, SQL_LOCK_NO_CHANGE, "SI"); else CHKSetPos(i, use_sql ? SQL_POSITION : SQL_DELETE, SQL_LOCK_NO_CHANGE, "S"); if (use_sql) { SWAP_STMT(stmt2); CHKPrepare(T("DELETE FROM #test WHERE CURRENT OF C1"), SQL_NTS, "S"); CHKExecute("S"); SWAP_STMT(stmt2); } } /* update another row */ i = 2; if (i > 0 && i <= num_row) { strcpy(c[i - 1], "foo"); c_len[i - 1] = 3; if (strstr(select_sql, "#a") == NULL || use_sql) { CHKSetPos(i, use_sql ? SQL_POSITION : SQL_UPDATE, SQL_LOCK_NO_CHANGE, "S"); } else { SQLTCHAR sqlstate[6]; SQLTCHAR msg[256]; n[i - 1] = 321; CHKSetPos(i, use_sql ? SQL_POSITION : SQL_UPDATE, SQL_LOCK_NO_CHANGE, "E"); CHKGetDiagRec(SQL_HANDLE_STMT, odbc_stmt, 1, sqlstate, NULL, msg, ODBC_VECTOR_SIZE(msg), NULL, "S"); if (strstr(C(msg), "Invalid column name 'c'") == NULL) { fprintf(stderr, "Expected message not found at line %d\n", __LINE__); exit(1); } } if (use_sql) { SWAP_STMT(stmt2); CHKPrepare(T("UPDATE #test SET c=? WHERE CURRENT OF C1"), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, C_LEN, 0, c[i - 1], 0, NULL, "S"); CHKExecute("S"); /* FIXME this is not necessary for mssql driver */ SQLMoreResults(odbc_stmt); SWAP_STMT(stmt2); } } } SWAP_STMT(stmt2); CHKFreeStmt(SQL_DROP, "S"); SWAP_STMT(stmt2); odbc_reset_statement(); /* test values */ CheckNoRow("IF (SELECT COUNT(*) FROM #test) <> 4 SELECT 1"); CheckNoRow("IF NOT EXISTS(SELECT * FROM #test WHERE i = 3 AND c = 'ccc') SELECT 1"); CheckNoRow("IF NOT EXISTS(SELECT * FROM #test WHERE i = 4 AND c = 'dddd') SELECT 1"); if (strstr(select_sql, "#a") == NULL || use_sql) { CheckNoRow("IF NOT EXISTS(SELECT * FROM #test WHERE i = 2 AND c = 'foo') SELECT 1"); CheckNoRow("IF NOT EXISTS(SELECT * FROM #test WHERE i = 6 AND c = 'foo') SELECT 1"); } }
int main(int argc, char **argv) { SQLLEN sql_nts = SQL_NTS; const char *query; SQLINTEGER id = 0; char string[64]; TDS_SYS_SOCKET last_socket; int port; const int num_inserts = 20; int is_freetds; #ifdef _WIN32 WSADATA wsaData; WSAStartup(MAKEWORD(1, 1), &wsaData); #endif if (tds_mutex_init(&mtx)) return 1; odbc_mark_sockets_opened(); odbc_connect(); /* * this does not work if server is not connected with socket * (ie ms driver connected locally) */ last_socket = odbc_find_last_socket(); if (TDS_IS_SOCKET_INVALID(last_socket)) { fprintf(stderr, "Error finding last socket opened\n"); return 1; } remote_addr_len = sizeof(remote_addr); if (tds_getpeername(last_socket, &remote_addr.sa, &remote_addr_len)) { fprintf(stderr, "Unable to get remote address\n"); return 1; } is_freetds = odbc_driver_is_freetds(); odbc_disconnect(); /* init fake server, behave like a proxy */ for (port = 12340; port < 12350; ++port) if (!init_fake_server(port)) break; if (port == 12350) { fprintf(stderr, "Cannot bind to a port\n"); return 1; } printf("Fake server bound at port %d\n", port); /* override connections */ if (is_freetds) { setenv("TDSHOST", "127.0.0.1", 1); sprintf(string, "%d", port); setenv("TDSPORT", string, 1); odbc_connect(); } else { char tmp[2048]; SQLSMALLINT len; CHKAllocEnv(&odbc_env, "S"); CHKAllocConnect(&odbc_conn, "S"); sprintf(tmp, "DRIVER={SQL Server};SERVER=127.0.0.1,%d;UID=%s;PWD=%s;DATABASE=%s;Network=DBMSSOCN;", port, odbc_user, odbc_password, odbc_database); printf("connection string: %s\n", tmp); CHKDriverConnect(NULL, T(tmp), SQL_NTS, (SQLTCHAR *) tmp, sizeof(tmp)/sizeof(SQLTCHAR), &len, SQL_DRIVER_NOPROMPT, "SI"); CHKAllocStmt(&odbc_stmt, "S"); } /* real test */ odbc_command("CREATE TABLE #test(i int, c varchar(40))"); odbc_reset_statement(); /* do not take into account connection statistics */ tds_mutex_lock(&mtx); round_trips = 0; inserts = 0; tds_mutex_unlock(&mtx); query = "insert into #test values (?, ?)"; CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts, "SI"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(string), 0, string, 0, &sql_nts, "SI"); CHKPrepare(T(query), SQL_NTS, "SI"); tds_mutex_lock(&mtx); printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); for (id = 0; id < num_inserts; id++) { sprintf(string, "This is a test (%d)", (int) id); CHKExecute("SI"); CHKFreeStmt(SQL_CLOSE, "S"); } tds_mutex_lock(&mtx); printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); odbc_reset_statement(); tds_mutex_lock(&mtx); if (inserts > 1 || round_trips > (unsigned) (num_inserts * 2 + 6)) { fprintf(stderr, "Too much round trips (%u) or insert (%u) !!!\n", round_trips, inserts); tds_mutex_unlock(&mtx); return 1; } printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); #ifdef ENABLE_DEVELOPING /* check for SQL_RESET_PARAMS */ tds_mutex_lock(&mtx); round_trips = 0; inserts = 0; tds_mutex_unlock(&mtx); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts, "SI"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(string), 0, string, 0, &sql_nts, "SI"); CHKPrepare((SQLCHAR *) query, SQL_NTS, "SI"); tds_mutex_lock(&mtx); printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); for (id = 0; id < num_inserts; id++) { CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts, "SI"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(string), 0, string, 0, &sql_nts, "SI"); sprintf(string, "This is a test (%d)", (int) id); CHKExecute("SI"); CHKFreeStmt(SQL_RESET_PARAMS, "S"); } tds_mutex_lock(&mtx); printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); odbc_reset_statement(); tds_mutex_lock(&mtx); if (inserts > 1 || round_trips > num_inserts * 2 + 6) { fprintf(stderr, "Too much round trips (%u) or insert (%u) !!!\n", round_trips, inserts); tds_mutex_unlock(&mtx); return 1; } printf("%u round trips %u inserts\n", round_trips, inserts); tds_mutex_unlock(&mtx); #endif odbc_disconnect(); alarm(10); tds_thread_join(fake_thread, NULL); return 0; }
static int TestOutput(const char *type, const char *value_to_convert, SQLSMALLINT out_c_type, SQLSMALLINT out_sql_type, const char *expected) { char sbuf[1024]; unsigned char out_buf[256]; SQLLEN out_len = 0; const char *sep; odbc_reset_statement(); /* build store procedure to test */ odbc_command("IF OBJECT_ID('spTestProc') IS NOT NULL DROP PROC spTestProc"); sep = "'"; if (strncmp(value_to_convert, "0x", 2) == 0) sep = ""; sprintf(sbuf, "CREATE PROC spTestProc @i %s OUTPUT AS SELECT @i = CONVERT(%s, %s%s%s)", type, type, sep, value_to_convert, sep); odbc_command(sbuf); memset(out_buf, 0, sizeof(out_buf)); if (use_cursors) { odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_SCROLLABLE, 0, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_DYNAMIC, 0, "S"); } /* bind parameter */ if (exec_direct) { CHKBindParameter(1, SQL_PARAM_OUTPUT, out_c_type, out_sql_type, precision, 0, out_buf, sizeof(out_buf), &out_len, "S"); /* call store procedure */ CHKExecDirect(T("{call spTestProc(?)}"), SQL_NTS, "S"); } else { if (prepare_before) CHKPrepare(T("{call spTestProc(?)}"), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_OUTPUT, out_c_type, out_sql_type, precision, 0, out_buf, sizeof(out_buf), &out_len, "S"); if (!prepare_before) CHKPrepare(T("{call spTestProc(?)}"), SQL_NTS, "S"); CHKExecute("S"); } /* * MS OBDC requires it cause first recordset is a recordset with a * warning caused by the way it execute RPC (via EXEC statement) */ if (use_cursors && !odbc_driver_is_freetds()) SQLMoreResults(odbc_stmt); /* test results */ odbc_c2string(sbuf, out_c_type, out_buf, out_len); if (strcmp(sbuf, expected) != 0) { if (only_test) { odbc_command("drop proc spTestProc"); ODBC_FREE(); return 1; } fprintf(stderr, "Wrong result\n Got: %s\n Expected: %s\n", sbuf, expected); exit(1); } only_test = 0; odbc_command("drop proc spTestProc"); ODBC_FREE(); return 0; }
static void TestInput(SQLSMALLINT out_c_type, const char *type, SQLSMALLINT out_sql_type, const char *param_type, const char *value_to_convert) { char sbuf[1024]; unsigned char out_buf[256]; SQLLEN out_len = 0; const char *expected = value_to_convert; size_t value_len = strlen(value_to_convert); const char *p; const char *sep = "'"; odbc_reset_statement(); /* execute a select to get data as wire */ if ((p = strstr(value_to_convert, " -> ")) != NULL) { value_len = p - value_to_convert; expected = p + 4; } if (value_len >= 2 && strncmp(value_to_convert, "0x", 2) == 0) sep = ""; sprintf(sbuf, "SELECT CONVERT(%s, %s%.*s%s)", type, sep, (int) value_len, value_to_convert, sep); odbc_command(sbuf); SQLBindCol(odbc_stmt, 1, out_c_type, out_buf, sizeof(out_buf), &out_len); CHKFetch("SI"); CHKFetch("No"); CHKMoreResults("No"); if (use_nts) { out_len = SQL_NTS; use_nts = 0; } /* create a table with a column of that type */ odbc_reset_statement(); sprintf(sbuf, "CREATE TABLE #tmp_insert (col %s)", param_type); odbc_command(sbuf); if (use_cursors) { odbc_reset_statement(); CHKSetStmtAttr(SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_SCROLLABLE, 0, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_DYNAMIC, 0, "S"); } /* insert data using prepared statements */ sprintf(sbuf, "INSERT INTO #tmp_insert VALUES(?)"); if (exec_direct) { CHKBindParameter(1, SQL_PARAM_INPUT, out_c_type, out_sql_type, 20, 0, out_buf, sizeof(out_buf), &out_len, "S"); if (check_truncation) CHKExecDirect(T(sbuf), SQL_NTS, "E"); else CHKExecDirect(T(sbuf), SQL_NTS, "SNo"); } else { if (prepare_before) CHKPrepare(T(sbuf), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, out_c_type, out_sql_type, 20, 0, out_buf, sizeof(out_buf), &out_len, "S"); if (!prepare_before) CHKPrepare(T(sbuf), SQL_NTS, "S"); if (check_truncation) CHKExecute("E"); else CHKExecute("SNo"); } /* check if row is present */ if (!check_truncation) { odbc_reset_statement(); sep = "'"; if (strncmp(expected, "0x", 2) == 0) sep = ""; if (strcmp(param_type, "TEXT") == 0) sprintf(sbuf, "SELECT * FROM #tmp_insert WHERE CONVERT(VARCHAR(255), col) = CONVERT(VARCHAR(255), %s%s%s)", sep, expected, sep); else if (strcmp(param_type, "NTEXT") == 0) sprintf(sbuf, "SELECT * FROM #tmp_insert WHERE CONVERT(NVARCHAR(2000), col) = CONVERT(NVARCHAR(2000), %s%s%s)", sep, expected, sep); else if (strcmp(param_type, "IMAGE") == 0) sprintf(sbuf, "SELECT * FROM #tmp_insert WHERE CONVERT(VARBINARY(255), col) = CONVERT(VARBINARY(255), %s%s%s)", sep, expected, sep); else sprintf(sbuf, "SELECT * FROM #tmp_insert WHERE col = CONVERT(%s, %s%s%s)", param_type, sep, expected, sep); odbc_command(sbuf); CHKFetch("S"); CHKFetch("No"); CHKMoreResults("No"); } check_truncation = 0; odbc_command("DROP TABLE #tmp_insert"); ODBC_FREE(); }
int main(int argc, char **argv) { SQLRETURN RetCode; SQLHSTMT old_odbc_stmt = SQL_NULL_HSTMT; int i; int key; SQLLEN vind0; int cnt = 2, wide; char sql[256]; test_info *t = NULL; odbc_use_version3 = 1; odbc_connect(); /* tests (W)CHAR/BINARY -> (W)CHAR/BINARY (9 cases) */ add_test(SQL_C_BINARY, SQL_LONGVARCHAR, "TEXT", 123, 1 ); add_test(SQL_C_BINARY, SQL_LONGVARBINARY, "IMAGE", 987, 25); add_test(SQL_C_CHAR, SQL_LONGVARBINARY, "IMAGE", 987, 25); add_test(SQL_C_CHAR, SQL_LONGVARCHAR, "TEXT", 343, 47); add_test(SQL_C_WCHAR, SQL_LONGVARBINARY, "IMAGE", 561, 29); add_test(SQL_C_WCHAR, SQL_LONGVARCHAR, "TEXT", 698, 24); if (odbc_db_is_microsoft()) { add_test(SQL_C_BINARY, SQL_WLONGVARCHAR, "NTEXT", 765, 12); add_test(SQL_C_CHAR, SQL_WLONGVARCHAR, "NTEXT", 237, 71); add_test(SQL_C_WCHAR, SQL_WLONGVARCHAR, "NTEXT", 687, 68); } strcpy(sql, "CREATE TABLE #tt(k INT"); for (t = test_infos; t < test_infos+num_tests; ++t) sprintf(strchr(sql, 0), ",f%u %s", t->num, t->db_type); strcat(sql, ",v INT)"); odbc_command(sql); old_odbc_stmt = odbc_stmt; odbc_stmt = SQL_NULL_HSTMT; /* Insert rows ... */ for (i = 0; i < cnt; i++) { /* MS do not save correctly char -> binary */ if (!odbc_driver_is_freetds() && i) continue; CHKAllocHandle(SQL_HANDLE_STMT, odbc_conn, &odbc_stmt, "S"); strcpy(sql, "INSERT INTO #tt VALUES(?"); for (t = test_infos; t < test_infos+num_tests; ++t) strcat(sql, ",?"); strcat(sql, ",?)"); CHKPrepare(T(sql), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &key, 0, &vind0, "S"); for (t = test_infos; t < test_infos+num_tests; ++t) CHKBindParameter(t->num+1, SQL_PARAM_INPUT, t->c_type, t->sql_type, 0x10000000, 0, t->buf, 0, &t->vind, "S"); CHKBindParameter(num_tests+2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &key, 0, &vind0, "S"); key = i; vind0 = 0; printf(">> insert... %d\n", i); RetCode = CHKExecute("SINe"); while (RetCode == SQL_NEED_DATA) { char *p; RetCode = CHKParamData((SQLPOINTER) & p, "SINe"); printf(">> SQLParamData: ptr = %p RetCode = %d\n", (void *) p, RetCode); if (RetCode == SQL_NEED_DATA) { for (t = test_infos; t < test_infos+num_tests && t->buf != p; ++t) ; assert(t < test_infos+num_tests); if (t->c_type == SQL_C_CHAR || t->c_type == SQL_C_WCHAR) { unsigned char_len = 1; fill_hex(p, NBYTES, t->gen1, t->gen2); if (t->c_type == SQL_C_WCHAR) { char_len = sizeof(SQLWCHAR); odbc_to_sqlwchar((SQLWCHAR*) p, p, NBYTES * 2); } CHKPutData(p, (NBYTES - (i&1)) * char_len, "S"); printf(">> param %p: total bytes written = %d\n", (void *) p, NBYTES - (i&1)); CHKPutData(p + (NBYTES - (i&1)) * char_len, (NBYTES + (i&1)) * char_len, "S"); printf(">> param %p: total bytes written = %d\n", (void *) p, NBYTES + (i&1)); } else { CHKPutData(p, NBYTES, "S"); printf(">> param %p: total bytes written = %d\n", (void *) p, NBYTES); } } } CHKFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) odbc_stmt, "S"); odbc_stmt = SQL_NULL_HSTMT; } /* Now fetch rows ... */ for (wide = 0; wide < 2; ++wide) for (i = 0; i < cnt; i++) { /* MS do not save correctly char -> binary */ if (!odbc_driver_is_freetds() && i) continue; CHKAllocHandle(SQL_HANDLE_STMT, odbc_conn, &odbc_stmt, "S"); if (odbc_db_is_microsoft()) { CHKSetStmtAttr(SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER, "S"); } strcpy(sql, "SELECT "); for (t = test_infos; t < test_infos+num_tests; ++t) sprintf(strchr(sql, 0), "f%u,", t->num); strcat(sql, "v FROM #tt WHERE k = ?"); CHKPrepare(T(sql), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &i, 0, &vind0, "S"); for (t = test_infos; t < test_infos+num_tests; ++t) { t->vind = SQL_DATA_AT_EXEC; CHKBindCol(t->num, SQL_C_BINARY, NULL, 0, &t->vind, "S"); } CHKBindCol(num_tests+1, SQL_C_LONG, &key, 0, &vind0, "S"); vind0 = 0; CHKExecute("S"); CHKFetchScroll(SQL_FETCH_NEXT, 0, "S"); printf(">> fetch... %d\n", i); for (t = test_infos; t < test_infos+num_tests; ++t) { if (t->c_type == SQL_C_CHAR || t->c_type == SQL_C_WCHAR) readBlobAsChar(t, i, wide); else readBlob(t); } CHKCloseCursor("S"); CHKFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) odbc_stmt, "S"); odbc_stmt = SQL_NULL_HSTMT; } odbc_stmt = old_odbc_stmt; free_tests(); odbc_disconnect(); if (!failed) printf("ok!\n"); return failed ? 1 : 0; }
static void Test(int level) { SQLRETURN result; SQLSMALLINT InParam = level; SQLSMALLINT OutParam = 1; SQLLEN cbReturnCode = 0, cbInParam = 0, cbOutParam = 0; SQLLEN cbOutString = SQL_NTS; char sql[80]; printf("ODBC %d nocount %s select %s level %d\n", odbc_use_version3 ? 3 : 2, g_nocount ? "yes" : "no", g_second_select ? "yes" : "no", level); ReturnCode = INVALID_RETURN; memset(&OutString, 0, sizeof(OutString)); /* test with SQLExecDirect */ sprintf(sql, "RAISERROR('An error occurred.', %d, 1)", level); result = odbc_command_with_result(odbc_stmt, sql); TestResult(result, level, "SQLExecDirect"); /* test with SQLPrepare/SQLExecute */ if (!SQL_SUCCEEDED(SQLPrepare(odbc_stmt, T(SP_TEXT), strlen(SP_TEXT)))) { fprintf(stderr, "SQLPrepare failure!\n"); exit(1); } SQLBindParameter(odbc_stmt, 1, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &ReturnCode, 0, &cbReturnCode); SQLBindParameter(odbc_stmt, 2, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &InParam, 0, &cbInParam); SQLBindParameter(odbc_stmt, 3, SQL_PARAM_OUTPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &OutParam, 0, &cbOutParam); strcpy(OutString, "Invalid!"); SQLBindParameter(odbc_stmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, OUTSTRING_LEN, 0, OutString, OUTSTRING_LEN, &cbOutString); CHKExecute("S"); /* first select, check data are returned. * SET statements before does not affect results */ CheckData(""); CHKFetch("S"); CheckData("Here is the first row"); result = SQLFetch(odbc_stmt); if (odbc_use_version3) { SQLTCHAR SqlState[6]; SQLINTEGER NativeError; SQLTCHAR MessageText[1000]; SQLSMALLINT TextLength; SQLRETURN expected; SQLLEN rows; if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLFetch should return NO DATA"); CHKGetDiagRec(SQL_HANDLE_STMT, odbc_stmt, 1, SqlState, &NativeError, MessageText, ODBC_VECTOR_SIZE(MessageText), &TextLength, "No"); result = SQLMoreResults(odbc_stmt); expected = level > 10 ? SQL_ERROR : SQL_SUCCESS_WITH_INFO; if (result != expected) ODBC_REPORT_ERROR("SQLMoreResults returned unexpected result"); if (!g_second_select && g_nocount) { if (ReturnCode == INVALID_RETURN) { result = SQLMoreResults(odbc_stmt); } else { CheckReturnCode(result, 0); ReturnCode = INVALID_RETURN; TestResult(result, level, "SQLMoreResults"); ReturnCode = 0; } } else { TestResult(result, level, "SQLMoreResults"); } /* a recordset with only warnings/errors do not contains rows */ if (CHKRowCount(&rows, "SE") == SQL_SUCCESS && rows != -1) ODBC_REPORT_ERROR("SQLRowCount returned some rows"); } else { /* in ODBC 2 errors/warnings are not handled as different recordset */ TestResult(result, level, "SQLFetch"); } if (odbc_driver_is_freetds()) CheckData(""); if (!g_second_select) { SQLLEN rows; if (CHKRowCount(&rows, "SE") == SQL_SUCCESS && rows != -1) ODBC_REPORT_ERROR("SQLRowCount returned some rows"); CheckReturnCode(result, g_nocount ? 0 : INVALID_RETURN); result = SQLMoreResults(odbc_stmt); #ifdef ENABLE_DEVELOPING if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLMoreResults should return NO DATA"); ODBC_CHECK_ROWS(-2); #endif CheckReturnCode(result, 0); return; } if (!odbc_use_version3 || !g_nocount) { /* mssql 2008 return SUCCESS_WITH_INFO with previous error */ CHKMoreResults("S"); result = SQL_SUCCESS; } CheckReturnCode(result, INVALID_RETURN); CheckData(""); if (g_nocount && odbc_use_version3 && g_second_select && level >= 10) { if (CHKFetch("SE") == SQL_ERROR) { SQLMoreResults(odbc_stmt); CHKFetch("S"); } } else { CHKFetch("S"); } CheckData("Here is the last row"); CHKFetch("No"); CheckData(""); if (!odbc_use_version3 || g_nocount) CheckReturnCode(result, 0); #ifdef ENABLE_DEVELOPING else CheckReturnCode(result, INVALID_RETURN); #endif /* FIXME how to handle return in store procedure ?? */ result = SQLMoreResults(odbc_stmt); #ifdef ENABLE_DEVELOPING if (result != SQL_NO_DATA) ODBC_REPORT_ERROR("SQLMoreResults return other data"); #endif CheckReturnCode(result, 0); CheckData(""); ODBC_FREE(); }
int main(int argc, char *argv[]) { SQLINTEGER input, output; SQLLEN ind, ind2, ind3, ind4; SQLINTEGER out1; char out2[30]; odbc_connect(); odbc_command("IF OBJECT_ID('simpleresult') IS NOT NULL DROP PROC simpleresult"); odbc_command("create proc simpleresult @i int as begin return @i end"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &input, 0, &ind2, "S"); CHKBindParameter(1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &output, 0, &ind, "S"); CHKPrepare(T("{ \n?\t\r= call simpleresult(?)}"), SQL_NTS, "S"); input = 123; ind2 = sizeof(input); output = 0xdeadbeef; CHKExecute("S"); if (output != 123) { printf("Invalid result\n"); exit(1); } /* should return "Invalid cursor state" */ if (SQLFetch(odbc_stmt) != SQL_ERROR) { printf("Data not expected\n"); exit(1); } ODBC_CHECK_COLS(0); /* just to reset some possible buffers */ odbc_command("DECLARE @i INT"); /* same test but with SQLExecDirect and same bindings */ input = 567; ind2 = sizeof(input); output = 0xdeadbeef; CHKExecDirect(T("{?=call simpleresult(?)}"), SQL_NTS, "S"); if (output != 567) { fprintf(stderr, "Invalid result\n"); exit(1); } /* should return "Invalid cursor state" */ CHKFetch("E"); odbc_command("drop proc simpleresult"); odbc_command("IF OBJECT_ID('simpleresult2') IS NOT NULL DROP PROC simpleresult2"); /* force cursor close */ SQLCloseCursor(odbc_stmt); /* test output parameter */ odbc_command("create proc simpleresult2 @i int, @x int output, @y varchar(20) output as begin select @x = 6789 select @y = 'test foo' return @i end"); CHKBindParameter(1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &output, 0, &ind, "S"); CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &input, 0, &ind2, "S"); CHKBindParameter(3, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &out1, 0, &ind3, "S"); CHKBindParameter(4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0, out2, sizeof(out2), &ind4, "S"); CHKPrepare(T("{ \n?\t\r= call simpleresult2(?,?,?)}"), SQL_NTS, "S"); input = 987; ind2 = sizeof(input); out1 = 888; output = 0xdeadbeef; ind3 = SQL_DATA_AT_EXEC; ind4 = SQL_DEFAULT_PARAM; strcpy(out2, "bad!"); CHKExecute("S"); if (output != 987 || ind3 <= 0 || ind4 <= 0 || out1 != 6789 || strcmp(out2, "test foo") != 0) { printf("ouput = %d ind3 = %d ind4 = %d out1 = %d out2 = %s\n", (int) output, (int) ind3, (int) ind4, (int) out1, out2); printf("Invalid result\n"); exit(1); } /* should return "Invalid cursor state" */ CHKFetch("E"); ODBC_CHECK_COLS(0); odbc_command("drop proc simpleresult2"); /* * test from shiv kumar * Cfr ML 2006-11-21 "specifying a 0 for the StrLen_or_IndPtr in the * SQLBindParameter call is not working on AIX" */ odbc_command("IF OBJECT_ID('rpc_read') IS NOT NULL DROP PROC rpc_read"); odbc_reset_statement(); odbc_command("create proc rpc_read @i int, @x timestamp as begin select 1 return 1234 end"); SQLCloseCursor(odbc_stmt); CHKPrepare(T("{ ? = CALL rpc_read ( ?, ? ) }"), SQL_NTS, "S"); ind = 0; CHKBindParameter(1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &output, 0, &ind, "S"); ind2 = 0; CHKBindParameter(2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &input, 0, &ind2, "S"); ind3 = 8; CHKBindParameter(3, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 8, 0, out2, 8, &ind3, "S"); CHKExecute("S"); CHKFetch("S"); CHKFetch("No"); odbc_reset_statement(); odbc_command("drop proc rpc_read"); /* * Test from Joao Amaral * This test SQLExecute where a store procedure returns no result * This seems similar to a previous one but use set instead of select * (with is supported only by mssql and do not return all stuff as * select does) */ if (odbc_db_is_microsoft()) { odbc_reset_statement(); odbc_command("IF OBJECT_ID('sp_test') IS NOT NULL DROP PROC sp_test"); odbc_command("create proc sp_test @res int output as set @res = 456"); odbc_reset_statement(); CHKPrepare(T("{ call sp_test(?)}"), SQL_NTS, "S"); CHKBindParameter(1, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &output, 0, &ind, "S"); output = 0xdeadbeef; CHKExecute("S"); if (output != 456) { fprintf(stderr, "Invalid result %d(%x)\n", (int) output, (int) output); return 1; } odbc_command("drop proc sp_test"); } odbc_disconnect(); if (odbc_db_is_microsoft()) test_with_conversions(); printf("Done.\n"); return 0; }
static void DoTest(int prepare) { int n = 0; static const char query[] = /* on prepared this recordset should be skipped */ "DECLARE @b INT SELECT @b = 25 " "SELECT * FROM #tmp1 WHERE i <= 3 " /* on prepare we cannot distinguish these recordset */ "INSERT INTO #tmp2 SELECT * FROM #tmp1 WHERE i = 1 " "INSERT INTO #tmp2 SELECT * FROM #tmp1 WHERE i <= 3 " "SELECT * FROM #tmp1 WHERE i = 1 " /* but FreeTDS can detect last recordset */ "UPDATE #tmp1 SET i=i+1 WHERE i >= 2"; if (prepare) { CHKPrepare(T(query), SQL_NTS, "S"); CHKExecute("S"); } else { /* execute a batch command select insert insert select and check rows */ CHKExecDirect(T(query), SQL_NTS, "S"); } if (!prepare) { printf("Result %d\n", ++n); ODBC_CHECK_COLS(0); ODBC_CHECK_ROWS(1); CHKMoreResults("S"); } printf("Result %d\n", ++n); ODBC_CHECK_COLS(1); ODBC_CHECK_ROWS(-1); CHKFetch("S"); CHKFetch("S"); ODBC_CHECK_COLS(1); ODBC_CHECK_ROWS(-1); CHKFetch("No"); ODBC_CHECK_COLS(1); ODBC_CHECK_ROWS(2); CHKMoreResults("S"); if (!prepare) { printf("Result %d\n", ++n); ODBC_CHECK_COLS(0); ODBC_CHECK_ROWS(1); CHKMoreResults("S"); printf("Result %d\n", ++n); ODBC_CHECK_COLS(0); ODBC_CHECK_ROWS(2); CHKMoreResults("S"); } printf("Result %d\n", ++n); ODBC_CHECK_COLS(1); ODBC_CHECK_ROWS(-1); CHKFetch("S"); ODBC_CHECK_COLS(1); ODBC_CHECK_ROWS(-1); CHKFetch("No"); ODBC_CHECK_COLS(1); if (prepare) { /* collapse 2 recordset... after a lot of testing this is the behavior! */ if (odbc_driver_is_freetds()) ODBC_CHECK_ROWS(2); } else { ODBC_CHECK_ROWS(1); CHKMoreResults("S"); ODBC_CHECK_COLS(0); ODBC_CHECK_ROWS(2); } CHKMoreResults("No"); #ifndef TDS_NO_DM if (!prepare) ODBC_CHECK_COLS(-1); ODBC_CHECK_ROWS(-2); #endif ODBC_FREE(); }
int main(int argc, char *argv[]) { SQLINTEGER len, out; int i; SQLHSTMT stmt1, stmt2; SQLHSTMT *pcur_stmt = NULL; odbc_use_version3 = 1; odbc_set_conn_attr = my_attrs; odbc_connect(); stmt1 = odbc_stmt; out = 0; len = sizeof(out); CHKGetConnectAttr(1224, (SQLPOINTER) &out, sizeof(out), &len, "S"); /* test we really support MARS on this connection */ /* TODO should out be correct ?? */ printf("Following row can contain an error due to MARS detection (is expected)\n"); if (!out || odbc_command2("BEGIN TRANSACTION", "SNoE") != SQL_ERROR) { printf("MARS not supported for this connection\n"); odbc_disconnect(); odbc_test_skipped(); return 0; } odbc_read_error(); if (!strstr(odbc_err, "MARS")) { printf("Error message invalid \"%s\"\n", odbc_err); return 1; } /* create a test table with some data */ odbc_command("create table #mars1 (n int, v varchar(100))"); for (i = 0; i < 60; ++i) { char cmd[120], buf[80]; memset(buf, 'a' + (i % 26), sizeof(buf)); buf[i * 7 % 73] = 0; sprintf(cmd, "insert into #mars1 values(%d, '%s')", i, buf); odbc_command(cmd); } /* and another to avid locking problems */ odbc_command("create table #mars2 (n int, v varchar(100))"); AutoCommit(SQL_AUTOCOMMIT_OFF); /* try to do a select which return a lot of data (to test server didn't cache everything) */ odbc_command("select a.n, b.n, a.v from #mars1 a, #mars1 b order by a.n, b.n"); CHKFetch("S"); /* try to do other commands */ CHKAllocStmt(&stmt2, "S"); SET_STMT(stmt2); odbc_command("insert into #mars2 values(1, 'foo')"); SET_STMT(stmt1); CHKFetch("S"); /* reset statements */ odbc_reset_statement(); SET_STMT(stmt2); odbc_reset_statement(); /* now to 2 select with prepare/execute */ CHKPrepare(T("select a.n, b.n, a.v from #mars1 a, #mars1 b order by a.n, b.n"), SQL_NTS, "S"); SET_STMT(stmt1); CHKPrepare(T("select a.n, b.n, a.v from #mars1 a, #mars1 b order by a.n desc, b.n"), SQL_NTS, "S"); SET_STMT(stmt2); CHKExecute("S"); SET_STMT(stmt1); CHKExecute("S"); SET_STMT(stmt2); CHKFetch("S"); SET_STMT(stmt1); CHKFetch("S"); SET_STMT(stmt2); CHKFetch("S"); odbc_reset_statement(); SET_STMT(stmt1); CHKFetch("S"); odbc_reset_statement(); EndTransaction(SQL_COMMIT); /* TODO test receiving large data should not take much memory */ odbc_disconnect(); return 0; }
static void Test(void) { #define ROWS 5 struct data_t { SQLINTEGER i; SQLLEN ind_i; char c[20]; SQLLEN ind_c; } data[ROWS]; SQLUSMALLINT statuses[ROWS]; SQLLEN num_row; odbc_reset_statement(); /* this should not fail or return warnings */ if (use_cursors) { CHKSetStmtAttr(SQL_ATTR_CONCURRENCY, int2ptr(SQL_CONCUR_READ_ONLY), 0, "S"); CHKSetStmtAttr(SQL_ATTR_CURSOR_TYPE, int2ptr(SQL_CURSOR_STATIC), 0, "S"); } CHKPrepare(T("SELECT c, i FROM #cursor6_test"), SQL_NTS, "S"); CHKExecute("S"); CHKSetStmtAttr(SQL_ATTR_ROW_BIND_TYPE, int2ptr(sizeof(data[0])), 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE, int2ptr(ROWS), 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROW_STATUS_PTR, statuses, 0, "S"); CHKSetStmtAttr(SQL_ATTR_ROWS_FETCHED_PTR, &num_row, 0, "S"); if (bind_all) CHKBindCol(1, SQL_C_CHAR, &data[0].c, sizeof(data[0].c), &data[0].ind_c, "S"); CHKBindCol(2, SQL_C_LONG, &data[0].i, sizeof(data[0].i), &data[0].ind_i, "S"); #define FILL(s, n) do { \ int _n; for (_n = 0; _n < sizeof(s)/sizeof(s[0]); ++_n) s[_n] = n; \ } while(0) FILL(statuses, 9876); num_row = -3; data[0].i = (SQLINTEGER) 0xdeadbeef; data[1].i = (SQLINTEGER) 0xdeadbeef; if (normal_fetch) CHKFetch("S"); else CHKFetchScroll(SQL_FETCH_NEXT, 0, "S"); /* now check row numbers */ printf("num_row %ld statuses[0] %d statuses[1] %d odbc3 %d\n", (long int) num_row, (int) statuses[0], (int) statuses[1], odbc_use_version3); if (odbc_use_version3 || !normal_fetch) { if (num_row != ROWS || statuses[0] != SQL_ROW_SUCCESS || statuses[1] != SQL_ROW_SUCCESS) { fprintf(stderr, "result error 1\n"); exit(1); } } else { if (data[0].i != 1 || data[1].i != 0xdeadbeef) { fprintf(stderr, "result error 2\n"); exit(1); } } FILL(statuses, 8765); num_row = -3; if (normal_fetch) CHKFetch("S"); else CHKFetchScroll(SQL_FETCH_NEXT, 0, "S"); }