podbc.cxx

pwlib源码库

/*
 * PODBC.cxx
 *
 * Virteos ODBC Implementation for PWLib Library.
 *
 * Virteos is a Trade Mark of ISVO (Asia) Pte Ltd.
 *
 * Copyright (c) 2004 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.cxx,v $
 * Revision 1.2  2005/08/09 09:08:12  rjongbloed
 * Merged new video code from branch back to the trunk.
 *
 * Revision 1.1.2.1  2005/04/25 13:09:49  shorne
 * Initial version
 *
 *
*/

#include <ptlib.h>
#include <ptlib/PODBC.h>

#pragma warning(disable:4244)
#pragma warning(disable:4100)

//// Utilities
//////////////////////////////////////////////////////////////////

static void AddField(PODBC::Field & newField, const PString FieldName, 
							PODBC::FieldTypes odbctype, PODBC::PwType pwtype)
{
  if (newField.Name.GetLength() == 0)   /// If not already set..
  {
	newField.ODBCType = odbctype;
	newField.Name = FieldName;
	newField.Type = pwtype;
  }
}

/// Simple converters
///////////////////////////////////////////////////////////////////////////////////////
/// To Bound Data

static Convert(long int & data, PString field)
{
	data = field.AsInteger();
}

static Convert(short int & data, PString field)
{
	data = field.AsInteger();
}

static Convert(unsigned char * data, PString field)
{
    data = (unsigned char *)(const char *)field;
}


static Convert(unsigned char & data, PString field)
{
	int f = field.AsInteger();
	data = (unsigned char)f;
}


static Convert(_int64 & data, PString field)
{
	data = field.AsInt64();
}

static Convert(double & data, PString field,int Precision = PODBCRecord::Precision)
{
	/// Reformat to the Required Decimal places
	data = PString(PString::Decimal,field.AsReal(),Precision).AsReal();
}

static Convert(DATE_STRUCT & data, PString field)
{
	PTime t = PTime(field);

	data.day = t.GetDay();
	data.month = t.GetMonth();
	data.year = t.GetYear();
}

static Convert(TIME_STRUCT & data, PString field)
{
	PTime t = PTime(field);

	data.second = t.GetSecond();
	data.minute = t.GetMinute();
	data.hour = t.GetHour();
}
			   		
static Convert(TIMESTAMP_STRUCT & data, PString field)
{
	PTime t = PTime(field);

	data.day = t.GetDay();
	data.month = t.GetMonth();
	data.year = t.GetYear();
	data.second = t.GetSecond();
	data.minute = t.GetMinute();
	data.hour = t.GetHour();
}

static Convert(SQLGUID & data, PString field)
{
// Yet To Be Implemented.
	field = PString();

}


/// To PString
///////////////////////////////////////////////////////////////////////////////////////
template <typename SQLField>
static PString Convert(SQLField field)
{
	return PString(field);
}

static PString Convert(double field,int Precision = PODBCRecord::Precision)
{
	return PString(PString::Decimal,field,Precision);
}

static PString Convert(unsigned char * field)
{
	return PString(*field);
}

static PString Convert(DATE_STRUCT date)
{
	return PTime(0,0,0,date.day,date.month,date.year).AsString(PODBCRecord::TimeFormat);
}

static PString Convert(TIME_STRUCT time)
{
	return PTime(time.second,time.minute,time.hour,0,0,0).AsString(PODBCRecord::TimeFormat);
}
			   		
static PString Convert(TIMESTAMP_STRUCT	timestamp)
{
	return PTime(timestamp.second,timestamp.minute,
		timestamp.hour,timestamp.day,timestamp.month,timestamp.year).AsString(PODBCRecord::TimeFormat);
}

static PString Convert(SQLGUID guid)
{
// To Be Implemented.
	return PString();
}


///PODBC
/////////////////////////////////////////////////////////////////


PODBC::PODBC()
{
	m_hDBC              = NULL;
	m_hEnv              = NULL;
	m_nReturn           = SQL_ERROR;
}

PODBC::~PODBC()
{
   if( m_hDBC != NULL ) {
    m_nReturn = SQLFreeHandle( SQL_HANDLE_DBC,  m_hDBC );
   }
   if( m_hEnv!=NULL )
    m_nReturn = SQLFreeHandle( SQL_HANDLE_ENV, m_hEnv );
}


