c - 在 C 程序的 MS Access 查询中使用变量

Question

我正在编写一个小型 C(.c 文件)应用程序，它连接到 OPC 服务器客户端，然后将数据输出到 MS Access 数据库中。

我需要在使用变量时使查询正常工作。我的查询是:

"INSERT INTO Data ( [Date / Time], [Hot Strip Mill rate], [Hot Strip Mill Comm Okay], [Hot Strip Mill Total] )  SELECT #variable1# AS Expr1, variable2 AS Expr2, 1 AS Expr3, variable2 AS Expr4;"; 

变量 1 将是日期时间值，变量 2/3 将是数字。我计划将从 OPC 客户端检索到的值分配给变量 1、2、3。我希望/需要我的查询识别变量代表的值。所以操作应该如下:代码可见

 "INSERT INTO Data ( [Date / Time], [Hot Strip Mill rate], [Hot Strip Mill Comm Okay], [Hot Strip Mill Total] )  SELECT #variable1# AS Expr1, variable2 AS Expr2, 1 AS Expr3, variable2 AS Expr4;"; 

Access 看到

"INSERT INTO Data ( [Date / Time], [Hot Strip Mill rate], [Hot Strip Mill Comm Okay], [Hot Strip Mill Total] )  SELECT #07/01/2011 04:02:01# AS Expr1, 1 AS Expr2, 14.54 AS Expr3, 64546.14 AS Expr4;"; 

如有任何帮助，我们将不胜感激。谢谢

#include <windows.h>

#include <stdio.h>

#include <sqlext.h>

#include <sstream>

/* Data Access Method used in this sample */
const char* DAM = "Direct ODBC";

/* Connection string for Direct ODBC */
char szDSN[256] =     "Dsn=Gas_meter_check";

main()
{
HENV    hEnv;
HDBC    hDbc;

/* ODBC API return status */
RETCODE rc;
char date[256]= "2/1/1900 23:35:45";
double var2=46;
double var3=168736;
int     iConnStrLength2Ptr;
char    szConnStrOut[256];
int i= 0;

unsigned char* InsertQuery = "INSERT INTO Data ( [Date / Time], [Hot Strip Mill rate], [Hot Strip Mill Comm Okay], [Hot Strip Mill Total] ) SELECT #***date***# AS Expr1, ***var2*** AS Expr2, ***var3*** AS Expr3, 345464 AS Expr4;";

SQLCHAR         chval1[128], chval2[128], colName[128];
int             ret1;
int             ret2;

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

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

/* Connect to the TakeCharge database */
rc = SQLDriverConnect(hDbc, NULL, (unsigned char*)szDSN, 
    SQL_NTS, (unsigned char*)szConnStrOut, 
    255, (SQLSMALLINT*)&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 */
    printf("%s: SQL InsertQuery:\n  %s\n", DAM, InsertQuery);

    rc = SQLAllocStmt(hDbc,&hStmt);
    rc = SQLPrepare(hStmt, InsertQuery, SQL_NTS);

    /* Excecute the query and create a record set */
    rc = SQLExecute(hStmt); 
    if (SQL_SUCCEEDED(rc)) 
    {
       printf("Executing query...");
       printf("\n");
        }

        while (SQL_SUCCEEDED(rc)) 
        {
            printf(" insert passed\n");
            rc = SQLFetch(hStmt);
            rowCount++;
        };         
    }
else
{
    printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
}

/* Disconnect*/
SQLDisconnect(hDbc);


printf("%s: Cleanup. Done.\n", DAM);
}

Answer 1

要处理语句中的变量值，请使用 statement parameters 。您将一个值绑定(bind)到使用 SQLBindParameter 的参数。请注意，SELECT 列表中不允许使用参数，因此您需要使用 INSERT ... VALUES。首先，请尝试:

unsigned char* InsertQuery = "INSERT INTO Data ( [Date / Time], [Hot Strip Mill rate], [Hot Strip Mill Comm Okay], [Hot Strip Mill Total] ) VALUES (?, ?, ?, 345464);";
...
    rc = SQLPrepare(hStmt, InsertQuery, SQL_NTS);

    SQLSMALLINT sqlType, decDigits, nullable;
    SQLULEN paramSize;

    SQLDescribeParam(hStmt, 1, &sqlType, &paramSize, &decDigits, &nullable);
    SQLBindParameter(hStmt, 1, 
                     SQL_PARAM_INPUT, SQL_C_CHAR, sqlType,
                     0 /* ignored for timestamps */, decDigits,
                     (SQLPOINTER)&date, strlen(date), NULL);

    SQLDescribeParam(hStmt, 2, &sqlType, &paramSize, &decDigits, &nullable);
    SQLBindParameter(hStmt, 2, 
                     SQL_PARAM_INPUT, SQL_C_DOUBLE, sqlType,
                     paramSize, decDigits,
                     (SQLPOINTER)&rate, 0 /* ignored */, NULL);

    SQLDescribeParam(hStmt, 3, &sqlType, &paramSize, &decDigits, &nullable);
    SQLBindParameter(hStmt, 3, 
                     SQL_PARAM_INPUT, SQL_C_DOUBLE, sqlType,
                     paramSize, decDigits,
                     (SQLPOINTER)&commOkay, 0 /* ignored */, NULL);

    rc = SQLExecute(hStmt);

与示例代码中一样，我没有考虑错误处理。