// SQLExpress demo program showing how to use TBrowse to browse ODBC tables
//------------------------------------------------------------------------------------

#include "sql.ch"
#include "sqlext.ch"
#include "inkey.ch"

#define __CONNECT_STRING  'DBQ=data\test.mdb;Driver={Microsoft Access Driver (*.mdb)};UID=admin;'
#define __STATEMENT       'SELECT * FROM [customer],[orders] '+;
                          'WHERE [orders].[customer id]=[customer].[customer id] '+;
                          'ORDER BY [customer name]'

PROCEDURE Main( cMyTable, cMyConnection )
   Local i, nKey := 0, nCol, cPrompt, oConn, oCursor, oTb, oTBCol, cSQLStatement, lAllowSeek

   if Empty(cMyConnection)
      cMyConnection := __CONNECT_STRING
   endif

   // establish the ODBC connection
   oConn := SQLConnection():new()
   oConn:driverConnect(nil, cMyConnection)

   if ! oConn:isConnected
      MsgBox("Connection error!")
      Return
   endif

   cSQLStatement := iif(Empty(cMyTable),__STATEMENT, "SELECT * FROM " + cMyTable)

   oCursor := SQLSelect():new(cSQLStatement, oConn, SQL_CONCUR_READ_ONLY)
   oCursor:DateTimeAsDate := .t.
   oCursor:Execute()

   ?? "Connected to: " + oConn:DbmsName + ", Ver: " + oConn:DbmsVersion + ", Database: " + oConn:DatabaseName
   ? "Connect string: " + oConn:ConnectString
   ? "SQL Statement: " + oCursor:SQLString

   // allow seeking if result set is sorted and the sort column is character type
   if lAllowSeek := !Empty(oCursor:SortOrder) .and. ValType(oCursor:FieldGet(oCursor:SortOrder[1])) == "C"
      cPrompt := "SEEK " + oCursor:FieldName(oCursor:SortOrder[1]) + ":"
      nCol    := Len(cPrompt) + 1
      DispOutAt(5,0,cPrompt,"N/GR")
   endif

   oTB := TBrowse():new( 6, 0, 24, 80 )

   oTB:goTopBlock := {|| oCursor:GoTop() }
   oTB:goBottomBlock := {|| oCursor:GoBottom() }
   oTB:skipBlock := {|n| oCursor:Skipper(n) }
   oTB:headSep := "-"
   oTB:colSep := "|"

   for i := 1 to oCursor:FCount
      oTBCol := TBColumn():new( oCursor:FieldName(i), SQLFieldBlock(oCursor, i) )
      oTB:AddColumn( oTBCol )
   next

   oTB:forceStable()  // display complete TBrowse

   while nKey <> K_ESC  // terminate on Esc
      while ! oTB:stabilize()  // incremental display
         if (nKey := Inkey()) <> 0
            exit
         endif
      enddo
      if oTB:stable  // TBrowse is stable
         nKey := Inkey(0)  // wait for keystroke
      endif
      if lAllowSeek
         TBSeek( nKey, oTB, oCursor, nCol )
      endif
      TBApplyKey( oTB, nKey )  // process key
    enddo

   oConn:destroy()  // destroy connection & all child SQL objects

   Return

//-----------------------------------------------------------------------------
STATIC FUNCTION TBSeek( nKey, oTB, oCursor, nCol )
   Static cSeek := ""

   // perform incremental seek each time user types a character
   if nKey >= 32 .and. nKey <= 254
      cSeek += Chr( nKey )
      DispOutAt(5,nCol, cSeek, "GR+/R")
      if ! oCursor:Seek( cSeek,, .t.)
         Tone(1000)
      endif
      oTB:RefreshAll():ForceStable()

   elseif nKey == K_BS
      cSeek := Left(cSeek, Len(cSeek)-1)
      DispOutAt(5,nCol, Space(80))
      DispOutAt(5,nCol, cSeek, "GR+/R")
      oCursor:Seek( cSeek,, .t.)
      oTB:RefreshAll():ForceStable()

   endif
   Return cSeek

//-----------------------------------------------------------------------------
STATIC FUNCTION SQLFieldBlock( oCursor, nField )
   Return {|x| iif(PCount()==0, oCursor:FieldGet(nField), oCursor:FieldPut(nField, x))}

//-----------------------------------------------------------------------------
PROCEDURE DbeSys()
   // don't need to load any of Alaska's database drivers
   Return