/*
* 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.3 2005/11/30 12:47:42 csoutheren
* Removed tabs, reformatted some code, and changed tags for Doxygen
*
* 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);
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:
if (B)
SQLGetData(*row->rec->Stmt,col,ODBCType,&b.time,0,&b.dataLen);
return Convert(b.time);
case PODBC::TimeStamp:
if (B)
SQLGetData(*row->rec->Stmt,col,ODBCType,&b.timestamp,0,&b.dataLen);
return Convert(b.timestamp);
case PODBC::Guid:
if (B)
SQLGetData(*row->rec->Stmt,col,ODBCType,&b.guid,0,&b.dataLen);
return Convert(b.guid);
/// Binary/Long Data Cannot be Bound Need to get it!
case PODBC::Binary:
case PODBC::VarBinary:
case PODBC::LongVarBinary:
case PODBC::LongVarChar:
return row->rec->GetLongData(col);
/// Character
case PODBC::DateTime:
case PODBC::Unknown:
case PODBC::VarChar:
default:
if (B)
SQLGetData(*row->rec->Stmt,col,ODBCType,b.sbin.GetPointer(len),len,&b.dataLen);
return b.sbin;
}
}
void PODBC::Field::SetValue(PString value)
{
Bind &b = Data;
/// If Field is marked as ReadOnly Do not Update
if (isReadOnly)
return;
/// If Field is not Nullable and the New Value length =0 No Update.
if ((!isNullable) && (value.GetLength() == 0))
return;
switch (ODBCType) {
case PODBC::BigInt:
Convert(b.sbint,value);
break;
case PODBC::TinyInt:
case PODBC::Bit:
Convert(b.sbit,value);
break;
case PODBC::Char:
Convert(b.suchar,value);
break;
case PODBC::Integer:
Convert(b.slint,value);
break;
case PODBC::SmallInt:
Convert(b.ssint,value);
break;
case PODBC::Numeric:
case PODBC::Decimal:
case PODBC::Float:
case PODBC::Real:
case PODBC::Double:
Convert(b.sdoub,value);
break;
/// Date Times
case PODBC::Date:
Convert(b.date,value);
break;
case PODBC::Time:
Convert(b.time,value);
break;
case PODBC::TimeStamp:
Convert(b.timestamp,value);
break;
case PODBC::Guid:
Convert(b.guid,value);
/// Binary Data
case PODBC::Binary:
case PODBC::VarBinary:
case PODBC::LongVarBinary:
case PODBC::LongVarChar:
b.sbinlong = value;
if (row->rec->dbase == PODBC::MSAccess) {
b.sbinlong.SetSize(MAX_DATA_LEN * PODBCRecord::MaxCharSize);
b.dataLen = b.sbinlong.GetLength();
}
break;
/// Character
case PODBC::DateTime:
case PODBC::Unknown:
case PODBC::VarChar:
default:
b.sbin = value;
b.sbin.SetSize(MAX_DATA_LEN);
b.dataLen = b.sbin.GetLength();
};
}
void PODBC::Field::SetDefaultValues()
{
if ((isReadOnly) || (isAutoInc))
return;
/// Set Default Values
switch (Type) {
case oPString:
case ochar:
if (isNullable)
SetValue(PString());
else
SetValue("?");
break;
case oPTime:
SetValue(PTime().AsString());
break;
default:
SetValue(0);
}
}
PString PODBC::Field::operator=(const PString & str)
{
return AsString();
}
BOOL PODBC::Field::DataFragment(PString & Buffer,PINDEX & fragment,long & size)
{
PINDEX fragcount = PINDEX(Data.sbinlong.GetLength()/size);
// Value less than Buffer Size
if (fragcount == 0) {
Buffer = Data.sbinlong;
return FALSE;
}
// Buffer Fragment
if (fragcount < fragment) {
Buffer.Splice(Data.sbinlong,(fragment * size)+1,size);
fragment = fragment++;
return TRUE;
}
// Last Fragment
PINDEX blen = Data.sbinlong.GetLength() - (fragment * size);
Buffer = Data.sbinlong.Right(blen);
size = blen;
return FALSE;
}
BOOL PODBC::Field::Post()
{
return row->Post();
}
/////////////////////////////////////////////////////////////////////////////
// PODBC::Table
PODBC::Table::Table(PODBC * odbc, PString Query)
: stmt(PODBCStmt(odbc))
{
PString query;
tableName = PString();
// Do the Query
if (Query.Trim().Left(6) == "SELECT") { // Select Query
query = Query;
} else { // Table Query
tableName = Query;
query = "SELECT * FROM [" + tableName + "];";
}
if (!stmt.Query(query))
return;
// Create the Row Handler
RowHandler = new Row(&stmt);
}
PODBC::Table::~Table()
{
}
PODBC::Row PODBC::Table::NewRow()
{
RowHandler->SetNewRow();
return *RowHandler;
}
BOOL PODBC::Table::DeleteRow(PINDEX row)
{
return RowHandler->Delete(row);
}
BOOL PODBC::Table::Post()
{
return RowHandler->Post();
}
PINDEX PODBC::Table::Rows()
{
return RowHandler->RowCount;
}
PINDEX PODBC::Table::Columns()
{
return RowHandler->Columns();
}
PStringArray PODBC::Table::ColumnNames()
{
return RowHandler->ColumnNames();
}
PODBC::Row & PODBC::Table::operator[] (PINDEX row)
{
if (RowHandler->Navigate(row))
return *RowHandler;
return *RowHandler;
}
PODBC::Row & PODBC::Table::RecordHandler()
{
return *RowHandler;
}
PODBC::Field & PODBC::Table::operator() (PINDEX row, PINDEX col)
{
RowHandler->Navigate(row);
return RowHandler->Column(col);
}
PODBC::Field & PODBC::Table::Column(PINDEX col)
{
return RowHandler->Column(col);
}
PODBC::Field & PODBC::Table::Column(PString Name)
{
return RowHandler->Column(Name);
}
///////////////////////////////////////////////////////////////////
// PODBC::Row
PODBC::Row::Row(PODBCStmt * stmt)
: rec(new PODBCRecord(stmt))
{
/// Create a blank Row (RecordHolder) with default Recordset Info.
for (PINDEX i=0; i < rec->ColumnCount(); i++)
{
Field * nfield = new Field;
nfield->row = this;
nfield->col = i+1;
// Default Attributes
nfield->isReadOnly = FALSE;
nfield->isNullable = TRUE;
nfield->isAutoInc = FALSE;
nfield->Decimals = 0;
nfield->LongData = stmt->odbclink->NeedLongDataLen();
rec->Data(i+1, *nfield);
/// Append to PArray
Fields.Append(nfield);
}
NewRow = FALSE;
// Attempt to get the first Record
if (!stmt->FetchFirst())
RowCount = 0;
else
RowCount = 1;
// Get the Record Count (Need a better way other than fetching! :-( )
while(stmt->Fetch())
RowCount++;
/// Put the RecordHolder to point to the First Row
if (RowCount > 0) {
stmt->FetchFirst();
CurRow = 1;
} else
CurRow = 0;
}
PODBC::Field & PODBC::Row::Column(PINDEX col)
{
/// Column = 0 return blank field
if ((col == 0) || (col > Fields.GetSize()))
return *(new PODBC::Field());
return Fields[col-1];
}
PODBC::Field & PODBC::Row::Column(PString name)
{
PINDEX i = rec->ColumnByName(name);
return Column(i);
}
PINDEX PODBC::Row::Columns()
{
return rec->ColumnCount();
}
PStringArray PODBC::Row::ColumnNames()
{
PStringArray Names;
for (PINDEX i = 0; i < Fields.GetSize(); i++)
{
Names.AppendString(rec->ColumnName(i+1));
}
return Names;
}
PINDEX PODBC::Row::Rows()
{
return RowCount;
}
PODBC::Field & PODBC::Row::operator[] (PINDEX col)
{
return Column(col);
}
PODBC::Field & PODBC::Row::operator[] (PString col)
{
PINDEX i = rec->ColumnByName(col);
return Column(i);
}
void PODBC::Row::SetNewRow()
{
CurRow = RowCount+1;
NewRow = TRUE;
for (PINDEX i = 0; i < Fields.GetSize(); i++)
{
Column(i).SetDefaultValues();
}
}
BOOL PODBC::Row::Navigate(PINDEX row)
{
if ((row > 0) && (CurRow != row)) {
if (!rec->Stmt->FetchRow(row,TRUE))
return FALSE;
CurRow = row;
}
return TRUE;
}
BOOL PODBC::Row::Post()
{
BOOL Success;
if (NewRow) {
NewRow = FALSE;
RowCount++;
CurRow = RowCount;
Success = rec->PostNew(*this);
} else {
Success = rec->PostUpdate(*this);
}
return Success;
}
BOOL PODBC::Row::Delete(PINDEX row)
{
if (row > 0) {
if (!Navigate(row))
return FALSE;
}
if (!rec->PostDelete())
return FALSE;
RowCount--;
return TRUE;
}
// PDSNConnection
/////////////////////////////////////////////////////////////////////////////////////
PDSNConnection::PDSNConnection()
{
}
PDSNConnection::~PDSNConnection()
{
}
BOOL PDSNConnection::Connect( PString Source ,PString Username, PString Password)
{
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 );
nConnect = SQLConnect( m_hDBC,
( SQLTCHAR *)(const char *)Source, SQL_NTS,
( SQLTCHAR *)(const char *)Username,
SQL_NTS,
( SQLTCHAR *)(const char *)Password,
SQL_NTS
);
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 FALSE;
}
//--
PODBCStmt::PODBCStmt(PODBC * odbc)
: odbclink(odbc)
{
HDBC hDBCLink = *odbc;
SQLRETURN m_nReturn;
m_nReturn = SQLAllocHandle( SQL_HANDLE_STMT, hDBCLink, &m_hStmt );
SQLSetStmtAttr(m_hStmt, SQL_ATTR_CONCURRENCY,
(SQLPOINTER) SQL_CONCUR_ROWVER, 0);
SQLSetStmtAttr(m_hStmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0);
SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_BIND_TYPE,
(SQLPOINTER)SQL_BIND_BY_COLUMN, 0);
SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER)1, 0);
SQLSetStmtAttr(m_hStmt, SQL_ATTR_ROW_STATUS_PTR,
NULL, 0);
dbase = odbc->dbase;
if(!SQL_OK(m_nReturn))
m_hStmt=INVALID_HANDLE_VALUE;
}
PODBCStmt::~PODBCStmt()
{
SQLCloseCursor(m_hStmt);
if(m_hStmt!=INVALID_HANDLE_VALUE)
SQLFreeHandle(SQL_HANDLE_STMT,m_hStmt);
}
BOOL PODBCStmt::IsValid()
{
return m_hStmt!=INVALID_HANDLE_VALUE;
}
DWORD PODBCStmt::GetChangedRowCount(void)
{
long nRows=0;
if(!SQL_OK(SQLRowCount(m_hStmt,&nRows)))
return 0;
return nRows;
}
BOOL PODBCStmt::Query(PString strSQL)
{
SQLRETURN nRet=SQLExecDirect( m_hStmt, (SQLTCHAR *)(const char *)strSQL, SQL_NTS );
return SQL_OK( nRet );
}
BOOL PODBCStmt::Fetch()
{
return SQL_OK(SQLFetch(m_hStmt));
}
BOOL PODBCStmt::FetchPrevious()
{
SQLRETURN nRet=SQLFetchScroll(m_hStmt,SQL_FETCH_PRIOR,0);
return SQL_OK(nRet);
}
BOOL PODBCStmt::FetchNext()
{
SQLRETURN nRet=SQLFetchScroll(m_hStmt,SQL_FETCH_NEXT,0);
return SQL_OK(nRet);
}
BOOL PODBCStmt::FetchRow(PINDEX nRow,BOOL Absolute)
{
SQLRETURN nRet=SQLFetchScroll(m_hStmt,
(Absolute ? SQL_FETCH_ABSOLUTE : SQL_FETCH_RELATIVE),nRow);
return SQL_OK(nRet);
}
BOOL PODBCStmt::FetchFirst()
{
SQLRETURN nRet=SQLFetchScroll(m_hStmt,SQL_FETCH_FIRST,0);
return SQL_OK(nRet);
}
BOOL PODBCStmt::FetchLast()
{
SQLRETURN nRet=SQLFetchScroll(m_hStmt,SQL_FETCH_LAST,0);
return SQL_OK(nRet);
}
BOOL PODBCStmt::Cancel()
{
SQLRETURN nRet=SQLCancel(m_hStmt);
return SQL_OK(nRet);
}
PStringArray PODBCStmt::TableList(PString option)
{
PString list;
PString entry;
SQLINTEGER len = 129;
SQLINTEGER cb = 0;
SQLRETURN nRet;
/// This Statement will need reviewing as it
/// depends on the Database, Might work on some
/// but not on others
nRet = SQLTables(m_hStmt, 0, SQL_NTS, 0, SQL_NTS,0, SQL_NTS,(unsigned char *)(const char *)option,option.GetLength());
if (SQL_OK(nRet)) {
SQLBindCol(m_hStmt, 3, SQL_C_CHAR, entry.GetPointer(len),129,&cb);
while (Fetch())
{
SQLGetData(m_hStmt, 3, SQL_C_CHAR, entry.GetPointer(len), 129, &cb);
if (entry.GetLength() > 0) {
list = list + ":" + entry;
entry.MakeEmpty();
}
}
}
return list.Tokenise(":",FALSE);
}
BOOL PODBCStmt::SQL_OK(SQLRETURN res)
{
if ((res==SQL_SUCCESS_WITH_INFO) || (res==SQL_SUCCESS))
return TRUE;
if (res != SQL_NEED_DATA)
GetLastError();
return FALSE;
}
void PODBCStmt::GetLastError()
{
SQLRETURN rc;
SQLINTEGER NativeError;
int buflen = SQL_MAX_MESSAGE_LENGTH;
SQLSMALLINT MsgLen, i = 1;
PString ErrStr, Msg;
while ((rc = SQLGetDiagRec(SQL_HANDLE_STMT, m_hStmt, i, (unsigned char *)ErrStr.GetPointer(6),
&NativeError,(unsigned char *)Msg.GetPointer(buflen), buflen, &MsgLen)) != SQL_NO_DATA)
{
odbclink->OnSQLError(ErrStr, Msg);
i++;
}
}
//////////////////////////////////////////////////////////////////////////////
/// PODBCRecord
unsigned int PODBCRecord::Precision;
int PODBCRecord::MaxCharSize;
PTime::TimeFormat PODBCRecord::TimeFormat;
//--
PODBCRecord::PODBCRecord(PODBCStmt * hStmt)
: Stmt(hStmt)
{
m_hStmt=*hStmt;
dbase = (PODBC::DataSources)hStmt->dbase; // Database name
if (!Precision)
Precision = 4;
if (!TimeFormat)
TimeFormat = PTime::RFC1123;
MaxCharSize = 56; //56 Kbytes (Stupid MSAccess)
}
PINDEX PODBCRecord::ColumnCount()
{
short nCols=0;
if(!Stmt->SQL_OK(SQLNumResultCols(m_hStmt,&nCols)))
return 0;
return nCols;
}
BOOL PODBCRecord::InternalBindColumn(USHORT Column,LPVOID pBuffer,
ULONG pBufferSize,LONG * pReturnedBufferSize,
USHORT nType)
{
LONG pReturnedSize=0;
SQLRETURN Ret=SQLBindCol(m_hStmt,Column,nType,
pBuffer,pBufferSize,&pReturnedSize);
if(*pReturnedBufferSize)
*pReturnedBufferSize=pReturnedSize;
cout << pReturnedSize;
return Stmt->SQL_OK(Ret);
}
PINDEX PODBCRecord::ColumnByName(PString Column)
{
PINDEX nCols=ColumnCount();
for(PINDEX i=1;i<(nCols+1);i++)
{
if(Column == ColumnName(i))
return i;
}
return 0;
}
BOOL PODBCRecord::InternalGetData(USHORT Column, LPVOID pBuffer,
ULONG pBufLen, LONG * dataLen, int Type)
{
SQLINTEGER od=0;
int Err=SQLGetData(m_hStmt,Column,Type,pBuffer,pBufLen,&od);
if (!Stmt->SQL_OK(Err))
return FALSE;
if(dataLen)
*dataLen=od;
return TRUE;
}
PString PODBCRecord::GetLongData(PINDEX Column)
{
PString sbin;
PString Data;
SQLINTEGER len = MAX_DATA_LEN;
SQLINTEGER cb =0;
while (InternalGetData((USHORT)Column,sbin.GetPointer(len + 1),len,&cb))
{
if (sbin.Right(1) == '\0') // Remove Null Char
Data = Data + sbin.Left(sbin.GetLength()-1);
else
Data = Data + sbin;
}
return Data;
}
void PODBCRecord::Data(PINDEX Column, PODBC::Field & field)
{
SQLINTEGER len = MAX_DATA_LEN;
// SQLINTEGER cb =0;
BOOL B = TRUE; /// Bind Switch (Readonly Fields are not Bound
PODBC::Field::Bind & b = field.Data;
PODBC::FieldTypes ctype = ColumnType(Column);
/// Set the Attributes
field.isReadOnly = !IsColumnUpdatable(Column);
field.isNullable = IsColumnNullable(Column);
field.isAutoInc = IsColumnAutoIndex(Column);
/// Mark Columns which ReadOnly to Be Ignored when Binding.
if (field.isReadOnly) B = FALSE;
switch (ctype) {
/// Numeric Data
case PODBC::BigInt:
if (B)
SQLBindCol(m_hStmt, Column,SQL_C_SBIGINT, &b.sbint,0,&b.dataLen);
AddField(field,ColumnName(Column),ctype, PODBC::oPInt64);
break;
case PODBC::TinyInt:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_UTINYINT, &b.sbit, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype, PODBC::oshort);
break;
case PODBC::Bit:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_BIT, &b.sbit, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype, PODBC::oBOOL);
break;
case PODBC::Char:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_CHAR, &b.suchar, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::ochar);
break;
case PODBC::Integer:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_LONG, &b.slint, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::olong);
break;
case PODBC::SmallInt:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_SSHORT, &b.ssint, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype, PODBC::oint);
break;
case PODBC::Numeric:
case PODBC::Decimal:
case PODBC::Float:
case PODBC::Real:
case PODBC::Double:
field.Decimals = ColumnPrecision(Column);
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_DOUBLE, &b.sdoub, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::odouble);
break;
/// Data Structures
case PODBC::Date:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_TYPE_DATE, &b.date, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::oPTime);
break;
case PODBC::Time:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_TYPE_TIME, &b.time, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::oPTime);
break;
case PODBC::TimeStamp:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_TYPE_TIMESTAMP, &b.timestamp, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::oPTime);
break;
case PODBC::Guid:
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_GUID, &b.guid, 0, &b.dataLen);
AddField(field,ColumnName(Column),ctype,PODBC::oPGUID);
break;
/// Binary Data
case PODBC::Binary:
case PODBC::VarBinary:
case PODBC::LongVarBinary:
case PODBC::LongVarChar:
if (dbase == PODBC::MSAccess) /// Stupid Access Stuff!
if (B)
SQLBindCol(m_hStmt, field.col, SQL_C_CHAR, b.sbinlong.GetPointer(len*MaxCharSize), len*MaxCharSize, &b.dataLen);
else {
if (B)
SQLBindCol(m_hStmt, Column, SQL_LONGVARCHAR, (SQLPOINTER)Column, 0, &b.dataLen);
if (field.LongData)
b.dataLen = SQL_LEN_DATA_AT_EXEC(0);
else
b.dataLen = SQL_LEN_DATA_AT_EXEC(len);
}
AddField(field,ColumnName(Column),ctype,PODBC::oPString);
break;
/// Character
case PODBC::DateTime:
case PODBC::Unknown:
case PODBC::VarChar:
default:
b.sbin.SetSize(len+1);
AddField(field,ColumnName(Column),ctype,PODBC::oPString);
if (B)
SQLBindCol(m_hStmt, Column, SQL_C_CHAR, b.sbin.GetPointer(len), len, &b.dataLen);
break;
};
}
BOOL PODBCRecord::PostNew(PODBC::Row & rec)
{
SQLRETURN nRet;
nRet = SQLBulkOperations(m_hStmt, // Statement handle
SQL_ADD // Operation
);
return InternalSaveLongData(nRet,rec);
}
BOOL PODBCRecord::PostUpdate(PODBC::Row & rec)
{
SQLRETURN nRet;
nRet = SQLSetPos(m_hStmt, // Statement handle
(unsigned short)1, // RowNumber
SQL_UPDATE, // Operation
SQL_LOCK_NO_CHANGE // LockType
);
return InternalSaveLongData(nRet,rec);
}
BOOL PODBCRecord::PostDelete(PINDEX row)
{
SQLRETURN nRet;
nRet = SQLSetPos(m_hStmt, // Statement handle
(unsigned short)row, // RowNumber
SQL_DELETE, // Operation
SQL_LOCK_NO_CHANGE // LockType
);
return (Stmt->SQL_OK(nRet));
}
BOOL PODBCRecord::InternalSaveLongData(SQLRETURN nRet, PODBC::Row & rec)
{
SQLPOINTER pToken;
SQLINTEGER cbData;
PString DataSlice;
PINDEX frag, col=0;
/// Everything OK but no Long Data
if (Stmt->SQL_OK(nRet))
return TRUE;
/// Error Somewhere else.
if (nRet != SQL_NEED_DATA)
return FALSE;
/// If More Data Required
while (nRet == SQL_NEED_DATA) {
nRet = SQLParamData(m_hStmt,&pToken);
if (col != (PINDEX)pToken) {
col = (PINDEX)pToken;
DataSlice = PString();
frag = 0;
cbData = MAX_DATA_LEN;
}
if (nRet == SQL_NEED_DATA) {
while (rec[col].DataFragment(DataSlice,frag, cbData))
SQLPutData(m_hStmt,DataSlice.GetPointer(cbData),cbData);
}
}
return TRUE;
}
PODBC::FieldTypes PODBCRecord::ColumnType( PINDEX Column )
{
int nType=SQL_C_DEFAULT;
SQLTCHAR svColName[ 256 ]=_T("");
SWORD swCol=0,swType=0,swScale=0,swNull=0;
UDWORD pcbColDef;
SQLDescribeCol( m_hStmt, // Statement handle
Column, // ColumnNumber
svColName, // ColumnName
sizeof( svColName), // BufferLength
&swCol, // NameLengthPtr
&swType, // DataTypePtr
&pcbColDef, // ColumnSizePtr
&swScale, // DecimalDigitsPtr
&swNull ); // NullablePtr
nType=(int)swType;
return( (PODBC::FieldTypes)nType );
}
DWORD PODBCRecord::ColumnSize( PINDEX Column )
{
// int nType=SQL_C_DEFAULT;
SQLTCHAR svColName[ 256 ]=_T("");
SWORD swCol=0,swType=0,swScale=0,swNull=0;
DWORD pcbColDef=0;
SQLDescribeCol( m_hStmt, // Statement handle
Column, // ColumnNumber
svColName, // ColumnName
sizeof( svColName), // BufferLength
&swCol, // NameLengthPtr
&swType, // DataTypePtr
&pcbColDef, // ColumnSizePtr
&swScale, // DecimalDigitsPtr
&swNull ); // NullablePtr
return pcbColDef;
}
DWORD PODBCRecord::ColumnScale( PINDEX Column )
{
// int nType=SQL_C_DEFAULT;
SQLTCHAR svColName[ 256 ]=_T("");
SWORD swCol=0,swType=0,swScale=0,swNull=0;
DWORD pcbColDef=0;
SQLDescribeCol( m_hStmt, // Statement handle
Column, // ColumnNumber
svColName, // ColumnName
sizeof( svColName), // BufferLength
&swCol, // NameLengthPtr
&swType, // DataTypePtr
&pcbColDef, // ColumnSizePtr
&swScale, // DecimalDigitsPtr
&swNull ); // NullablePtr
return swScale;
}
PString PODBCRecord::ColumnName(PINDEX Column) //, PString Name, SHORT NameLen )
{
// int nType=SQL_C_DEFAULT;
SWORD swCol=0,swType=0,swScale=0,swNull=0;
DWORD pcbColDef=0;
TCHAR Name[256]=_T("");
SQLRETURN Ret=
SQLDescribeCol( m_hStmt, // Statement handle
Column, // ColumnNumber
(SQLTCHAR*)(LPTSTR)Name, // ColumnName
sizeof(Name), // BufferLength
&swCol, // NameLengthPtr
&swType, // DataTypePtr
&pcbColDef, // ColumnSizePtr
&swScale, // DecimalDigitsPtr
&swNull ); // NullablePtr
if (!Stmt->SQL_OK(Ret))
return PString();
return Name;
}
BOOL PODBCRecord::IsColumnNullable( PINDEX Column )
{
// int nType=SQL_C_DEFAULT;
SQLTCHAR svColName[ 256 ]=_T("");
SWORD swCol=0,swType=0,swScale=0,swNull=0;
UDWORD pcbColDef;
SQLDescribeCol( m_hStmt, // Statement handle
Column, // ColumnNumber
svColName, // ColumnName
sizeof( svColName), // BufferLength
&swCol, // NameLengthPtr
&swType, // DataTypePtr
&pcbColDef, // ColumnSizePtr
&swScale, // DecimalDigitsPtr
&swNull ); // NullablePtr
return (swNull==SQL_NULLABLE);
}
BOOL PODBCRecord::IsColumnUpdatable(PINDEX Column )
{
SWORD colUpdate=0;
SQLColAttribute(m_hStmt, // StatementHandle
(SQLSMALLINT)(Column), // ColumnNumber
SQL_DESC_UPDATABLE, // FieldIdentifier
NULL, // CharacterAttributePtr
0, // BufferLength
NULL, // StringLengthPtr
&colUpdate); // NumericAttributePtr
return (colUpdate != SQL_ATTR_READONLY);
}
BOOL PODBCRecord::IsColumnAutoIndex(PINDEX Column )
{
SWORD colIndex=0;
SQLColAttribute(m_hStmt, // StatementHandle
(SQLSMALLINT)(Column), // ColumnNumber
SQL_DESC_AUTO_UNIQUE_VALUE, // FieldIdentifier
NULL, // CharacterAttributePtr
0, // BufferLength
NULL, // StringLengthPtr
&colIndex); // NumericAttributePtr
return (colIndex == SQL_TRUE);
}
unsigned int PODBCRecord::ColumnPrecision(PINDEX Column )
{
SWORD coldigits=0;
SQLColAttribute(m_hStmt, // StatementHandle
(SQLSMALLINT)(Column), // ColumnNumber
SQL_DESC_PRECISION, // FieldIdentifier
NULL, // CharacterAttributePtr
0, // BufferLength
NULL, // StringLengthPtr
&coldigits); // NumericAttributePtr
if (Precision < (unsigned)coldigits)
return Precision;
else
return coldigits;
}
#pragma warning(default:4100)
#pragma warning(default:4244)
syntax highlighted by Code2HTML, v. 0.9.1