不执行SQL语句 [英] SQL Statements Not Executing
本文介绍了不执行SQL语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用Visual Studio 2010，Win 7，Access 2010、32位.第一个SQL语句执行以下四个命令.
代码...有时插入有效，有时无效. TOTAL和QUOTA也会返回0.
I''m using Visual Studio 2010, Win 7, Access 2010, 32 bit. The first SQL statement executes the following four don''t.
The code...Sometimes the insert works, sometimes no. The TOTAL and QUOTA return 0 as well.
//Burton
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>
const char* DAM = "Direct ODBC";
SQLCHAR szDSN[256] =
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\FILEBLOCK\\Fileblocker.accdb;";
main()
{
HENV hEnv, hEnv2, hEnv3, hEnv4, hEnv5;
HDBC hDbc, hDbc2, hDbc3, hDbc4, hDbc5;
SQLRETURN rc, NEW, TOTAL, QUOTA, UP;
SQLSMALLINT iConnStrLength2Ptr;
SQLCHAR szConnStrOut[255];
SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
SQLCHAR* query2 = (SQLCHAR*)"INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1)";
SQLCHAR* query3 = (SQLCHAR*)"SELECT SUM(DownloadCount) AS DT FROM tblDownloads WHERE tblDownloads.[DownloadIP]='173.201.216.2'";
SQLCHAR* query4 = (SQLCHAR*)"SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] = '173.201.216.2'";
SQLCHAR* query5 = (SQLCHAR*)"UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2'";
/* Number of rows and columns in result set */
SQLINTEGER rowCount = 0;
SQLSMALLINT fieldCount = 0;
HSTMT hStmt, hStmt2, hStmt3, hStmt4, hStmt5;
/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
NEW = SQLAllocEnv(&hEnv2);
TOTAL = SQLAllocEnv(&hEnv3);
QUOTA = SQLAllocEnv(&hEnv4);
UP = SQLAllocEnv(&hEnv5);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);
NEW = SQLAllocConnect(hEnv3, &hDbc2);
TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
QUOTA = SQLAllocConnect(hEnv4, &hDbc4);
UP = SQLAllocConnect(hEnv5, &hDbc5);
/* Connect to the 'Fileblocker.accdb' database */
rc = SQLDriverConnect(hDbc, NULL, szDSN, _countof(szDSN),
szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc))
{
printf("%s: Successfully connected to database. Data source name: \n %s\n",
DAM, szConnStrOut);
/* Prepare SQL query */
rc = SQLAllocStmt(hDbc,&hStmt);
rc = SQLPrepare(hStmt, query, SQL_NTS);
/* Execute the query and create a record set */
rc = SQLExecute(hStmt);
/* Loop through the rows in the result set */
rc = SQLFetch(hStmt);
while (SQL_SUCCEEDED(rc))
{
rc = SQLFetch(hStmt);
rowCount++;
};
printf("%s: Total Legit IP Count: %d\n", DAM, rowCount);
rc = SQLFreeStmt(hStmt, SQL_DROP);
if (rowCount >= 1)
{
printf("PASS\n");
NEW = SQLDriverConnect(hDbc2, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
NEW = SQLAllocStmt(hDbc2,&hStmt2);
NEW = SQLPrepare(hStmt2, query2, SQL_NTS);
NEW = SQLExecute(hStmt2);
NEW = SQLFreeStmt(hStmt2, SQL_DROP);
TOTAL = SQLDriverConnect(hDbc3, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
TOTAL = SQLAllocStmt(hDbc3,&hStmt3);
TOTAL = SQLPrepare(hStmt3, query3, SQL_NTS);
TOTAL = SQLExecute(hStmt3);
TOTAL = SQLFetch(hStmt3);
printf("%s: Total of Downloads: %d\n", DAM, TOTAL);
TOTAL = SQLFreeStmt(hStmt3, SQL_DROP);
QUOTA = SQLDriverConnect(hDbc4, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
QUOTA = SQLAllocStmt(hDbc4,&hStmt4);
QUOTA = SQLPrepare(hStmt4, query4, SQL_NTS);
QUOTA = SQLExecute(hStmt4);
QUOTA = SQLFetch(hStmt4);
printf("%s: Quota For IP Address: %d\n", DAM, QUOTA);
QUOTA = SQLFreeStmt(hStmt4, SQL_DROP);
if (TOTAL >= QUOTA)
{
UP = SQLDriverConnect(hDbc5, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
UP = SQLAllocStmt(hDbc5,&hStmt5);
UP = SQLPrepare(hStmt5, query5, SQL_NTS);
UP = SQLExecute(hStmt5);
UP = SQLFetch(hStmt5);
UP = SQLFreeStmt(hStmt5, SQL_DROP);
}
else if (rowCount == 0)
{
printf("FAIL\n");
rc = SQLFreeStmt(hStmt, SQL_DROP);
}
system("pause");
//}
}
else
{
printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
}
/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLDisconnect(hDbc2);
SQLDisconnect(hDbc3);
SQLDisconnect(hDbc4);
SQLDisconnect(hDbc5);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc3);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc4);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc5);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv3);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv4);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv5);
}
}</sqlext.h></stdio.h></windows.h>
推荐答案
使用调试器逐步浏览代码.您的if (SQL_SUCCEEDED(rc))调用可以隐藏发生的问题，但是最好保留在此处以确保最终版本的故障安全.您可能会发现那里的呼叫失败了，而您只是没有正确捕获错误.
Step through your code using the debugger. Your if (SQL_SUCCEEDED(rc)) calls can hide issues that occur, but are a good idea to keep there for the final release as a failsafe. You''ll probably find that there is a call there that fails and you''re just not trapping the error correctly.
我不确定我在说什么可以解决您的问题，因为我从未使用过Access，但是使用SQL Server进行此操作时，我记得用另一组单引号转义了单引号.
因此，该语句将以-... WHERE tblIPID.[IPAddress] = ''''173.201.216.2'''';"
I''m not sure if what I''m saying will solve you''re problem as I''ve never worked with Access, but when doing this with SQL Server, I remember escaping the single quotes with another set of single quotes.
So the statement would end like -... WHERE tblIPID.[IPAddress] = ''''173.201.216.2'''';"
这篇关于不执行SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