BOOL PODBC::Connect(LPCTSTR svSource)
{
   int nConnect = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_hEnv );
   if( nConnect == SQL_SUCCESS || nConnect == SQL_SUCCESS_WITH_INFO ) {
    nConnect = SQLSetEnvAttr( m_hEnv, 
                      SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0 );
    if( nConnect == SQL_SUCCESS || nConnect == SQL_SUCCESS_WITH_INFO ) {
     nConnect = SQLAllocHandle( SQL_HANDLE_DBC, m_hEnv, &m_hDBC );
     if( nConnect == SQL_SUCCESS || nConnect == SQL_SUCCESS_WITH_INFO ) {
      SQLSetConnectOption( m_hDBC,SQL_LOGIN_TIMEOUT,5 );                
      short shortResult = 0;
      SQLTCHAR szOutConnectString[ 1024 ];
      nConnect = SQLDriverConnect( m_hDBC,	// Connection Handle
          NULL,                           // Window Handle
          (SQLTCHAR*)svSource,			  // InConnectionString
          _tcslen(svSource),              // StringLength1
          szOutConnectString,             // OutConnectionString
          sizeof( szOutConnectString ),   // Buffer length
          &shortResult,                   // StringLength2Ptr
          SQL_DRIVER_NOPROMPT );          // no User prompt
      return ((nConnect == SQL_SUCCESS) || (nConnect == SQL_SUCCESS_WITH_INFO));
     }
    }
   }
   if( m_hDBC != NULL ) {
    m_nReturn = SQLDisconnect( m_hDBC );
    m_nReturn = SQLFreeHandle( SQL_HANDLE_DBC,  m_hDBC );
   }
   if( m_hEnv!=NULL )
    m_nReturn = SQLFreeHandle( SQL_HANDLE_ENV, m_hEnv );
   m_hDBC              = NULL;
   m_hEnv              = NULL;
   m_nReturn           = SQL_ERROR;
   return ((m_nReturn == SQL_SUCCESS) || (m_nReturn == SQL_SUCCESS_WITH_INFO));
}


void PODBC::Disconnect()
  {
   if( m_hDBC != NULL )
   {
    m_nReturn = SQLDisconnect( m_hDBC );
    m_hDBC = NULL;
   }
}


//--

BOOL PODBC::Connect_MSSQL(PString User,PString Pass, 
         PString Host,BOOL Trusted, 
         MSSQLProtocols Proto)
{  
	PString Network = PString();

	   switch(Proto)
	   {
	   case MSSQLNamedPipes:
		Network ="dbnmpntw";
		break;
	   case MSSQLWinSock:
		Network ="dbmssocn";
		break;
	   case MSSQLIPX:
		Network = "dbmsspxn";
		break;
	   case MSSQLBanyan:
		Network = "dbmsvinn";
		break;
	   case MSSQLRPC:
		Network = "dbmsrpcn";
		break;
	   default:
		Network = "dbmssocn";
		break;
	   }

	PString ConStr = "Driver={SQL Server};Server=" + Host + ";Uid=" + User + ";Pwd=" +
		 Pass +";Trusted_Connection=" + (Trusted ? "Yes" : "No") + ";Network=" + Network + ";";

   return PODBC::Connect(ConStr);
}


 //--
