Difference between revisions of "ODBC: Accessing a Resultset from a Stored Procedure"
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
(9 intermediate revisions by one user not shown) | |||
Line 1: | Line 1: | ||
− | + | 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 '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. | |
− | + | ||
− | Stored Procedures can return a Resultset using the 4GL [[SETRESULTSET()]] function. The | + | |
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. | 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. | ||
Line 11: | Line 9: | ||
// Stored Procedure getexamplecursor.prg | // Stored Procedure getexamplecursor.prg | ||
lparameters lcAccountNo | lparameters lcAccountNo | ||
− | + | select account_no from example; | |
− | select account_no from example | + | where account_no = lcAccountNo; |
− | where account_no = lcAccountNo | + | into cursor curExample |
− | into cursor curExample | + | |
return setresultset("curExample") | return setresultset("curExample") | ||
// end of getexamplecursor.prg | // end of getexamplecursor.prg | ||
Line 61: | Line 58: | ||
SQLRETURN retcode; | SQLRETURN retcode; | ||
− | retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); | + | retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); |
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { | if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { | ||
− | retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); | + | retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); |
− | if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { | + | if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { |
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); | retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); | ||
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { | 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, | retcode = SQLConnect(hdbc, (SQLCHAR*) "southwind", SQL_NTS, | ||
Line 100: | Line 97: | ||
static int get_vardata(SQLHSTMT hstmt) | static int get_vardata(SQLHSTMT hstmt) | ||
{ | { | ||
− | SQLCHAR szName[NAME_LEN]; | + | SQLCHAR szName[NAME_LEN]; |
− | SQLINTEGER cbName; | + | SQLINTEGER cbName; |
− | SQLRETURN retcode; | + | SQLRETURN retcode; |
− | retcode = SQLExecDirect(hstmt,"recital getexamplecursor(' | + | retcode = SQLExecDirect(hstmt,"recital getexamplecursor('00001')", SQL_NTS); |
− | if (retcode == SQL_SUCCESS) { | + | if (retcode == SQL_SUCCESS) { |
− | while (TRUE) { | + | while (TRUE) { |
− | retcode = SQLFetch(hstmt); | + | retcode = SQLFetch(hstmt); |
− | if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) { | + | if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) { |
− | show_error(__LINE__,SQL_HANDLE_STMT, hstmt); | + | show_error(__LINE__,SQL_HANDLE_STMT, hstmt); |
− | } | + | } |
− | if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){ | + | if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){ |
− | SQLGetData(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName); | + | SQLGetData(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName); |
− | fprintf(stdout, "%sn", szName); | + | fprintf(stdout, "%sn", szName); |
− | } else { | + | } else { |
− | break; | + | break; |
− | } | + | } |
− | } | + | } |
− | } | + | } |
− | return(retcode); | + | return(retcode); |
} | } | ||
Line 143: | Line 140: | ||
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:Reference]] | [[Category:Reference]] | ||
− | [[Category:ODBC]] | + | [[Category:ODBC|Resultset]] |
Latest revision as of 10:42, 4 February 2016
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 '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 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('00001')", 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); }