podbc.h

Go to the documentation of this file.

/*
 * 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): ______________________________________.
 *
 * $Revision: 19008 $
 * $Author: rjongbloed $
 * $Date: 2007-11-29 09:17:41 +0000 (Thu, 29 Nov 2007) $
 */

//--

#if !defined(PODBC_H)
#define PODBC_H

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

#ifndef _PTLIB_H
#include <ptlib.h>
#endif

#ifdef P_ODBC

namespace PTODBC {

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

#ifdef _MSC_VER
 #include <tchar.h>
 #pragma comment(lib,"odbc32.lib")
 #pragma comment(lib,"odbcCP32.lib")
 typedef ::LPCTSTR LPCTSTR;
 typedef ::LPVOID LPVOID;
 typedef ::DWORD DWORD;
 typedef ::LONG LONG;
 typedef ::ULONG ULONG;
 typedef ::USHORT USHORT;
#else
 typedef const char *LPCTSTR;
 typedef void *LPVOID;
 typedef long long _int64;
 typedef long LONG;
 #define _T(x) x
#endif // _MSC_VER


// Max SQL String Data Length
#define MAX_DATA_LEN 1024

}; // PTODBC

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

PTODBC::HSTMT m_hStmt;

public:

          PODBCStmt(PODBC * odbc);

          ~PODBCStmt();

          operator PTODBC::HSTMT() { return m_hStmt; };


          PBoolean IsValid();

          PTODBC::DWORD GetChangedRowCount(void);

          PBoolean Query(PString strSQL);

          PBoolean Fetch();

          PBoolean FetchRow(PINDEX nRow,PBoolean Absolute=1);

          PBoolean FetchPrevious();

          PBoolean FetchNext();

          PBoolean FetchFirst();

          PBoolean FetchLast();

          PBoolean Cancel();

          PStringArray TableList(PString option = "");


          PBoolean SQL_OK(PTODBC::SQLRETURN res);

          void GetLastError();

          PODBC * odbclink;             

          int dbase; 

};



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

public:

        PODBC();

        ~PODBC();

          enum FieldTypes
          {
                LongVarChar     =-1,     
                Binary                  =-2,
                VarBinary       =-3,
                LongVarBinary   =-4,
                BigInt                  =-5,
                TinyInt             =-6,
                Bit             =-7,     
                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,            
                Time                    =92,            
                TimeStamp               =93                     
          };

        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...?
        };

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

        enum MSSQLProtocols
        {
           MSSQLNamedPipes,
           MSSQLWinSock,
           MSSQLIPX,
           MSSQLBanyan,
           MSSQLRPC
         };


         class ConnectData
          {
                public:
                  PFilePath DBPath;    
                  PString DefDir;          
                  PString User;            
                  PString Pass;            
                  PBoolean Excl_Trust;     
                  PString Host;            
                  int Port;                        
                  int opt;                         
          };


          class Row;
          class Field : public PObject
          {
                  PCLASSINFO(Field, PObject);
          public:

                  class Bind
                  {
                  public:
                           PString                              sbin;           
                           PString                              sbinlong;   
                           short int                    ssint;          
                           long int                             slint;          
                           double                               sdoub;          
                           unsigned char                sbit;           
                           unsigned char *              suchar;         
#if _WIN32
                           _int64                               sbint;          
#else
         PTODBC::_int64                         sbint;          
#endif
                           PTODBC::DATE_STRUCT                  date;       
                           PTODBC::TIME_STRUCT                  time;           
                           PTODBC::TIMESTAMP_STRUCT             timestamp;      
                           PTODBC::SQLGUID                              guid;           
                           PTODBC::SQLINTEGER                   dataLen;        
                  };

                  PBoolean Post();

                  PString operator=(const PString & str);

                  PString AsString();   

                  void SetValue(PString value);  

                  void SetDefaultValues();

                  PBoolean DataFragment(PString & Buffer ,PINDEX & fragment, PTODBC::SQLINTEGER & size);


                          Bind  Data;                    
                          PwType  Type;                  
                          FieldTypes ODBCType;   

                          PString Name;                  
                          PINDEX col;                    

                          PBoolean isReadOnly;           
                          PBoolean isNullable;           
                          PBoolean isAutoInc;            
                          int Decimals;                  
                          PBoolean LongData;             

                          Row * row;                     
          };


          class Row : public PObject
          {
          public:

                  Row(PODBCStmt * stmt);

                  Field & Column(PINDEX col);

                  Field & Column(PString name);

                  PStringArray ColumnNames();

                  PINDEX Columns();

                  PINDEX Rows();

                  Field & operator[] (PINDEX col);

                  Field & operator[] (PString col);

                  PBoolean Navigate(PINDEX row);

                  void SetNewRow();

                  PBoolean Post();

                  PBoolean Delete(PINDEX row =0);

                  PODBCRecord * rec;      

                  PINDEX CurRow;          
                  PBoolean NewRow;                        
                  PINDEX RowCount;                

          protected:
                  PArray<Field> Fields;   
          };

  class Table : public PObject
  {
  public:

          Table(PODBC * odbc, PString Query);

          ~Table();

          Row NewRow();

          PBoolean DeleteRow(PINDEX row = 0);

          PBoolean Post();

          PINDEX Rows();

          PINDEX Columns();

          PStringArray ColumnNames();

          Row & RecordHandler();

          Row & operator[] (PINDEX row);

          Field & operator() (PINDEX row, PINDEX col);

          Field & Column(PINDEX col);

          Field & Column(PString Name);

  protected:
          PODBCStmt stmt;                       
          PString tableName;            
          Row * RowHandler;                     
  };

          Table LoadTable(PString table);

          PBoolean Query(PString Query);


          PBoolean DataSource(DataSources Source, ConnectData Data);
  
          virtual PBoolean Connect(PTODBC::LPCTSTR svSource);

          PBoolean Connect_DB2(PFilePath DBPath);

          PBoolean Connect_XLS(PFilePath XLSPath,PString DefDir = "");

          PBoolean Connect_TXT(PFilePath TXTPath);

          PBoolean Connect_FOX(PFilePath DBPath,PString User = "",
                          PString Pass = "",PString Type= "DBF",
                          PBoolean Exclusive=PFalse);

          PBoolean Connect_MDB(PFilePath MDBPath,PString User ="",
                                PString Pass = "",PBoolean Exclusive=PFalse);

          PBoolean Connect_PDOX(PDirectory DBPath,PDirectory DefaultDir,
                                int version =5);

          PBoolean Connect_Oracle(PString Server,PString User="", PString Pass="");

          PBoolean Connect_DBASE(PDirectory DBPath);

          PBoolean Connect_MSSQL(PString User="",PString Pass="", 
                         PString Host ="(local)",PBoolean Trusted = PTrue, 
                         MSSQLProtocols Proto=MSSQLNamedPipes);

          PBoolean Connect_mySQL(PString User="",PString Pass="",
                         PString Host= "localhost",
                         int Port=3306,int Option=0);

          PBoolean ConnectDB_mySQL(PString DB,PString User="",
                        PString Pass="",PString Host= "localhost",
                        int Port=3306,int Option=0);

          PBoolean Connect_postgreSQL(PString DB,PString User,
        PString Pass,PString Host, int Port=5432,int Option=0);

          void Disconnect();

          PStringArray TableList(PString option = "");

          PBoolean NeedLongDataLen();

          virtual void OnSQLError(PString RetCode, PString RetString) {};


          void SetPrecision(int Digit);

          void SetTimeFormat(PTime::TimeFormat tformat);

          operator PTODBC::HDBC() { return m_hDBC; };
                
         PODBC::DataSources  dbase; 

