ODBC：如何在执行SQL插入操作时返回主键列的自增值和其它列的默认生成值

        执行SQL插入操作时，如果能将主键列的自增值和其它列的默认生成值一同返回，将极大的提高SQL的执行效率。我们知道，如果是编写存储过程的话，是可以解决这个问题的。但编写存储过程的问题是需要单独编写并在数据库中创建，如果表比较多的话不太好维护。那是否可以不用存储过程，只用一条SQL语句就可以解决这个问题呢？下面我们使用MS SQL数据库和ODBC驱动看怎么解决这个问题。

假设有如下表：

CREATE TABLE tb_test(
    id          bigint      IDENTITY(1,1) PRIMARY KEY,   --id，自增主键
    name        varchar(50) NOT NULL,                    --名字
    crt_dtm     datetime    DEFAULT (sysdatetime()),     --创建时间，插入时会赋当前默认时间
    opt_dtm     datetime    NULL,                        --操作时间
    size        int         NOT NULL                     --大小
)

 其中，id是自增主键列，crt_dtm在用户未输入时提供当前时间作为默认值。一般会执行的SQL语句如下：

insert into tb_test (name, opt_dtm, size) values (?,?,?)

但这条语句本身不会返回id和crt_dtm的值，所以我们要借助MS SQL的表变量和output语句将这两个值取出。

declare @t table (id bigint, crt_dtm datetime); 
INSERT INTO tb_test(name, opt_dtm, size) OUTPUT INSERTED.id, INSERTED.crt_dtm INTO @t VALUES (?, ?, ?); 
SELECT id, crt_dtm FROM @t;

首先，创建表变量t，其中定义了id和crt_dtm两个字段，用于将insert后生成的值存放到这两个字段中；

然后，在insert语句中使用OUTPUT语句，将insert后生成的id和crt_dtm的值保存到表变量t对应的列中；

最后，查询表变量t，将id和crt_dtm两个字段的值返回。

下面来看看如何使用ODBC来执行此SQL语句：

void execSql(SQLHSTMT hstmt, SQLCHAR* name, SQL_TIMESTAMP_STRUCT optDtm, SQLUINTEGER size) {
  SQLRETURN retCode;
  
  SQLLEN cbSize = 0, cbOptDtm = 0, cbName = SQL_NTS;
  
  retCode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 100, 0, (SQLPOINTER)name, 0, &cbName);
  retCode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 30, 0, &optDtm, 0, &cbOptDtm);
  retCode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT, 20, 0, &size, 0, &cbSize);
  
  SQLWCHAR* sql = (SQLWCHAR*)L"declare @t table (id bigint, crt_dtm datetime); 
INSERT INTO tb_test(name, opt_dtm, size) OUTPUT INSERTED.id, INSERTED.crt_dtm INTO @t VALUES (?, ?, ?); 
SELECT id, crt_dtm FROM @t;";
  
  retCode = SQLPrepare(hstmt, sql, SQL_NTS);
  retCode = SQLExecute(hstmt);
  
  long id = 0;
  SQL_TIMESTAMP_STRUCT crtDtm;
  SQLLEN cbId = 0, cbCrtDtm = 0;
  
  retCode = SQLBindCol(hstmt, 1, SQL_C_SLONG, &id, 8, &cbId);
  retCode = SQLBindCol(hstmt, 2, SQL_C_TYPE_TIMESTAMP, &crtDtm, sizeof(crtDtm), &cbCrtDtm);
  
  retCode = SQLMoreResults(hstmt);  // (1)
  
  retCode = SQLFetch(hstmt);
  
  std::stringstream ss;
  ss << crtDtm.year << "-" << crtDtm.month << "-" << crtDtm.day << " " << crtDtm.hour << ":" << crtDtm.minute << ":" << crtDtm.second << "." << crtDtm.fraction;
  
  std::cout << std::to_string(id) << ":" << ss.str() << std::endl;
}

此函数执行完成后就可以把最新的id和crtDtm的值取出。

      这段代码中最关键的是(10)这行代码，由于sql中包含一个insert和一个select，所以返回结果是两个结果集，如果要取到select的返回结果集，就需要调用SQLMoreResults移动到下一个结果集。

 

参考文档

SQLMoreResults 函数
OUTPUT 子句 (Transact-SQL)