BOOL PODBC::Connect_DB2(PFilePath DBPath)
{
   PString ConStr ="Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + DBPath + ";";
   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_XLS(PFilePath XLSPath,PString DefDir)
{
   PString ConStr = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;bq="+ XLSPath 
						+ ";DefaultDir=" + DefDir + ";";
   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_TXT(PFilePath TXTPath)
{
   PString ConStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="+ TXTPath 
						+ ";Extensions=asc,csv,tab,txt;";

   return PODBC::Connect(ConStr);
};

 //--
BOOL PODBC::Connect_FOX(PFilePath DBPath,PString User,
          PString Pass,PString Type,
          BOOL Exclusive)
{
   PString ConStr = "Driver={Microsoft Visual Foxpro Driver};Uid=" + User + ";Pwd=" + Pass 
				+";SourceDB=" + DBPath + ";SourceType=" + Type + ";Exclusive=" + (Exclusive ? "yes": "no") + ";";
   
   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_MDB(PFilePath MDBPath,PString User,
            PString Pass,BOOL Exclusive)
{
   PString ConStr = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + MDBPath + ";Uid=" + User +
	   ";Pwd=" + Pass + ";Exclusive=" + (Exclusive ? "yes" : "no");

   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_PDOX(PDirectory DBPath,PDirectory DefaultDir,int version)
{
 
   PString driver = "3.X";
   if (version == 4) driver = "4.X";
   if (version > 4) driver = "5.X";

   PString ConStr = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox " + 
						 driver + ";DefaultDir=" + DefaultDir +
						 "\\;Dbq=" + DBPath + "\\;CollatingSequence=ASCII;";

   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_DBASE(PDirectory DBPath)
{

   PString ConStr = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + DBPath + ";";
   return PODBC::Connect(ConStr);
}
 //--
BOOL PODBC::Connect_Oracle(PString Server,PString User, PString Pass)
{

  PString ConStr = "Driver={Microsoft ODBC for Oracle};Server=" + Server + 
					";Uid=" + User + ";Pwd=" + Pass + ";";

  return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::Connect_mySQL(PString User,PString Pass,
         PString Host, int Port,int Option)
{

   PString ConStr = "Driver={MySQL ODBC 3.51 Driver};Uid=" + User + ";Pwd=" + Pass
				 + ";Server=" + Host + ";Port=" + PString(Port) + ";";

   return PODBC::Connect(ConStr);
}

BOOL PODBC::ConnectDB_mySQL(PString DB,PString User,
        PString Pass,PString Host, int Port,int Option)
{
   PString ConStr = "Driver={MySQL ODBC 3.51 Driver};Database=" + DB + ";Uid=" + User
					+ ";Pwd=" + Pass +";Server=" + Host + ";Port=" + PString(Port) + ";";

   return PODBC::Connect(ConStr);
}

 //--
BOOL PODBC::DataSource(DataSources Source, ConnectData Data)
{

	dbase = Source;

	switch (Source)
	{
		case PODBC::mySQL:
			if (Data.Host.GetLength() == 0) Data.Host = "localhost";
			if (Data.Port == 0) Data.Port = 3306;

			if (Data.DefDir.GetLength() == 0) {
				return Connect_mySQL(Data.User,Data.Pass,
					Data.Host,Data.Port,Data.opt);
			} else {
			  return ConnectDB_mySQL(Data.DefDir,Data.User,Data.Pass,
					Data.Host,Data.Port,Data.opt);
			}
		case PODBC::MSSQL:
			  if (Data.Host.GetLength() == 0) Data.Host = "(local)";
			  return Connect_MSSQL(Data.User,Data.Pass, 
					 Data.Host,Data.Excl_Trust, 
					 (MSSQLProtocols)Data.opt);
		case PODBC::Oracle:
			  return Connect_Oracle(Data.Host,Data.User, Data.Pass);
		case PODBC::IBM_DB2:
			  return Connect_DB2(Data.DBPath);
		case PODBC::DBASE:
			  return Connect_DBASE(Data.DBPath);
		case PODBC::Paradox:
			  return Connect_PDOX(Data.DBPath,Data.DefDir,
						Data.opt);
		case PODBC::Excel:
			  return Connect_XLS(Data.DBPath,Data.DefDir);
		case PODBC::Ascii:
			  return Connect_TXT(Data.DBPath);
		case PODBC::Foxpro:
			  return Connect_FOX(Data.DBPath,Data.User,
					  Data.Pass,"DBF",
					  Data.Excl_Trust);
		case PODBC::MSAccess:
			  return Connect_MDB(Data.DBPath,Data.User,
					  Data.Pass,Data.Excl_Trust);
	};

	return FALSE;
}

PStringArray PODBC::TableList(PString option)
{
    PODBCStmt data(this);
	return data.TableList(option);
}

PODBC::Table PODBC::LoadTable(PString table)
{
  PODBC::Table newTable(this,table);
  return newTable;
}

BOOL PODBC::Query(PString Query)
{
  if (m_hDBC == NULL)
	  return FALSE;

   PODBCStmt stmt(this);
	return stmt.Query(Query);
}

void PODBC::SetPrecision(int Digit)
{
	PODBCRecord::Precision = Digit;
}

void PODBC::SetTimeFormat(PTime::TimeFormat tformat)
{
	PODBCRecord::TimeFormat = tformat;
}

BOOL PODBC::NeedLongDataLen()
{
  
  PString  f;

  SQLGetInfo(m_hDBC,SQL_NEED_LONG_DATA_LEN,
   f.GetPointer(2),2, NULL);

	if (f == "N")   
		  return FALSE;
	else                                 
		  return TRUE;
}
/////////////////////////////////////////////////////////////////////////////
// PODBC::Field

PString PODBC::Field::AsString()
{
	 
Bind & b = Data;	   /// Bound Buffer container		
BOOL B = isReadOnly;   /// ReadOnly Columns are not Buffer Bound and have to get Data;
SQLINTEGER len = MAX_DATA_LEN;   

	switch (ODBCType) {
		case PODBC::BigInt:
		  if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.sbint,0,&b.dataLen);
			return Convert(b.sbint);

		case PODBC::TinyInt:
		case PODBC::Bit:
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.sbit,0,&b.dataLen);
			 return Convert(b.sbit);				
	
		case PODBC::Char:
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.suchar,0,&b.dataLen);
			 return Convert(b.suchar);		

		case PODBC::Integer:
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.slint,0,&b.dataLen);
			 return Convert(b.slint);
				
		case PODBC::SmallInt:
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.ssint,0,&b.dataLen);
			 return Convert(b.ssint);

		case PODBC::Numeric:
		case PODBC::Decimal:
		case PODBC::Float:
		case PODBC::Real:
		case PODBC::Double:
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.sdoub,0,&b.dataLen);
			 return Convert(b.sdoub,Decimals);

	  /// Date Times
		case PODBC::Date: 
			if (B) SQLGetData(*row->rec->Stmt,col,ODBCType,&b.date,0,&b.dataLen);
			 return Convert(b.date);

		case PODBC::Time: