Retrieving Data Through ODBC
To retrieve data through ODBC, you execute a query that returns a result set (SELECT, for example), then retrieve the results using one of two methods:
- Use the
SQLFetch()
function to retrieve a row of the result set, then access column values in the row by callingSQLGetData()
. - Use the
SQLBindColumn()
function to bind a variable or array to a column in the result set, then callSQLExtendedFetch()
orSQLFetchScroll()
to read a row of the result set and insert its values into the variable or array.
In both methods you loop through the result set until you either reach the end (signaled by the SQL_NO_DATA return status) or encounter an error.
Note: Vertica supports one cursor per connection. Attempting to use more than one cursor per connection will result in an error. For example, you receive an error if you execute a statement while another statement has a result set open.
The following code example demonstrates retrieving data from Vertica by:
- Connecting to the database.
- Executing a SELECT statement that returns the IDs and names of all tables.
- Binds two variables to the two columns in the result set.
- Loops through the result set, printing the ids and name values.
// Demonstrate running a query and getting results by querying the tables // system table for a list of all tables in the current schema. // Some standard headers #include <stdlib.h> #include <sstream> #include <iostream> #include <assert.h> // Standard ODBC headers #include <sql.h> #include <sqltypes.h> #include <sqlext.h> // Use std namespace to make output easier using namespace std; // Helper function to print SQL error messages. template <typename HandleT> void reportError(int handleTypeEnum, HandleT hdl) { // Get the status records. SQLSMALLINT i, MsgLen; SQLRETURN ret2; SQLCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH]; SQLINTEGER NativeError; i = 1; cout << endl; while ((ret2 = SQLGetDiagRec(handleTypeEnum, hdl, i, SqlState, &NativeError, Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) { cout << "error record #" << i++ << endl; cout << "sqlstate: " << SqlState << endl; cout << "detailed msg: " << Msg << endl; cout << "native error code: " << NativeError << endl; } } int main() { // Set up the ODBC environment SQLRETURN ret; SQLHENV hdlEnv; ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv); assert(SQL_SUCCEEDED(ret)); // Tell ODBC that the application uses ODBC 3. ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER); assert(SQL_SUCCEEDED(ret)); // Allocate a database handle. SQLHDBC hdlDbc; ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc); assert(SQL_SUCCEEDED(ret)); // Connect to the database cout << "Connecting to database." << endl; const char* dsnName = "ExampleDB"; const char* userID = "dbadmin"; const char* passwd = "password123"; ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName, SQL_NTS,(SQLCHAR*)userID,SQL_NTS, (SQLCHAR*)passwd, SQL_NTS); if(!SQL_SUCCEEDED(ret)) { cout << "Could not connect to database" << endl; reportError<SQLHDBC>(SQL_HANDLE_DBC, hdlDbc); exit(EXIT_FAILURE); } else { cout << "Connected to database." << endl; } // Set up a statement handle SQLHSTMT hdlStmt; SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt); assert(SQL_SUCCEEDED(ret)); // Execute a query to get the names and IDs of all tables in the schema // search p[ath (usually public). ret = SQLExecDirect( hdlStmt, (SQLCHAR*)"SELECT table_id, table_name " "FROM tables ORDER BY table_name", SQL_NTS ); if(!SQL_SUCCEEDED(ret)) { // Report error an go no further if statement failed. cout << "Error executing statement." << endl; reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt); exit(EXIT_FAILURE); } else { // Query succeeded, so bind two variables to the two colums in the // result set, cout << "Fetching results..." << endl; SQLBIGINT table_id; // Holds the ID of the table. SQLTCHAR table_name[256]; // buffer to hold name of table ret = SQLBindCol(hdlStmt, 1, SQL_C_SBIGINT, (SQLPOINTER)&table_id, sizeof(table_id), NULL); ret = SQLBindCol(hdlStmt, 2, SQL_C_TCHAR, (SQLPOINTER)table_name, sizeof(table_name), NULL); // Loop through the results, while( SQL_SUCCEEDED(ret = SQLFetchScroll(hdlStmt, SQL_FETCH_NEXT,1))) { // Print the bound variables, which now contain the values from the // fetched row. cout << table_id << " | " << table_name << endl; } // See if loop exited for reasons other than running out of data if (ret != SQL_NO_DATA) { // Exited for a reason other than no more data... report the error. reportError<SQLHDBC>( SQL_HANDLE_STMT, hdlStmt ); } } // Clean up by shutting down the connection cout << "Free handles." << endl; ret = SQLDisconnect( hdlDbc ); if(!SQL_SUCCEEDED(ret)) { cout << "Error disconnecting. Transaction still open?" << endl; exit(EXIT_FAILURE); } SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt); SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc); SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv); exit(EXIT_SUCCESS); }
Running the example code in the vmart database produces output similar to this:
Connecting to database. Connected to database. Fetching results... 45035996273970908 | call_center_dimension 45035996273970836 | customer_dimension 45035996273972958 | customers 45035996273970848 | date_dimension 45035996273970856 | employee_dimension 45035996273970868 | inventory_fact 45035996273970904 | online_page_dimension 45035996273970912 | online_sales_fact 45035996273970840 | product_dimension 45035996273970844 | promotion_dimension 45035996273970860 | shipping_dimension 45035996273970876 | store_dimension 45035996273970894 | store_orders_fact 45035996273970880 | store_sales_fact 45035996273972806 | t 45035996273970852 | vendor_dimension 45035996273970864 | warehouse_dimension Free handles.