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: 20467 $
 * $Author: shorne $
 * $Date: 2008-06-18 22:30:35 +0000 (Wed, 18 Jun 2008) $
 */

//--

#if !defined(PODBC_H)
#define PODBC_H

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

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

#if defined(P_ODBC) && !defined(_WIN32_WCE)

#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")
#endif // _MSC_VER


// Max SQL String Data Length
#define MAX_DATA_LEN 1024

class PODBC;
class PODBCRecord;


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

  public:
    PODBCStmt(PODBC * odbc);

    ~PODBCStmt();

    operator HSTMT() { return m_hStmt; };


    PBoolean IsValid();

    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(SQLRETURN res);

    void GetLastError();

    PODBC * GetLink() const { return odbclink; }
    int GetDBase() const { return dbase; }

  protected:
    HSTMT   m_hStmt;
    PODBC * odbclink; 
    int     dbase;    
};



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;    
            PInt64           sbint;     
            DATE_STRUCT      date;      
            TIME_STRUCT      time;      
            TIMESTAMP_STRUCT timestamp; 
            SQLGUID          guid;      
            SQLLEN           dataLen;   
        };

        PBoolean Post();

        PString operator=(const PString & str);

        PString AsString();  

        void SetValue(PString value);  

        void SetDefaultValues();

        PBoolean DataFragment(PString & Buffer ,PINDEX & fragment, 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(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 HDBC() { return m_hDBC; };

    PODBC::DataSources  dbase; 

  protected:
    SQLRETURN       m_nReturn;      // Internal SQL Error code
    HENV            m_hEnv;         // Handle to environment
    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);

  public:
    PODBCRecord(PODBCStmt * hStmt);

    ~PODBCRecord(){};

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

    PBoolean InternalGetData(
      USHORT Column,
      LPVOID pBuffer, 
      ULONG pBufLen,
      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(SQLRETURN nRet,PODBC::Row & rec);

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

    PINDEX ColumnByName(PString Column);

    PINDEX ColumnCount();

    PODBC::FieldTypes ColumnType(PINDEX Column );

    DWORD ColumnSize( PINDEX Column );

    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;


  protected:
    HSTMT m_hStmt;
    PODBCStmt * Stmt;          
    PODBC::DataSources dbase;      

  friend class PODBC::Field;
  friend class PODBC::Row;
};

#endif // P_ODBC

#endif // !defined(PODBC_H)

---