osqldlg.cpp

对Oracle OCI编程的一个例子

// OsqlDlg.cpp : implementation file
//
// Release 1, Copyright (C) 1999 Ben Bryant
// This is sample source code, nothing more is implied. Use it only as such.
// This software is provided 'as-is', without warranty. In no event will the
// author be held liable for any damages arising from the use of this software.
// Permission is granted to anyone to use this software for any purpose.
// The origin of this software must not be misrepresented; you must not claim
// that you wrote the original software. Altered source versions must be plainly
// marked as such, and must not be misrepresented as being the original software.
// Ben Bryant [e-mail]
//

#include "stdafx.h"
#include "Osql.h"
#include "OsqlDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// COsqlDlg dialog

COsqlDlg::COsqlDlg(CWnd* pParent /*=NULL*/)
	: CDialog(COsqlDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(COsqlDlg)
	//}}AFX_DATA_INIT
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
	m_csTitle = "Osql 1.0";
}

void COsqlDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(COsqlDlg)
	DDX_Control(pDX, IDC_EDIT_OUTPUT, m_cmdOutput);
	DDX_Control(pDX, IDC_EDIT_COMMAND, m_cmdInput);
	DDX_Control(pDX, IDC_BUTTON_DO, m_buttonDo);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(COsqlDlg, CDialog)
	//{{AFX_MSG_MAP(COsqlDlg)
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BUTTON_DO, OnButtonDo)
	ON_WM_SIZE()
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// COsqlDlg message handlers

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

	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon

	// Set dialog title
	SetWindowText( m_csTitle );

	// Initialize command controls
	m_cmd.Init( this, &m_cmdOutput, &m_cmdInput, &m_buttonDo );

	// Initial resize
	CRect rect;
	GetClientRect( &rect );
	CalcSize( rect.Width(), rect.Height() );

	// Startup intro
	About();

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

void COsqlDlg::About()
{
	Output( m_csTitle, CCmdOutCtrl::Large );
	m_cmdOutput.SetFontFace( "Arial" );
	Output( "Osql is a command line graphical user interface that helps you "
		"use PL/SQL to access Oracle databases. The source code features my COdb "
		"class which is a simple C++ wrapper for the Oracle Call Interface (OCI). "
		"Osql connects to databases through SQL*Net 8.0. The OCI, SQL*Net, SQL*Plus, "
		"and the PL/SQL extension of SQL are products of Oracle Corporation. "
		"The Osql program is provided 'as-is', without warranty. In no event will "
		"the author be held liable for any damages arising from the use of this "
		"software."
		);
	Output( "Copyright (C) 1999 Ben Bryant", CCmdOutCtrl::Bold );
	Output( "[e-mail]" );
	Output( "First Objective Software, Inc." );
	Output( "www.firstobject.com" );
	m_cmdOutput.SetFontFace();
}

void COsqlDlg::Help()
{
	ShowWindow( SW_MAXIMIZE );
	m_cmdOutput.SetFontFace( "Arial" );
	Output(
		"You can immediately connect to an Oracle database, select from "
		"tables and execute SQL statements, much as you would in SQL*Plus "
		"without the semi-colons. As in SQL*Plus, there are also some non-SQL "
		"commands. Type a question mark to see a list of the commands available "
		"in Osql. Each command is listed with its named arguments and defaults "
		"if any. Only use semi-colons where they are required in PL/SQL blocks. "
		"If you enter something that does not start with any of these Osql "
		"commands, Osql tries to execute it as an SQL statement."
		);
	Output(
		"The interface for re-using and modifying chunks of commands and pl/sql "
		"blocks is handy. If you right click in the output area you can copy text "
		"to the command entry box or immediately run it. The command entry box "
		"resizes to fit multiple lines of text when you paste in a large pl/sql "
		"block or use ctrl-enter to start a new line. The escape key exits the "
		"program (closing the session if open). The first command you will need "
		"is the connect command. It works like SQL*Plus 8.0 and uses your Net80 "
		"configuration to connect to the Oracle database. No host name is "
		"necessary if the database is on the same machine."
		);
	m_cmdOutput.SetFontFace( "Courier New" );
	Output( "connect scott/tiger@host" );
	m_cmdOutput.SetFontFace( "Arial" );
	Output(
		"One of the differences from SQL*Plus is that semi-colons are not allowed "
		"except after anonymous blocks. This corresponds to what the OCI expects "
		"in an Execute statement. If you have the necessary permissions, you can "
		"try copying and pasting the following commands into Osql, one create "
		"command at a time:"
		);
	m_cmdOutput.SetFontFace( "Courier New" );
	Output( "create table table1 (n NUMBER(22), d date, c VARCHAR2(80) )" );
	Output( "create sequence sequence1" );
	Output(
		"create or replace package package1 is\n"
		"  function add_entry (c in VARCHAR2 ) return NUMBER;\n"
		"end package1;"
		);
	Output(
		"create or replace package body package1 is\n"
		"  function add_entry (c in VARCHAR2 ) return NUMBER is\n"
		"  n NUMBER(22);\n"
		"  begin\n"
		"    select sequence1.nextval into n from dual;\n"
		"    insert into table1 values ( n, sysdate, c );\n"
		"    return n;\n"
		"  end add_entry;\n"
		"end package1;"
		);
	m_cmdOutput.SetFontFace( "Arial" );
	Output(
		"After you have created these objects, you can try them out using the "
		"following commands:"
		);
	m_cmdOutput.SetFontFace( "Courier New" );
	Output( "declare n number(22); begin n := package1.add_entry( 'something' ); end;" );
	Output( "select * from table1" );
	m_cmdOutput.SetFontFace( "Arial" );
	Output( "Have Fun!!!" );
	m_cmdOutput.SetFontFace();
}

