FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Postby Mulyadi » Sat May 09, 2020 3:50 pm

Please help for my case like this:

if open data with FW_OpenTRecSet(oCon, cSql) then
the data in oBrw2 is not refreshed,
but if it is open in FW_OpenRecordSet( oCon, cSql ) mode then the data in oBrw2 can be displayed.

in TRecSet mode can run if only filter command.
while in complex applications, I don't use filter mode. '
This is just a simple example of an application that uses complex queries
Code: Select all  Expand view

#include "FiveWin.ch"

function Main()

    local oDlg, oBrw1, oBrw2, ors1, ors2
    local xcode := "", xunit := "", i
    local aLocationDesc := {}, aLocationCode := {}

    aLocationCode := { "001", "002", "003", "004"}
    aLocationDesc := { "Location 001", "Location 002", "Location 003", "Location 004" }
   
    ors1 := open_rs1()
   
    if ors1:Recordcount > 0
        // TRecSet
        // xcode := ors1:Code  
        // xunit := ors1:Brand
       
        // ADO
        xcode := ors1:fields("Code"):Value
        xunit := ors1:fields("Brand"):Value
       
    Endif
    ors2 := open_rs2( xcode )
   
  DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL TITLE "ADO vs TRecSet" RESIZABLE

        @ 5, 5 XBROWSE oBrw1 SIZE 200,-20 PIXEL OF oDlg ;
        DATASOURCE ors1
        COLUMNS "CODE", "BRAND" ;
        HEADERS "CODE", "BRAND" ;
      CELL LINES NOBORDER AUTOSORT
           
      oBrw1:CreateFromCode()
      oBrw1:bChange := ;
        <||
           
            // the difference between TRecSet and ADO RecordSet :
           
            // TRecSet
            xcode := ors1:Code  
            xUnit := ors1:Brand
           
            // 1 : display data on oBrw2 with open new recordset  ---> Data in oBrw2 not displayed
            //
            ors2 := open_rs2( xcode )
            oBrw2:oRs := ors2
            oBrw2:Refresh()
           
            // filter mode:     ---> on oBrw2 with filter mode, data can be displayed
            //
            ors2:filter := ""
            ors2:filter := "CODE='" + xcode + "'"
            oBrw2:Refresh()
           

            // 2 : ADO
            // data can be displayed in the new open recordset mode or filter mode
            //
            xcode := ors1:fields("Code"):Value
            xunit := ors1:fields("Brand"):Value

            // open new recordset = FW_OpenRecordSet( oCn, cSql ) ---> Data in oBrw2 can displayed
            ors2 := open_rs2( xcode )
            oBrw2:oRs := ors2
            oBrw2:Refresh()

            // filter mode:     ---> oBrw2 displayed
            //
            ors2:filter := ""
            ors2:filter := "CODE='" + xcode + "'"
            oBrw2:Refresh()
           
            oBrw2:SetGroupHeader("DETAIL UNIT : " + xUnit, 1, 3)
               
            Return Nil
        >
   

        @ 5,215 XBROWSE oBrw2 SIZE 200,-20 PIXEL OF oDlg ;
        DATASOURCE ors2
        COLUMNS "UNIT", "LOCATION", "SERIALNO", "YEAR", "CONDITION" ;
        HEADERS "UNIT", "LOCATION / POSITION", "SERIALNO", "YEAR", "CONDITION" ;
      CELL LINES NOBORDER AUTOSORT
     
      with object oBrw2         
        :AutoFit()
        :CreateFromCode()
      End
     
      for i := 1 to 5
        if i = 2
            oBrw2:aCols[i]:nEditType := EDIT_LISTBOX
        Else
            oBrw2:aCols[i]:nEditType := EDIT_GET
        Endif
      next i
      oBrw2:aCols[2]::aEditListTxt  := aLocationDesc
      oBrw2:aCols[2]:aEditListBound := aLocationCode
      oBrw2:aCols[2]:bEditValue     := {|| ors2:fields("location"):value }
         
      oBrw2:SetGroupHeader("DETAIL UNIT : " + xUnit, 1, 3)
     

  ACTIVATE DIALOG oDlg CENTERED NOMODAL

    return nil


