C++ SQLAllocStmt函数代码示例

/*************************************************************************
 *
 *	Function: sql_query
 *
 *	Purpose: Issue a non-SELECT query (ie: update/delete/insert) to
 *	       the database.
 *
 *************************************************************************/
static sql_rcode_t sql_query(rlm_sql_handle_t *handle, rlm_sql_config_t *config, char const *query) {

	rlm_sql_iodbc_conn_t *conn = handle->conn;
	SQLRETURN rcode;

	rcode = SQLAllocStmt(conn->dbc_handle,
			     &conn->stmt_handle);
	if (!SQL_SUCCEEDED(rcode)) {
		ERROR("sql_create_socket: SQLAllocStmt failed:  %s",
				sql_error(handle, config));
		return -1;
	}

	if (!conn->dbc_handle) {
		ERROR("sql_query:  Socket not connected");
		return -1;
	}

	{
		SQLCHAR *statement;

		memcpy(&statement, &query, sizeof(statement));
		rcode = SQLExecDirect(conn->stmt_handle, statement, SQL_NTS);
	}

	if (!SQL_SUCCEEDED(rcode)) {
		ERROR("sql_query: failed:  %s",
				sql_error(handle, config));
		return -1;
	}

	return 0;
}

ResultSet_T SqlServerConnection_executeQuery(T C, const char *sql, va_list ap) {
	va_list ap_copy;
	const char *tail;
	SQLHSTMT hstmt;

	assert(C);
	StringBuffer_clear(C->sb);
	va_copy(ap_copy, ap);
	StringBuffer_vappend(C->sb, sql, ap_copy);
	va_end(ap_copy);

	C->lastError = SQLAllocStmt(C->db->hdbc,&hstmt);


	C->lastError = SQLPrepare(hstmt,
		StringBuffer_toString(C->sb), StringBuffer_length(C->sb)); 
	if(!SQLSERVERSUCCESS(C->lastError)) {
		getSqlErr(C,hstmt);
		return NULL;
	}
	C->lastError = SQLExecute(hstmt);
	if(SQLSERVERSUCCESS(C->lastError))
		return ResultSet_new(SqlServerResultSet_new(hstmt, C->maxRows, false), (Rop_T)&sqlserverrops);
	else {
		getSqlErr(C,hstmt);
	}
	return NULL;
}

int main(int argc, char **argv)
{
	int rc;
	HSTMT hstmt = SQL_NULL_HSTMT;
	char sql[100000];
	char *sqlend;
	int i;

	test_connect();

	rc = SQLAllocStmt(conn, &hstmt);
	if (!SQL_SUCCEEDED(rc))
	{
		print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
		exit(1);
	}

	rc = SQLTables(hstmt, "", SQL_NTS,
				   "public", SQL_NTS,
				   "%", SQL_NTS,
				   "TABLE", SQL_NTS);

	CHECK_STMT_RESULT(rc, "SQLTables failed", hstmt);
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();
}

void
odbc_reset_statement_proc(SQLHSTMT *stmt, const char *file, int line)
{
	SQLFreeStmt(*stmt, SQL_DROP);
	*stmt = SQL_NULL_HSTMT;
	odbc_check_res(file, line, SQLAllocStmt(odbc_conn, stmt), SQL_HANDLE_DBC, odbc_conn, "SQLAllocStmt", "S");
}

/* We allocate a new hstmt for this query, and leave it sitting in the
   db->hstmt slot.

   We need to free these things eventually, but that is the job of
   gsql_store_result() and gsql_free_result().  gsql_store_result() copies
   the hstmt pointer to a gsql_result structure, and sets the db->hstmt
   slot to NULL.  gsql_free_result is responsible for calling SQLFreeStmt().

   Thus, a call to gsql_query() should always be followed by a call to
   gsql_store_result(), and the result must eventually be freed using
   gsql_free_result(). */
