testoradlg.cpp

一个利用OTL访问ORACLE数据库的例子

// testOraDlg.cpp : implementation file
//

#include "stdafx.h"
#include "testOra.h"
#include "testOraDlg.h"


#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL

// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CTestOraDlg dialog

CTestOraDlg::CTestOraDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CTestOraDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CTestOraDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CTestOraDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CTestOraDlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CTestOraDlg, CDialog)
	//{{AFX_MSG_MAP(CTestOraDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(ID_CONNECT_DB, OnConnectDb)
	ON_BN_CLICKED(IDC_INSERT, OnInsert)
	ON_BN_CLICKED(IDC_UPDATE, OnUpdate)
	ON_BN_CLICKED(IDC_QUERY, OnQuery)
	ON_BN_CLICKED(IDC_DIRECT_EXCUTE, OnDirectExcute)
	ON_BN_CLICKED(ID_SHUTDOWN, OnShutdown)
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	ON_BN_CLICKED(IDC_BUTTON3, OnButton3)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CTestOraDlg message handlers

BOOL CTestOraDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	// TODO: Add extra initialization here
	otl_connect::otl_initialize(); // initialize OCI environment
	//

	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CTestOraDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CTestOraDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CTestOraDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CTestOraDlg::OnConnectDb() 
{
	// TODO: Add your control notification handler code here
	
	
	if(m_OtlHandle.ConnectDB("zyt/zyt@kj"))
       GetDlgItem(ID_CONNECT_DB)->EnableWindow(FALSE);
	else
		GetDlgItem(ID_CONNECT_DB)->EnableWindow(TRUE);
}

void CTestOraDlg::OnInsert() 
{
	// TODO: Add your control notification handler code here
	// open a stream with no implicit committing
	
	try
	{
		otl_stream
			o(1, // stream buffer size should be set to 1
			"insert into test_tab values(:f1<int>,:f2<char[31]>,:f3<int>,:f4<int>,:f5<int>,:f6<int>,:f7<int>,:f8<int>,:f9<int>,:f10<int>,:f11<int>,:f12<int>,:f13<int>,:f14<int>,:f15<int>,:f16<int>)", 
			// SQL statement
			m_OtlHandle.m_db  // connect object
			);
				
		char tmp[32];
		CTime nowTime = CTime::GetCurrentTime();
		TRACE("begin time is : %d:%d\r\n",nowTime.GetMinute(),nowTime.GetSecond());
		for(int i = 0; i <= 10000; ++i)
		{
			sprintf(tmp,"Name%d",(int)i);
			
			o<<i<<tmp<<i+3<<i+4<<i+5<<i+6<<i+7<<i+8<<i+9<<i+10<<i+11<<i+12<<i+13<<i+14<<i+15<<i+16;
		}
		CTime lastTime = CTime::GetCurrentTime();
		TRACE("end time is : %d:%d\r\n",lastTime.GetMinute(), lastTime.GetSecond());
		CTimeSpan span = (lastTime - nowTime);
		TRACE("insert 10000 items to Db using time is : %d 秒\n",span.GetTotalSeconds());
	}
	catch(otl_exception& p)
	{
		TRACE("%s",p.msg); // print out error message
		TRACE("%s",p.stm_text); // print out SQL that caused the error
		TRACE("%d",p.sqlstate); // print out SQLSTATE message
		TRACE("%d",p.var_info); // print out the variable that caused the error
	}
	
}

void CTestOraDlg::OnUpdate() 
{
	// TODO: Add your control notification handler code here
	int af1 = 3;
	otl_stream 
		o(1, // buffer size
		"UPDATE test_tab "
		"   SET f2=:f2<char[31]> "
		" WHERE f1=:f1<bigint>", 
        // UPDATE statement
		m_OtlHandle.m_db // connect object
		);
	
	o<<"Name changed"<<af1;
	o<<otl_null()<<af1+1; // set f2 to NULL
	
}

