Problem with browse sql dataset

[Victorio]

Hi,

I have problem with scrolling in filtered data, here is example, when I open Foxpro database , make Select and filter with Browsefilter (from SQL Express).
I do not know,why :
1. do not work oCursor:GoTop(), after it record pointer still in bottom of list records
2. why after click to first row in DCBROWSE and scroll by mouse I can go up to "cloned" rows.
I read several info about skipper and other, but I do not know, if is problem with BrowseFilter or I must realize own "listing" function to scrolling.

Same problem I have with SQL database in SQL 2000 server.

Can somebody look to my source what can be bad :

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

* nastavenie charset tu treba ANSI, inak zle
SET CHARSET TO ANSI
*SET CHARSET TO OEM // toto funguje ok

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

cConnectString := "SourceDB=d:\#POKUSY\SQLDBFFOXPRO;DRIVER=Microsoft Visual FoxPro Driver;SERVER=(local);UID=;PWD=;SourceType=DBF;Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO"

cTableName := 'VL800490.DBF' // toto môže byť nastavené pri cconnectstring alebo tu

cStatement := 'SELECT * FROM VL800490.DBF ORDER BY VLA'

oConnection := SQLConnection():new()

oConnection:driverConnect(nil, @cConnectString,SQL_DRIVER_NOPROMPT,.T.)

if ! oConnection:isConnected
* DC_WinAlert("Pripojenie ku databaze neuspesne !!!")
Return
else
* DC_WinAlert("Pripojenie ku databaze uspesne")
endif

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

* here I want filter records with CLV bigger than 1330 and smaller than 1340
oCursor:BrowseFilter := {|aRow,nRow,oData| ((oData:FieldGet("CLV"))>1330 .and.(oData:FieldGet("CLV"))<1340)}

oCursor:GoTop() // not work ?

@ 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) == 'CLV' ;
PARENT oBrowse
NEXT

DCGETOPTIONS ;
AUTORESIZE ;
BUTTONALIGN DCGUI_BUTTONALIGN_CENTER

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

* testing also this
* EVAL {||oCursor:Refresh(),iif(oCursor:bof(),oCursor:skip(1),nil),iif(oCursor:eof(),oCursor:skip(-1),nil)}


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}

[Auge_Ohr]

I have problem with scrolling in filtered data, here is example, when I open Foxpro database , make Select and filter with Browsefilter (from SQL Express).
...

Code: Select all

cStatement := 'SELECT * FROM VL800490.DBF ORDER BY VLA'

...
* here I want filter records with CLV bigger than 1330 and smaller than 1340

Code: Select all

oCursor:BrowseFilter := {|aRow,nRow,oData| ((oData:FieldGet("CLV"))>1330 .and.(oData:FieldGet("CLV"))<1340)}

i do not know about "BrowseFilter" but working with SQL i recommend to put "Filter" into you SQL Query.

[Victorio]

Jimmy, thanks for answer.
I know, can put filter or to "when" ... to cStatement, but I want use same statement and filtering only "once filtered" record , no always full database.

here is example statement, what I use :
cStatement := 'SELECT LIS_PLOMBA.KU_ID , LIS_PLOMBA.CLV , LIS_PLOMBA_HIS.PCS, LIS_KNIHY.ZNA,'+;
'LIS_KNIHY.CPS, LIS_KNIHY.ROL, LCI_ZNA.OZN, LIS_KNIHY.CTL, LCI_PLM.POPIS, '+;
'LCI_CTL.POPIS AS POPIS2, LIS_PLOMBA_HIS.CPA, LCI_STL.POPIS AS POPIS3, '+;
'LIS_POHYB.CPO, LIS_POHYB.DTV '+;
'FROM LIS_PLOMBA INNER JOIN LIS_PLOMBA_HIS ON LIS_PLOMBA.PLO_ID = LIS_PLOMBA_HIS.PLO_ID '+;
'INNER JOIN LIS_KNIHY ON LIS_PLOMBA.LIS_ID = LIS_KNIHY.LIS_ID '+;
'INNER JOIN LCI_ZNA ON LIS_KNIHY.ZNA = LCI_ZNA.ZNA '+;
'INNER JOIN LCI_PLM ON LIS_PLOMBA.PLM = LCI_PLM.PLM '+;
'INNER JOIN LCI_CTL ON LIS_KNIHY.CTL = LCI_CTL.CTL '+;
'INNER JOIN LCI_STL ON LIS_KNIHY.STL = LCI_STL.STL '+;
'INNER JOIN LIS_POHYB ON LIS_POHYB.LIS_ID = LIS_PLOMBA.LIS_ID '+;
'WHERE '+;
'(LIS_KNIHY.ZNA = 1 OR LIS_KNIHY.ZNA = 2 OR LIS_KNIHY.ZNA = 3 OR LIS_KNIHY.ZNA = 4 OR LIS_KNIHY.ZNA=15 OR LIS_KNIHY.ZNA=18) AND '+;
'(LIS_KNIHY.STL=1 OR LIS_KNIHY.STL=4 OR LIS_KNIHY.STL=5 OR LIS_KNIHY.STL=6 OR LIS_KNIHY.STL=7) AND '+; // povolený,v konaní,v konaní po lehote,prer, prer.po leh.
'(LIS_PLOMBA.PLM = 2 OR LIS_PLOMBA.PLM = 3 OR LIS_PLOMBA.PLM = 7 OR LIS_PLOMBA.PLM = 4 OR LIS_PLOMBA.PLM = 5 OR LIS_PLOMBA.PLM = 6) AND '+;
'(LIS_PLOMBA.PLS = 1 OR LIS_PLOMBA.PLS = 2) AND '+;
'(LIS_KNIHY.LTY = 1) AND '+;
'(LIS_POHYB.CPO = 1)'
*

and if I put there "filter" by CLV and KU_ID, works fine :
*'WHERE (LIS_PLOMBA.CLV ='+qcislolv+') AND (LIS_PLOMBA.KU_ID ='+qcisloku+') AND '+;

but I want implement this filter to created dataset.

Today I want examine create "cursor" , no "dataset", but I mean it will be same.

In documentation for SQL Express is note than for example GoTop() can be not implement for some systems., maybe RecCount also not work for SQL 2000.