View source for JDBC: 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 '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.


<code lang="recital">
// Stored Procedure getexamplecursor.prg
lparameters lcAccountNo
select * from example;
 where account_no = lcAccountNo;
 into cursor curExample
return setresultset("curExample")
// end of getexamplecursor.prg
</code>


The Java program establishes the JDBC connection, calls the Stored Procedure and displays the data.


<code lang="java">
import java.sql.*;
import java.io.*;
import java.net.URL;
import Recital.sql.*;

public class rs_example {

	public static void main(String[] args) {

		int	i;
		ResultSet rs;
		ResultSetMetaData rsmd;
		String s;

		System.out.println("Recital resultset example program started.");
		try {
			new RecitalDriver();
			String url = "jdbc:Recital:" +
			"SERVERNAME=?;" +
			"DATABASE=southwind;" +
			"USERNAME=?;" +
			"PASSWORD=?";
			Connection con = DriverManager.getConnection(url);
			Statement stmt = con.createStatement();

			rs = stmt.executeQuery("recital 	getexamplecursor(‘0001’)");
			rsmd = rs.getMetaData();
			int nr_cols = rsmd.getColumnCount();
			while (rs.next()) {
				for (i = 1; i <= nr_cols; i++) {
					s = rs.getString(i);
					System.out.println(rsmd.getColumnName(i) +" ("+ rsmd.getColumnTypeName(i)+") = "+s);
				}
				System.out.println("***** Next Record *****");
			}
			System.out.println("End of results:");
			stmt.close();
			con.close();
		} catch (Exception e) {
			System.out.flush();
			System.err.flush();
			DriverManager.println("Driver exception: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			System.out.println("Press any key to continue...");
			System.in.read();
		} catch(IOException ie) {
			;
		}
	}
}
</code>

[[Category:Documentation]]
[[Category:Reference]]
[[Category:JDBC|Resultset]]