podbc.h

sloedgy open sip stack source code

/*
 * podbc.h
 *
 * Virteos ODBC Implementation for PWLib Library.
 *
 * Virteos is a Trade Mark of ISVO (Asia) Pte Ltd.
 *
 * Copyright (c) 2005 ISVO (Asia) Pte Ltd. All Rights Reserved.
 *
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.0 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
 * the License for the specific language governing rights and limitations
 * under the License.
 *
 *
 * The Original Code is derived from and used in conjunction with the 
 * pwlib Libaray of the OpenH323 Project (www.openh323.org/)
 *
 * The Initial Developer of the Original Code is ISVO (Asia) Pte Ltd.
 *
 *	 Portions: Simple ODBC Wrapper Article www.codeproject.com
 *
 * Contributor(s): ______________________________________.
 *
 * $Log: podbc.h,v $
 * Revision 1.1  2006/08/04 03:34:05  joegenbaclor
 * Moved pwlib and OPAL headers to root include directory
 *
 * Revision 1.1  2006/06/29 04:17:42  joegenbaclor
 * *** empty log message ***
 *
 * Revision 1.1  2006/01/27 06:35:30  shorne
 * added ODBC support
 *
 *
*/

/**
	ODBC Support for PWLIB

  Class Description
	 PODBC				:  Main DataBase Connection Class (Derive Class for Error Handling)
	 PODBC::ConnectData :  Class used to store information for Connecting to DataSource
	 PODBC::Table		:  Retrieved Data Structure (RecordSet) from Table or Select SQL Query
	 PODBC::Row			:  Record Pointer Class for the PODBC::Table (PArray of Fields)
	 PODBC::Field		:  Database Field Information (Field Structure & Data(Bound))
	 PODBC::Field:Bind	:  DataTypes Bound to the RecordSet (change with Record Navigation)
	 PDSNConnection		:  Derived Class of PODBC for ODBC Configured Connections
	 PODBCStmt			:  Wrapper for RecordSet (Internal)
	 PODBCRecord		:  Handle Retrieve/Post/Bind Data from RecordSet (Internal)

\begin{verbatim}
  Example of Use

  PODBC link;
  PODBC::ConnectData data;
  data.DBPath = "test.mdb";

  if (link.DataSource(PODBC::MSAccess,data)) {
    // Load a Database Table (could also be a SELECT Query)
			PODBC::Table table(&link,"FooTable");
	// Bind to Column 1 
			PODBC::Field & field = table.Column(1):
    // Display Contents
			cout << " Value " << field.AsString(); << endl;
	// Move to Record 2 of fooTable
			table[2];  
	// Display contents of Record 2 Column 1
			cout << " Value " << field.AsString(); << endl;
	// Set New Value for Record 2 Field 1 of FooTable
			field.SetValue("NewValue");
	// Send Update to Database.
			field.Post();

    // To Add New Record.(with Default Values)
			table.NewRow();
	// Alter the Value of field 1
			field.SetValue("Somethng");
	// Post the New Field to the Database
			field.Post();

    // Run General Query;
	    PString SQLStmt = "INSERT foo into [FooTable] ..."
		Link.Query(SQLStmt);
  }
// Disconnect from ODBC Source
  link.Disconnect();

\end{verbatim}
*/
//--

#if !defined(PODBC_H)
#define PODBC_H

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

#include <ptlib.h>
#include <tchar.h>
#include <sql.h> 
#include <sqlext.h>
#include <odbcinst.h>

#pragma comment(lib,"odbc32.lib")
#pragma comment(lib,"odbcCP32.lib")


// Max SQL String Data Length
#define MAX_DATA_LEN 1024

//--
/** PODBC Statement Class 
	This class is use to parse store queries and Fetch data
	It is not designed to process the actual data only access it.
	PODBC::Record is used to Bind/Retrieve/Store Data Elements. .
*/

class PODBC;
class PODBCRecord;
class PODBCStmt : public PObject
{
  PCLASSINFO(PODBCStmt, PObject);

HSTMT m_hStmt;

public:

  /**@name Constructor/Deconstructor */
  //@{
	/** Constructor PODBC (Datasources call) or thro' DSNConnection (Connection call). 
	    In General this class is constructed within the PODBC::Table Class.
	*/
	  PODBCStmt(PODBC * odbc);

	/** Deconstructor. This Class should be available for the duration of which
		a specific query/table is required and be deconstructed at the time of
		the PODBC::Table deconstruction.
	*/
	  ~PODBCStmt();
  //@}

