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): ______________________________________.
 */

//--

#ifndef PTLIB_PODBC_H
#define PTLIB_PODBC_H

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


#if P_ODBC

#include <ptclib/vartype.h>


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

    ~PODBC();

    typedef unsigned RowIndex;
    enum { UndefinedRowIndex = 0 };

    class Field;
    class Row;
    class RecordSet;
    class Statement;  // Internal use
    struct FieldExtra;  // Internal use


    class Field : public PVarType
    {
        PCLASSINFO(Field, PObject);
      public:
        ~Field();

        Field & operator=(const Field & other) { PVarType::operator=(other); return *this; }
        Field & operator=(const PVarType & other) { PVarType::operator=(other); return *this; }

        // Overrides from PVarType, make sure can't change type
        virtual bool SetType(BasicType type, PINDEX options = 0);

        void SetDefaultValues();

        void SetNULL();

        bool IsNULL() const;

        PINDEX GetColumn() const { return m_column; }

        const PString GetName() const { return m_name; }

        int GetDataType() const { return m_odbcType; }
        unsigned GetScale() const { return m_scale; }
        bool IsNullable() const { return m_isNullable; }
        bool IsReadOnly() const { return m_isReadOnly; }
        bool IsUpdatable() const { return !IsReadOnly(); }
        bool IsAutoIncrement() const { return m_isAutoIncrement; }
        bool IsAutoIndex() { return IsAutoIncrement(); }
        unsigned GetPrecision() const { return m_decimals; }

        // For backward compatibility
        bool Post();

      protected:
        Field(Row & row, PINDEX column);

        // Call backs from PVarType
        virtual void OnGetValue();
        virtual void OnValueChanged();
        virtual void InternalCopy(const PVarType & other);

        Row   & m_row;       
        PINDEX  m_column;    

        PString    m_name;       
        int        m_odbcType;
        unsigned   m_scale;
        bool       m_isNullable;
        bool       m_isReadOnly;
        bool       m_isAutoIncrement;
        unsigned   m_decimals;     

        FieldExtra * m_extra; // Some types are not compatible with PVarType

      private:
        Field(const Field & other) : PVarType(), m_row(other.m_row) { }

      friend class Row;
      friend class RecordSet;
    };


    class Row : public PObject
    {
        PCLASSINFO(Row, PObject);
      public:
        Row(RecordSet & recordSet);

        PINDEX Columns() const { return m_fields.GetSize(); }

        PStringArray ColumnNames() const;

        PINDEX ColumnByName(const PCaselessString & columnName) const;

        Field & Column(PINDEX column) const;

        Field & Column(const PString & name) const;

        Field & operator[](PINDEX column) const { return Column(column); }

        Field & operator[](const PString & columnName) const { return Column(columnName); }

        PString ColumnName(PINDEX column) const { return Column(column).GetName(); }

        unsigned ColumnType(PINDEX column) const { return Column(column).GetDataType(); }

        PINDEX ColumnSize(PINDEX column) const { return Column(column).GetSize(); }

        unsigned ColumnScale(PINDEX column) const { return Column(column).GetScale(); }

        unsigned ColumnPrecision(PINDEX column) const { return Column(column).GetPrecision(); }

        bool IsColumnNullable(PINDEX column) const { return Column(column).IsNullable(); }

        bool IsColumnUpdatable(PINDEX column) const { return Column(column).IsUpdatable(); }

        bool IsColumnAutoIndex(PINDEX column) const { return Column(column).IsAutoIndex(); }

        void SetNewRow();

        bool MoveTo(RowIndex row);

        bool Move(int offset);

        bool First();

        bool Next();

        bool Previous();

        bool Last();

        bool Delete(RowIndex rowIndex = 0);

        bool Commit();

        RowIndex GetRowIndex() const { return m_rowIndex; }

        // For backward compatibility
        PINDEX Rows();
        PINDEX ColumnCount() { return Columns(); }
        bool PostNew() { return Commit(); }
        bool PostUpdate() { return Commit(); }
        bool Post() { return Commit(); }
        bool Navigate(RowIndex row) { return MoveTo(row); }

      protected:
        RecordSet   & m_recordSet;
        RowIndex      m_rowIndex;    
        PArray<Field> m_fields;    

      private:
        Row(const Row & other) : PObject(other), m_recordSet(other.m_recordSet) { }
        void operator=(const Row &) { }

      friend class Field;
      friend class RecordSet;
    };


    class RecordSet : public PObject
    {
        PCLASSINFO(RecordSet, PObject);
      public:
        RecordSet(PODBC & odbc, const PString & query = PString::Empty());

        // For backward compatibility
        RecordSet(PODBC * odbc, const PString & query);

        ~RecordSet();

        bool Query(const PString & query);

        bool Select(
          const PString & table,
          const PString & whereClause = PString::Empty(),
          const PString & fields = PString::Empty(), 
          const PString & orderedBy = PString::Empty(),
          bool descending = false
        );

        RowIndex Rows(bool forceCount = true);

        PINDEX Columns() { return m_cursor.Columns(); }

        PString ColumnName(PINDEX column) { return m_cursor.ColumnName(column); }

        PStringArray ColumnNames() { return m_cursor.ColumnNames(); }

        Row & NewRow();

        bool MoveTo(RowIndex row) { return m_cursor.MoveTo(row); }

        bool Move(int offset) { return m_cursor.Move(offset); }

        bool First() { return m_cursor.First(); }

        bool Next() { return m_cursor.Next(); }

        bool Previous() { return m_cursor.Previous(); }

        bool Last() { return m_cursor.Last(); }

        bool DeleteRow(RowIndex row = 0);

        Row & operator[](PINDEX row);

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

        Field & Column(PINDEX column) { return m_cursor.Column(column); }

        Field & Column(const PString & name) { return m_cursor.Column(name); }

        bool Commit() { return m_cursor.Commit(); }

        // For backward compatibility
        bool Post() { return m_cursor.Commit(); }

      protected:
        Statement * m_statement; // ODBC Fetched Statement Info
        RowIndex    m_totalRows;
        Row         m_cursor;

      private:
        RecordSet(const RecordSet & other);
        void operator=(const RecordSet &) { }

      friend class Field;
      friend class Row;
    };

    typedef RecordSet Table; // For backward compatibility


    P_DECLARE_ENUM(DriverType,
      DSN,
      mySQL,
      postgreSQL,
      Oracle,
      IBM_DB2,
      MSSQL,
      MSAccess,
      Paradox,
      Foxpro,
      dBase,
      Excel,
      Ascii,
      ConnectionString
    );
    static const char * GetDriverName(DriverType type);
    friend std::ostream & operator<<(std::ostream & strm, DriverType type) { return strm << PODBC::GetDriverName(type); }


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

    struct ConnectData
    {
      ConnectData() : m_driver(DSN), m_port(0), m_exclusive(false), m_trusted(false), m_options(0) { }

      DriverType m_driver;    
      PString    m_database;  
      PDirectory m_directory; 
      PString    m_username;  
      PString    m_password;  
      PString    m_host;      
      unsigned   m_port;      
      bool       m_exclusive; 
      bool       m_trusted;   
      int        m_options;   
    };

    bool Connect(const ConnectData & connectInfo);

    bool Connect(
      const PString & source
    );

    bool Connect(
      const PString & source,
      const PString & username,
      const PString & password
    );

    bool Connect_DB2(
      const PFilePath & dbPath
    );

    bool Connect_XLS(
      const PFilePath & xlsPath,
      const PString & defDir = PString::Empty()
    );

    bool Connect_TXT(
      const PFilePath & txtPath
    );

    bool Connect_FOX(
      const PFilePath & dbPath,
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty(),
      const PString & type = "DBF",
      bool exclusive = false
    );

    bool Connect_MDB(
      const PFilePath & mdbPath,
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty(),
      bool exclusive = false
    );

    bool Connect_PDOX(
      const PDirectory & dbPath,
      const PDirectory & defaultDir,
      int version = 5
    );

    bool Connect_Oracle(
      const PString & server,
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty());

    bool Connect_DBASE(
      const PDirectory & dbPath
    );

    bool Connect_MSSQL(
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty(),
      const PString & host = "(local)",
      bool trusted = true,
      MSSQLProtocols Proto = MSSQLNamedPipes
    );

    bool Connect_mySQL(
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty(),
      const PString & host = "localhost",
      int port = 3306
    );

    bool ConnectDB_mySQL(
      const PString & db,
      const PString & user = PString::Empty(),
      const PString & pass = PString::Empty(),
      const PString & host = "localhost",
      int port = 3306
    );

    bool Connect_postgreSQL(
      const PString & db,
      const PString & user,
      const PString & pass,
      const PString & host,
      int port = 5432
    );

    bool IsConnected() const;

    virtual void OnConnected();

    void Disconnect();

    PStringList GetDrivers(
      bool withAttributes = true
    ) const;

    PStringList GetSources(
      bool system = false,
      bool withDescription = true
    ) const;

    PStringArray TableList(const PString & options = PString::Empty());

    bool Execute(const PString & sql);

    // For backward compatibility
    __inline bool Query(const PString & sql) { return Execute(sql); }


    static PString GetFieldType(DriverType driver, PVarType::BasicType type, unsigned size = 0);

    void SetPrecision(unsigned precision);

    unsigned GetPrecision() const { return m_precision; }

    PTime::TimeFormat GetTimeFormat() const { return m_timeFormat; }
    void SetTimeFormat(PTime::TimeFormat fmt) { m_timeFormat = fmt; }

    PTime::TimeFormat GetDateFormat() const { return m_dateFormat; }
    void SetDateFormat(PTime::TimeFormat fmt) { m_dateFormat = fmt; }

    PTime::TimeFormat GetDateTimeFormat() const { return m_dateTimeFormat; }
    void SetDateTimeFormat(PTime::TimeFormat fmt) { m_dateTimeFormat = fmt; }

    PINDEX GetMaxChunkSize() const { return m_needChunking ? m_maxChunkSize : P_MAX_INDEX; }

    int GetLastError() const { return m_lastError; }

    PString GetLastErrorText() const { return m_lastErrorText; }

    virtual void OnSQLError(
      int native,               
      const PString & code,     
      const PString & message   
    );

    // For backward compatibility
    bool DataSource(DriverType driver, ConnectData Data);

  protected:
    struct Link;

    Link            * m_link;
    int               m_lastError;
    PString           m_lastErrorText;
    unsigned          m_precision;   
    PTime::TimeFormat m_timeFormat;
    PTime::TimeFormat m_dateFormat;
    PTime::TimeFormat m_dateTimeFormat;
    bool              m_needChunking;
    PINDEX            m_maxChunkSize;

    P_REMOVE_VIRTUAL_VOID(OnSQLError(const PString &, const PString &));

  friend class Statement;
};


// For backward compatibility
typedef PODBC::Statement PODBCStmt;
typedef PODBC::Row POBDCRecord;
typedef PODBC PDSNConnection;


#endif // P_ODBC

#endif // PTLIB_PODBC_H


// End Of File ///////////////////////////////////////////////////////////////