void CTestOraDlg::OnQuery() 
{
	// TODO: Add your control notification handler code here
    
	CTime nowTime = CTime::GetCurrentTime();
	
	int af1 = 10;
	otl_stream i(50, // buffer size may be > 1
		"select f1,f2 from test_tab "
		"where f1>=:f11<int> "
		"  and f1<=:f12<int>*2", 
		m_OtlHandle.m_db // connect object
		); 
	
    otl_stream * newi = &i;
	//
	int f1;
	
	*newi<<af1<<af1; // Writing input values into the stream
	try
	{
		while(!newi->eof())
		{ // while not end-of-data
			*newi>>f1;
			cout<<"f1="<<f1<<", f2=";
			char f2[64];
			*newi>>f2;
			if(newi->is_null())
				cout<<"NULL";
			else
				cout<<f2;
			cout<<endl;
		}
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s",p.msg); // print out error message
		TRACE("%s",p.stm_text); // print out SQL that caused the error
		TRACE("%d",p.sqlstate); // print out SQLSTATE message
		TRACE("%d",p.var_info); // print out the variable that caused the error
	}
	
	CTime lastTime = CTime::GetCurrentTime();
	CTimeSpan span = (lastTime - nowTime);
	TRACE("query data from  Db using time is : %d 秒\n",span.GetTotalSeconds());
	
}
void CTestOraDlg::OnDirectExcute() 
{
	// TODO: Add your control notification handler code here
	std::string strSQL = "drop table test_tab";
	m_OtlHandle.ExcuteSQL(strSQL.c_str());
	strSQL = "create table test_tab(f1 number, f2 varchar(30), f3 number, f4 number,f5 number,f6 number,f7 number,f8 number,f9 number,f10 number, f11 number, f12 number, f13 number, f14 number, f15 number, f16 number)";
    m_OtlHandle.ExcuteSQL(strSQL.c_str());
	return;
    /*
	otl_cursor::direct_exec
		(
		m_db,
		"drop table test_tab",
		otl_exception::disabled // disable OTL exceptions
		); // drop table
	try
	{
		
		otl_cursor::direct_exec
			(
			m_db,
			"create table test_tab(f1 number, f2 varchar(30), f3 number, f4 number,f5 number,f6 number,f7 number,f8 number,f9 number,f10 number, f11 number, f12 number, f13 number, f14 number, f15 number, f16 number)"
			);  // create table
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s",p.msg); // print out error message
		TRACE("%s",p.stm_text); // print out SQL that caused the error
		TRACE("%d",p.sqlstate); // print out SQLSTATE message
		TRACE("%d",p.var_info); // print out the variable that caused the error
		GetDlgItem(ID_CONNECT_DB)->EnableWindow(TRUE);
		
	}
	*/
}