 /**@name Handles */
  //@{  
	  /** Statement Handle Created by the Query Function.
	  */
	  operator HSTMT() { return m_hStmt; };
  //@}


 /**@name Data Management */
  //@{ 
	  /** IsValid Checks to ensure a Handle has been allocated and
			is effective.
	  */
	  BOOL IsValid();

	  /** GetChangedRowCount retreives the number of rows updated/altered by
			UPDATE/INSERT statements.
	  */
	  DWORD GetChangedRowCount(void);

	  /** Query function is the Main function to pass SQL statements to retreive/
			add/Modify database data. It accepts generally acceptable SQL Statements.
			  ie. Select * from [table-x]
	  */
	  BOOL Query(PString strSQL);
  //@}

  /**@name Data Retrieval */
  //@{  
	  /** Fetch General call to retreive the next row of data. 
	  */
	  BOOL Fetch();

	  /** FetchRow More detailed fetching of Rows. This allows you to fetch an
			Absolute row or a row relative to the current row fetched.
	  */
	  BOOL FetchRow(PINDEX nRow,BOOL Absolute=1);

	  /** FetchPrevious Fetch the previous Row from current row.
	  */
	  BOOL FetchPrevious();

	  /** FetchNext: Fetch the Next row. 
	  */
	  BOOL FetchNext();

	  /** FetchFirst Fetch the First row in the RecordSet 
	  */
	  BOOL FetchFirst();

	  /** FetchLast Fetch the Last row in the RecordSet
	  */
	  BOOL FetchLast();

	  /** Cancel the Current Statement
	  */
	  BOOL Cancel();
  //@}

  /**@name Utilities */
  //@{ 
	  /** Retreive the List of Tables from the current Datasource
			The option field can be used to specify the Table Types
			ie "TABLE" for Tables or "VIEW" for preconfigured datasource
			queries. *Further investigation is required*
	  */
	  PStringArray TableList(PString option = "");


	  /** Is the SQL Instruction OK
			If an Error is detected then GetLastError is called
			to Retrieve the SQL Error Information and Returns FALSE
	  */
	  BOOL SQL_OK(SQLRETURN res);

	  /** Get the Last Error 
			This returns the Error ID & String to PODBC::OnSQLError
	  */
	  void GetLastError();
  //@}

	  PODBC * odbclink;		/// Reference to the PODBC Class

	  int dbase; /// Database Type connecting to

};



/** PODBC Class
	The Main ODBC class. This Class should be used in the there is 
	not a preconfigured DSN setup in the MDAC. This class will use
	the applicable ODBC drivers to connect to a compliant Datasource.
	It Supports a wide variety of Datasources but others can added 
	by simply creating your custom connection string and then calling
	PODBC::Connect. For Defined sources the PODBC::DataSource function
	should be used.
*/

class PODBCRecord;
class PODBC  : public PObject
{
  PCLASSINFO(PODBC, PObject);

public:

  /**@name Constructor/Deconstructor */
  //@{
	/** Constructor
	*/
	PODBC();

	/** Deconstructor
	*/
	~PODBC();
  //@}

  /**@name Enumerators */
  //@{
	/** Raw SQL data type codes Refer <sql.h> SQL_*
		This list is not inclusive. If an item 
		is not listed or Unknown it is treated 
		as a character string.

	*/
	  enum FieldTypes
	  {
		LongVarChar     =-1,	 
		Binary			=-2,
		VarBinary       =-3,
		LongVarBinary	=-4,
		BigInt			=-5,
		TinyInt		    =-6,
		Bit             =-7,	 /// Boolean
		Guid			=-11,
		Unknown			= 0,
		Char			= 1,
		Numeric			= 2,
		Decimal			= 3,
		Integer			= 4,
		SmallInt		= 5,
		Float			= 6,
		Real			= 7,
		Double			= 8,
		DateTime		= 9,
		VarChar			=12,		
		Date			=91,		/// Structure   
		Time			=92,		/// Structure
		TimeStamp		=93			/// Structure	PTime		
	  };

	/** Converted Pwlib Field Types.	
		Data is stored as a PString
		and the pwType enumerator indicates
		the conversion required on the PODBC::Field.
	*/
	enum PwType
	  { 
		oPString,	// String Value
		oBOOL,		// Boolean 
		ochar,		// Character
		oshort,		// Short	
		oint,		// Integer  use .AsInteger()
		olong,		// long		
		odouble,	// Double   use .AsReal()
		oPBYTEArray,// Binary Data
		oPInt64,	// BigInt	use .AsInt64()
		oPTime,		// Time		use  PTime( "Value" ) 
		oPGUID		// GUID		use  PGUID( "Value" ) To Be Implemented...?
	};

