Scope dbf with SQL Express++

This forum is for eXpress++ general support.
Post Reply
Message
Author
Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Scope dbf with SQL Express++

#1 Post by Victorio »

Hi,

How can I realise scope with eXpress++ and SQL Express++ ?

Now I have two DBF files opened with ODBC VFP driver.

From old system with only DBF / CDX system I have code block for Scope :

bOLPD:={||OLPD->(DC_SetScope(0,OLP->C_PROC)), ;
OLPD->(DC_SetScope(1,OLP->C_PROC)), ;
OLPD->(DC_DbGoTop()), ;
oBrowsed:refreshAll()}

How modify it to use SQL express++ ? In eXpress manual I found only info AX_SetScope for Advantage, but nothing about SQL Express.
I do not know, if to bOLPD need use something like this

oBrowsed:refreshAll change to oCursord:refresh()
and what with field names C_PROC for example, because in browse I show it with this :
DATA SQLFieldBlock(oCursord,oCursord:FieldPos("C_PROC"))

Here is part of browsing two databases:

*************************
* BROWSE TABUĽKA PRE OLP
*************************
DCSETPARENT oBrowse

@ 0,0 DCBROWSE oBrowse ALIAS "OLP" DATA oCursor ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
SCOPE ;
ITEMMARKED {||Eval(bOLPD), ;
DC_GetRefresh(GetList,, ;
DCGETREFRESH_TYPE_EXCLUDE,(GETLIST_BROWSE))}


/*
* automatické vygenerovanie polí , ale to nechcem, lebo chcem vlastné popisy hlavičiek
FOR i := 1 TO oCursor:fCount
DCBROWSECOL DATA SQLFieldBlock( oCursor, i ) ;
HEADER oCursor:fieldName(i) ;
SORT SQLSortBlock( oCursor, i ) ;
_DEFAULT oCursor:fieldName(i) == 'C_PROC' ;
SIZE 150,20 ;
PARENT oBrowse
NEXT
*/

* pre databázu RAUKN_OLP

DCSETPARENT oBrowse

* tu zobrazí ak je záznam zrušený
DCBROWSECOL DATA {|x|x:=deleted(),IIF(x,'X',' ')} ;
HEADER "Zru" WIDTH 5 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursor,oCursor:FieldPos("C_PROC")) ; // ozn. registra
HEADER "C_PROC" WIDTH 10 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursor,oCursor:FieldPos("DAT_ZAD")) ; // ozn. registra
HEADER "DAT_ZAD" WIDTH 10 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursor,oCursor:FieldPos("CAS_ZAD")) ; // ozn. registra
HEADER "CAS_ZAD" WIDTH 20 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursor,oCursor:FieldPos("STAV")) ; // ozn. registra
HEADER "STAV" WIDTH 20 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursor,oCursor:FieldPos("NAZPTK")) ; // ozn. registra
HEADER "NAZPTK" WIDTH 20 PARENT oBrowse

*
*************************
* BROWSE TABUĽKA PRE OLPD
*************************
DCSETPARENT TO oBrowsed

@ 22,0 DCBROWSE oBrowsed ALIAS "OLPD" DATA oCursord ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
SCOPE ;

/*
* automatické vygenerovanie polí , ale to nechcem, lebo chcem vlastné popisy hlavičiek
FOR i := 1 TO oCursord:fCount
DCBROWSECOL DATA SQLFieldBlock( oCursor, i ) ;
HEADER oCursord:fieldName(i) ;
SORT SQLSortBlock( oCursord, i ) ;
_DEFAULT oCursord:fieldName(i) == 'C_PROC' ;
SIZE 150,20 ;
PARENT oBrowsed
NEXT
*/

* pre databázu RAUKN_OLP_D

DCSETPARENT TO oBrowsed

* tu zobrazí ak je záznam zrušený
DCBROWSECOL DATA {|x|x:=deleted(),IIF(x,'X',' ')} ;
HEADER "Zru" WIDTH 5 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursord,oCursord:FieldPos("C_PROC")) ; // ozn. registra
HEADER "C_PROC" WIDTH 10 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursord,oCursord:FieldPos("DAT_ZAD")) ; // ozn. registra
HEADER "DAT_ZAD" WIDTH 10 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursord,oCursord:FieldPos("CAS_ZAD")) ; // ozn. registra
HEADER "CAS_ZAD" WIDTH 20 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursord,oCursord:FieldPos("STAV")) ; // ozn. registra
HEADER "STAV" WIDTH 20 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursord,oCursord:FieldPos("NAZPTK")) ; // ozn. registra
HEADER "NAZPTK" WIDTH 20 PARENT oBrowsed

