browse two tables in relation with SQL

[Victorio]

Hi,
I still work on relation between two tables (DBF Visual Foxpro) opened with VFP ODBC driver and SQL express.

Actually status :
On screen I have two browsers (with DCBROWSE),
when move to some row on first table record move to same identifier on second table, this works with my created code block here :
bOLPD:={|pomcislo1,pomcislo2|;
pomcislo1:={oCursorolp:fieldget(1)},; // ako číslo
pomcislo2:=alltrim(str(oCursorolp:fieldget(1))),; // ako text
cStatementpd := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP_D.DBF WHERE C_PROC='+
(pomcislo2)+' ORDER BY C_PROC ',;
oCursorolpd:close(),;
oCursorolpd := CreateSQLCursor( oConnectionpd, 4, cStatementpd ),;
oBrowsed:refreshall(),;
oCursorolpd:GoTop(),;
DC_GETREFRESH(GETLIST),tone(200,1) ; // refresh prostredia
}

In DCBROWSE for first table is used here
@ 0,0 DCBROWSE oBrowse ALIAS "OLP" DATA oCursorolp ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
ITEMMARKED {||Eval(bOLPD), ;
DC_GetRefresh(GetList,, ;
DCGETREFRESH_TYPE_EXCLUDE,(GETLIST_BROWSE))}

Problem is , that in second table show only one record (for one record in table 1 exist 0,1,2, or n record in table 2 )
No refreshall, gotop etc works, only one record show.
when move in table 2 with ocursorolp:skip(1), skip record and show again only one record.
when try reccount() in table 2, show correct numbers of records.

I do not why ???

On pictures is what show after positioning in table 1, and at pict2 after skip(1).

Please, some idea to solve it ??? many thanks ...

Viktor

[rdonnay]

You are asking a lot of questions about SQLexpress.

Have you tried to get support from Boris Borzics' support forum?

[Victorio]

I am sorry, I understand, this is eXpress forum
But I mean, this is some problem with browsing and views content of dataset.
Because when I try show content of dataset oCursorolp with FOR NEXT manualy with dc_msgbox, record show good, in dataset is as in two dimensional array.
I have problem with DCBROWSE, when two browsers on one screen opened, and in one close data source and reopen it, then not show correct data.
May be similar when DCBROWSE DATA aData,
when aData is array
and I destroy array and create new.

But I can write also to Boris Borzics' support forum, but he sometime said me , he not use express , then do not know why my program not work correctly.

[rdonnay]

But I can write also to Boris Borzics' support forum, but he sometime said me , he not use express , then do not know why my program not work correctly.

I would need to create a test program to help you and this can consume a lot of time, especially since I would have to arrange to get SQLexpress from Boris. I only have a very old 1.9 version.

[digitsoft]

Hello Roger
That Problem is already reported to me and the solution is in the eXpress code.
The problem is of eXpress not SQLExpress that if the Cursor is Created the RefreshAll does not update the DataSource, I attach the Code of how I solved the problem.

This code has fixed several problems that had the dcbrowse with sqlexpress

SQLExpress is an Excellent Tool.




METHOD DC_XbpBrowse:RefreshAll( cDataSource )

LOCAL nRecno := 0, lThumbLock

// Nolberto Paulino 03-02-2016
if !EMPT(cDataSource)
::dataSource := cDataSource
END if

IF !::dataEnabled .or. ::Status <> XBP_STAT_CREATE
RETURN self
ENDIF

if ::dataSourceType = BROWSE_DATABASE // J. Duijf 27-11-2002
IF Empty((::dataSource)->(dbScope(1))) .AND. !Empty((::dataSource)->(DC_SetScope(0)))
nRecno := (::dataSource)->(Recno()) // Save current record
(::dataSource)->(DC_DbSkip()) // Next
(::dataSource)->(DC_DbSkip(-1)) // Previous
if (::dataSource)->(Bof()) .or. (::dataSource)->(Eof()) .OR. ;
(::dataSource)->(RecNo()) != nRecno // Current record NOT within scope, or deleted
(::dataSource)->(DC_DbGoTop()) // To first record
::GoTop() // Same goes for xbpBrowse
endif
ENDIF
elseif ::dataSourceType = BROWSE_DATAOBJECT // J. Duijf 27-11-2002
nRecno := ::dataSource:Recno() // J. Duijf 27-11-2002
::dataSource:DbSkip() // J. Duijf 27-11-2002
::dataSource:DbSkip(-1) // J. Duijf 27-11-2002
if ::dataSource:Bof() .or. ::dataSource:Eof() .or. ; // J. Duijf 27-11-2002
::datasource:Recno() != nRecno // J. Duijf 27-11-2002
// ::dataSource:DbGoTop() // J. Duijf 27-11-2002 ::Gotop() does same
::GoTop() // J. Duijf 27-11-2002
endif
elseif ::dataSourceType = BROWSE_ARRAY // J. Duijf 26-03-2011 Force initialisation of nPointer
::arrayElement := Min(::arrayElement,Len(::dataSource)) // J. Duijf 19-04-2015 Keep ::arrayElement in range
If Len(::dataSource) > 0 .and. ::arrayElement = 0 // J. Duijf 19-04-2015 Set nPointer to 1 if data availeble
::arrayElement := 1
Endif
IIF(Valtype(::pointerBlock)=='B',Eval(::pointerBlock,::arrayElement),nil)
endif