protected:

  PTODBC::SQLRETURN       m_nReturn;      // Internal SQL Error code
  PTODBC::HENV            m_hEnv;         // Handle to environment
  PTODBC::HDBC            m_hDBC;         // Handle to database connection
};

class PDSNConnection : public PODBC
{
  PCLASSINFO(PDSNConnection, PODBC);

public:

          PDSNConnection();
          ~PDSNConnection();

          PBoolean Connect( PString Source ,PString Username, PString Password);

};

 //--
class PODBCRecord : public PObject
{
  
  PCLASSINFO(PODBCRecord, PObject);

PTODBC::HSTMT m_hStmt;

public:

           PODBCRecord(PODBCStmt * hStmt);

          ~PODBCRecord(){};

          void Data(PINDEX Column, PODBC::Field & field);

          PBoolean InternalGetData(
                PTODBC::USHORT Column,
                PTODBC::LPVOID pBuffer, 
                PTODBC::ULONG pBufLen,
                PTODBC::SQLINTEGER * dataLen=NULL,
                int Type=SQL_C_DEFAULT
          );

          /* Get Long Character Data. Long Data fields cannot be bound
                        and Data must be Got from the RecordSet.
          */
          PString GetLongData(PINDEX Column);
  
          PBoolean PostNew(PODBC::Row & rec);

          PBoolean PostUpdate(PODBC::Row & rec);

          PBoolean PostDelete(PINDEX row= 1);

          PBoolean InternalSaveLongData(PTODBC::SQLRETURN nRet,PODBC::Row & rec);

          PBoolean InternalBindColumn(
            PTODBC::USHORT Column,PTODBC::LPVOID pBuffer,
            PTODBC::ULONG pBufferSize,
            PTODBC::LONG * pReturnedBufferSize=NULL,
            PTODBC::USHORT nType=SQL_C_TCHAR
          );

          PINDEX ColumnByName(PString Column);

          PINDEX ColumnCount();

          PODBC::FieldTypes ColumnType(PINDEX Column );

          PTODBC::DWORD ColumnSize( PINDEX Column );

          PTODBC::DWORD ColumnScale( PINDEX Column );

          PString ColumnName( PINDEX Column);

          unsigned int ColumnPrecision( PINDEX Column );

          PBoolean IsColumnNullable( PINDEX Column );

          PBoolean IsColumnUpdatable( PINDEX Column );

          PBoolean IsColumnAutoIndex( PINDEX Column );


          static unsigned int Precision;      
          static int MaxCharSize;                     
          static PTime::TimeFormat TimeFormat;


          PODBCStmt * Stmt;                                     
          PODBC::DataSources dbase;                     


};

#endif // PTODBC

#endif // !defined(PODBC_H)

---