Page 1 of 1

FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Posted: Sat May 09, 2020 3:50 pm
by Mulyadi
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

#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

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Posted: Sat May 09, 2020 8:39 pm
by nageswaragunupudi
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

            xcode := ors1:Code  
 
use

Code: Select all

            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.

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Posted: Sun May 10, 2020 4:49 am
by Mulyadi
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

Re: FWH 20.04 Help for xbrowser TRecSet vs ADO Recordset

Posted: Sun May 10, 2020 9:53 am
by nageswaragunupudi
FWH complete documentation for this TRecSet Class?
We did not prepare documentation for this class.
Please study the source code provided.