	/** Datasources that are supported by this implementation
		used in the PODBC::DataSource Function.
	*/

	enum DataSources
	{
		mySQL,			
		MSSQL,			
		Oracle,			
		IBM_DB2,
		DBASE,
		Paradox,
		Excel,
		Ascii,
		Foxpro,
		MSAccess,
		postgreSQL
	};

	/** MSSQL protocols.If your interested?
	*/
	enum MSSQLProtocols
	{
	   MSSQLNamedPipes,
	   MSSQLWinSock,
	   MSSQLIPX,
	   MSSQLBanyan,
	   MSSQLRPC
	 };

  //@}

  /**@name Connection Class */
  //@{
	/** This class is a multipurpose use
		class for storing parameters when
		initiating connection to DataSource.
		Not all field are required. By default
		all non-essential params are set to a 
		datasource specific default value.
	*/
	 class ConnectData
	  {
		public:
		  PFilePath DBPath;    /// Database file Path (not Oracle,xxSQL)
		  PString DefDir;	   /// Used with Paradox/DBase/Excel (& mySQL db)
		  PString User;		   /// UserName
		  PString Pass;		   /// Password
		  BOOL Excl_Trust;	   /// Whether Datasource is locked or Trusted.
		  PString Host;		   /// URL for Host Datasouce xxSQL
		  int Port;			   /// Port to connect to mySQL
		  int opt;			   /// General Option Value.mySQL & Paradox
	  };
  //@}


  /**@name Database Field Class */
  //@{
	 /** Class for Field Data
	 */
	  class Row;
	  class Field : public PObject
	  {
		  PCLASSINFO(Field, PObject);
	  public:

		  /** SQL compliant Bound DataTypes.
				The appropriate Field is bound
				to the SQL Driver and alters 
				when a new record is fetched.
		  */
		  class Bind
		  {
		  public:
			   PString				sbin;		/// Strings & Binary Data
			   PString				sbinlong;   /// Long Data
			   short int			ssint;		/// Short Integer		SQLSMALLINT
			   long int				slint;		/// Integer				SQLINTEGER
			   double				sdoub;		/// Double				SQLDOUBLE
			   unsigned char		sbit;		/// Bit					SQLCHAR
			   unsigned char *		suchar;		/// Unsigned char		SQLCHAR *
			   _int64				sbint;		/// Bit Integer			SQLBIGINT
			   DATE_STRUCT			date;	    /// Date Structure	
			   TIME_STRUCT			time;		/// Time Structure
			   TIMESTAMP_STRUCT		timestamp;	/// TimeStamp Structure
			   SQLGUID				guid;		/// GUID Structure (not Fully Supported)
			   SQLINTEGER			dataLen;	/// DataLength pointer (StrLen_or_Ind for Col Bind)
		  };

		/** Post the Changes back to the Database
		*/
		  BOOL Post();

		/** Returns a String representation of the field.
		*/
		  PString operator=(const PString & str);

		/** Display the Field Data as String
		*/
		  PString AsString();	

		/** Set the Field Data. Note a Post() must be called
			to post the changes back to the database.
		*/
		  void SetValue(PString value);  /// Set the Value

		/** Initialise/Set the Default values for Field of New Record
		*/
		  void SetDefaultValues();

		/** DataFragment Data is broken into fragment to be passed
			to the Database
		*/
		  BOOL DataFragment(PString & Buffer ,PINDEX & fragment, long & size);

		/** Settings
		*/
		 /// Data
			  Bind	Data;			 /// Data Field to ODBC Bind to
			  PwType  Type;			 /// pwlib Type for conversion
			  FieldTypes ODBCType;	 /// ODBC Type (For saving/Conversion)

		 /// Column
			  PString Name;			 /// Column Name
			  PINDEX col;			 /// Column Number (For Saving/Conversion)

         /// Column Attributes
			  BOOL isReadOnly;		 /// Is Field Readonly
			  BOOL isNullable;		 /// Allows Nulls
			  BOOL isAutoInc;		 /// Field AutoIncrements
			  int Decimals;			 /// Number of decimal places to Round
			  BOOL LongData;		 /// LongData Length is Required

		 /// RecordHolder Reference
			  Row * row;			 /// Back Reference to the Row
	  };
  //@}


   /**@name Database Row Class */
  //@{ 
	  /** This class functions as a simple wrapper 
			of the PODBCStmt class to fetch/Save
			data to the Datasource. Data is fetched
			on a need to basis and not cached except
			to create a new row.