Olá!
Se preferir ADO veja o exemplo abaixo, em SQL Server. Abre uma conexão, executa uma
stored procedure e exibe o
record set resultante através de um TBrowseDB() no próprio objeto retornado ( note as funções ADORecordSetSkipper() e ADORecordSetFieldBlock() ). Fiz uma alteração nesta última mas no momento estou sem SQL Server e não testei. Qualquer coisa ajustamos.
/*
Exibição das linhas de um Record set ADO usando TBrowseDB()
Alexandre Santos
Compilar: Hbmk2 tbado hbwin.hbc
*/
#include "tbrowse.ch"
#include "inkey.ch"
#include "setcurs.ch"
#include "box.ch"
#include "set.ch"
#include "ado.ch"
FUNCTION Teste()
LOCAL oCn, oCmd, oRs, oTbr As Object
LOCAL i, nLen, nKey, nRecno As Numeric
LOCAL cCnString as Character
LOCAL bErr := ErrorBlock( __BreakBlock() ), oErr
Set( _SET_DATEFORMAT, "dd/mm/yyyy" )
BEGIN SEQUENCE
/*
Ajuste aqui a connection string conforme o banco
Ou pequise aqui...: https://www.connectionstrings.com/]
*/
// SQL Server 2008 R2
cCnString := "Provider=SQLNCLI10;Server=172.16.40.5;Database=NomeBanco;Uid=usuario;Pwd=senha;"
oCn := win_OleCreateObject("ADODB.Connection")
oCn:ConnectionString := cCnString
oCn:CursorLocation := adUseClient
oCn:Mode := adModeReadWrite
oCn:open()
oCmd := win_OleCreateObject("ADODB.Command")
oCmd:ActiveConnection := oCn
// Prepare the stored procedure
oCmd:CommandText := "spCountryGetAllRows"
oCmd:CommandType := adCmdStoredProc
DispOutAt( MaxRow(), 3, " Obtendo registros... ", "N/W" )
// Execute the stored procedure
// This returns a recordset
oRs := oCmd:Execute()
DispOutAt( MaxRow(), 3, Space(22), "W/W" )
oCmd := NIL
IF oRs != NIL
oTbr := TBrowseDB():new( 04, 3, MaxRow() - 7, MaxCol() - 3 )
// Separators
oTbr:headSep := DEF_HSEP
oTbr:colSep := DEF_CSEP
oTbr:footSep := DEF_FSEP
oTbr:cargo := oRs:bookMark
// Navigation code blocks for Record Set
oTbr:goTopBlock := { || oRs:moveFirst() }
oTbr:goBottomBlock := { || oRs:moveLast() }
oTbr:skipBlock := { |n| ADORecordSetSkipper( oRs,n ) }
// Colors
oTbr:colorSpec := "N/W, W+/BG,N/W*"
// create TBColumn objects and add them to TBrowse object - zero based
nLen := oRs:fields():count() - 1
FOR i := 0 TO nLen
// add code block for individual columns of the record set
oColumn := TBColumnNew( oRs:fields(i):name(), ADORecordSetFieldBlock( oRs, i ) )
// Column widths. For some data types, definedSize returns -1...
oColumn:width := Max( Min( oRs:Fields(i):definedSize,50), Len( oRs:fields(i):name ) )
/// TODO: set Column pictures
// Add new column to TBrowse
oTbr:addColumn( oColumn )
NEXT
// border
DispBox( oTbr:nBottom, oTbr:nLeft - 1, oTbr:nBottom + 4, oTbr:nRight + 1, B_SINGLE + " " )
DispBox( oTbr:nTop - 1, oTbr:nLeft - 1, oTbr:nBottom, oTbr:nRight + 1, B_SINGLE )
DispBox( oTbr:nBottom, oTbr:nLeft - 1, oTbr:nBottom + 4, oTbr:nRight + 1, B_SINGLE + " " )
nOldCursor := SetCursor( SC_NONE )
IF ( oRs:eof() )
DispOutAt( Int( ( oTbr:nBottom - oTbr:nTop ) / 2 ), oTbr:nLeft + 2, ;
PadC( "Não há dados disponÃveis para exibição.", Int( oTbr:nRight - oTbr:nLeft ) - 2 ), "W+/RB" )
ELSE
DispOutAt( MaxRow(), 1, PadR( " Registro " + Ltrim( Str( oRs:AbsolutePosition ) ) + " de " + Ltrim( Str( oRs:recordCount ) ) + " ", 20 ), "N/W" )
ENDIF
DO WHILE .T.
DispBegin()
oTbr:forceStable()
// Paint TBrowse current line...
oTbr:ColorRect( { oTbr:RowPos, oTbr:LeftVisible, oTbr:RowPos, oTbr:RightVisible }, { 2, 1 } )
// ... and current cell in different colors
oTbr:ColorRect( { oTbr:rowPos, oTbr:colPos, oTbr:rowPos, oTbr:colPos }, { 3, 2 } )
oTbr:refreshCurrent()
DispEnd()
IF ( oRs:eof() )
DispOutAt( Int( ( oTbr:nBottom - oTbr:nTop ) / 2 ), oTbr:nLeft + 2, ;
PadC( "Não há dados disponÃveis para exibição.", Int( oTbr:nRight - oTbr:nLeft ) - 2 ), "W+/RB" )
ELSE
DispOutAt( MaxRow(), 1, PadR( " Registro " + Ltrim( Str( oRs:AbsolutePosition ) ) + " de " + Ltrim( Str( oRs:recordCount ) ) + " ", 20 ), "N/W" )
ENDIF
nKey := Inkey(0)
IF oTbr:applyKey( nKey ) == TBR_EXIT
EXIT
//ELSEIF nKey == K_ENTER
// nRecno := oRs:bookMark
// DispRow( oRs:getRows( 1, adBookmarkCurrent ) )
// oRs:bookMark := nRecno
ENDIF
ENDDO
ENDIF
RECOVER USING oErr
HB_Alert( { "Ocorreu o erro : ;" + oErr:Description, "", "Operação: " + oErr:Operation }, ;
{ ' Fechar ' },"W+/B", 15 )
ALWAYS
IF oRs != NIL .And. oRs:state() = adStateOpen
oRs:close()
ENDIF
IF oCn != NIL .And. oCn:state() = adStateOpen
oCn:close()
ENDIF
oCn := NIL
oRs := NIL
SetCursor( nOldCursor )
END SEQUENC
CLS
ErrorBlock( cbErr )
RETURN NIL
//------------------------------------------------------------------------------
FUNCTION ADORecordSetFieldBlock( oRs, i, xVal )
/*
RETURN { || If( oRs:eof(),Space(Max(oRs:Fields(i):DefinedSize,Len(oRs:Fields(i):name))), ;
oRs:fields(i):Value() ) }
*/
LOCAL bRet
IF xVal == NIL
If oRs:eof()
bRet := { || Space( Max( oRs:Fields( i ):DefinedSize , Len( oRs:Fields( i ):name ) ) ) }
Else
bRet := { || oRs:Fields( i ):value }
Endif
Else
bRet := { |xVal| oRs:Fields( i ):Value := xVal }
ENDIF
RETURN bRet
//------------------------------------------------------------------------------
STATIC FUNCTION ADORecordSetSkipper(oRecordSet,nSkip)
LOCAL nRec := oRecordSet:AbsolutePosition
IF ! ( oRecordSet:eof )
oRecordSet:Move( nSkip )
IF oRecordSet:eof
oRecordSet:moveLast()
ENDIF
IF oRecordSet:bof
oRecordSet:moveFirst()
ENDIF
ENDIF
RETURN (oRecordSet:AbsolutePosition - nRec)
//------------------------------------------------------------------------------
Nota: Caso necessite carregar o Record set para um array, use o método getRows() do ADO. Exemplo:
LOCAL aArray, oRs, etc...
// ...
// Recuperação das kinhas do banco
// ...
aArray := oRs:getRows()
SQLMix ADO TBrowse