IF Valtype(::thumbRecords) == 'B' .AND. Valtype(::dataSource) = 'C'
lThumbLock := Eval(::thumbRecords)
IF ::isScope .AND. DC_ScopeExpress()
IF lThumbLock
::posBlock := {||50}
::lastPosBlock := {||100}
ELSE
::posBlock := {| |(::dataSource)->(DC_KeyNo32k()) }
::lastPosBlock := {| |(::dataSource)->(DC_KeyCount32k())}
ENDIF
ELSE
IF lThumbLock
::posBlock := {||50}
ELSE
::PosBlock := {||(::dataSource)->(DC_DbPosition(,::descending))}
ENDIF
ENDIF
ENDIF

Return ::xbpBrowse:RefreshAll()

But I can write also to Boris Borzics' support forum, but he sometime said me , he not use express , then do not know why my program not work correctly.

I would need to create a test program to help you and this can consume a lot of time, especially since I would have to arrange to get SQLexpress from Boris. I only have a very old 1.9 version.

[Auge_Ohr]

hi

I am sorry, I understand, this is eXpress forum
you Problem is : you still think in Xbase++ not SQL

a SQL Statment can include fields "from other Table" so there is no "SET RELATION" like in Xbase++ PgDBE ISAM Style Emulation.
also a row in a Resultset "might" have empty fields ("from other Table") so a Query must "exclude" them.

this type of SQL Question you have to learn from Boris or other SQL Professional.
i use PgAdmin.EXE Workbench to "test" my Query until it work and than copy/paste it into my Source

But I can write also to Boris Borzics' support forum, but he sometime said me , he not use express , then do not know why my program not work correctly.

it is the same with me while i (still) not use Express++ so i don't understand "your Code".

[Victorio]

"I would need to create a test program to help you and this can consume a lot of time, especially since I would have to arrange to get SQLexpress from Boris. I only have a very old 1.9 version."...

Roger, it is ok, I only ask, if you or somebody know this problem and have quick solution, no long task for you.

digisoft:
I must try it, it is important for me, thanks for info

jimmy:
you are right, I will try write to Boris forum, but several days I have some long time delays to refresh his page,
several months I worked on sql browser but with SQL 2000 and 2008 server, now I work with ODBC Visual Foxpro, and here is not full supported parameters as other driver, for example "AS" not work.

thanks all.

[Victorio]

Hi everybody !,

I found solution in Roger samples (xdemo), which are very very valuable help , thanks Roger !.

I test open two tables in two own browsers , as in exampel one to many, and this works fine. I am only at begin, but scope records work as I want. My inspiration is too some post here about open browsers in threads.

First browser for Table 1 is :
@ 0,0 DCBROWSE oBrowse ALIAS "OLP" DATA oCursorolp ;
SIZE 150,15 ;
PRESENTATION DC_BrowPres() ;
CURSORMODE XBPBRW_CURSOR_ROW ;
ITEMMARKED {||Eval(bOLPD), ;
DC_GetRefresh(GetList,, ;
DCGETREFRESH_TYPE_EXCLUDE,(GETLIST_BROWSE))}

where call bOLPD :

bOLPD:={|pomcislo1,pomcislo2|;
pomcislo1:={oCursorolp:fieldget(1)},; // ako číslo
pomcislo2:=alltrim(str(oCursorolp:fieldget(1))),; // ako text
cStatementpd := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP_D.DBF WHERE C_PROC='+(pomcislo2)+' ORDER BY C_PROC ',;
oCursorolpd:close(),;
oCursorolpd := CreateSQLCursor( oConnectionpd, 4, cStatementpd ),;
oCursorolpd:GoTop(),;
BROWSEOLPD(oDlg:drawingArea,Getlist), ;
DC_GETREFRESH(GETLIST) ; // refresh prostredia
}

and BROWSEOLPD is function with browser for table 2 :

FUNCTION BROWSEOLPD(oBrowsed,Getlist)

DCSETPARENT TO oDlg // kukuk

DCGETOPTIONS HIDE

oDlg:hide()

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

...DCBROWSECOL...

DCREAD GUI ;
PARENT oBrowse ; // kukuk
OPTIONS GetOptions ;
TITLE 'Browsing with SQLexpress - použitý oCursorolpd' ;
EVAL {|o|SetAppWindow(o), ;
SetAppFocus(oBrowse:GetColumn(1)),;
oDlg:show();
}

This is only short list from my test program.

Here is result :

[rdonnay]

I found solution in Roger samples (xdemo), which are very very valuable help , thanks Roger !

.

That is good news. That code was written over 15 years ago.
I'm glad that it could help you today.

[Victorio]

Some news, I received answer from Boris Borzic , relation between tables with SQL express is easier, than I think !.

Only this need to join tables :
* table 1 statement
cStatementp := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP.DBF ORDER BY C_PROC' // OLP
* table 2 statement, where ? is for variable parameter
cStatementpd := 'SELECT C_PROC,DAVKA,DAT_ZAD,CAS_ZAD,STAV,NAZPTK FROM RAUKN_OLP_D.DBF WHERE C_PROC=?' // OLPD
* create dataset for table 1
oCursorolp := SQLDataSet():new(cStatementp,oConnectionp,,,1000)
* create dataset for table 2, where are code block for join between datasets C_PROC in table 1 = C_PROC in table 2
oCursorolpd := SQLDataSet():new(cStatementpd,oConnectionp,{||{oCursorolp:FieldGet('C_PROC')}},{'C_PROC'})

oCursorolp:DelEmptyColumns()

* and final set relations
oCursorolp:Setrelation(oCursorolpd)

Works fine, no other changes need, tables are join and can listing in both.