SQL Statements

SELECT Statements

To execute an SQL statement, first create an environment handle and a connection handle using SQLAllocHandle. Then, use SQLDriverConnect to connect to the database. Once a connection has been successfully created, you can allocate a statement handle and execute the statement. Use SQLExecDirect to execute the statement and SQLFetch to fetch the records.

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); 
  SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
  SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
  SQLDriverConnect(hdbc, 0, (SQLTCHAR*)_T("Dsn=RSSBus NetSuite Source"), SQL_NTS, 0, 0, 0, 0);
  SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  SQLExecDirect(hstmt, (SQLTCHAR*)"SELECT AcctName FROM Account", SQL_NTS);  
  SQLTCHAR strAcctName[20] = {0};
  SQLLEN strAcctNameLen = 0;
  SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLTCHAR*)strColumnA, 20, &strAcctName);
  SQLRETURN retcode = SQLFetch(hstmt);
  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
     ...
  }
  ...

You can also use the SQLGetData function to get data instead of binding the data buffer with SQLBindCol.

  SQLTCHAR strAcctName[20] = {0};
  SQLINTEGER strAcctName = 0;
  SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLTCHAR*)strColumnA, 20, &strAcctName);

See Code Samples for an example with a complete sequence of calls.

INSERT, UPDATE, and DELETE Statements

Use SQLExecDirect to execute INSERT, UPDATE, and DELETE statements and use the SQLRowCount function to obtain the affected rows.

  ...
  SQLExecDirect(hstmt, (SQLTCHAR*)"INSERT INTO Account (AcctName) VALUES ('Petty Cash')", SQL_NTS);  
  SQLINTEGER  rowCount = 0;
  SQLRowCount(hstmt, &rowCount);

Parameterized Statements

Use SQLBindParameter to bind a variable to a parameter in the statement. Then, call SQLExecDirect to execute the query. The parameters are represented by the ? character. If there are parameters in the query, SQLExecDirect will not execute the query until all the parameters are supplied. You can then call SQLParamData while it is returning SQL_NEED_DATA and supply the parameters using SQLPutData.

  SQLCHAR param1[20] = {0};
  SQLINTEGER paramlen1 = SQL_LEN_DATA_AT_EXEC(0);
  SQLPOINTER pData = 0;
  SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)param1, 20, &paramlen1);
  SQLExecDirect(hstmt, (SQLTCHAR*)_T("INSERT INTO Account (AcctName) VALUES (?)"), SQL_NTS);
  while(SQL_NEED_DATA == SQLParamData(hstmt, &pData)) {
	SQLPutData(hstmt, pData, SQL_NTS);
  }

See Code Samples for an example with a complete sequence of calls.

Error Handling

All ODBC API functions return an error code indicating success or failure. After an error occurs, you can use the SQLError function to obtain the last error information, which contains the error code, the error state, and the error message. The SQLGetDiagField and the SQLGetDiagRec methods can return error information as well.

  SQLTCHAR state[6];
  SQLINTEGER code;
  SQLTCHAR message[255];
  SQLSMALLINT messageLen;
  SQLError(0, 0, hstmt, state, &code, message, 255, &messageLen);