SQL语句不执行

发布于 2024-12-11 01:35:38 字数 2914 浏览 0 评论 0原文

我正在使用 Visual Studo 2010、Win7、C++ 和 Microsoft Access 2010 al 32 位。我正在使用 Direct ODBC 连接。第一个 SELECT 语句执行。 INSERT、SELECT、SELECT 和 UPDATE 未执行。我认为这不是 SQL 语句的语法。任何帮助表示赞赏。谢谢。

#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;
    HDBC    hDbc;

    SQLRETURN  rc, TOTAL, QUOTA;

    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;";

/* Number of rows and columns in result set */
SQLINTEGER      rowCount = 0;
SQLSMALLINT     fieldCount = 0, column = 0;
HSTMT           hStmt;

/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);

/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);

/* 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 Row Count: %d\n", DAM, rowCount);
        rc = SQLFreeStmt(hStmt, SQL_DROP);
        if (rowCount >= 1)
            {
            printf("PASS\n");
            SQLExecute ("INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);");

            TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
            QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");

            if (TOTAL >= QUOTA)
                {
                SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
                }
        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);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
}

I'm using Visual Studo 2010, Win7, C++ and Microsoft Access 2010 al 32 bit. I'm connecting with Direct ODBC. The first SELECT Statement executes. The INSERT,SELECT,SELECT and UPDATE are not executing. I don't think it's the syntax of the SQL statement. Any help is appreciated. Thank you.

#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;
    HDBC    hDbc;

    SQLRETURN  rc, TOTAL, QUOTA;

    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;";

/* Number of rows and columns in result set */
SQLINTEGER      rowCount = 0;
SQLSMALLINT     fieldCount = 0, column = 0;
HSTMT           hStmt;

/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);

/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);

/* 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 Row Count: %d\n", DAM, rowCount);
        rc = SQLFreeStmt(hStmt, SQL_DROP);
        if (rowCount >= 1)
            {
            printf("PASS\n");
            SQLExecute ("INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);");

            TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
            QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");

            if (TOTAL >= QUOTA)
                {
                SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
                }
        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);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
}
}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

灯下孤影 2024-12-18 01:35:38

SQL 语法。
未执行的查询在语法上并不正确,全部是:

查询 1:

INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);

INSERT INTO 后的列列表必须不以表名作为前缀。
应如下所示:

INSERT INTO tblDownloads (DownloadIP, DownloadCount) VALUES('173.201.216.2', 1);

查询 2:

SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';

缺少 FROM 子句。
应如下所示:

SELECT tblDownloads.[DownloadCount] FROM tblDownloads WHERE tblDownloads.[DownloadIP] = '173.201.216.2';

查询 3:

SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';

同样,没有 FROM 子句。另外,逗号太多,WHERE子句中的表名错误。
应如下所示:

SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] = '173.201.216.2';

查询 4:

UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');

末尾有一个右大括号,没有左大括号。
应该看起来像这样:

UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2';

It is the SQL syntax.
The queries that are not executing are not syntactically correct, all of them:

Query 1:

INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);

The column list after INSERT INTO must be without the table name as prefix.
Should look like this:

INSERT INTO tblDownloads (DownloadIP, DownloadCount) VALUES('173.201.216.2', 1);

Query 2:

SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';

Missing FROM clause.
Should look like this:

SELECT tblDownloads.[DownloadCount] FROM tblDownloads WHERE tblDownloads.[DownloadIP] = '173.201.216.2';

Query 3:

SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';

Again, no FROMclause. Additionally, a comma too much and the table name in the WHEREclause is wrong.
Should look like this:

SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] = '173.201.216.2';

Query 4:

UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');

One closing brace at the end without an opening brace.
Should look like this:

UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2';
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文