📄 ocilib_demo.c
字号:
/*
+----------------------------------------------------------------------+
| |
| OCILIB - C Driver for Oracle |
| |
| (C Wrapper for Oracle OCI) |
| |
| DEMO SOURCE FILE |
| |
+----------------------------------------------------------------------+
| Website : http://orclib.sourceforge.net |
+----------------------------------------------------------------------+
| Copyright (c) 2007-2008 Vincent ROGIER <vince.rogier@gmail.com> |
+----------------------------------------------------------------------+
| This library is free software; you can redistribute it and/or |
| modify it under the terms of the GNU Library General Public |
| License as published by the Free Software Foundation; either |
| version 2 of the License, or (at your option) any later version. |
| |
| This library is distributed in the hope that it will be useful, |
| but WITHOUT ANY WARRANTY; without even the implied warranty of |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
| Library General Public License for more details. |
| |
| You should have received a copy of the GNU Library General Public |
| License along with this library; if not, write to the Free |
| Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. |
+----------------------------------------------------------------------+
| Author: Vincent ROGIER <vince.rogier@gmail.com> |
+----------------------------------------------------------------------+
*/
#include "ocilib_demo.h"
/* ------------------------------------------------------------------------ *
* prototypes
* ------------------------------------------------------------------------ */
void err_handler(OCI_Error *err);
void print_version(void);
void cleanup(void);
void disconnect(void);
void create_tables(void);
void drop_tables(void);
void test_format(void);
void test_immediate(void);
void test_immediate_format(void);
void test_fetch(void);
void test_bind1(void);
void test_bind2(void);
void test_piecewise_insert(void);
void test_piecewise_fetch(void);
void test_lob(void);
void test_nested_table(void);
void test_ref_cursor(void);
void test_plsql(void);
void test_dates(void);
void test_timestamp(void);
void test_describe(void);
void test_returning(void);
void test_returning_array(void);
void test_object_insert(void);
void test_object_fetch(void);
void test_scrollable_cursor(void);
void test_collection(void);
/* ------------------------------------------------------------------------ *
* variables
* ------------------------------------------------------------------------ */
static OCI_Connection *cn = NULL;
static OCI_Statement *st = NULL;
static OCI_Resultset *rs = NULL;
static mtext str[SIZE_STR+1];
static dtext temp[SIZE_STR+1];
static int nb_err = 0;
/* ------------------------------------------------------------------------ *
* err_handler
* ------------------------------------------------------------------------ */
void err_handler(OCI_Error *err)
{
print_text("\n");
if (OCI_ErrorGetType(err) == OCI_ERR_ORACLE)
{
const mtext *sql = OCI_GetSql(OCI_ErrorGetStatement(err));
if (sql != NULL)
{
print_text("> ERROR - SQL : "); print_mstr(sql);
print_text("\n");
}
}
print_text("> ERROR - MSG : ");
print_mstr(OCI_ErrorGetString(err));
print_text("\n");
nb_err++;
}
/* ------------------------------------------------------------------------ *
* main
* ------------------------------------------------------------------------ */
int mtmain(int argc, mtarg* argv[])
{
/* CHECK COMMAND LINE --------------------------------------------------- */
if (argc < (ARG_COUNT-1))
{
return EXIT_FAILURE;
}
/* INITIALIZE OCI ------------------------------------------------------- */
if (!OCI_Initialize(err_handler, (argc==ARG_COUNT) ? argv[ARG_HOME]:NULL,
OCI_ENV_DEFAULT))
return EXIT_FAILURE;
/* CONNECTION TO SERVER ------------------------------------------------- */
print_text("Connecting to ");
print_args(argv[ARG_USER]);
print_text("/");
print_args(argv[ARG_PWD]);
print_text("@");
print_args(argv[ARG_DB]);
print_text("\n\n");
cn = OCI_ConnectionCreate(argv[ARG_DB],
argv[ARG_USER],
argv[ARG_PWD],
OCI_SESSION_DEFAULT);
if (cn)
{
st = OCI_StatementCreate(cn);
print_version();
create_tables();
test_format();
test_immediate();
test_immediate_format();
test_fetch();
test_bind1();
test_bind2();
test_piecewise_insert();
test_piecewise_fetch();
test_lob();
test_nested_table();
test_ref_cursor();
test_plsql();
test_dates();
test_timestamp();
test_describe();
test_returning();
test_returning_array();
test_object_insert();
test_object_fetch();
test_scrollable_cursor();
test_collection();
drop_tables();
disconnect();
}
else
{
print_mstr(OCI_ErrorGetString(OCI_GetLastError()));
}
cleanup();
print_text("\npress any key to exit...");
getchar();
return EXIT_SUCCESS;
}
/* ------------------------------------------------------------------------ *
* cleanup
* ------------------------------------------------------------------------ */
void cleanup(void)
{
OCI_Cleanup();
print_frmt("\n%i errors encountered\n\n", nb_err);
}
/* ------------------------------------------------------------------------ *
* disconnect
* ------------------------------------------------------------------------ */
void disconnect(void)
{
OCI_ConnectionFree(cn);
}
/* ------------------------------------------------------------------------ *
* print_version
* ------------------------------------------------------------------------ */
void print_version(void)
{
/* print server string version */
print_mstr(OCI_GetVersionServer(cn));
print_text("\n\n");
print_text("\n>>>>> VERSIONS INFORMATION \n\n");
print_frmt("OCILIB major version : %i\n", OCILIB_MAJOR_VERSION);
print_frmt("OCILIB minor version : %i\n", OCILIB_MINOR_VERSION);
print_frmt("OCILIB revision version : %i\n\n", OCILIB_REVISION_VERSION);
/* print all versions */
print_frmt("OCI compile version : %i\n", OCI_GetOCICompileVersion());
print_frmt("OCI runtime version : %i\n\n", OCI_GetOCIRuntimeVersion());
print_frmt("Server major version : %i\n", OCI_GetServerMajorVersion(cn));
print_frmt("Server minor version : %i\n", OCI_GetServerMinorVersion(cn));
print_frmt("Server revision version : %i\n\n", OCI_GetServerRevisionVersion(cn));
print_frmt("Connection version : %i\n\n", OCI_GetVersionConnection(cn));
}
/* ------------------------------------------------------------------------ *
* create_tables
* ------------------------------------------------------------------------ */
void create_tables(void)
{
print_text("\n>>>>> CREATE TABLES FOR DEMO \n\n");
/* create types for the demo */
OCI_ExecuteStmt(st, MT("create type t_type as OBJECT (code int, name varchar2(20))"));
OCI_ExecuteStmt(st, MT("create type t_test as object ")
MT("( ")
MT(" val_int number, ")
MT(" val_flt float, ")
MT(" val_str varchar2(30), ")
MT(" val_date date, ")
MT(" val_lob clob, ")
MT(" val_file bfile, ")
MT(" val_obj t_type, ")
MT(" val_raw raw(20) ")
MT(")"));
OCI_ExecuteStmt(st, MT("create type t_tab1_emp as VARRAY(100) of varchar2(50)"));
OCI_ExecuteStmt(st, MT("create type t_tab2_emp as table of varchar2(50)"));
/* create table for the demo */
OCI_ExecuteStmt(st, MT("create table test_fetch(code int, article ")
MT("varchar2(30), price float, creation date)"));
OCI_ExecuteStmt(st, MT("create table test_long_raw(code int, ")
MT("content long raw)"));
OCI_ExecuteStmt(st, MT("create table test_long_str(code int, ")
MT("content long)"));
OCI_ExecuteStmt(st, MT("create table test_lob(code int, content CLOB)"));
OCI_ExecuteStmt(st, MT("create table test_object(val T_TEST)"));
OCI_ExecuteStmt(st, MT("create table test_array ")
MT("( ")
MT(" val_int number, ")
MT(" val_flt float, ")
MT(" val_str varchar2(30), ")
MT(" val_date date, ")
MT(" val_lob clob, ")
MT(" val_file bfile ")
MT(")")
);
OCI_ExecuteStmt(st, MT("create table test_coll_varray ")
MT("( ")
MT(" departement number, ")
MT(" employees t_tab1_emp ")
MT(")")
);
OCI_ExecuteStmt(st, MT("create table test_coll_nested ")
MT("( ")
MT(" departement number, ")
MT(" employees t_tab2_emp ")
MT(") nested table employees store as test_table_emp")
);
/* insert data into the demo tables */
OCI_ExecuteStmt(st, MT("insert into test_fetch ")
MT("(code, article, price, creation) ")
MT("values (1, 'shoes', 3.14, to_date('1978-12-23', 'YYYY-MM-DD'))"));
OCI_ExecuteStmt(st, MT("insert into test_fetch ")
MT("(code, article, price, creation) ")
MT("values (2, 'shirt', 5.99, to_date('1999-09-12', 'YYYY-MM-DD'))"));
OCI_ExecuteStmt(st, MT("insert into test_lob(code,content) ")
MT("values (1, EMPTY_CLOB())"));
OCI_ExecuteStmt(st, MT("insert into test_long_str(code,content) ")
MT("values (1, 'Rugby rocks !')"));
OCI_ExecuteStmt(st, MT("insert into test_coll_varray(departement,employees) ")
MT("values (1, t_tab1_emp('Peter', 'John', 'Paula', 'Gina'))"));
OCI_ExecuteStmt(st, MT("insert into test_coll_varray(departement,employees) ")
MT("values (2, t_tab1_emp('Ben', 'Alice', 'Joel', 'Maria'))"));
OCI_ExecuteStmt(st, MT("insert into test_coll_nested(departement,employees) ")
MT("values (1, t_tab2_emp('Vince', 'Richard', 'Rita', 'Sophia'))"));
OCI_ExecuteStmt(st, MT("insert into test_coll_nested(departement,employees) ")
MT("values (2, t_tab2_emp('Paul', 'Sarah', 'Robert', 'Zoe'))"));
OCI_Commit(cn);
}
/* ------------------------------------------------------------------------ *
* drop_tables
* ------------------------------------------------------------------------ */
void drop_tables(void)
{
print_text("\n>>>>> DROPPING TABLES AND TYPES \n\n");
OCI_ExecuteStmt(st, MT("drop table test_fetch"));
OCI_ExecuteStmt(st, MT("drop table test_long_str"));
OCI_ExecuteStmt(st, MT("drop table test_long_raw"));
OCI_ExecuteStmt(st, MT("drop table test_lob"));
OCI_ExecuteStmt(st, MT("drop table test_array"));
OCI_ExecuteStmt(st, MT("drop table test_object"));
OCI_ExecuteStmt(st, MT("drop table test_coll_varray"));
OCI_ExecuteStmt(st, MT("drop table test_coll_nested"));
OCI_ExecuteStmt(st, MT("drop type t_test"));
OCI_ExecuteStmt(st, MT("drop type t_type"));
OCI_ExecuteStmt(st, MT("drop type t_tab1_emp"));
OCI_ExecuteStmt(st, MT("drop type t_tab2_emp"));
}
/* ------------------------------------------------------------------------ *
* test_format
* ------------------------------------------------------------------------ */
void test_format(void)
{
int code = 1;
print_text("\n>>>>> TEST FORMATTING \n\n");
OCI_ExecuteStmtFmt(st, MT("select * from test_fetch where code = %i"), code);
rs = OCI_GetResultset(st);
while (OCI_FetchNext(rs))
{
print_frmt("> code : %i", OCI_GetInt(rs, 1));
print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
print_frmt(", price : %g", OCI_GetDouble(rs,3));
print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
print_text("\n");
}
print_frmt("\n%d row(s) fetched\n", OCI_GetRowCount(rs));
}
/* ------------------------------------------------------------------------ *
* test_immediate
* ------------------------------------------------------------------------ */
void test_immediate(void)
{
int code = 1;
print_text("\n>>>>> TEST IMMEDIATE \n\n");
OCI_Immediate(cn, MT("select code, article from test_fetch where code = 1"),
OCI_ARG_INT, &code,
OCI_ARG_TEXT, temp);
print_frmt("> code : %i ", code);
print_text("- article : "); print_dstr(temp);
print_text("\n");
}
/* ------------------------------------------------------------------------ *
* test_immediate_format
* ------------------------------------------------------------------------ */
void test_immediate_format(void)
{
int code = 1;
print_text("\n>>>>> TEST IMMEDIATE FORMATTED\n\n");
OCI_ImmediateFmt(cn, MT("select article from test_fetch where code = %i"),
code, OCI_ARG_TEXT, temp);
print_text("> article : "); print_dstr(temp); print_text("\n");
}
/* ------------------------------------------------------------------------ *
* test_fetch
* ------------------------------------------------------------------------ */
void test_fetch(void)
{
int i, n;
print_text("\n>>>>> SIMPLE TEST FETCH WITH META DATA\n\n");
/* execute query in one go */
OCI_ExecuteStmt(st, MT("select * from test_fetch"));
rs = OCI_GetResultset(st);
n = OCI_GetColumnCount(rs);
/* print resultset columns info */
for(i = 1; i <= n; i++)
{
OCI_Column *col = OCI_GetColumn(rs, i);
print_frmt("> Field : #%i ", i);
print_text("- Name : "); print_mstr(OCI_ColumnGetName(col));
print_text("\n");
}
print_text("\n");
/* print resultset content */
while (OCI_FetchNext(rs))
{
print_frmt("> code : %i", OCI_GetInt(rs, 1));
print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
print_frmt(", price : %g", OCI_GetDouble(rs,3));
print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
print_text("\n");
}
print_frmt("\n%d row(s) fetched\n", OCI_GetRowCount(rs));
}
/* ------------------------------------------------------------------------ *
* test_bind1
* ------------------------------------------------------------------------ */
void test_bind1(void)
{
int code = 1;
print_text("\n>>>>> TEST BINDING \n\n");
/* execute query in three steps */
OCI_Prepare(st, MT("select * from test_fetch where code = :code"));
OCI_BindInt(st, MT(":code"), &code);
OCI_Execute(st);
rs = OCI_GetResultset(st);
/* print resultset content */
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -