/**********************************************************************
* FILENAME : ListProcedures.c
*
* DESCRIPTION :
* Example uses SQLProcedures to return a list of procedure names
* stored in a specific data source.
*
* ODBC USAGE :
* selectDSN() to get data source name
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
// Arbitary sizes for char buffs
#define BUFF_SIZE 255
// Declare buffers for result set data
SQLCHAR strProcedureCat[BUFF_SIZE];
SQLCHAR strProcedureSchema[BUFF_SIZE];
SQLCHAR strProcedureName[BUFF_SIZE];
SQLSMALLINT ProcedureType;
SQLLEN lenProcedureCat, lenProcedureSchema, lenProcedureName, lenProcedureType;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
char procName[BUFF_SIZE];
char dsn[BUFF_SIZE];
char confirm='N';
char reply=' ';
int header;
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr (SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr (SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// prompt for DSN
retcode = selectDSN (henv, dsn, "Select DSN : ");
if (retcode != SQL_SUCCESS) {
printf ("DSN not selected, exiting.");
goto exit;
}
retcode = SQLConnect(hdbc, (SQLCHAR*) dsn, SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect (SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle (SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
getStr ("Procedure Name", procName, sizeof (procName), 'N');
printf ("\nProc Name : %s\n", procName);
if (strcmp(procName, "*")==0) {
retcode = SQLProcedures (hstmt,
NULL, 0,
NULL, 0,
NULL, 0); // this will return all procedures
} else {
retcode = SQLProcedures (hstmt,
NULL, 0,
NULL, 0,
procName, strlen(procName));
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Bind columns in result set to buffers
SQLBindCol(hstmt, 1, SQL_C_CHAR,
strProcedureCat,
sizeof(strProcedureCat),
&lenProcedureCat);
SQLBindCol(hstmt, 2, SQL_C_CHAR,
strProcedureSchema,
sizeof(strProcedureSchema),
&lenProcedureSchema);
SQLBindCol(hstmt, 3, SQL_C_CHAR,
strProcedureName,
sizeof(strProcedureName),
&lenProcedureName);
SQLBindCol(hstmt, 8, SQL_C_SHORT,
&ProcedureType,
sizeof(ProcedureType),
&lenProcedureType);
header=0;
while (SQL_SUCCESS == retcode) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
extract_error("xSQLSpecialColumns Error : ",
hstmt, SQL_HANDLE_STMT);
}
if (header++==0) {
printf ("\nDSN : %s\n",dsn);
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf ("\nProcedure Cat : %s\n", strProcedureCat);
printf ("Procedure Schema : %s\n", strProcedureSchema);
printf ("Procedure Name : %s\n", strProcedureName);
printf ("Procedure Type : %i\n", (int) ProcedureType);
}
if (retcode==SQL_NO_DATA && header==1) {
printf ("(NO DATA)\n");
}
}
}
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
See Also