void COsqlDlg::CalcSize(int cx, int cy)
{
	// Position controls according to size of dialog within margins
	m_cmd.MoveControls( CRect( 8, 6, cx-7, cy-7 ) );
}

void COsqlDlg::OnSize(UINT nType, int cx, int cy) 
{
	CDialog::OnSize(nType, cx, cy);
	
	// Calculate child window sizes if they exist
	if ( ::IsWindow(m_buttonDo.m_hWnd) )
		CalcSize( cx, cy );
}

void COsqlDlg::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();
	}
}

HCURSOR COsqlDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void COsqlDlg::OnButtonDo() 
{
	CString csCommand;
	m_cmdInput.GetWindowText( csCommand );
	HRESULT hr = RunCommand( csCommand );
	if ( SUCCEEDED(hr) )
		m_cmdInput.SetCommandText( "" );
	m_cmdInput.SetFocus();
}

HRESULT COsqlDlg::RunCommand( CString csCommandLine ) 
{
	// Array of commands
	// This makes it very easy to add commands with required arguments and defaults
	// Arguments are comma delimited, use the last argument to receive values with commas
	static const char* szaCommandDefns[] =
	{
		// Database commands
		"db.connect connectstring=/#user/password@host",
		"db.disconnect",
		"db.exec sql#explicit execute",
		"db.select sql#select a set of records",

		// Miscellaneous commands
		"misc.about",
		"misc.? command=*",
		"misc.help#instructions for using Osql",
		"misc.max max=1#maximize dialog, specify 0 to restore",

		// Terminator
		""
	};

	// Parse command line
	m_cmd.ParseCommandLine( csCommandLine, szaCommandDefns );
	CString csCommand = m_cmd.GetCommand();
	Output( m_cmd.GetCommandLine(), CCmdOutCtrl::Bold );
	CString csService = m_cmd.GetService();
	CStringArray& csaArgs = *m_cmd.GetArgs();

	// Check command
	if ( csCommand.IsEmpty() )
	{
		// Command not recognized, pretend it was exec
		csCommand = "exec";
		csaArgs.Add( csCommandLine );
		csService = "db";
	}
	else if ( ! m_cmd.GetSyntaxError().IsEmpty() )
	{
		// Syntax error
		Output( m_cmd.GetSyntaxError() );
		csCommand.Empty();
	}

	// Prepare to do command
	CWaitCursor wait;
	HRESULT hr = 0;

	// Non-db commands
	if ( csCommand == "?" )
	{
		m_cmdOutput.OutputHelp( csaArgs[0], szaCommandDefns );
		return 0;
	}
	else if ( csCommand == "max" )
	{
		if ( atoi(csaArgs[0]) )
			ShowWindow( SW_MAXIMIZE );
		else
			ShowWindow( SW_RESTORE );
	}
	else if ( csCommand == "about" )
		About();
	else if ( csCommand == "help" )
		Help();

	// Database
	else if ( csCommand == "connect" )
	{
		m_db.StartLog();
		hr = m_db.Open( csaArgs[0] );
		Output( m_db.GetLog() );
	}
	else if ( csCommand == "disconnect" )
		hr = m_db.Close();
	else if ( csCommand == "exec" )
		hr = m_db.Exec( csaArgs[0] );
	else if ( csCommand == "select" )
	{
		// Set SQL in recordset and run it
		CString csSelect( csaArgs[0] );
		if ( csSelect.GetLength() < 7 || csSelect.Left(7) != "select " )
			csSelect = "select " + csSelect;
		hr = m_db.Select( csSelect );

		// Loop through results
		while ( SUCCEEDED(hr) && ! m_db.IsEOS() )
		{
			Output( m_db.GetResults() );
			m_db.FetchNext();
			hr = 0;
		}
	}

	if ( csService == "db" )
		Output( m_db.GetErrorDescription() );
	return hr;
}