static int
gsql_query (Database *db, char *query, int save_errors_p)
{
  pagefunc_set_variable ("odbc::recent-query", query);
  pagefunc_set_variable ("gsql::recent-query", query);

  if (db->connected)
    {
      /* If there is an old hstmt sitting here, free it. */
      if (db->hstmt)
	SQLFreeStmt (db->hstmt, SQL_DROP);

      if (SQLAllocStmt (db->hdbc, &(db->hstmt)) != SQL_SUCCESS)
	return (GSQL_ERROR);

      if (SQLExecDirect (db->hstmt, (UCHAR *) query, SQL_NTS) != SQL_SUCCESS)
	{
	  if (save_errors_p)
	    gsql_save_error_message (db, GSQL_DEFAULT_ERRMSG);
	  return (GSQL_ERROR);
	}
      else
	return (GSQL_SUCCESS);
    }
  else
    return (GSQL_ERROR);
}

unsigned CMssqlConnection::GetInsertIdentity(const char *table_hint)
{
	HSTMT hStmt;

	m_lasterror=SQLAllocStmt(m_hDbc,&hStmt);
	if(!SQL_SUCCEEDED(m_lasterror))
		return 0;

	m_lasterror=SQLExecDirect(hStmt,(SQLWCHAR*)L"SELECT @@IDENTITY",SQL_NTS);
	if(!SQL_SUCCEEDED(m_lasterror))
	{
		SQLFreeStmt(hStmt,SQL_DROP);
		return 0;
	}

	long id;
	SQLINTEGER len;

	m_lasterror=SQLBindCol(hStmt,1,SQL_C_LONG,&id,sizeof(id),&len);
	if(!SQL_SUCCEEDED(m_lasterror))
	{
		SQLFreeStmt(hStmt,SQL_DROP);
		return 0;
	}

	m_lasterror=SQLFetch(hStmt);
	if(!SQL_SUCCEEDED(m_lasterror))
		return 0;

	SQLFreeStmt(hStmt,SQL_DROP);

	return (unsigned)id;
}

int MADB_KeyTypeCount(MADB_Dbc *Connection, char *TableName, int KeyFlag)
{
  int Count= 0;
  unsigned int i;
  char StmtStr[1024];
  char *p= StmtStr;
  char Database[65];
  SQLHSTMT Stmt= NULL;
  MADB_Stmt *KeyStmt;
  
  SQLGetConnectAttr((SQLHDBC)Connection, SQL_ATTR_CURRENT_CATALOG, Database, 65, NULL);
  p+= my_snprintf(p, 1024, "SELECT * FROM ");
  if (Database)
    p+= my_snprintf(p, 1024 - strlen(p), "`%s`.", Database);
  p+= my_snprintf(p, 1024 - strlen(p), "%s LIMIT 0", TableName);
  if (SQLAllocStmt((SQLHDBC)Connection, &Stmt) == SQL_ERROR ||
      SQLPrepare(Stmt, (SQLCHAR *)StmtStr, SQL_NTS) == SQL_ERROR ||
      SQLExecute(Stmt) == SQL_ERROR ||
      SQLFetch(Stmt) == SQL_ERROR)
      goto end;
  KeyStmt= (MADB_Stmt *)Stmt;
  for (i=0; i < mysql_stmt_field_count(KeyStmt->stmt); i++)
    if (KeyStmt->stmt->fields[i].flags & KeyFlag)
      Count++;
end:
  if (Stmt)
    SQLFreeHandle(SQL_HANDLE_STMT, Stmt);
  return Count;
}

static void
newStatement(void)
{
    rc = SQLAllocStmt(hdbc, &hstmt);
    if(rc)
	errorPrint("@could not alloc singleton ODBC statement handle");
}				/* newStatement */

