main.cpp

Oracle调用接口

#include "_p.h"
#include <crtdbg.h>

#include "../oralib/oralib.h"

using namespace oralib;
using namespace std;

int main (void)
{
	try
	{
		connection cn ("ora1", "thesis", "thesis");
		cn.execute ("create table a (id numeric, name varchar2 (10))");

		cn.execute ("insert into a (id, name) values (1, 'aaa')");
		cn.execute ("insert into a (id, name) values (2, 'aba')");
		cn.execute ("insert into a (id, name) values (3, 'aac')");
		cn.execute ("insert into a (id, name) values (4, 'ada')");
		cn.execute ("insert into a (id, name) values (5, 'aae')");

 	statement &st = *cn.prepare (
		"begin :result := package_name.function_name ("
			":param1, :param2, :param3); end;");
	st.bind (":param1", DT_TYPE) = param1_value;
	st.bind (":param2", DT_TYPE) = param2_value;
	st.bind (":param3", DT_TYPE) = param3_value;
	st.bind (":result", DT_TYPE);
	st.execute ();
		// use st [":result"] here
	st.release ();

		cn.execute ("drop table a");
		cn.close ();
	}
	catch (oralib::error &e)
	{
		std::cout << e.details ();
	}
	return (0);
}



int main1 (void)
{
	_CrtSetDbgFlag (
		_CRTDBG_ALLOC_MEM_DF |
		//_CRTDBG_CHECK_ALWAYS_DF |
		//_CRTDBG_DELAY_FREE_MEM_DF |
		_CRTDBG_LEAK_CHECK_DF);

	try
	{
		oralib::connection c ("ora1", "thesis", "thesis");

		oralib::statement &s1 = *c.prepare ("insert into a values (:p)");
		oralib::statement &s2 = *c.prepare ("insert into b values (:s1)");
		oralib::statement &s3 = *c.prepare ("insert into c values (:p)");

		oralib::parameter &p1 = s1.bind (":p", oralib::DT_NUMBER);
		oralib::parameter &p2 = s2.bind (":s1");
		oralib::parameter &p3 = s3.bind (":p", oralib::DT_TEXT);

		p2 = _T ("Hello, World!");
		p3 = _T ("Hello, World!");

		putch ('.');
		for (int i=0; i<1; ++i, p1 = (long) i)
		{
			s1.execute ();
			s2.execute ();
			s3.execute ();
		}
		s3.release ();
		s2.release ();
		s1.release ();

		putch ('.');
		oralib::resultset	&rs = *c.select ("select * from b");
		while (!rs.eod ())
		{
			++rs;
		}
		rs.release ();

		putch ('.');
		c.execute ("delete from a");
		c.execute ("delete from b");
		c.execute ("delete from c");

		putch ('.');
		c.commit ();
		c.close ();
	}
	catch (oralib::error &e)
	{
		std::cout << e.details ();
	}
	
	return (0);
}



