ODBC: Accessing a Resultset from a Stored Procedure

Accessing a Resultset from a Stored Procedure

Stored procedures and user-defined functions are collections of SQL statements and optional control-of-flow statements written in the Recital 4GL (compatible with VFP) stored under a name and saved in a Database. Both stored procedures and user-defined functions are just-in-time compiled by the Recital database engine.

Stored Procedures can return a Resultset using the 4GL SETRESULTSET() function. The ODBC RECITAL command is used to execute the 4GL Stored Procedure and return the Resultset to the client application for processing. Resultset that are returned from Stored Procedures are read-only.

In this example, the getexamplecursor.prg stored procedure is in the 'southwind' database on the server. It accepts a parameter and runs a query, saving the results into a cursor. This cursor is then returned as a Resultset using the SETRESULTSET() 4GL function.

// Stored Procedure getexamplecursor.prg
lparameters lcAccountNo
exec sql
select account_no from example
where account_no = lcAccountNo
into cursor curExample;
return setresultset("curExample")
// end of getexamplecursor.prg

A DSN has been set up to access the southwind database as follows:

[southwind]
Driver = Recital
DATABASE = ODBC:RECITAL:SERVERNAME=?;USERNAME=?;
PASSWORD=?;DATABASE=southwind;

The C program establishes the ODBC connection, calls the Stored Procedure and displays the data.

/* Example C program to call stored procedure and display data from returned resultset */
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <malloc.h>
#include <fcntl.h>
#include <sys/types.h>
#include <sys/stat.h>
 
static	int get_vardata(
		SQLHSTMT		hstmt);
static	void show_error(
		int					line,
SQLSMALLINT	HandleType,
SQLHSTMT		hstmt);
 
#define TRUE 1
#define NAME_LEN 6
 
int main(int argc, char* argv[])
{
	SQLHENV		henv;
	SQLHDBC		hdbc;
	SQLHSTMT		hstmt;
	SQLRETURN	retcode;
 
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
 
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
			retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
 
			if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
				 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)5, 0);
 
				retcode = SQLConnect(hdbc, (SQLCHAR*) "southwind", SQL_NTS,
					(SQLCHAR*) "?", SQL_NTS,
					(SQLCHAR*) "?", SQL_NTS);
 
				if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
					retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 
 
					if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
						retcode = get_vardata(hstmt);
 
						SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
					}
					SQLDisconnect(hdbc);
				} else {
					show_error(__LINE__,SQL_HANDLE_DBC, hdbc);
				}
				SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
			}
		}
	} else {
		show_error(__LINE__,SQL_HANDLE_ENV, henv);
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
	}
 
	return 0;
}
 
static	int get_vardata(SQLHSTMT hstmt)
{
SQLCHAR		szName[NAME_LEN];
SQLINTEGER	cbName;
SQLRETURN	retcode;
 
retcode = SQLExecDirect(hstmt,"recital getexamplecursor('0001')", SQL_NTS);
if (retcode == SQL_SUCCESS) {
while (TRUE) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
show_error(__LINE__,SQL_HANDLE_STMT, hstmt);
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
SQLGetData(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName);
fprintf(stdout, "%sn", szName);
} else {
break;
}
}
}
return(retcode);
}
 
static	void	show_error(int line, SQLSMALLINT HandleType, SQLHSTMT hstmt)
{
	SQLINTEGER NativeErrorPtr;
	unsigned	char	Sqlstate[6];
	unsigned	 char	MessageText[512];
	SQLSMALLINT	TextLengthPtr;
 
	SQLGetDiagRec(HandleType,
			hstmt,
			1,
			Sqlstate,
			&NativeErrorPtr,
		 	MessageText,
			512,
		 	&TextLengthPtr);
	fprintf(stdout, "%d:%sn",line, MessageText);
}