bb.donnay-software.com

Code: Select all

///////////////////////////////////////////////////////////////////////////////
//
//  SQLExpress demo program showing how to create a DCBROWSE object
//  to browse SQL/ODBC tables.
//
///////////////////////////////////////////////////////////////////////////////

#include "sql.ch"
#include "sqlext.ch"
#include "dcdialog.ch"

//-----------------------------------------------------------------------------
PROCEDURE DbeSys() ; Return
PROCEDURE AppSys() ; Return


PROCEDURE Main()

LOCAL oConnection, oCursor, GetList[0], GetOptions, oBrowse, i, cStatement, ;
      cConnectString, cTableName, aSort

// set up default configuration of GUI browse headers for sorting
aSort := Array(4)
aSort[1] := GRA_CLR_WHITE    // Sort Selected Color (Foreground)
aSort[2] := GRA_CLR_RED      // Sort Selected Color (Background)
aSort[3] := GRA_CLR_WHITE    // Sort Unselected Color (Foreground)
aSort[4] := GRA_CLR_DARKGRAY // Sort Unselected Color (Background)
DC_BrowseSort(aSort)

DC_AutoRestoreWindow({HKEY_LOCAL_MACHINE,'Software\Donnay Software\Samples\Windows'})

// establish the ODBC connection
cConnectString := 'DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)};UID=admin;'
cTableName := 'AGENTS'
cStatement := 'SELECT * FROM AGENTS'

oConnection := SQLConnection():new()
oConnection:driverConnect(nil, @cConnectString)

if ! oConnection:isConnected
  DC_WinAlert("Connection error!")
  Return
endif

oCursor := CreateSQLCursor( oConnection, 2, cStatement )
IF Valtype(oCursor) # 'O'
  RETURN
ENDIF

@ 0,0 DCBROWSE oBrowse DATA oCursor SIZE 70,20 ;
      PRESENTATION DC_BrowPres()

FOR i := 1 TO oCursor:fCount
  DCBROWSECOL DATA SQLFieldBlock( oCursor, i ) ;
      HEADER oCursor:fieldName(i) ;
      SORT SQLSortBlock( oCursor, i ) ;
        _DEFAULT oCursor:fieldName(i) == 'AGENTID' ;
      PARENT oBrowse
NEXT

DCGETOPTIONS ;
   AUTORESIZE ;
   BUTTONALIGN DCGUI_BUTTONALIGN_CENTER

DCREAD GUI ;
   FIT ;
   OPTIONS GetOptions ;
   BUTTONS DCGUI_BUTTON_EXIT ;
   TITLE 'Browsing with SQLexpress'

oConnection:destroy()

Return

//-----------------------------------------------------------------------------

STATIC FUNCTION CreateSQLCursor( oConnection, nMode, cStatement )

Local oCursor, nSuccess := SQL_XPP_ERROR

oConnection:displayErrors := .t.

DEFAULT nMode := 1

if nMode == 1
  oCursor  := SQLSelect():new(cStatement, oConnection, SQL_CONCUR_READ_ONLY, SQL_CURSOR_DYNAMIC)
  nSuccess := oCursor:execute()
elseif nMode == 2
  // retrieve a max of 1000 rows and convert date-time values to Xbase++ dates (loose time portion)
  oCursor  := SQLDataSet():new(cStatement, oConnection,,,1000,,,.t.,.t.)
  nSuccess := oCursor:execute() // don't really need to execute an SQLDataSet, it's just here for consistency
endif

oConnection:displayErrors := .f.

RETURN oCursor

* -------------

FUNCTION SQLFieldBlock( oCursor, nField )

Return {|x|iif(Pcount()==0, oCursor:fieldGet(nField), oCursor:fieldPut(nField, x))}

* -------------

FUNCTION SQLSortBlock( oCursor, nField, nColumn )

RETURN {|a,b,descend|oCursor:sort(IIF(descend,;
              {|x,y|IIF(Valtype(x[nField])='C', ;
                    Upper(x[nField]) > Upper(y[nField]), x[nField] > y[nField])}, ;
              {|x,y|IIF(Valtype(x[nField])='C', ;
                    Upper(x[nField]) < Upper(y[nField]), x[nField] < y[nField])})), ;
              nColumn := nField}