#if	0
void	main1 (void)
{
	oralib::connection	c;
	int	i;
	DWORD	ticks;
	const int ITERS = 100;
	const int SLEEP = 100;
	const char *server = "ora1";
	const char *login = "thesis";
	const char *password = "thesis";

	_CrtSetDbgFlag (
		_CRTDBG_ALLOC_MEM_DF |
		//_CRTDBG_CHECK_ALWAYS_DF |
		//_CRTDBG_DELAY_FREE_MEM_DF |
		_CRTDBG_LEAK_CHECK_DF);

	try
	{
		c.open (server, login, password);

		// select test
		oralib::resultset &r = *c.select (
			"select OBJECT_NAME, OBJECT_TYPE, TIMESTAMP, STATUS from OBJ");
		oralib::column &c1 = r [1], &c2 = r [2], &c3 = r [3], &c4 = r [4];
		while (!r.eod ())
		{
			_tprintf (_T ("%-18s %-18s %-20s %s\n"),
				(oralib::Pstr) c1,
				c2.as_string (),
				c3.as_string (),
				c4.as_string ());
			r.next ();
		}
		c1.release (); c2.release (); c3.release (); c4.release ();
		r.release ();

		// pl/sql block test
		oralib::statement &s = *c.prepare (
			"declare n numeric; begin n := 4; :n1 := n; end;");
		oralib::parameter &p = s.bind (":n1") = (long) 5;
		wprintf (L"n1 were = %d\n", (long) p);
		s.execute ();
		wprintf (L"n1 goes = %d\n", (long) s [1]);
		p.release ();
		s.release ();

		c.execute ("create table test_table ( s varchar2(50), n numeric, d date )");
		
		s = *c.prepare ("insert into test_table (s, n, d) values (:s1, :n1, :d1)");
		s.bind (":s1") = "Hello, World!";
		s.bind (":n1") = (long) 12345;
		s.bind (":d1") = oralib::datetime (1, oralib::jan, 2001);
		s.execute ();
		s.release ();

		r = *c.select ("select * from test_table");
		oralib::datetime dt = r [3];
		_tprintf (_T ("%s %d %d-%d-%d\n"),
			(oralib::Pstr) r [1],
			(long) r [2],
			(int) dt.day (), (int) dt.mon (), (int) dt.year ());
		r.release ();

		c.execute ("drop table test_table");

		c.close ();
	}
	catch (oralib::error &e)
	{
		e.display ();
	}

	try
	{
		c.open (server, login, password);

		/* simple select test */
		oralib::resultset &r = *c.select ("select TNAME from TAB");
		do
			_putts (r ["TNAME"]);
		while (r.next ());
		r.release ();

		/* insert test # 2 */
		ticks = GetTickCount ();
		for (i=0; i<ITERS; i++)
		{
			char	sz [100];
			sprintf (sz, "insert into a values (-%d)", i);
			c.execute (sz);
		}
		c.commit ();
		std::cout << "insert # 2 Ticks: " << (GetTickCount () - ticks) << "\n";

		std::cout << "sleeping for " << SLEEP / 1000.0 << " seconds...";
		Sleep (SLEEP);
		std::cout << " ok\n";

		/* insert test # 1 */
		ticks = GetTickCount ();
		oralib::statement &s = *c.prepare ("insert into a values (:in1)");
		s.bind (":in1");
		for (i=0; i<ITERS; i++)
		{
			s [":in1"] = (long) i;
			s.execute ();
		}
		s.release ();
		c.commit ();
		std::cout << "insert # 1 Ticks: " << (GetTickCount () - ticks) << "\n";

		std::cout << "sleeping for " << SLEEP / 1000.0 << " seconds...";
		Sleep (SLEEP);
		std::cout << " ok\n";

		/* select test */
		ticks = GetTickCount ();
		s = *c.prepare ("select N from A where N > :in1");
		s.bind (":in1");
		for (i=0; i<ITERS; i++)
		{
			if (i % 10 == 0) std::cout << ".";

			s [":in1"] = (long) i;
			oralib::resultset &r = *s.select ();

			while (r.next ())
				;

			r.release ();
		}
		s.release ();
		std::cout << "select Ticks: " << (GetTickCount () - ticks) << "\n";

		/* truncate */
		std::cout << "truncating table...";
		c.execute ("truncate table a");
		std::cout << " ok\n";

		c.close ();
	}
	catch (oralib::error &e)
	{
		e.display ();
	}

#if	0
	try
	{
		c.open ("ora1", "thesis", "thesis");
		
		c.execute ("create table a ( b numeric )");
		c.execute ("alter table a add constraint a_a primary key (b)");
		c.execute ("declare b numeric; begin select -1 into b from dual; insert into a values (b); end;");
		c.execute ("delete from a");
		c.execute ("drop table a");

		try
		{
			/* execute statement */
			oralib::statement	*s = NULL;
			s = c.prepare ("begin select -1 into :n from dual; end;");
			s->bind (":n");
			s->execute ();
			s->release ();
		}
		catch (oralib::error &e)
		{
			puts ("--- error executing the statement ---");
			e.display ();
		}

		try
		{
			/* execute statement */
			oralib::statement	*s = NULL;
			s = c.prepare ("begin open :c for select -1 from dual; end;");
			s->bind (":c");
			s->execute ();
			s->release ();
		}
		catch (oralib::error &e)
		{
			puts ("--- error executing the statement ---");
			e.display ();
		}

		oralib::resultset *r = c.select ("select -1 from dual");
		r->release ();

		r = c.select ("select * from bb");
		r->release ();

		c.close ();
	}
	catch (oralib::error &e)
	{
		puts ("--- error opening the connection ---");
		e.display ();
	}
#endif

	return;
}

#endif




#if	0
void	cdemo81 (void)
{
	oralib::connection cn;

	try
	{
		// connect
		cn.open ("ora1", "thesis", "thesis");

		// retrieve the current maximum employee number
		oralib::resultset &rs = *cn.select ("SELECT NVL(MAX(empno), 0) FROM emp");
		sword empno = (sword) (long) rs [1];
		rs.release ();

		// prepare both statements for execution
		oralib::statement &st_insert = *cn.prepare (
			"INSERT INTO emp(empno, ename, job, sal, deptno) "
			"VALUES (:nEmpno, :sEname, :sJob, :nSal, :nDeptno)");
		
		oralib::statement &st_dep_name = *cn.prepare (
			"SELECT dname FROM dept WHERE deptno = :nDeptno");

		// bind named variables
		st_insert.bind (":nEmpno");
		st_insert.bind (":sEname");
		st_insert.bind (":sJob");
		st_insert.bind (":nSal");
		st_insert.bind (":nDeptno");
		st_dep_name.bind (":nDeptno");

		while (true)
		{
			oralib::String ename, ejob, dept;
			sword esal, deptno;

			// get information
			std::cout << _T ("\nEnter employee name (or Ctrl+Z, Enter to EXIT): ");
			std::cin >> ename;
			if (ename.length () == 0) break;
			std::cout << _T ("Enter employee job: ");
			std::cin >> ejob;
			std::cout << _T ("Enter employee salary: ");
			std::cin >> esal;

			// ask for department and verify that exists
			bool found = false;
			do
			{
				std::cout << _T ("Enter employee dept: ");
				std::cin >> deptno;
				st_dep_name [1] = (long) deptno;
				oralib::resultset &rs = *st_dep_name.select ();
				found = !rs.eod ();
				if (found)
					dept = rs [1].as_string ();
				else
					std::cout << _T ("The dept you entered doesn't exist.\n");
				rs.release ();
			}
			while (!found);

			// insert an employee record
			st_insert [":sEname"] = ename.c_str ();
			st_insert [":sJob"] = ejob.c_str ();
			st_insert [":nSal"] = (long) esal;
			st_insert [":nDeptno"] = (long) deptno;
			bool succeeded = false;
			do
			{
				st_insert [":nEmpno"] = (long) empno;
				try
				{
					st_insert.execute ();
					cn.commit ();
					succeeded = true;
				}
				catch (oralib::error& /* e */)
				{
					empno += 10;
				}
			}
			while (!succeeded);

			std::cout << ename << _T (" added to the ") << dept <<
				_T (" department as employee number ") << empno;
		}

		st_insert.release ();
		st_dep_name.release ();
		cn.close ();
	}
	catch (oralib::error &e)
	{
		std::cout << e.details ();
	}
}
#endif