// Allocate environment handle, allocate connection handle,
// connect to data source, and allocate statement handle.
bool direxec::sqlconn(FILE* file)
{
	logFile = file;
	unsigned int timeout = 10; // seconds
	SQLAllocEnv(&henv);
	SQLAllocConnect(henv,&hdbc);
	SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, &timeout, 0);
	rc=SQLConnect(hdbc,chr_ds_name,SQL_NTS,NULL,0,NULL,0);
   
	// Deallocate handles, display error message, and exit.
	if (!MYSQLSUCCESS(rc))
	{
		SQLCHAR SqlState[6];
		SQLINTEGER NativeError;
		SQLSMALLINT MsgLen;

		SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, SqlState, &NativeError, (unsigned char *) szData, 2048, &MsgLen);
		SQLFreeEnv(henv);
		SQLFreeConnect(hdbc);

		fprintf(logFile, "! Error while connecting to database: %s\n", szData);
		errorstate = TRUE;
		return FALSE;
	}

	rc=SQLAllocStmt(hdbc,&hstmt);
	errorstate = FALSE;
	return TRUE;
}

/*************************************************************************
 *
 *	Function: sql_init_socket
 *
 *	Purpose: Establish connection to the db
 *
 *************************************************************************/
static int sql_init_socket(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
    rlm_sql_unixodbc_sock *unixodbc_sock;
    long err_handle;

	if (!handle->conn) {
		handle->conn = (rlm_sql_unixodbc_sock *)rad_malloc(sizeof(rlm_sql_unixodbc_sock));
		if (!handle->conn) {
			return -1;
		}
	}
	unixodbc_sock = handle->conn;
	memset(unixodbc_sock, 0, sizeof(*unixodbc_sock));

    /* 1. Allocate environment handle and register version */
    err_handle = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&unixodbc_sock->env_handle);
    if (sql_state(err_handle, handle, config))
    {
	radlog(L_ERR, "rlm_sql_unixodbc: Can't allocate environment handle\n");
	return -1;
    }
    err_handle = SQLSetEnvAttr(unixodbc_sock->env_handle, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    if (sql_state(err_handle, handle, config))
    {
	radlog(L_ERR, "rlm_sql_unixodbc: Can't register ODBC version\n");
	SQLFreeHandle(SQL_HANDLE_ENV, unixodbc_sock->env_handle);
	return -1;
    }
    /* 2. Allocate connection handle */
    err_handle = SQLAllocHandle(SQL_HANDLE_DBC, unixodbc_sock->env_handle, &unixodbc_sock->dbc_handle);
    if (sql_state(err_handle, handle, config))
    {
	radlog(L_ERR, "rlm_sql_unixodbc: Can't allocate connection handle\n");
	SQLFreeHandle(SQL_HANDLE_ENV, unixodbc_sock->env_handle);
	return -1;
    }

    /* 3. Connect to the datasource */
    err_handle = SQLConnect(unixodbc_sock->dbc_handle,
	(SQLCHAR*) config->sql_server, strlen(config->sql_server),
	(SQLCHAR*) config->sql_login, strlen(config->sql_login),
	(SQLCHAR*) config->sql_password, strlen(config->sql_password));
    if (sql_state(err_handle, handle, config))
    {
	radlog(L_ERR, "rlm_sql_unixodbc: Connection failed\n");
	SQLFreeHandle(SQL_HANDLE_DBC, unixodbc_sock->dbc_handle);
	SQLFreeHandle(SQL_HANDLE_ENV, unixodbc_sock->env_handle);
	return -1;
    }

    /* 4. Allocate the statement */
    err_handle = SQLAllocStmt(unixodbc_sock->dbc_handle, &unixodbc_sock->stmt_handle);
    if (sql_state(err_handle, handle, config))
    {
	radlog(L_ERR, "rlm_sql_unixodbc: Can't allocate the statement\n");
	return -1;
    }

    return 0;
}

/*************************************************************************
 *
 *	Function: sql_socket_init
 *
 *	Purpose: Establish connection to the db
 *
 *************************************************************************/
static sql_rcode_t sql_socket_init(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
    rlm_sql_unixodbc_conn_t *conn;
    long err_handle;

    MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_unixodbc_conn_t));
    talloc_set_destructor((void *) conn, sql_socket_destructor);

    /* 1. Allocate environment handle and register version */
    err_handle = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&conn->env);
    if (sql_state(err_handle, handle, config)) {
        ERROR("rlm_sql_unixodbc: Can't allocate environment handle\n");
        return -1;
    }

    err_handle = SQLSetEnvAttr(conn->env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    if (sql_state(err_handle, handle, config)) {
        ERROR("rlm_sql_unixodbc: Can't register ODBC version\n");
        SQLFreeHandle(SQL_HANDLE_ENV, conn->env);
        return -1;
    }

    /* 2. Allocate connection handle */
    err_handle = SQLAllocHandle(SQL_HANDLE_DBC, conn->env, &conn->dbc);
    if (sql_state(err_handle, handle, config)) {
        ERROR("rlm_sql_unixodbc: Can't allocate connection handle\n");
        SQLFreeHandle(SQL_HANDLE_ENV, conn->env);
        return -1;
    }

    /* 3. Connect to the datasource */
    {
        SQLCHAR *odbc_server, *odbc_login, *odbc_password;

        memcpy(&odbc_server, &config->sql_server, sizeof(odbc_server));
        memcpy(&odbc_login, &config->sql_login, sizeof(odbc_login));
        memcpy(&odbc_password, &config->sql_password, sizeof(odbc_password));
        err_handle = SQLConnect(conn->dbc,
                                odbc_server, strlen(config->sql_server),
                                odbc_login, strlen(config->sql_login),
                                odbc_password, strlen(config->sql_password));
    }

    if (sql_state(err_handle, handle, config)) {
        ERROR("rlm_sql_unixodbc: Connection failed\n");
        SQLFreeHandle(SQL_HANDLE_DBC, conn->dbc);
        SQLFreeHandle(SQL_HANDLE_ENV, conn->env);
        return -1;
    }

    /* 4. Allocate the statement */
    err_handle = SQLAllocStmt(conn->dbc, &conn->statement);
    if (sql_state(err_handle, handle, config)) {
        ERROR("rlm_sql_unixodbc: Can't allocate the statement\n");
        return -1;
    }

    return 0;
}

StoragePtr TableFunctionODBC::executeImpl(const ASTPtr & ast_function, const Context & context) const
{
    const ASTFunction & args_func = typeid_cast<const ASTFunction &>(*ast_function);

    if (!args_func.arguments)
        throw Exception("Table function 'odbc' must have arguments.", ErrorCodes::LOGICAL_ERROR);

    ASTs & args = typeid_cast<ASTExpressionList &>(*args_func.arguments).children;

    if (args.size() != 2)
        throw Exception("Table function 'odbc' requires exactly 2 arguments: ODBC connection string and table name.",
            ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH);

    for (int i = 0; i < 2; ++i)
        args[i] = evaluateConstantExpressionOrIdentifierAsLiteral(args[i], context);

    std::string connection_string = static_cast<const ASTLiteral &>(*args[0]).value.safeGet<String>();
    std::string table_name = static_cast<const ASTLiteral &>(*args[1]).value.safeGet<String>();

    Poco::Data::ODBC::SessionImpl session(connection_string, DBMS_DEFAULT_CONNECT_TIMEOUT_SEC);
    SQLHDBC hdbc = session.dbc().handle();

    SQLHSTMT hstmt = nullptr;

    if (Poco::Data::ODBC::Utility::isError(SQLAllocStmt(hdbc, &hstmt)))
        throw Poco::Data::ODBC::ODBCException("Could not allocate connection handle.");

    SCOPE_EXIT(SQLFreeStmt(hstmt, SQL_DROP));

    /// TODO Why not do SQLColumns instead?
    std::string query = "SELECT * FROM " + table_name + " WHERE 1 = 0";
    if (Poco::Data::ODBC::Utility::isError(Poco::Data::ODBC::SQLPrepare(hstmt, reinterpret_cast<SQLCHAR *>(&query[0]), query.size())))
        throw Poco::Data::ODBC::DescriptorException(session.dbc());

    if (Poco::Data::ODBC::Utility::isError(SQLExecute(hstmt)))
        throw Poco::Data::ODBC::StatementException(hstmt);

    SQLSMALLINT cols = 0;
    if (Poco::Data::ODBC::Utility::isError(SQLNumResultCols(hstmt, &cols)))
        throw Poco::Data::ODBC::StatementException(hstmt);

    /// TODO cols not checked

    NamesAndTypesList columns;
    for (SQLSMALLINT ncol = 1; ncol <= cols; ++ncol)
    {
        SQLSMALLINT type = 0;
        /// TODO Why 301?
        SQLCHAR column_name[301];
        /// TODO Result is not checked.
        Poco::Data::ODBC::SQLDescribeCol(hstmt, ncol, column_name, sizeof(column_name), NULL, &type, NULL, NULL, NULL);
        columns.emplace_back(reinterpret_cast<char *>(column_name), getDataType(type));
    }

    auto result = StorageODBC::create(table_name, connection_string, "", table_name, ColumnsDescription{columns});
    result->startup();
    return result;
}

