PTLib  Version 2.10.11
podbc.h
Go to the documentation of this file.
1 /*
2  * podbc.h
3  *
4  * Virteos ODBC Implementation for PWLib Library.
5  *
6  * Virteos is a Trade Mark of ISVO (Asia) Pte Ltd.
7  *
8  * Copyright (c) 2005 ISVO (Asia) Pte Ltd. All Rights Reserved.
9  *
10  * The contents of this file are subject to the Mozilla Public License
11  * Version 1.0 (the "License"); you may not use this file except in
12  * compliance with the License. You may obtain a copy of the License at
13  * http://www.mozilla.org/MPL/
14  *
15  * Software distributed under the License is distributed on an "AS IS"
16  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
17  * the License for the specific language governing rights and limitations
18  * under the License.
19  *
20  *
21  * The Original Code is derived from and used in conjunction with the
22  * pwlib Libaray of the OpenH323 Project (www.openh323.org/)
23  *
24  * The Initial Developer of the Original Code is ISVO (Asia) Pte Ltd.
25  *
26  * Portions: Simple ODBC Wrapper Article www.codeproject.com
27  *
28  * Contributor(s): ______________________________________.
29  *
30  * $Revision: 24177 $
31  * $Author: rjongbloed $
32  * $Date: 2010-04-05 06:52:04 -0500 (Mon, 05 Apr 2010) $
33  */
34 
88 //--
89 
90 #ifndef PTLIB_PODBC_H
91 #define PTLIB_PODBC_H
92 
93 #if _MSC_VER > 1000
94 #pragma once
95 #endif // _MSC_VER > 1000
96 
97 
98 #if defined(P_ODBC) && !defined(_WIN32_WCE)
99 
100 #include <odbcinst.h>
101 #include <sql.h>
102 #include <sqlext.h>
103 
104 #ifdef _MSC_VER
105  #include <tchar.h>
106  #pragma comment(lib,"odbc32.lib")
107  #pragma comment(lib,"odbcCP32.lib")
108 #else
109 
110  #ifdef UNICODE
111  typedef WCHAR TCHAR;
112  typedef LPWSTR LPTSTR;
113  typedef LPCWSTR LPCTSTR;
114  // Needs a definition one day ... #define _T(x)
115  #else
116  typedef CHAR TCHAR;
117  typedef LPSTR LPTSTR;
118  typedef LPCSTR LPCTSTR;
119  #define _T(x) x
120  #endif
121 
122 #endif // _MSC_VER
123 
124 // Max SQL String Data Length
125 #define MAX_DATA_LEN 1024
126 
134 class PODBC;
135 class PODBCRecord;
136 
137 
138 class PODBCStmt : public PObject
139 {
140  PCLASSINFO(PODBCStmt, PObject);
141 
142  public:
148  PODBCStmt(PODBC * odbc);
149 
154  ~PODBCStmt();
156 
161  operator HSTMT() { return m_hStmt; };
163 
164 
170  PBoolean IsValid();
171 
175  DWORD GetChangedRowCount(void);
176 
181  PBoolean Query(PString strSQL);
183 
188  PBoolean Fetch();
189 
193  PBoolean FetchRow(PINDEX nRow,PBoolean Absolute=1);
194 
198 
202 
206 
210 
213  PBoolean Cancel();
215 
223  PStringArray TableList(PString option = "");
224 
225 
230  PBoolean SQL_OK(SQLRETURN res);
231 
235  void GetLastError();
236 
237  PODBC * GetLink() const { return odbclink; }
238  int GetDBase() const { return dbase; }
240 
241  protected:
242  HSTMT m_hStmt;
244  int dbase;
245 };
246 
247 
248 
259 class PODBC : public PObject
260 {
262 
263  public:
268  PODBC();
269 
272  ~PODBC();
274 
284  {
285  LongVarChar =-1,
286  Binary =-2,
287  VarBinary =-3,
288  LongVarBinary =-4,
289  BigInt =-5,
290  TinyInt =-6,
291  Bit =-7,
292  Guid =-11,
293  Unknown = 0,
294  Char = 1,
295  Numeric = 2,
296  Decimal = 3,
297  Integer = 4,
298  SmallInt = 5,
299  Float = 6,
300  Real = 7,
301  Double = 8,
302  DateTime = 9,
303  VarChar =12,
304  Date =91,
305  Time =92,
306  TimeStamp =93
307  };
308 
314  enum PwType
315  {
316  oPString, // String Value
317  oBOOL, // Boolean
318  ochar, // Character
319  oshort, // Short
320  oint, // Integer use .AsInteger()
321  olong, // long
322  odouble, // Double use .AsReal()
323  oPBYTEArray,// Binary Data
324  oPInt64, // BigInt use .AsInt64()
325  oPTime, // Time use PTime( "Value" )
326  oPGUID // GUID use PGUID( "Value" ) To Be Implemented...?
327  };
328 
334  {
345  postgreSQL
346  };
347 
351  {
356  MSSQLRPC
357  };
358 
360 
371  {
372  public:
379  int Port;
380  int opt;
381  };
383 
384 
389  class Row;
390  class Field : public PObject
391  {
393  public:
394 
400  class Bind
401  {
402  public:
405  short int ssint;
406  long int slint;
407  double sdoub;
408  unsigned char sbit;
409  unsigned char * suchar;
410  PInt64 sbint;
411  DATE_STRUCT date;
412  TIME_STRUCT time;
413  TIMESTAMP_STRUCT timestamp;
414  SQLGUID guid;
415  SQLLEN dataLen;
416  };
417 
420  PBoolean Post();
421 
424  PString operator=(const PString & str);
425 
428  PString AsString();
429 
433  void SetValue(PString value);
434 
437  void SetDefaultValues();
438 
442  PBoolean DataFragment(PString & Buffer ,PINDEX & fragment, SQLINTEGER & size);
443 
446  Bind Data;
450 
453  PINDEX col;
454 
459  int Decimals;
461 
463  Row * row;
464  };
466 
467 
476  class Row : public PObject
477  {
478  public:
479 
485  Row(PODBCStmt * stmt);
486 
490  Field & Column(PINDEX col);
491 
494  Field & Column(PString name);
495 
498  PStringArray ColumnNames();
499 
502  PINDEX Columns();
503 
506  PINDEX Rows();
507 
510  Field & operator[] (PINDEX col);
511 
514  Field & operator[] (PString col);
515 
518  PBoolean Navigate(PINDEX row);
519 
522  void SetNewRow();
523 
530  PBoolean Post();
531 
535  PBoolean Delete(PINDEX row =0);
536 
538 
539  PINDEX CurRow;
541  PINDEX RowCount;
542 
543  protected:
545  };
547 
553  class Table : public PObject
554  {
555  public:
556 
563  Table(PODBC * odbc, PString Query);
564 
567  ~Table();
569 
574  Row NewRow();
575 
578  PBoolean DeleteRow(PINDEX row = 0);
579 
582  PBoolean Post();
584 
589  PINDEX Rows();
590 
593  PINDEX Columns();
594 
597  PStringArray ColumnNames();
598 
603  Row & RecordHandler();
604 
607  Row & operator[] (PINDEX row);
608 
613  Field & operator() (PINDEX row, PINDEX col);
614 
618  Field & Column(PINDEX col);
619 
622  Field & Column(PString Name);
624 
625  protected:
629  };
630 
639  Table LoadTable(PString table);
640 
648 
649 
659  PBoolean DataSource(DataSources Source, ConnectData Data);
660 
665  virtual PBoolean Connect(LPCTSTR svSource);
666 
669  PBoolean Connect_DB2(PFilePath DBPath);
670 
673  PBoolean Connect_XLS(PFilePath XLSPath,PString DefDir = "");
674 
677  PBoolean Connect_TXT(PFilePath TXTPath);
678 
681  PBoolean Connect_FOX(PFilePath DBPath,PString User = "",
682  PString Pass = "",PString Type= "DBF",
683  PBoolean Exclusive=false);
684 
687  PBoolean Connect_MDB(PFilePath MDBPath,PString User ="",
688  PString Pass = "",PBoolean Exclusive=false);
689 
692  PBoolean Connect_PDOX(PDirectory DBPath,PDirectory DefaultDir,
693  int version =5);
694 
697  PBoolean Connect_Oracle(PString Server,PString User="", PString Pass="");
698 
701  PBoolean Connect_DBASE(PDirectory DBPath);
702 
705  PBoolean Connect_MSSQL(PString User="",PString Pass="",
706  PString Host ="(local)",PBoolean Trusted = true,
707  MSSQLProtocols Proto=MSSQLNamedPipes);
708 
711  PBoolean Connect_mySQL(PString User="",PString Pass="",
712  PString Host= "localhost",
713  int Port=3306,int Option=0);
714 
717  PBoolean ConnectDB_mySQL(PString DB,PString User="",
718  PString Pass="",PString Host= "localhost",
719  int Port=3306,int Option=0);
720 
723  PBoolean Connect_postgreSQL(PString DB,PString User,
724  PString Pass,PString Host, int Port=5432,int Option=0);
725 
728  void Disconnect();
730 
737  PStringArray TableList(PString option = "");
738 
742  PBoolean NeedLongDataLen();
743 
746  virtual void OnSQLError(PString RetCode, PString RetString) {};
747 
748 
755  void SetPrecision(int Digit);
756 
759  void SetTimeFormat(PTime::TimeFormat tformat);
760 
763  operator HDBC() { return m_hDBC; };
765 
767 
768  protected:
769  SQLRETURN m_nReturn; // Internal SQL Error code
770  HENV m_hEnv; // Handle to environment
771  HDBC m_hDBC; // Handle to database connection
772 };
773 
774 
781 class PDSNConnection : public PODBC
782 {
784 
785  public:
788  PDSNConnection();
789  ~PDSNConnection();
791 
798  PBoolean Connect( PString Source ,PString Username, PString Password);
799 };
800 
801 
802  //--
809 class PODBCRecord : public PObject
810 {
812 
813  public:
818  PODBCRecord(PODBCStmt * hStmt);
819 
824 
830  void Data(PINDEX Column, PODBC::Field & field);
831 
836  PBoolean InternalGetData(
837  USHORT Column,
838  LPVOID pBuffer,
839  ULONG pBufLen,
840  SQLINTEGER * dataLen=NULL,
841  int Type=SQL_C_DEFAULT
842  );
843 
844  /* Get Long Character Data. Long Data fields cannot be bound
845  and Data must be Got from the RecordSet.
846  */
847  PString GetLongData(PINDEX Column);
848 
851  PBoolean PostNew(PODBC::Row & rec);
852 
855  PBoolean PostUpdate(PODBC::Row & rec);
856 
860  PBoolean PostDelete(PINDEX row= 1);
861 
864  PBoolean InternalSaveLongData(SQLRETURN nRet,PODBC::Row & rec);
865 
868  PBoolean InternalBindColumn(
869  USHORT Column,LPVOID pBuffer,
870  ULONG pBufferSize,
871  LONG * pReturnedBufferSize=NULL,
872  USHORT nType=SQL_C_TCHAR
873  );
875 
881  PINDEX ColumnByName(PString Column);
882 
885  PINDEX ColumnCount();
886 
889  PODBC::FieldTypes ColumnType(PINDEX Column );
890 
893  DWORD ColumnSize( PINDEX Column );
894 
897  DWORD ColumnScale( PINDEX Column );
898 
901  PString ColumnName( PINDEX Column);
902 
907  unsigned int ColumnPrecision( PINDEX Column );
908 
911  PBoolean IsColumnNullable( PINDEX Column );
912 
915  PBoolean IsColumnUpdatable( PINDEX Column );
916 
919  PBoolean IsColumnAutoIndex( PINDEX Column );
920 
922 
927  static unsigned int Precision;
928  static int MaxCharSize;
930 
931 
932  protected:
933  HSTMT m_hStmt;
936 
937  friend class PODBC::Field;
938  friend class PODBC::Row;
939 };
940 
941 #endif // P_ODBC
942 
943 #endif // PTLIB_PODBC_H
944 
945 
946 // End Of File ///////////////////////////////////////////////////////////////
Definition: podbc.h:319
CHAR TCHAR
ODBC Support for PWLIB.
Definition: podbc.h:116
HSTMT m_hStmt
Definition: podbc.h:242
PwType Type
Data Field to ODBC Bind to.
Definition: podbc.h:448
Definition: podbc.h:342
virtual void OnSQLError(PString RetCode, PString RetString)
OnSQL Error.
Definition: podbc.h:746
PInt64 sbint
Unsigned char SQLCHAR *.
Definition: podbc.h:410
SQLLEN dataLen
GUID Structure (not Fully Supported)
Definition: podbc.h:415
int GetDBase() const
Definition: podbc.h:238
PODBCStmt stmt
Definition: podbc.h:626
Definition: podbc.h:336
Definition: podbc.h:343
PODBC::DataSources dbase
Statement Class.
Definition: podbc.h:935
Definition: podbc.h:320
PBoolean isNullable
Is Field Readonly.
Definition: podbc.h:457
Row * RowHandler
Name of the Fetched Table (if used in Constructor)
Definition: podbc.h:628
PString tableName
ODBC Fetched Statement Info.
Definition: podbc.h:627
#define PCLASSINFO(cls, par)
Declare all the standard PTLib class information.
Definition: object.h:1049
PBoolean FetchFirst()
FetchFirst Fetch the First row in the RecordSet.
PArray< Field > Fields
Number of Rows.
Definition: podbc.h:544
DWORD GetChangedRowCount(void)
GetChangedRowCount retreives the number of rows updated/altered by UPDATE/INSERT statements.
PODBC * GetLink() const
Definition: podbc.h:237
PwType
Converted Pwlib Field Types.
Definition: podbc.h:314
HSTMT m_hStmt
Time Format.
Definition: podbc.h:933
PString sbinlong
Strings & Binary Data.
Definition: podbc.h:404
Definition: podbc.h:335
PODBC Class The Main ODBC class.
Definition: podbc.h:259
PBoolean IsValid()
IsValid Checks to ensure a Handle has been allocated and is effective.
HDBC m_hDBC
Definition: podbc.h:771
PBoolean Excl_Trust
Password.
Definition: podbc.h:377
Definition: podbc.h:325
int dbase
Reference to the PODBC Class.
Definition: podbc.h:244
This class is a multipurpose use class for storing parameters when initiating connection to DataSourc...
Definition: podbc.h:370
DSN (Data Source Name) Connection.
Definition: podbc.h:781
PBoolean isAutoInc
Allows Nulls.
Definition: podbc.h:458
This class describes a full description for a file on the particular platform.
Definition: filepath.h:65
TIMESTAMP_STRUCT timestamp
Time Structure.
Definition: podbc.h:413
HENV m_hEnv
Definition: podbc.h:770
int opt
Port to connect to mySQL.
Definition: podbc.h:380
Definition: podbc.h:337
SQLGUID guid
TimeStamp Structure.
Definition: podbc.h:414
Definition: podbc.h:354
Definition: podbc.h:317
void GetLastError()
Get the Last Error This returns the Error ID & String to PODBC::OnSQLError.
Definition: podbc.h:352
This is an array collection class of PString objects.
Definition: pstring.h:2024
PString DefDir
Database file Path (not Oracle,xxSQL)
Definition: podbc.h:374
PBoolean FetchPrevious()
FetchPrevious Fetch the previous Row from current row.
PString Pass
UserName.
Definition: podbc.h:376
~PODBCStmt()
Deconstructor.
Definition: podbc.h:316
PODBC * odbclink
Definition: podbc.h:243
int Decimals
Field AutoIncrements.
Definition: podbc.h:459
TimeFormat
Standard time formats for string representations of a time and date.
Definition: ptime.h:399
DataSources
Datasources that are supported by this implementation used in the PODBC::DataSource Function...
Definition: podbc.h:333
BOOL PBoolean
Definition: object.h:102
Class to represent a directory in the operating system file system.
Definition: pdirect.h:182
PINDEX CurRow
Record Structure.
Definition: podbc.h:539
unsigned char sbit
Double SQLDOUBLE.
Definition: podbc.h:408
unsigned char * suchar
Bit SQLCHAR.
Definition: podbc.h:409
FieldTypes
Raw SQL data type codes Refer <sql.h> SQL_* This list is not inclusive.
Definition: podbc.h:283
PFilePath DBPath
Definition: podbc.h:373
long int slint
Short Integer SQLSMALLINT.
Definition: podbc.h:406
PBoolean Fetch()
Fetch General call to retreive the next row of data.
Definition: podbc.h:324
PBoolean Query(PString strSQL)
Query function is the Main function to pass SQL statements to retreive/ add/Modify database data...
Definition: podbc.h:390
PBoolean Cancel()
Cancel the Current Statement.
LPCSTR LPCTSTR
Definition: podbc.h:118
PBoolean NewRow
Current Row.
Definition: podbc.h:540
PBoolean LongData
Number of decimal places to Round.
Definition: podbc.h:460
Definition: podbc.h:338
PODBC::Table This is the main Class to access Data returned by a Select Query.
Definition: podbc.h:553
SQLRETURN m_nReturn
Database Type connected to.
Definition: podbc.h:769
Definition: podbc.h:355
The character string class.
Definition: pstring.h:108
LPSTR LPTSTR
Definition: podbc.h:117
PODBCRecord This Class is used to analyse the fetched data and handles Data Conversion/Read Write ope...
Definition: podbc.h:809
SQL compliant Bound DataTypes.
Definition: podbc.h:400
PString User
Used with Paradox/DBase/Excel (& mySQL db)
Definition: podbc.h:375
static PTime::TimeFormat TimeFormat
Long Data Limit KBytes def = 56; (56 Kbytes)
Definition: podbc.h:929
~PODBCRecord()
Deconstructor.
Definition: podbc.h:822
Definition: podbc.h:339
Row * row
LongData Length is Required.
Definition: podbc.h:463
MSSQLProtocols
MSSQL protocols.If your interested?
Definition: podbc.h:350
This class functions as a simple wrapper of the PODBCStmt class to fetch/Save data to the Datasource...
Definition: podbc.h:476
PBoolean FetchLast()
FetchLast Fetch the Last row in the RecordSet.
PODBCRecord * rec
Definition: podbc.h:537
PString Name
ODBC Type (For saving/Conversion)
Definition: podbc.h:452
PINDEX RowCount
Flag to Indicate New Row (requires either Post or Delete)
Definition: podbc.h:541
static int MaxCharSize
Double Real Float Decimal digit rounding def= 4;.
Definition: podbc.h:928
Definition: podbc.h:340
Definition: podbc.h:321
This template class maps the PArrayObjects to a specific object type.
Definition: array.h:1024
PString Host
Whether Datasource is locked or Trusted.
Definition: podbc.h:378
PBoolean FetchNext()
FetchNext: Fetch the Next row.
FieldTypes ODBCType
pwlib Type for conversion
Definition: podbc.h:449
PBoolean FetchRow(PINDEX nRow, PBoolean Absolute=1)
FetchRow More detailed fetching of Rows.
Definition: podbc.h:341
short int ssint
Long Data.
Definition: podbc.h:405
TIME_STRUCT time
Date Structure.
Definition: podbc.h:412
PBoolean SQL_OK(SQLRETURN res)
Is the SQL Instruction OK If an Error is detected then GetLastError is called to Retrieve the SQL Err...
Definition: podbc.h:138
PODBCStmt(PODBC *odbc)
Constructor PODBC (Datasources call) or thro&#39; DSNConnection (Connection call).
int Port
URL for Host Datasouce xxSQL.
Definition: podbc.h:379
PString sbin
Definition: podbc.h:403
PODBCStmt * Stmt
Definition: podbc.h:934
Definition: podbc.h:344
double sdoub
Integer SQLINTEGER.
Definition: podbc.h:407
PINDEX col
Column Name.
Definition: podbc.h:453
Ultimate parent class for all objects in the class library.
Definition: object.h:1118
PStringArray TableList(PString option="")
Retreive the List of Tables from the current Datasource The option field can be used to specify the T...
PBoolean isReadOnly
Column Number (For Saving/Conversion)
Definition: podbc.h:456
Definition: podbc.h:322
DATE_STRUCT date
Bit Integer SQLBIGINT.
Definition: podbc.h:411
Definition: podbc.h:353
static unsigned int Precision
Conversion Settings.
Definition: podbc.h:927
Definition: podbc.h:318
Definition: podbc.h:323