void CTestOraDlg::OnShutdown() 
{
	// TODO: Add your control notification handler code here
	//m_db.logoff();
	
	
}
//
//read bigint example
void CTestOraDlg::OnButton2() 
{
	// TODO: Add your control notification handler code here
	CTime nowTime = CTime::GetCurrentTime();
	
    //	otl_long_string f1(70000); 
    //	m_OtlHandle.SetMaxLongSize(70000);

	otl_stream i(50, // buffer size may be > 1
		"select addr :#1<char[100]> from tab_bigint ", 
		m_OtlHandle.m_db // connect object
		); 
	
    //
	otl_column_desc* desc;
	int desc_len;
	
	desc = i.describe_select(desc_len);
	
	CString strTmp;
	for(int n=0;n<desc_len;++n){
		strTmp.Format( "========== COLUMN #%d ===========\r\n", n+1 );
		TRACE( strTmp );
        //  
		strTmp.Format("name=%s\r\n", desc[n].name );
		TRACE( strTmp );
		//
		strTmp.Format("dbtype=%d\r\n", desc[n].dbtype );
		TRACE( strTmp );
		//
		strTmp.Format("otl_var_dbtype==%d\r\n", desc[n].otl_var_dbtype );
		TRACE( strTmp );
		//
		strTmp.Format("dbsize=%d\r\n", desc[n].dbsize );
		TRACE( strTmp );
		//
		strTmp.Format("scale=%d\r\n", desc[n].scale );
		TRACE( strTmp );
		//
		strTmp.Format("prec=%d\r\n", desc[n].prec );
		TRACE( strTmp );
		//
		strTmp.Format("nullok=%d\r\n", desc[n].nullok );
		TRACE( strTmp );
	
	}

    otl_stream * newi = &i;
	//
//	OTL_BIGINT f1 = 0;
//	double f1;
	BYTE f1[22];
	UINT64 nRet;
	CString strOut;
	try
	{
		while(!newi->eof())
		{ // while not end-of-data
			memset( f1, 0, sizeof(f1) );
			*newi>>(BYTE*)f1;

			strOut = _T("");
			nRet = 0;
			for( int j=0;f1[j]!=0;++j)
			{     
				strOut += f1[j];
				nRet *= 10;
				nRet += f1[j] - 48;
			}  
			
		//	nRet = (UINT)f1;
		//	cout<<"addr="<<f1;
		//	cout<<"f1="<<f1[0]<<f1[f1.len()-1]<<", len="<<f1.len()<<endl;
			
		//	strOut.Format("%I64d", f1);
			TRACE( strOut );
			TRACE( "\t" );
			strOut.Format("%I64x", nRet);
			TRACE( strOut );
			TRACE( "\r\n" );
			cout<<endl;
		}
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s",p.msg); // print out error message
		TRACE("%s",p.stm_text); // print out SQL that caused the error
		TRACE("%d",p.sqlstate); // print out SQLSTATE message
		TRACE("%d",p.var_info); // print out the variable that caused the error
	}
	
	CTime lastTime = CTime::GetCurrentTime();
	CTimeSpan span = (lastTime - nowTime);
	TRACE("query data from  Db using time is : %d 秒\n",span.GetTotalSeconds());	
}
//
void CTestOraDlg::OnButton3() 
{
	// TODO: Add your control notification handler code here
	CTime nowTime = CTime::GetCurrentTime();
	
	//	otl_long_string f1(70000); 
	//	m_OtlHandle.SetMaxLongSize(70000);
	
	try
	{
		otl_stream i(50, // buffer size may be > 1
			"select addr :#1<double> from tab_bigint ", 
			m_OtlHandle.m_db // connect object
			); 
		
		otl_stream * newi = &i;
		//
		//	OTL_BIGINT f1 = 0;
		double f1;
		CString strOut;
		while(!newi->eof())
		{ // while not end-of-data
			*newi>>f1;
			
			strOut.Format("%I64x\r\n", (UINT64)f1);;
			
			//	strOut.Format("%I64d", f1);
			TRACE( strOut );
			//	TRACE( "\t" );
			//	strOut.Format("%I64x", nRet);
			//	TRACE( strOut );
			//	TRACE( "\r\n" );
		}
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s",p.msg); // print out error message
		TRACE("%s",p.stm_text); // print out SQL that caused the error
		TRACE("%d",p.sqlstate); // print out SQLSTATE message
		TRACE("%d",p.var_info); // print out the variable that caused the error
	}
	
	CTime lastTime = CTime::GetCurrentTime();
	CTimeSpan span = (lastTime - nowTime);
	TRACE("query data from  Db using time is : %d 秒\n",span.GetTotalSeconds());	
}
//bigint insert example
void CTestOraDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	//
    try
	{
		otl_stream o( 50,
			"insert into tab_bigint values(:f1<char[22]>)",
			m_OtlHandle.m_db);
		
		OTL_BIGINT n64 = 0x10101010101010;
		//byte n64[8] = {0x10,0x10,0x10,0x10,0x10,0x10,0x10,0};
		o<<n64;
		//std::string str("0x10101010101010");
		//o<<str.c_str();
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s\r\n",p.msg); // print out error message
		TRACE("%s\r\n",p.stm_text); // print out SQL that caused the error
		TRACE("%d\r\n",p.sqlstate); // print out SQLSTATE message
		TRACE("%d\r\n",p.var_info); // print out the variable that caused the error
	} 
	return;
	
	try
	{
		otl_stream o( 50,
		"insert into tab_bigint values(:f1<double>,:f2<double>)",
		m_OtlHandle.m_db);

	//	OTL_BIGINT n64 = 0xC0A8006301;
		double n64 = 0xC0A8006301;
		o<<n64<<n64;
	}
	catch(otl_exception& p)
	{ // intercept OTL exceptions
		TRACE("%s\r\n",p.msg); // print out error message
		TRACE("%s\r\n",p.stm_text); // print out SQL that caused the error
		TRACE("%d\r\n",p.sqlstate); // print out SQLSTATE message
		TRACE("%d\r\n",p.var_info); // print out the variable that caused the error
	}

}