static int  display_columns( SQLHDBC hDbc, char *sql )
{
    SQLHSTMT hStmt;
    SQLRETURN ret;
    char *args[10];
    int n_args;
    SQLCHAR *table;
    int len;

    if ( version3 )
    {
        if ( SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt ) != SQL_SUCCESS )
        {
            if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
            fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocHandle( SQL_HANDLE_STMT )\n" );
            return 0;
        }
    }
    else
    {
        if ( SQLAllocStmt( hDbc, &hStmt ) != SQL_SUCCESS )
        {
            if ( bVerbose ) DumpODBCLog( hEnv, hDbc, 0 );
            fprintf( stderr, "[ISQL]ERROR: Could not SQLAllocStmt\n" );
            return 0;
        }
    }

    n_args = get_args(sql, &args[0], sizeof(args) / sizeof(args[0]));

    if ( n_args == 0 )
    {
        table = NULL;
        len = 0;
    }
    else
    {
        table = (SQLCHAR*)args[ 0 ];
        len = SQL_NTS;
    }

    ret = SQLColumns( hStmt, NULL, 0, NULL, 0, table, len, NULL, 0 );
    if ( ret == SQL_ERROR )
    {
        if ( bVerbose ) DumpODBCLog( hEnv, hDbc, hStmt );
        fprintf( stderr, "[ISQL]ERROR: Could not SQLTables\n" );
    }
    else
    {
        display_result_set( hDbc, hStmt );
    }

    SQLFreeStmt( hStmt, SQL_DROP );
    free_args(args, sizeof(args) / sizeof(args[0]));

    return 1;
}

