/* * 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 #include #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 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)