Data from dbDataTable is not updated

[Anonymous]

Hello,

I moved our database to SQLITE local files and modified the Design accordingly.

Now, the data is not updated according to the behaviour before.

As example I added the code of one Database below.

<%%Icon("")> _
Design I_Driver_BBS : SPDE_M_ODBC ivComponentGroup 

'####################################################
'# Parameter
'####################################################	
	
	Parameter Rule framesize As Integer  = 250

'####################################################
'# Database 
'####################################################	
	<%%Category("ODBC")> _
	Rule SPDE_dsn As String = "SPDE_M_Pump"

	<%%Category("ODBC")> _
	Rule SPDE_Tabelle As String = "I_Driver_BBS_170316_lokal_OhneExcel_Export"
	
	<%%Category("ODBC")> _
	Rule SPDE_where As String
		SPDE_where = "framesize=" + format("%d",Me.framesize) 
	End Rule

	<%%Category("ODBC")> _
	Rule SPDE_orderBy As String
		SPDE_OrderBy = "framesize"  
	End Rule

	<%%Category("SPDE_Database")> _
	Rule shaftdiam As Number = SQLiteTable_Filtered.getfieldvalue(0,"shaftdiam")
	<%%Category("SPDE_Database")> _
	Rule shaftlength As Number = SQLiteTable_Filtered.getfieldvalue(0,"shaftlength")
	<%%Category("SPDE_Database")> _
	Rule bearingdiam As Number = SQLiteTable_Filtered.getfieldvalue(0,"bearingdiam")
	<%%Category("SPDE_Database")> _
	Rule bearinglength As Number = SQLiteTable_Filtered.getfieldvalue(0,"bearinglength")
	<%%Category("SPDE_Database")> _
	Rule corpuslength As Number = SQLiteTable_Filtered.getfieldvalue(0,"corpuslength")
	<%%Category("SPDE_Database")> _
	Rule footlength As Number = SQLiteTable_Filtered.getfieldvalue(0,"footlength")
	<%%Category("SPDE_Database")> _
	Rule footwidth As Number = SQLiteTable_Filtered.getfieldvalue(0,"footwidth")
	<%%Category("SPDE_Database")> _
	Rule footfromCenter As Number = SQLiteTable_Filtered.getfieldvalue(0,"footfromCenter")
	<%%Category("SPDE_Database")> _
	Rule footdistanceX As Number = SQLiteTable_Filtered.getfieldvalue(0,"footdistanceX")
	<%%Category("SPDE_Database")> _
	Rule footfromshaft As Number = SQLiteTable_Filtered.getfieldvalue(0,"footfromshaft")
	<%%Category("SPDE_Database")> _
	Rule footthick As Number = SQLiteTable_Filtered.getfieldvalue(0,"footthick")
	<%%Category("SPDE_Database")> _
	Rule holedimChalf As Number = SQLiteTable_Filtered.getfieldvalue(0,"holedimChalf")
	<%%Category("SPDE_Database")> _
	Rule holefromshaft As Number = SQLiteTable_Filtered.getfieldvalue(0,"holefromshaft")
	<%%Category("SPDE_Database")> _
	Rule holedistanceX As Number = SQLiteTable_Filtered.getfieldvalue(0,"holedistanceX")
	<%%Category("SPDE_Database")> _
	Rule holediam As Number = SQLiteTable_Filtered.getfieldvalue(0,"holediam")
	<%%Category("SPDE_Database")> _
	Rule jboxwidth As Number = SQLiteTable_Filtered.getfieldvalue(0,"jboxwidth")
	<%%Category("SPDE_Database")> _
	Rule jboxlength As Number = SQLiteTable_Filtered.getfieldvalue(0,"jboxlength")
	<%%Category("SPDE_Database")> _
	Rule jboxfromshaft As Number = SQLiteTable_Filtered.getfieldvalue(0,"jboxfromshaft")
	<%%Category("SPDE_Database")> _
	Rule jboxheight As Number = SQLiteTable_Filtered.getfieldvalue(0,"jboxheight")
	<%%Category("SPDE_Database")> _
	Rule coolerwidth As Number = SQLiteTable_Filtered.getfieldvalue(0,"coolerwidth")	
	<%%Category("SPDE_Database")> _
	Rule coolerheight As Number = SQLiteTable_Filtered.getfieldvalue(0,"coolerheight")		
	<%%Category("SPDE_Database")> _
	Rule jboxfromfoot As Number = SQLiteTable_Filtered.getfieldvalue(0,"jboxfromfoot")		
	<%%Category("SPDE_Database")> _
	Rule cooler As Number = SQLiteTable_Filtered.getfieldvalue(0,"cooler")		
		