static HB_ERRCODE odbcExecute( SQLDDCONNECTION * pConnection, PHB_ITEM pItem )
{
   SDDCONN *         pSDDConn = ( SDDCONN * ) pConnection->pSDDConn;
   const O_HB_CHAR * pchStatement;
   void *            hStatement;
   HB_SIZE           nStatementLen;
   SQLHSTMT          hStmt;
   SQLLEN            iCount;
   char *            szError;
   HB_ERRCODE        errCode;
   SQLRETURN         result;

#if ODBCVER >= 0x0300
   if( ! SQL_SUCCEEDED( SQLAllocHandle( SQL_HANDLE_STMT, pSDDConn->hConn, &hStmt ) ) )
#else
   if( ! SQL_SUCCEEDED( SQLAllocStmt( pSDDConn->hConn, &hStmt ) ) )
#endif
   {
      szError = odbcGetError( pSDDConn->hEnv, pSDDConn->hConn, SQL_NULL_HSTMT, &errCode );
      hb_errRT_ODBCDD( EG_OPEN, ESQLDD_STMTALLOC, szError, hb_itemGetCPtr( pItem ), errCode );
      hb_xfree( szError );
      return HB_FAILURE;
   }

   pchStatement = O_HB_ITEMGETSTR( pItem, &hStatement, &nStatementLen );
   result = SQLExecDirect( hStmt,
                           ( SQLTCHAR * ) HB_UNCONST( pchStatement ),
                           ( SQLINTEGER ) nStatementLen );
   hb_strfree( hStatement );

   if( SQL_SUCCEEDED( result ) )
   {

      if( SQL_SUCCEEDED( SQLRowCount( hStmt, &iCount ) ) )
      {
         /* TODO: new id */
         hb_rddsqlSetError( 0, NULL, hb_itemGetCPtr( pItem ), NULL, ( unsigned long ) iCount );
#if ODBCVER >= 0x0300
         SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
#else
         SQLFreeStmt( hStmt, SQL_DROP );
#endif
         return HB_SUCCESS;
      }
   }

   szError = odbcGetError( pSDDConn->hEnv, pSDDConn->hConn, hStmt, &errCode );
   hb_rddsqlSetError( errCode, szError, hb_itemGetCPtr( pItem ), NULL, errCode );
   hb_xfree( szError );
#if ODBCVER >= 0x0300
   SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
#else
   SQLFreeStmt( hStmt, SQL_DROP );
#endif
   return HB_FAILURE;
}

void CheckEventJoin(CHARLIST *ch)		// 020115 LTS
{
	HSTMT	hStmt=NULL;
	RETCODE	ret;
	SWORD	nClos;
	char	query_stmt[80]={0,};
	SDWORD	cbValue;
	int		tempEventJoin[8];

	//ch->EventJoin=1;	// 나중에 나머지는 주석 처리 한다.

	SQLAllocStmt(hDBC,&hStmt);

	wsprintf(query_stmt,"select * from EventUser Where CharacterName='%s'",ch->Name);
	ret = SQLExecDirect(hStmt,(UCHAR*)query_stmt,SQL_NTS);
	if (ret!=SQL_SUCCESS_WITH_INFO && ret !=SQL_SUCCESS)
	{
		//MyLog(0,"Query Failure!! Event User Data.. (Not Join Event..)");
		SQLFreeStmt(hStmt,SQL_DROP);
		memset(&ch->EventJoin,0,1);
		return;
	}
	SQLNumResultCols(hStmt,&nClos);

	ret=SQLFetch(hStmt);
	if (ret!=SQL_SUCCESS_WITH_INFO && ret !=SQL_SUCCESS)
	{
		//MyLog(0,"Query Failure!! Event User Data.. (Not Join Event..)");
		SQLFreeStmt(hStmt,SQL_DROP);
		memset(&ch->EventJoin,0,1);
		return ;
	}
	for (int i=0;i<8;i++)
	{
		ret=SQLGetData(hStmt,3+i,SQL_C_SLONG,&tempEventJoin[i],sizeof(int),&cbValue);	// Event1
		if (ret!=SQL_SUCCESS_WITH_INFO && ret!=SQL_SUCCESS)
		{
			MyLog(0,"Query Failure!! Event User Data.. (Get Data..)",ret);
			SQLFreeStmt(hStmt,SQL_DROP);
			memset(&ch->EventJoin,0,1);
			return ;
		}   
	}

    SQLFreeStmt(hStmt,SQL_DROP);

	ch->EventJoin.Event1=tempEventJoin[0];
	ch->EventJoin.Event2=tempEventJoin[1];
	ch->EventJoin.Event3=tempEventJoin[2];
	ch->EventJoin.Event4=tempEventJoin[3];
	ch->EventJoin.Event5=tempEventJoin[4];
	ch->EventJoin.Event6=tempEventJoin[5];
	ch->EventJoin.Event7=tempEventJoin[6];
	ch->EventJoin.Event8=tempEventJoin[7];
}