static Function open_rs1
  local cSQL, ores, oCon := connect_to_oracle()

    // my header table like this :
    // CODE   VARCHAR2(5 BYTE)
    // BRAND    VARCHAR2(50 BYTE)
    // FOTO   BLOB
    // AKTIF    VARCHAR2(1 BYTE)
   
    // my data like this :
    // CODE BRAND
    //----------------
    // 001  BRAND 1
    // 002  BRAND 2
    // 003  BRAND 3

    text into cSql
        select * from MACHINE_HDR order by kode
    endtext
   
// Return AdoExecute( oCon, { |oCn| FW_OpenTRecSet( oCn, cSql ) } )
Return AdoExecute( oCon, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )

static Function open_rs2( xcode )
  local cSQL, oCon := connect_to_oracle()

    // detail table like this
    // CODE         VARCHAR2(5 BYTE)
    // LOCATION VARCHAR2(5 BYTE)
    // SERIALNO VARCHAR2(50 BYTE)
    // YEAR         VARCHAR2(4 BYTE)
    // CONDITION    VARCHAR2(100 BYTE)
   
    // my data like this :
    // CODE LOCATION SERIALNO   YEAR  CONDITION
    //----------------------------------------
    // 001  001          SERIAL 111 2010    Condition 1
    // 001  001          SERIAL 22  2013    Condition 2 
    // 003  002          SERIAL 002 2014    Condition 1
    // 003  003          SERIAL 003 2015    Condition 3
   
    if ! Empty( xcode )
        text into cSql
            select * from MACHINE_DTL where kode = &1 order by kode
        endtext
        cSql := FW_AdoApplyParams( cSql, { xcode } )    
    Else
        text into cSql
            select * from MACHINE_DTL order by code
        endtext
    Endif

// Return AdoExecute( oCon, { |oCn| FW_OpenTRecSet( oCn, cSql ) } )
Return AdoExecute( oCon, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )

static Function connect_to_oracle()
    local oCon
    // etc
    // TNS and connection string to open oracle database server
    // ...
Return oCon

// the function on this forum
function AdoExecute( oCn, bAction )

   local uRet
   
   if oCn:State == 0 .or. Empty( oCn:Properties( "Current Catalog" ):Value )
      oCn:Close()
      oCn:Open()
   endif
   if oCn:State > 0
      TRY
         uRet  := Eval( bAction, oCn )
      CATCH
         FW_ShowAdoError( oCn )
      END
   else
      ? "Connection Lost. Check your connection"
   endif

return uRet
//--------------------------------------------------//

 

is this the limitation of xBrowse or is there just a filter solution ...?
all solutions are welcome.
thanks.
Regards.
Mulyadi
User avatar
Mulyadi
 
Posts: 82
Joined: Mon Apr 18, 2011 2:32 am

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Postby nageswaragunupudi » Sat May 09, 2020 8:39 pm

Field code is VarChar(5) and you are storing only 3 char value.
TRecSet pads the value to 5 chars
So,
instead of
Code: Select all  Expand view

            xcode := ors1:Code  
 

use
Code: Select all  Expand view

            xcode := Trim( ors1:Code  )
 


Because you are not fully conversant using TRecSet class, I suggest you use simple RecordSet only.

I have another important suggestion.
For master-detail tables with ADO, you better use Data Shaping.
This simplifies master-detail table views a lot.

https://docs.microsoft.com/en-us/sql/ad ... rver-ver15

You can use it with any ADO data source including Oracle.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Postby Mulyadi » Sun May 10, 2020 4:49 am

Mr Rao,
Thank you for the response.

5 bytes of code is the maximum allowed data length.
in the realization of code data between 2 and 5 bytes.

- I try to recover the data to 5 chars and still can't.

"xcode := Trim( ors1:Code )"
- try Trim () and Alltrim () as well, the recordset results cannot be displayed on oBrw2.
- oBrw2 can only display the correct data in the oBrw2:Filter command only.

please I was given a full explanation about TRecSet Class,
starting from the beginning of the formation, and the commands that can be used for that.

I am interested in using this class, where I can find it
FWH complete documentation for this TRecSet Class?

Regards

Mulyadi
User avatar
Mulyadi
 
Posts: 82
Joined: Mon Apr 18, 2011 2:32 am

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Postby nageswaragunupudi » Sun May 10, 2020 9:53 am

FWH complete documentation for this TRecSet Class?

We did not prepare documentation for this class.
Please study the source code provided.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 39 guests