//The following example shows how to obtain data from openGauss through the ODBC interface.// DBtest.c (compile with: libodbc.so) #include <stdlib.h> #include <stdio.h> #include <sqlext.h>#ifdef WIN32#include <windows.h>#endif SQLHENV V_OD_Env; // Handle ODBC environment SQLHSTMT V_OD_hstmt; // Handle statement SQLHDBC V_OD_hdbc; // Handle connection char typename[100];SQLINTEGER value = 100;SQLINTEGER V_OD_erg,V_OD_buffer,V_OD_err,V_OD_id;int main(int argc,char *argv[]) { // 1. Allocate an environment handle. V_OD_erg = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error AllocHandle\n"); exit(0); } // 2. Set environment attributes (version information). SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); // 3. Allocate a connection handle. V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } // 4. Set connection attributes. SQLSetConnectAttr(V_OD_hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, 0); // 5. Connect to the data source. userName and password indicate the username and password for connecting to the database. Set them as needed. // If the username and password have been set in the odbc.ini file, you do not need to set userName or password here, retaining "" for them. However, you are not advised to do so because the username and password will be disclosed if the permission for odbc.ini is abused. V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "gaussdb", SQL_NTS, (SQLCHAR*) "userName", SQL_NTS, (SQLCHAR*) "password", SQL_NTS); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)) { printf("Error SQLConnect %d\n",V_OD_erg); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); exit(0); } printf("Connected !\n"); // 6. Set statement attributes. SQLSetStmtAttr(V_OD_hstmt,SQL_ATTR_QUERY_TIMEOUT,(SQLPOINTER *)3,0); // 7. Allocate a statement handle. SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt); // 8. Run SQL statements. SQLExecDirect(V_OD_hstmt,"drop table IF EXISTS customer_t1",SQL_NTS); SQLExecDirect(V_OD_hstmt,"CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));",SQL_NTS); SQLExecDirect(V_OD_hstmt,"insert into customer_t1 values(25,li)",SQL_NTS); // 9. Prepare for execution. SQLPrepare(V_OD_hstmt,"insert into customer_t1 values(?)",SQL_NTS); // 10. Bind parameters. SQLBindParameter(V_OD_hstmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0, &value,0,NULL); // 11. Run prepared statements. SQLExecute(V_OD_hstmt); SQLExecDirect(V_OD_hstmt,"select id from testtable",SQL_NTS); // 12. Obtain attributes of a specific column in the result set. SQLColAttribute(V_OD_hstmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL); printf("SQLColAtrribute %s\n",typename); // 13. Bind the result set. SQLBindCol(V_OD_hstmt,1,SQL_C_SLONG, (SQLPOINTER)&V_OD_buffer,150, (SQLLEN *)&V_OD_err); // 14. Obtain data in the result set by executing SQLFetch. V_OD_erg=SQLFetch(V_OD_hstmt); // 15. Obtain and return data by executing SQLGetData. while(V_OD_erg != SQL_NO_DATA) { SQLGetData(V_OD_hstmt,1,SQL_C_SLONG,(SQLPOINTER)&V_OD_id,0,NULL); printf("SQLGetData ----ID = %d\n",V_OD_id); V_OD_erg=SQLFetch(V_OD_hstmt); }; printf("Done !\n"); // 16. Disconnect data source connections and release handles. SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt); SQLDisconnect(V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc); SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); return(0); }
#include <stdio.h>#include <stdlib.h>#include <libpq-fe.h>static void exit_nicely(PGconn *conn){ PQfinish(conn); exit(1);}int main(int argc, char **argv){ const char *conninfo; PGconn *conn; PGresult *res; int nFields; int i,j; /* * If a user provides a parameter on the command line, it is used as the conninfo string. * Otherwise, the environment variables or the default values are used for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Connect to the database. */ conn = PQconnectdb(conninfo); /* Check whether the backend connection was successfully established. */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } /* * Since a cursor is used in our test case, we must use a transaction block. * We can use a single PQexec() of "select * from pg_database" to complete the entire thing, * but it is too simple to be a good example. */ /* Start a transaction block. */ res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* Execute the PQclear statement when PGresult is no longer needed, to avoid memory leakage. */ PQclear(res); /* Obtain data from the pg_database system catalog. */ res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn, "FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* First, print out the attribute name. */ nFields = PQnfields(res); for (i = 0; i < nFields; i++) printf("%-15s", PQfname(res, i)); printf("\n\n"); /* Print out the rows. */ for (i = 0; i < PQntuples(res); i++) { for (j = 0; j < nFields; j++) printf("%-15s", PQgetvalue(res, i, j)); printf("\n"); } PQclear(res); /* Close the portal. We do not need to check for errors. */ res = PQexec(conn, "CLOSE myportal"); PQclear(res); /* End the transaction. */ res = PQexec(conn, "END"); PQclear(res); /* Close the database connection and clean up the database. */ PQfinish(conn); return 0;}