FiveTech Software tech support forums

Code: Select all

xPROVIDER := "OraOledb.oracle"
xSOURCE   := "yourdatabase"
xUSERID   := "youruserid"
xPASSWORD := "yourpassword"


cSql := "Select * from [YourTable]"

oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType     := 1        // opendkeyset
oRs:CursorLocation := 3        // local cache
oRs:LockType       := 3        // lockoportunistic

TRY
   oRs:Open(cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
   MsgInfo( "Error in Opening Oracle table" )
   oDlg:End()
   RETURN(.F.)
END TRY

xBrowse( oRs )

oRs:CLose()


 

Code: Select all

*
*   Program name: AdoConct.Prg
*   Purpose: Create a connection with various databases through ADO (TADOConnect class)
*   Author: Nagesh G. Rao
*   Co-authors: Kleyber Derick and Antonio Ortega
*/

#ifndef __XHARBOUR__
#err "Library for xHarbour only. Does not work for Clipper/Harbour"
#endif

#include "fivewin.ch"
#include "adodef.ch"

///////////////////////
CLASS TAdoConnect

EXPORT:
DATA   oConnect   READONLY
DATA   oDataShape READONLY

EXPORT:
DATA   cProvider    AS CHARACTER
DATA   cDatabase    AS CHARACTER
DATA   cDataSource  AS CHARACTER
DATA   cUserID      AS CHARACTER
DATA   cPassWord    AS CHARACTER

DATA   cError INIT "S_OK"

EXPORT:
METHOD   New()
METHOD   Connect()
METHOD   ConnectDS()
METHOD   IsOpen()  INLINE ( ::oConnect:State > 0 )
METHOD   RecordSet()
METHOD   OpenTable()
METHOD   GetTables()
METHOD   Close()

ENDCLASS

METHOD New( cProvider, cDataSource, cDatabase, cUserID, cPassWord ) CLASS TAdoConnect
if valtype( cProvider   ) == "C" ; ::cProvider    := cProvider ;   endif
if valtype( cDatabase   ) == "C" ; ::cDatabase    := cDatabase ;   endif
if valtype( cDataSource ) == "C" ; ::cDataSource  := cDataSource ; endif
if valtype( cUserID     ) == "C" ; ::cUserID      := cUserID ;     endif
if valtype( cPassWord   ) == "C" ; ::cPassWord    := cPassWord ;   endif
return( Self )
///////////////////////
METHOD Connect() CLASS TAdoConnect
local cStr := "Provider="    + alltrim( ::cProvider )   + ";" + ;
              "Data Source=" + alltrim( ::cDataSource ) + ";" + ;
              "Database="    + alltrim( ::cDataSource ) + ";" + ;
              "User id="     + alltrim( ::cUserID )     + ";" + ;
              "Password="    + alltrim( ::cPassWord )

::oConnect := TOleAuto():New( "ADODB.Connection" )
::cError   := Ole2TxtError()
if ::cError == "S_OK"
   MsgRun( "Jungimasis prie serverio ...", alltrim( ::cDataSource ), { || ::oConnect:Open( cStr ) } )
endif
return( Self )

METHOD ConnectDS() CLASS TAdoConnect
if ::oConnect != NIL .and. ::oDataShape == NIL
   ::oDataShape  := TOleAuto():New( "ADODB.Connection" )
   ::cError      := Ole2TxtError()
   ::oDataShape:ConnectionString := "Provider=MSDataShape;Data " + ::oConnect:ConnectionString
   if ::cError == "S_OK"
      MsgRun( "Jungimasis prie serverio ...", alltrim( ::cDataSource ), ;
      { || ::oDataShape:Open() } )
   endif
endif
return( Self )

METHOD RecordSet( cSql, cWhere, nCursorType, nLockType, nMaxRows ) CLASS TAdoConnect
local oRecSet
local oConnect := ::oConnect
local bOldError
DEFAULT  nCursorType := adOpenDynamic,   ;
         nLockType   := adLockOptimistic
if upper( cSql ) == "SHAPE" // DataShaping is used
   if ::oDataShape == NIL
      ::ConnectDS()
   endif
   oConnect:= ::oDataShape
   cWhere   := NIL
  else  // Normal Sql
   if cWhere != NIL .and. .NOT. " WHERE " $ cSql
      cSql   += (  " WHERE  "+ cWhere )
      cWhere := NIL
   endif
   if " WHERE " $ cSql .and. !( cSql = "SELECT " )
      cSql   := "SELECT * FROM " + cSql
   endif
endif
oRecSet  := TOleAuto():New( "ADODB.RecordSet" )
if ( ::cError := Ole2TxtError() ) == "S_OK"
   WITH OBJECT oRecSet
      :CursorLocation   := adUseClient
      :LockType         := nLockType
      :CursorType       := nCursorType
      :ActiveConnection := oConnect
      :Source           := cSql
      if nMaxRows != NIL
         :MaxRecords    := nMaxRows
      endif
      :CacheSize        := 40
   END
endif
return( oRecSet )

METHOD OpenTable( cSql, cWhere, nCursorType, nLockType, nOptions, nMaxRows, lSilent ) CLASS TAdoConnect
local oRecSet
local oTable
// not using nOptions right now
DEFAULT lSilent := .t. // ( valtype( nMaxRows ) == "N" .and. nMaxRows == 1 )

oRecSet  := ::RecordSet( cSql, cWhere, nCursorType, nMaxRows )
if lSilent
   oRecSet:Open()
  else
   MsgRun( "Lenteles atidarymas ...", "Prasome palaukti ...", { || oRecSet:Open() } )
endif
oTable  := AdoTable():New( oRecSet )
return( oTable )

METHOD GetTables() CLASS TAdoConnect
// This SQL works only for Oracle
// Either use specific SQL for each or use slower ADOX
return( ::OpenTable( "SELECT TABLE_NAME,OWNER,NUM_ROWS FROM ALL_TABLES" ) )

METHOD Close() CLASS TAdoConnect
#ifdef __XHARBOUR__
   ::oConnect:Close()
#else
   ::oConnect:End()
#endif
return( Self )

 

Code: Select all

//
// defines for the ADO Stuff
//

// LockType
#define adLockUnspecified   -1
#define adLockReadOnly       1
#define adLockPessimistic    2
#define adLockOptimistic     3
#define adLockBatchOptimistic 4

// CursorType
#define adOpenUnspecified     1
#define adOpenForwardOnly     0
#define adOpenKeyset          1
#define adOpenDynamic         2
#define adOpenStatic          3

// Cursor Location
#define adUseClient           3
#define adUseServer           2

// Options
#define adCmdUnspecified     -1
#define adCmdUnknown          8
#define adCmdText             1
#define adCmdTable            2
#define adCmdStoredProc       4
#define adCmdFile           256
#define adCmdTableDirect    512

#define adHoldRecords       0x100
#define adMovePrevious      0x200
#define adAddNew            0x1000400
#define adDelete            0x1000800
#define adUpdate            0x1008000
#define adBookmark          0x2000
#define adApproxPosition    0x4000
#define adUpdateBatch       0x10000
#define adResync            0x20000
#define adNotify            0x40000
#define adFind              0x80000
#define adSeek              0x400000
#define adIndex             0x800000

// DataType
#define adEmpty              0
#define adTinyInt           16
#define adSmallInt           2
#define adInteger            3
#define adBigInt            20
#define adUnsignedTinyInt   17
#define adUnsignedSmallInt  18
#define adUnsignedInt       19
#define adUnsignedBigInt    21
#define adSingle             4
#define adDouble             5
#define adCurrency           6
#define adDecimal           14
#define adNumeric          131
#define adBoolean           11
#define adError             10
#define adUserDefined      132
#define adVariant           12
#define adIDispatch          9
#define adIUnknown          13
#define adGUID              72
#define adDate               7
#define adDBDate           133
#define adDBTime           134
#define adDBTimeStamp      135
#define adBSTR               8
#define adChar             129
#define adVarChar          200
#define adLongVarChar      201
#define adWChar            130
#define adVarWChar         202
#define adLongVarWChar     203
#define adBinary           128
#define adVarBinary        204
#define adLongVarBinary    205
#define adChapter          136
#define adFileTime          64
#define adDBFileTime       137
#define adPropVariant      138
#define adVarNumeric       139

// BookMark
#define adBookmarkCurrent    0
#define adBookmarkFirst      1
#define adBookmarkLast       2

// Field attributes
#define adFldMayBeNull     0x40


// Options
#define adOptionUnspecified -1
#define adAsyncExecute      16
#define adAsyncFetch        32
#define adAsyncFetchNonBlocking 64
#define adExecuteNoRecords  128
#define adExecuteStream     256
#define adExecuteRecord     512

// Resync
// objRecordset:Resync( affectrecords,resyncvalues )

// affect records
#define adAffectCurrent     1
#define adAffectGroup       2
#define adAffectAll         3   // default (only inside filter)
#define adAffectAllChapters 4

// ResyncValues
#define adResyncAllValues       2   // default
#define adResyncUnderlyingValues    1


// rs=objconn.OpenSchema(querytype,criteria,schemaid)
// ignore schemaid . provider specific

#define adSchemaTables      20
#define adSchemaColumns     4

// State constants

#define adStateClosed       0   //The object is closed
#define adStateOpen     1   //The object is open
#define adStateConnecting   2   //The object is connecting
#define adStateExecuting    4   //The object is executing a command
#define adStateFetching     8   //The rows of the object are being retrieved