*

DCSETPARENT oToolbarplb

***********************
* zobrazenie toolbaru
***********************
@ 0,160 DCTOOLBAR oToolbarplb SIZE 22,70 ;
ALIGN DCGUI_ALIGN_LEFT ;
BUTTONSIZE 20,1.5 ;
SPACE 2 ;
RESIZE DCGUI_RESIZE_REPOSONLY_X ;
FIT

* refresh kurzora - funguje, aj keď pridám cez RAUKN
* pre Cursor namiesto FetchData použiť Refresh, Sort tiež nepozná, musí asi server alebo statement na začiatku definovať
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'REFRESH';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
oCursor:Gotop(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* skok na začiatok - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'GOTOP';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Gotop(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* skok na koniec - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'GOBOTTOM';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Gobottom(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* vyhľadanie čisla 20 - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'SEEK' ;
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Seek({20}),; // triedenie podľa C_PROC
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* vyhľadanie čísla 20 bez dvojitých zátvoriek - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'SEEK' ;
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Seek(20),; // triedenie podľa C_PROC
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* pridanie prázdneho riadku a zadanie údajov - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'PRIDAJ 999-A';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Append(),;
oCursor:FieldPut("C_PROC", cp++ ),; // inkrementálne pridáva vždy nasledujúce číslo procesu ale to iba test, inak musím zistiť nasledujúce inak nie recno
oCursor:FieldPut("DAT_ZAD", date() ),;
oCursor:FieldPut("CAS_ZAD", time() ),;
oCursor:FieldPut("NAZPTK", "PROTOKOL-A" ),;
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
oCursor:Gobottom(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* pridanie prázdneho riadku a zadanie údajov - pridávanie s inkrementovaním por.čísla - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'PRIDAJ 999-INCR';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Gobottom(),; // skok na koniec
maxc:=oCursor:FieldGet("C_PROC"),; // zistenie posledného čísla
maxc++,; // inkrementovanie
oCursor:Append(),; // pridanie prázneho záznamu
oCursor:FieldPut("C_PROC", maxc ),; // zadanie obsahu polí
oCursor:FieldPut("DAT_ZAD", date() ),;
oCursor:FieldPut("CAS_ZAD", time() ),;
oCursor:FieldPut("NAZPTK", "PROTOKOL-A" ),;
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
oCursor:Gobottom(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia


* pridanie prázdneho riadku a zadanie údajov - naraz všetky údaje v riadku - NEFUNGUJE , neviem presný syntax
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'PRIDAJ 999-B';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Append({55,date(),"-","-",0,.F.,date(),"-",0,.F.,.F.,.F.,0,0,"-","-","-","-",0,0,"-",0,0,date(),date(),0,0,0,"-","-",0,0,.F.,"-","-","-","-","-",.F.,0,0,0,0,0,0,0,0,0,0,0,0,0,0,"-",0,"-","-",0}), ;
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* aktualizácia údajov v riadku - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'UPRAV STAV 0';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:FieldPut("STAV",0),; // znovunačítanie databázy, refresh datasetu
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* aktualizácia údajov v riadku - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'UPRAV STAV 1';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:FieldPut("STAV",1),; // znovunačítanie databázy, refresh datasetu
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* aktualizácia údajov v riadku - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'UPRAV STAV 2';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:FieldPut("STAV",2),; // znovunačítanie databázy, refresh datasetu
oCursor:UpdateRow(),;
oCursor:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia


* vymazanie riadku - funguje
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'VYMAŽ';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursor:Delete(),; // znovunačítanie databázy, refresh datasetu
oCursor:UpdateRow(),; // toto asi musí byť
oCursor:skip(),; // ? možno nemusí byť
oCursor:Refresh(),; // refresh
oCursor:gotop(),; // skok na koniec
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* tu už samotné zobrazenie BROWSERom
DCGETOPTIONS ;
AUTORESIZE ;
BUTTONALIGN DCGUI_BUTTONALIGN_CENTER

DCREAD GUI ;
FIT ;
OPTIONS GetOptions ;
BUTTONS DCGUI_BUTTON_EXIT ;
TITLE 'Browsing with SQLexpress - použitý oCursor' ;
EVAL {|o|SetAppWindow(o) ;
}

* SetAppFocus(oBrowse:GetColumn(1));

* zatvorenie cursoru a uvoľnenie pamäti
oCursor:Close()
oCursord:Close()

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Scope dbf with SQL Express++

#2 Post by rdonnay »

SQLexpress does not support any kind of filter or scope methods. I don't know why. Maybe nobody ever asked Boris for it.
Typically, scoping and filtering are done in the SQL statement with WHERE and ORDER BY clauses.

It is also possible to handle this in the browser for a better user experience but would require writing your own methods and overloading the default :skip(), :gotop(), :gobottom(), :seek() methods that are included in SQLexpress.
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Scope dbf with SQL Express++

#3 Post by Victorio »

Thank's for info,

Ok, now I am trying create statement with WHERE clausule, but this need everytime close and reopen new cursor. At this moment I have some problem with refresh browser after create new cursor... but hope I fix it.

Victorio

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Scope dbf with SQL Express++

#4 Post by rdonnay »

but this need everytime close and reopen new cursor.
Yes. This is why dynamic scoping of the current cursor would be a desirable feature.
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Scope dbf with SQL Express++

#5 Post by Victorio »

I do not know how tell to work this :

I want use code block in DCBROWSE :

ITEMMARKED {||Eval(bOLPD), ;
DC_GetRefresh(GetList,, ;
DCGETREFRESH_TYPE_EXCLUDE,(GETLIST_BROWSE)) ;

where bOLPD is some like this :
bOLPD:={||;
oCursord:Close(),;
oCursord := CreateSQLCursor( oConnection, 3, cStatementd ),;
oCursord:GoTop(), ;
oCursord:refresh()}

and statement for create cursor :
cStatementd := 'SELECT RAUKN_OLP_D.C_PROC,RAUKN_OLP_D.DAVKA,RAUKN_OLP_D.DAT_ZAD,RAUKN_OLP_D.CAS_ZAD,RAUKN_OLP_D.STAV,RAUKN_OLP_D.NAZPTK FROM RAUKN_OLP_D.DBF,RAUKN_OLP WHERE RAUKN_OLP_D.C_PROC=RAUKN_OLP.C_PROC AND RAUKN_OLP_D.C_PROC='+{str(oCursor:fieldget(1))}+' ORDER BY RAUKN_OLP_D.C_PROC '

this not work , because problem with this statement :
RAUKN_OLP_D.C_PROC='+{str(oCursor:fieldget(1))}

here I want tell browsers two tables to filter records in second table to show only records with C_PROC number from first table - oCursor:fieldget(1) is field C_PROC.

Second table not refresh, but when I manually set number, and then go to record with seek, works good:
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'OLP skok OLPD';
ACTION {|| dc_msgbox({oCursor:fieldget(1)}), ;
x:=oCursor:fieldget(1), ;
oCursord:Seek(x),; // triedenie podľa C_PROC
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia



To ODBC driver Visual Foxpro I go because long delays when user connect to database via VPN and slow LAN.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Scope dbf with SQL Express++

#6 Post by rdonnay »

You need to assign the new cursor to the DC_XbpBrowse() object:

oBrowse:dataSource := oCursord
oBrowse:refreshAll()
The eXpress train is coming - and it has more cars.

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Scope dbf with SQL Express++

#7 Post by Victorio »

Roger,

thanks but I absolutely do not know why this not work for me.

Where can put oBrowse:dataSource:=oCursord
and oBrowse:refreshAll ?

oBrowse is main table with alias OLP , and SQL cursor oCursor
oBrowse is table in relation, his records must be "filtered" by field C_PROC in oBrowse with alias OLPD and SQL cursor oCursord

Now I play with cstatement, because values for this was not accepted, now i use code block

Here is part of test source.

At this moment is relations disabled, I want only after click on button REFRESH-OLPD3 show in secondary table OLPD (oCursorolpd) only records with number C_PROC in main table.

But in second table always after refresh(), refreshall() is default data, interesting is that pointer is on good positon only view is not good.

Please if no lot of time need to look to my source. I mean, there are many strange combinations, so I am still beginner :)


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

PROCEDURE DbeSys() ; Return
PROCEDURE AppSys() ; Return

PROCEDURE Main()

PUBLIC oConnection, oCursorolp, oCursorolpd, GetList := {}, GetOptions, oBrowse,oBrowsed,oBrowseds, i, cStatement, cStatementd, ;
cConnectString, cTableName, aSort,oDataset,bOLPD
PUBLIC cislo,bcislo,bstatementd

// 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)

SET CHARSET TO OEM // toto funguje ok


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

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

* OLP *
cStatement := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP.DBF ORDER BY C_PROC ' // OLP
* OLPD *
cStatementd := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP_D.DBF ORDER BY C_PROC ' // OLPD

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

oCursorolp := CreateSQLCursor( oConnection, 3, cStatement ) // OLP
oCursorolpd := CreateSQLCursor( oConnection, 3, cStatementd ) // OLPD

IF Valtype(oCursorolp) # 'O' .or. Valtype(oCursorolpd) # 'O' // ak OLP aj OLPD
RETURN
ENDIF

* skúška s codeblokom

****************************************
* Otvorenie databázy pomocou Cursora
****************************************
BROWCURSOR()
****************************************

* ukončenie spojenia z SQL databázoi
oConnection:destroy()

Return

******************************************************
FUNCTION BROWCURSOR()
***********************
cp:=1 // iba pomocné číslo procesu

cislo="1"
bcislo:={||alltrim(str(oCursorolp:fieldget(1)))}

bstatementd:={||cStatementd := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP_D.DBF WHERE (C_PROC='+(eval(bcislo))+') ORDER BY C_PROC '}

* temporary code bloc without relation for testing
bOLPD:={||nil}

* this is for close and reopen cursor with new statement
/*
bOLPD:={||eval(bcislo),;
oCursorolpd:Close(),;
oCursorolpd := CreateSQLCursor( oConnection, 3, cStatementd ),;
oCursorolpd:refresh()}
* oCursorolpd:GoTop(), ;
*/

*************************
* BROWSE TABUĽKA PRE OLP
*************************
DCSETPARENT oBrowse

@ 0,0 DCBROWSE oBrowse ALIAS "OLP" DATA oCursorolp ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
SCOPE ;
ITEMMARKED {||Eval(bOLPD), ;
DC_GetRefresh(GetList,, ;
DCGETREFRESH_TYPE_EXCLUDE,(GETLIST_BROWSE))}

DCSETPARENT oBrowse

* tu zobrazí ak je záznam zrušený
DCBROWSECOL DATA {|x|x:=deleted(),IIF(x,'X',' ')} ;
HEADER "Zru" WIDTH 5 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("C_PROC")) ; // ozn. registra
HEADER "C_PROC" WIDTH 10 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("DAVKA")) ; // ozn. registra
HEADER "DAVKA" WIDTH 10 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("DAT_ZAD")) ; // ozn. registra
HEADER "DAT_ZAD" WIDTH 10 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("CAS_ZAD")) ; // ozn. registra
HEADER "CAS_ZAD" WIDTH 20 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("STAV")) ; // ozn. registra
HEADER "STAV" WIDTH 5 PARENT oBrowse

DCBROWSECOL DATA SQLFieldBlock(oCursorolp,oCursorolp:FieldPos("NAZPTK")) ; // ozn. registra
HEADER "NAZPTK" WIDTH 10 PARENT oBrowse

*************************
* BROWSE TABUĽKA PRE OLPD
*************************
DCSETPARENT TO oBrowsed

@ 22,0 DCBROWSE oBrowsed ALIAS "OLPD" DATA oCursorolpd ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
SCOPE

DCSETPARENT TO oBrowsed

* tu zobrazí ak je záznam zrušený
DCBROWSECOL DATA {|x|x:=deleted(),IIF(x,'X',' ')} ;
HEADER "Zru" WIDTH 5 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("C_PROC")) ; // ozn. registra
HEADER "C_PROC" WIDTH 10 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("DAVKA")) ; // ozn. registra
HEADER "DAVKA" WIDTH 10 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("DAT_ZAD")) ; // ozn. registra
HEADER "DAT_ZAD" WIDTH 10 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("CAS_ZAD")) ; // ozn. registra
HEADER "CAS_ZAD" WIDTH 20 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("STAV")) ; // ozn. registra
HEADER "STAV" WIDTH 5 PARENT oBrowsed