Rule checkDB As Boolean = unbindSlot(SQLiteTable_All,:%%Dataset,unbindDependents := True) And _
unbindSlot(SQLiteTable_Filtered,:%%Dataset,unbindDependents := True)

Child dbConnect As :SQLITE_dbConnection
	dataProvider = :SQLite 
	connectionString = "Data Source=" + ProjectLocation() + "\Data Base\SPDE_M_Pump.db3;Version=3;"
End Child

Child SQLiteTable_All As :SQLITE_Recordset	
	tableName = SPDE_Tabelle
	Connection = dbConnect
	where = ""
	orderBy = ""
	showInModelBrowser? = checkDB
End Child
	
Child SQLiteTable_Filtered As :SQLITE_Recordset	
	tableName = SPDE_Tabelle
	Connection = dbConnect
	where = SPDE_where
	orderBy = ""
	showInModelBrowser? = checkDB
End Child

	<%%Category("ODBC")> _
Rule all_Sizes As List
	 unbindSlot(SQLiteTable_Filtered,:%%Dataset,unbindDependents := True)
	 all_Sizes = {}
	 For I = 0 To SQLiteTable_Filtered.rowCount-1 Step 1
	 	 all_Sizes = all_Sizes + {SQLiteTable_Filtered.getFieldValue(I, "Pstage")}
	 Next
	     all_Sizes = removeDuplicates(sort(all_Sizes,:Ascending))
 End Rule

End Design

in the main design I call this data by simply refering to the database and value.

 	<%%Category("Motor_anpassen"),_
 	%%Prompt("enter shaft diameter")> _
	Parameter Rule shaftdiam  As Number = I_Driver_BBS.shaftdiam

Is there anything which Needs to be done to get the filtered data updated as soon as the

"framesize" changes. This is not happening at the moment.

Any Idea ?

Thank you / Regards

Timo

[GeorgeWilliams]

I would try adding the rule flag "uncached" on some of the rules.  The rule "checkDB" might need that flag since it might not be unbound when the state of the connection string changes.

You could also use the function IsBound?() to check the state of each rule to see if its unbound right after you make the change to the frame size.  That would help you track down which rule is not getting unbound as expected.  We've seen this issue before with database calls - some rule in the dependency chain is not getting unbound which results in any change to the query not returning a new set of results.

FWIW: This is how we would do the same thing...

        Rule shaftDiam As Number = findInPlist(:shaftDiam, FrameData)
        Rule shaftlength As Number = findInPlist(:shaftlength, FrameData)
        '...
        Rule cooler As Number = findInPlist(:cooler, FrameData)

        'Get frame data for the current framesize value
        'returns pList of format - {:frameSize, 250, :shaftDiam, 50, :shaftLength, 100, ..., :cooler, 25}
        Rule FrameData As List = GetFrameDataBySize(frameSize)

        'For a given framesize, use en_GetMapValue to either get the value 
        'from the ETO map memory space(if its been queried in this session)
        'or query the database to get the value and store it in the ETO map.
        Method GetFrameDataBySize(frameSize As Number) As List
            Dim results As List = en_GetMapValue(:frameData, {frameSize}, valueFactory:= :%%GetFrameDataBySize, factoryContext:= Me)
            Return en_Where(results, ruleName:=:FrameSize, value:=frameSize)
        End Method
        
        'Called by the en_GetMapValue function above to query the database
        'using a .NET method to return an ETO pList from a database query
        Method %%GetFrameDataBySize(inputList As list) As List
            If en_EmptyList?(inputList) Then
                Return NoValue
            Else
                Dim result As List = SomeProjectName.ETO.SomeProjectNameDataHelper.GetFrameDataBySize(first(inputList))
                Return en_Select(result, transform:=:en_MakePlist)
	    End If
        End Method

The major difference being that we are using our own .NET methods to return the data.  We've not had good results using the ETO methods over the years.  So, since Entity Framework in .NET is a powerful and full featured database library, we decided some time ago to come up with a strategy to use .NET in ETO.  Our overall strategy is to query the database using a .NET call such as "SomeProjectName.ETO.SomeProjectNameDataHelper.GetFrameDataBySize()" to return results and store those results in memory (ETO map) for very quick access in the current session the next time the user creates a configuration that needs the same data.

Granted, we do have some custom functions in that code example above, but I wanted to show you another approach since our approach has worked well for us on production projects for many years now.