Difference between revisions of "JDBC: Accessing a Resultset from a Stored Procedure"
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
(3 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 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 | + | 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 | + | 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 10: | Line 9: | ||
// Stored Procedure getexamplecursor.prg | // Stored Procedure getexamplecursor.prg | ||
lparameters lcAccountNo | lparameters lcAccountNo | ||
− | + | select * from example; | |
− | select * from example | + | where account_no = lcAccountNo; |
− | where account_no = lcAccountNo | + | into cursor curExample |
− | into cursor curExample | + | return setresultset("curExample") |
− | return setresultset( | + | |
// end of getexamplecursor.prg | // end of getexamplecursor.prg | ||
</code> | </code> | ||
Line 37: | Line 35: | ||
String s; | String s; | ||
− | System.out.println( | + | System.out.println("Recital resultset example program started."); |
try { | try { | ||
new RecitalDriver(); | new RecitalDriver(); | ||
− | String url = | + | String url = "jdbc:Recital:" + |
− | + | "SERVERNAME=?;" + | |
− | + | "DATABASE=southwind;" + | |
− | + | "USERNAME=?;" + | |
− | + | "PASSWORD=?"; | |
Connection con = DriverManager.getConnection(url); | Connection con = DriverManager.getConnection(url); | ||
Statement stmt = con.createStatement(); | Statement stmt = con.createStatement(); | ||
− | rs = stmt.executeQuery( | + | rs = stmt.executeQuery("recital getexamplecursor(‘0001’)"); |
rsmd = rs.getMetaData(); | rsmd = rs.getMetaData(); | ||
int nr_cols = rsmd.getColumnCount(); | int nr_cols = rsmd.getColumnCount(); | ||
Line 54: | Line 52: | ||
for (i = 1; i <= nr_cols; i++) { | for (i = 1; i <= nr_cols; i++) { | ||
s = rs.getString(i); | s = rs.getString(i); | ||
− | + | System.out.println(rsmd.getColumnName(i) +" ("+ rsmd.getColumnTypeName(i)+") = "+s); | |
} | } | ||
System.out.println("***** Next Record *****"); | System.out.println("***** Next Record *****"); | ||
} | } | ||
− | System.out.println( | + | System.out.println("End of results:"); |
stmt.close(); | stmt.close(); | ||
con.close(); | con.close(); | ||
Line 64: | Line 62: | ||
System.out.flush(); | System.out.flush(); | ||
System.err.flush(); | System.err.flush(); | ||
− | DriverManager.println( | + | DriverManager.println("Driver exception: " + e.getMessage()); |
e.printStackTrace(); | e.printStackTrace(); | ||
} | } | ||
try { | try { | ||
− | System.out.println( | + | System.out.println("Press any key to continue..."); |
System.in.read(); | System.in.read(); | ||
} catch(IOException ie) { | } catch(IOException ie) { | ||
Line 76: | Line 74: | ||
} | } | ||
</code> | </code> | ||
+ | |||
+ | [[Category:Documentation]] | ||
+ | [[Category:Reference]] | ||
+ | [[Category:JDBC|Resultset]] |
Latest revision as of 16:17, 5 July 2011
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 * from example; where account_no = lcAccountNo; into cursor curExample return setresultset("curExample") // end of getexamplecursor.prg
The Java program establishes the JDBC connection, calls the Stored Procedure and displays the data.
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) { ; } } }