DCBROWSECOL DATA SQLFieldBlock(oCursorolpd,oCursorolpd:FieldPos("NAZPTK")) ; // ozn. registra
HEADER "NAZPTK" WIDTH 10 PARENT oBrowsed

DCSETPARENT oToolbarplb

***********************
* zobrazenie toolbaru
***********************
@ 0,160 DCTOOLBAR oToolbarplb SIZE 22,70 ;
ALIGN DCGUI_ALIGN_LEFT ;
BUTTONSIZE 20,1.5 ;
SPACE 2 ;
RESIZE DCGUI_RESIZE_REPOSONLY_X ;
FIT

* refresh
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'REFRESH';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursorolp:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* jump to begin - ok
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'GOTOP';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursorolp:Gotop(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* jump to end - ok
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'GOBOTTOM';
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursorolp:Gobottom(),; // skok na začiatok
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* seek to number 3 - C_PROC=3 in OLP - ok
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'SEEK-OLP-3' ;
ACTION {|| ; // neviem čo robí, asi iba číta stavy
oCursorolp:Seek({3}),; // triedenie podľa C_PROC
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* show number C_PROC in OLP - OK
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'C_PROC-OLP';
ACTION {|| dc_msgbox({oCursorolp:fieldget(1)}), ;
x:=oCursorolp:fieldget(1) ;
}

* show number C_PROC in OLPD - OK, but in table is not at wanted position
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'C_PROC-OLPD';
ACTION {|| dc_msgbox({oCursorolpd:fieldget(1)});
}

* refresh OLPD - ok
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'REFRESH-OLP';
ACTION {|| ;
oCursorolp:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* refresh OLPD - NOT WORK,
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'REFRESH-OLPD';
ACTION {|| ;
oCursorolpd:Refresh(),; // znovunačítanie databázy, refresh datasetu
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia



* !!! HERE I WANT to show data from OLPD (oCursord) with new filtered data from cStatementd -

* refresh OLPD
DCADDBUTTON PARENT oToolbarplb ;
CAPTION 'REFRESH-OLPD3';
ACTION {|cislo|cislo:=eval(bcislo),ladenie(cislo),cstatementd:=eval(bstatementd),ladenie(cstatementd),;
oCursorolpd:close(),;
oCursorolpd := CreateSQLCursor( oConnection, 3, cStatementd ),;
oCursorolpd:refresh(),;
oCursorolpd:gotop(),;
oBrowsed:refreshall(),;
oCursorolpd:Seek(val(cislo)),;
oCursorolpd:refresh(),;
DC_GETREFRESH(GETLIST),tone(200,1) } // refresh prostredia

* tu už samotné zobrazenie BROWSERom
DCGETOPTIONS ;
AUTORESIZE ;
BUTTONALIGN DCGUI_BUTTONALIGN_CENTER

DCREAD GUI ;
FIT ;
OPTIONS GetOptions ;
BUTTONS DCGUI_BUTTON_EXIT ;
TITLE 'Browsing with SQLexpress - použitý oCursorolp' ;
EVAL {|o|SetAppWindow(o), ;
SetAppFocus(oBrowse:GetColumn(1));
}
*oBrowsed:dataSource:=oCursorolp,oBrowsed:refreshAll();
* SetAppFocus(oBrowse:GetColumn(1));

* zatvorenie cursoru a uvoľnenie pamäti
oCursorolp:Close()
oCursorolpd:Close()

RETURN NIL



******************************************************************
STATIC FUNCTION CreateSQLCursor( oConnection, nMode, cStatement )
******************************************************************

* typy kurzora, pôvodne tu v tomto príklade bol SQL_CONCUR_READ_ONLY
*Read-Only (SQL_CONCUR_READ_ONLY):
*Optimistic with Values (SQL_CONCUR_VALUES):
*Optimistic with Row Versioning (SQL_CONCUR_ROWVER):
*Scroll Locks (SQL_CONCUR_LOCK):

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
elseif nMode == 3
oCursor := SQLSelect():new(cStatement, oConnection, SQL_CONCUR_LOCK, SQL_CURSOR_DYNAMIC)
nSuccess := oCursor:execute()
endif

oConnection:displayErrors := .f.

RETURN oCursor

******************************************
FUNCTION SQLFieldBlock( oCursor, nField )
******************************************

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

******************************************
FUNCTION SQLFieldBlock2( oCursor, nField ) // ˙prava pre konverziu ANSI to OEM
******************************************

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

******************************************
FUNCTION SQLFieldBlock5( oCursor, nField ) // ˙prava pre konverziu ANSI to OEM
******************************************

Return {|x|iif(Pcount()==0, iif( !empty(oCursor:fieldGet(nField)), lat_ibm(upper( 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}

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Scope dbf with SQL Express++

#8 Post by Victorio »

edit : now I modify source where main table is with SQLselect - as cursor, and second table with dataset - offline
with this combination relation works.

BUT now I do not know why dataset in second table browser show only one record. I must study how dataset work, maybe I need use some array to read dataset items and then show with browser...

Post Reply