📄 group__g__fetch.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html;charset=UTF-8"><title>OCILIB (C Driver for Oracle): Fetching data</title><link href="doxygen.css" rel="stylesheet" type="text/css"><link href="tabs.css" rel="stylesheet" type="text/css"></head><body><!-- Generated by Doxygen 1.5.4 --><div class="tabs"> <ul> <li><a href="index.html"><span>Main Page</span></a></li> <li><a href="modules.html"><span>Modules</span></a></li> <li><a href="annotated.html"><span>Data Structures</span></a></li> <li><a href="files.html"><span>Files</span></a></li> </ul></div><h1>Fetching data</h1><hr><a name="_details"></a><h2>Detailed Description</h2>OCILIB offers a really easy and smart mechanism to fetch data from a SQL Statement. It looks like what's found in JDBC and other object oriented databases frameworks.<p>Only SELECTs statement, DML with returning clause and some PL/SQL blocks return a cursor that can be fetched by host programs. That cursor, or resultset, is encapsulated in OCILIB by the <a class="el" href="struct_o_c_i___resultset.html" title="Collection of output columns from a select statement.">OCI_Resultset</a> object.<p>So, after any successful call to an OCI_Executexxx() function that executed a fetchable statement or filled output bind variables, the resultset can be retrieved by calling <a class="el" href="group__g__fetch.html#gf2a9e28b66a9538ba0ffb62bffb87c16" title="Retrieve the resultset handle from an executed statement.">OCI_GetResultset()</a><p>The creation of a <a class="el" href="struct_o_c_i___resultset.html" title="Collection of output columns from a select statement.">OCI_Resultset</a> object consists in :<p><ul><li>Describing the output columns of the resultset</li><li>Allocating memory to hold the content data</li></ul><p>OCILIB supports multi-row fetching for increasing performances. Instead of fetching data row by row from the server (that induces lots of roundtrips between the client and the server), the library prefetches data chunk by chunk (default is 20 rows). So, less network traffic and better performances. These mechanisms are completely hidden from the application which fetches the resultset row by row.<p>Once the Resultset handle is retrieved :<p><ul><li>It can be fetched by calling <a class="el" href="group__g__fetch.html#g36ac26dcea78f6074421781e401f97ba" title="Fetch the next row of the resultset.">OCI_FetchNext()</a> as long as it returns TRUE.</li><li>To retrieve the value of a column, call OCI_GetXXXX() where XXXX is the type of data you want to fetch.</li></ul><p><dl class="user" compact><dt><b>Scrollable Resultsets</b></dt><dd></dd></dl>Oracle 9i introduced scrollable cursors (resultsets in OCILIB) that can be fetched :<p><ul><li>Sequentially in both direction : <a class="el" href="group__g__fetch.html#g81e501b509b84d999e4337e2e11a90ec" title="Fetch the previous row of the resultset.">OCI_FetchPrev()</a> and <a class="el" href="group__g__fetch.html#g81e501b509b84d999e4337e2e11a90ec" title="Fetch the previous row of the resultset.">OCI_FetchPrev()</a></li><li>To a relative position in the resultset : <a class="el" href="group__g__fetch.html#g99372dc21a4965ca74d8fdde2069cc10" title="Custom Fetch of the resultset.">OCI_FetchSeek()</a> with OCI_SFD_RELATIVE</li><li>To an absolute position in the resultset : <a class="el" href="group__g__fetch.html#g99372dc21a4965ca74d8fdde2069cc10" title="Custom Fetch of the resultset.">OCI_FetchSeek()</a> with OCI_SFD_ABOSLUTE</li><li>To the first or last row in the resultset : <a class="el" href="group__g__fetch.html#gf26bde3f9db77231d66107af24a51d96" title="Fetch the first row of the resultset.">OCI_FetchFirst()</a> and <a class="el" href="group__g__fetch.html#gb2b3f30ace2a89b07dd7803c82a523e7" title="Fetch the last row of the resultset.">OCI_FetchLast()</a></li></ul><p>Scrollable statements uses more server and client resources and should only be used when necessary.<p>OCILIB support scrollable cursors from version OCILIB 3.0.0.<p>Resultsets are "forward only" by default. Call <a class="el" href="group__g__stmt.html#g83e1c31e40926a84651eb60fefd50d5e" title="Set the fetch mode of a SQL statement.">OCI_SetFetchMode()</a> with OCI_SFM_SCROLLABLE to enable scrollable resultsets for a given statement.<p><dl class="warning" compact><dt><b>Warning:</b></dt><dd>Any use of scrollable fetching functions with a resultset that depends on a statement with fetch mode = OCI_SFM_DEFAULT will fail !</dd></dl><dl class="note" compact><dt><b>Note:</b></dt><dd>If the column internal data does not match the requested type, OCILIB tries to convert the data when it's possible and throws an error if not.</dd></dl>The properties (columns names, types, ...) of the resultset are accessible through a set of APIs.<p><dl class="user" compact><dt><b>Implicit conversion to string types</b></dt><dd></dd></dl><a class="el" href="group__g__fetch.html#g755a9b07c174feacd05c86e0e44b0ab0" title="Return the current string value of the column at the given index in the resultset...">OCI_GetString()</a> performs an implicit conversion from the following datatypes :<p><ul><li>Numerics (based on the current connection handle numeric format)</li><li><a class="el" href="struct_o_c_i___date.html" title="Oracle internal date representation.">OCI_Date</a> (based on the current connection handle date format)</li><li><a class="el" href="struct_o_c_i___timestamp.html" title="Oracle internal timestamp representation.">OCI_Timestamp</a> (based on the current connection handle date format)</li><li><a class="el" href="struct_o_c_i___interval.html" title="Oracle internal interval representation.">OCI_Interval</a> (based on Oracle default conversion)</li><li><a class="el" href="struct_o_c_i___lob.html" title="Oracle Internal Large objects:.">OCI_Lob</a> (maximum number of character is defined by OCI_SIZE_BUFFER)</li><li><a class="el" href="struct_o_c_i___long.html" title="Oracle Long datatype.">OCI_Long</a></li><li><a class="el" href="struct_o_c_i___file.html" title="Oracle External Large objects:.">OCI_File</a> (maximum number of character is defined by OCI_SIZE_BUFFER)</li><li>RAW buffer</li></ul><p>The following type are not supported for implicit conversion :<ul><li><a class="el" href="struct_o_c_i___statement.html" title="Oracle SQL or PL/SQL statement.">OCI_Statement</a></li><li><a class="el" href="struct_o_c_i___coll.html" title="Oracle Collections (VARRAYs and Nested Tables) representation.">OCI_Coll</a></li><li><a class="el" href="struct_o_c_i___object.html" title="Oracle Named types representation.">OCI_Object</a></li></ul><p><dl class="user" compact><dt><b>Fetch Example</b></dt><dd><div class="fragment"><pre class="fragment"><span class="preprocessor">#include "ocilib.h"</span><span class="keywordtype">int</span> main(<span class="keywordtype">void</span>){ <a class="code" href="struct_o_c_i___connection.html" title="Oracle physical connection.">OCI_Connection</a> *cn; <a class="code" href="struct_o_c_i___statement.html" title="Oracle SQL or PL/SQL statement.">OCI_Statement</a> *st; <a class="code" href="struct_o_c_i___resultset.html" title="Collection of output columns from a select statement.">OCI_Resultset</a> *rs; <span class="keywordflow">if</span> (!<a class="code" href="group__g__init.html#gcdb642d75f7c8478e083634144bc813c" title="Initializes the library.">OCI_Initialize</a>(NULL, NULL, OCI_ENV_DEFAULT)) <span class="keywordflow">return</span> EXIT_FAILURE; cn = <a class="code" href="group__g__connect.html#gdb6e2cdf759587dd81c2fda7c5f44338" title="Create a physical connection to an Oracle database server.">OCI_ConnectionCreate</a>(<span class="stringliteral">"db"</span>, <span class="stringliteral">"usr"</span>, <span class="stringliteral">"pwd"</span>, OCI_SESSION_DEFAULT); st = <a class="code" href="group__g__exec.html#g335822f983af0fb5c529431f06a9a17b" title="Create a statement object and return its handle.">OCI_StatementCreate</a>(cn); <a class="code" href="group__g__exec.html#gf2163ec44e644cc734c393188707985b" title="Parse and execute a SQL statement or PL/SQL block.">OCI_ExecuteStmt</a>(st, <span class="stringliteral">"select * from products"</span>); rs = <a class="code" href="group__g__fetch.html#gf2a9e28b66a9538ba0ffb62bffb87c16" title="Retrieve the resultset handle from an executed statement.">OCI_GetResultset</a>(st); <span class="keywordflow">while</span> (<a class="code" href="group__g__fetch.html#g36ac26dcea78f6074421781e401f97ba" title="Fetch the next row of the resultset.">OCI_FetchNext</a>(rs)) printf(<span class="stringliteral">"code: %i, name %s\n"</span>, <a class="code" href="group__g__fetch.html#ga8d80dc30b2012eaddd13efa9dfb711d" title="Return the current integer value of the column at the given index in the resultset...">OCI_GetInt</a>(rs, 1) , <a class="code" href="group__g__fetch.html#g755a9b07c174feacd05c86e0e44b0ab0" title="Return the current string value of the column at the given index in the resultset...">OCI_GetString</a>(rs, 2)); printf(<span class="stringliteral">"\n%d row(s) fetched\n"</span>, <a class="code" href="group__g__fetch.html#g93bd9197720088dbda2bef593ad1c07c" title="Retrieve the number of rows fetched so far.">OCI_GetRowCount</a>(rs)); <a class="code" href="group__g__init.html#g639706aa8e9689c7ebffc018fac6d3ae" title="Clean up all resources allocated by the library.">OCI_Cleanup</a>(); <span class="keywordflow">return</span> EXIT_SUCCESS;}</pre></div></dd></dl><dl class="user" compact><dt><b>Metadata Example</b></dt><dd><div class="fragment"><pre class="fragment"><span class="preprocessor">#include "ocilib.h"</span><span class="keywordtype">int</span> main(<span class="keywordtype">void</span>){ <a class="code" href="struct_o_c_i___connection.html" title="Oracle physical connection.">OCI_Connection</a> *cn; <a class="code" href="struct_o_c_i___statement.html" title="Oracle SQL or PL/SQL statement.">OCI_Statement</a> *st; <a class="code" href="struct_o_c_i___resultset.html" title="Collection of output columns from a select statement.">OCI_Resultset</a> *rs; <span class="keywordtype">int</span> i, n; <span class="keywordflow">if</span> (!<a class="code" href="group__g__init.html#gcdb642d75f7c8478e083634144bc813c" title="Initializes the library.">OCI_Initialize</a>(NULL, NULL, OCI_ENV_DEFAULT)) <span class="keywordflow">return</span> EXIT_FAILURE; cn = <a class="code" href="group__g__connect.html#gdb6e2cdf759587dd81c2fda7c5f44338" title="Create a physical connection to an Oracle database server.">OCI_ConnectionCreate</a>(<span class="stringliteral">"db"</span>, <span class="stringliteral">"usr"</span>, <span class="stringliteral">"pwd"</span>, OCI_SESSION_DEFAULT); st = <a class="code" href="group__g__exec.html#g335822f983af0fb5c529431f06a9a17b" title="Create a statement object and return its handle.">OCI_StatementCreate</a>(cn); <a class="code" href="group__g__exec.html#gf2163ec44e644cc734c393188707985b" title="Parse and execute a SQL statement or PL/SQL block.">OCI_ExecuteStmt</a>(st, <span class="stringliteral">"select * from test_fetch"</span>); rs = <a class="code" href="group__g__fetch.html#gf2a9e28b66a9538ba0ffb62bffb87c16" title="Retrieve the resultset handle from an executed statement.">OCI_GetResultset</a>(st); n = <a class="code" href="group__g__fetch.html#g8935f9c6ff0d6f1d94c70afb3418a916" title="Return the number of columns in the resultset.">OCI_GetColumnCount</a>(rs); <span class="keywordflow">for</span>(i = 1; i <= n; i++) { <a class="code" href="struct_o_c_i___column.html" title="Oracle SQL Column and Type member representation.">OCI_Column</a> *col = <a class="code" href="group__g__fetch.html#g7b64be082fc14ac73347b081cdb66de0" title="Return the column object handle at the given index in the resultset.">OCI_GetColumn</a>(rs, i); printf(<span class="stringliteral">"Field #%i : name '%s' - size %i\n"</span>, i, OCI_GetColumnName(col),⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -