Getting "Date" type property from an AOP data store

[RSenior]

So this is a two part question.  Part A:  When I search for data in a data store, using the Navigator object to search for data returns the correct number of results, while using object.GetElementList does not.  I'm searching in the data store for Measurements created between a certain date (using the iStartTime property).  It seems that these should return the same results.  Please see part A of my pasted code below.  Any suggestions?  Perhaps is it related to part B of my question. 

Part B:  the iStartTime property is data type 30, or Date type.  Whenever I try to access the value of the iStartTime property I get an error saying "The object does not support this property or method".  Is there a special way to get value of a property when the property is a date?  

Any suggestions would be appreciated!

-Russ

Option Explicit  'Forces the explicit declaration of all the variables in a script.
  Dim MyElementList, oMyDataStore, StartDate, EndDate, MySearchString
  
  Dim myDataStorePlugin: myDataStorePlugin = "AOP"
	Dim myDataStoreParam: myDataStoreParam = "<user>AVL</user><pwd c=""1"" s=""1"">7D667E7B1EBFBAB6736F66010CCAB1AC8CB28FD2</pwd><server>ROCMSAPP01</server><rpc>600000000</rpc><version></version><usenewquerystyle>YES</usenewquerystyle><attributesbyqueryinterface>YES</attributesbyqueryinterface><santorinlike>YES</santorinlike><wildcards>auto</wildcards>"
	Set oMyDataStore = Navigator.ConnectDataStoreByParameter(myDataStorePlugin, myDataStoreParam)
  
  StartDate=Cdate(DateAdd("d",-3,Date)) + TimeSerial(0,0,0)
  EndDate=Cdate(DateAdd("d",0,Date)) + TimeSerial(23,59,59)
  
'------------------------------------------------------------------------------------------------------------------------------------------------
  'PROBLEM A:  THESE TWO SEARCH SHOULD BE THE SAME, YET THEY RETURN DIFFERENT RESULTS
  'This returns zero results from the search
  Set MyElementList = oMyDataStore.GetElementList("Measurement","iStartTime>=" & StartDate & "; iStartTime<=" & EndDate)
  MsgBox("GetElementList count: " & MyElementList.Count)
  
  
  'This returns 18 results
  Call navigator.Display.CurrDataStore.Browser.refreshALL
	Call Navigator.Display.CurrDataStore.QueryForm.Clear
	Call Navigator.Display.CurrDataStore.QueryForm.Conditions.Add("Measurement","iStartTime",">=",Startdate)  'Here's where I'm adding a new condition.
	Call Navigator.Display.CurrDataStore.QueryForm.Conditions.Add("Measurement","iStartTime","<=",EndDate)  'Here's where I'm adding a new condition.
	Navigator.Display.CurrDataStore.QueryForm.Conditions.Logic = "C1 AND C2"
	Navigator.Display.CurrDataStore.ResultsList.Elements.MaxCount = 10000
	Call Navigator.Display.CurrDataStore.QueryForm.Search()  'and this triggers the search based on my conditions and the datastore I've accessed. 
  MsgBox("Navigator Search Count: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Count)
  
  
  
  
  '------------------------------------------------------------------------------------------------------------------------------------
  'PROBLEM B:  iStartTime IS DATA TYPE 30 OR DATE TYPE. HOW DO I ACCESS THE VALUE?
      
  MsgBox("Id data type: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Item(1).Properties.Item("Id").DataType)   'Message: "Id data type: 3"
  MsgBox("Id value: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Item(1).Properties.Item("Id").Value)   'Message: "Id Value: 266888"
  
  MsgBox("iStartTime data type: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Item(1).Properties.Item("iStartTime").DataType)   'Message: "Id data type: 30"
  MsgBox("iStartTime value: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Item(1).Properties.Item("iStartTime").Value)   'Results in error, Object does not support this property or method

[RSenior]

I may have found a work around for part B that is tenuous at best.  My main goal is to find the element of a list that was created most recently, which is why I wanted to use the "iStartTime" property.  Theoretically, "Id" should be in chronological order as well, but I don't know for sure.  Anyway, if I load the properties of my element list into the portal, I can just find the maximum date and the corresponding Id.  Unfortunately, this requires loading data into the portal, which could affect data already there.  For now, this won't be an issue, but it's not an ideal situation.  

Here are the commands I'm using for reference.  I would still like to know if there is a way to get the "iStartTime" property directly from the store.  Also, Part A would be nice since I could access the data store object directly from the script, rather than going through the navigator object, which can be slow.  

Thanks. 

  Call Data.Root.Clear

  Call Navigator.LoadProperty("iStartTime",Navigator.Display.CurrDataStore.ResultsList.Elements)
  Call Navigator.LoadProperty("iD",Navigator.Display.CurrDataStore.ResultsList.Elements)

[AndreasK]

A short answer to PartB:

If there is a DataTypeDate property the result is an object.

Check the help for

UsiTimeDisp

This is done because of the reduced precision of VBS CDate datatype.

I just attach a short script that I use to represnt Property values in a SUD dialog.

          dim strVal
          dim propitem : set propitem = ????.Properties.Item("iStartTime")
          select case propitem.Size
            case 0 strVal = ""
            case else
              dim valI : for valI = 1 to propitem.Size
                if(valI > 3) then
                  strVal = strVal & ", ..."
                  Exit For
                end if
                if valI > 1 then
                  strVal = strVal & ", "
                end if
                if  DataTypeDate = propitem.datatype then
                  dim timedisp : set timedisp = propitem.values(valI)
                  strVal = strVal & CStr(timedisp.VariantDate)
                else
                  strVal = strVal & str(propitem.values(valI))
                end if
              Next
          end select

 So the easiest way is to use

 MsgBox("iStartTime value: " & Navigator.Display.CurrDataStore.ResultsList.Elements.Item(1).Properties.Item("iStartTime").Value.VariantDate)

 that will only work if the property is really set in the ASAM store.

P.S.: I will look into Part A

[RSenior]

I see.  So simple, just adding ".VariantDate" works like a charm.  UsiTimeDisp makes it clear as well.  So I can use "Value.year" or "Value.Day", etc.  It also makes sense now why I was getting object type with the VarType command on the "iStartTime" property value.  

Besides UsiTimeDisp, where is this in the help file?  Can I make a request to have this put into "Property: Value for Property <DataStore>" help document?  Seems like a logical place for this information.  

As usual, thanks for the help. 🙂

[AndreasK]

Part A:

You can use the Datastore without using the Navigator GUI. See the attached code. So you do not have to use GetElementList

Option Explicit  'Forces the explicit declaration of all the variables in a script.
  Dim MyElementList, oMyDataStore, StartDate, EndDate, MySearchString
  
  Dim myDataStorePlugin: myDataStorePlugin = "AOP"
	Dim myDataStoreParam: myDataStoreParam = "<user>AVL</user><pwd c=""1"" s=""1"">7D667E7B1EBFBAB6736F66010CCAB1AC8CB28FD2</pwd><server>ROCMSAPP01</server><rpc>600000000</rpc><version></version><usenewquerystyle>YES</usenewquerystyle><attributesbyqueryinterface>YES</attributesbyqueryinterface><santorinlike>YES</santorinlike><wildcards>auto</wildcards>"
	Set oMyDataStore = Navigator.ConnectDataStoreByParameter(myDataStorePlugin, myDataStoreParam)
  
  StartDate=Cdate(DateAdd("d",-3,Date)) + TimeSerial(0,0,0)
  EndDate=Cdate(DateAdd("d",0,Date)) + TimeSerial(23,59,59)
  MsgBox StartDate & " " & EndDate
  
'------------------------------------------------------------------------------------------------------------------------------------------------
  'PROBLEM A:  THESE TWO SEARCH SHOULD BE THE SAME, YET THEY RETURN DIFFERENT RESULTS
  'This returns zero results from the search
  Set MyElementList = oMyDataStore.GetElementList("Measurement","measurement_begin>=" & StartDate & "; measurement_begin<=" & EndDate)
  MsgBox("GetElementList count: " & MyElementList.Count)
  
  'This returns 18 results
  dim query : set query = oMyDataStore.CreateQuery
  Call query.Conditions.Add("Measurement","measurement_begin",">=",Startdate)  'Here's where I'm adding a new condition.
	Call query.Conditions.Add("Measurement","measurement_begin","<=",EndDate)  'Here's where I'm adding a new condition.
	query.Conditions.Logic = "C1 AND C2"
  oMyDataStore.Search(query) 'and this triggers the search based on my conditions and the datastore I've accessed. 
  MsgBox("Navigator Search Count: " & oMyDataStore.Results.Count)
  
  '------------------------------------------------------------------------------------------------------------------------------------
  'PROBLEM B:  measurement_begin IS DATA TYPE 30 OR DATE TYPE. HOW DO I ACCESS THE VALUE?
      
  MsgBox("Id data type: " & oMyDataStore.Results.Item(1).Properties.Item("Id").DataType)   'Message: "Id data type: 3"
  MsgBox("Id value: " & oMyDataStore.Results.Item(1).Properties.Item("Id").Value)   'Message: "Id Value: 266888"
  
  MsgBox("measurement_begin data type: " & oMyDataStore.Results.Item(1).Properties.Item("measurement_begin").DataType)   'Message: "Id data type: 30"
  MsgBox("measurement_begin value: " & oMyDataStore.Results.Item(1).Properties.Item("measurement_begin").Value.VariantDate)   'Results in error, Object does not support this 

 Why doe GetElementList return a wrong result?

GetElementList uses the same syntax as OdsInstList. The command is introduced to allow easy migration of old scrits to new DataStore API. In a lot of scripts building the queries contains a lot of code.

The Problem that you are looking at is that in OdsInstList DT_Date is represented by ASAM time.

While your code generates

"iStartTime>=" & StartDate & "; iStartTime<=" & EndDate  

--->

"iStartTime>=01.12.2012 10:33; iStartTime<=02.12.2012 10:33"

it has to be

"iStartTime>=20121201033; iStartTime<=201212021033"

I would assume using the above code to avoid error prone string creation.

[RSenior]

I see what you are saying.  The problem is that "iStartTime" and "measurement_begin" refer to the same property ("measurement_begin" is listed as a "Base Name" of "iStartTime").  So each one returns an object if you use "Property.Value" property.  I tried your code using "measurement_begin" in the GetElementList function and got the same result as using "iStartTime".

It seems that the Navigator object recognizes the iStartTime data type and converts it accordingly, so we can search it.  However, it seems the GetElementList command does not.  How do I build the query in GetElementList so that that "iStartTime" or "measurement_begin" are returned with an actual number so GetElementList can query it? 

[AndreasK · Accepted solution]

Sorry I replaced iStartTime by measurement_begin to make it run on my local database 🙂

The point is that the syntax for GetElementList is the following one

"iStartTime>=20121201033; iStartTime<=201212021033"

while converting a CDate to str it result in locale dependent string.

But the suggestion would be use the

dim query : set query = oMyDataStore.CreateQuery

 instead of the string query. Because it avoid those conversion errors. Be aware that even a double value put to a string in e.g. germany will convert to a comma written number while the stringquery expects point notation.

Set MyElementList = oMyDataStore.GetElementList("Measurement","measurement_begin>=" & RTT(StartDate,"#YYYYMMDDhhnnss") & "; measurement_begin<=" & RTT(EndDate,"#YYYYMMDDhhnnss"))

 should deliver correct results.

[RSenior]

I see now.  I was looking at the left side of the equation.  But, it still doesn't work.  I get this error "The text iStartdate>=2012128000000; iStartDate<=20121112235959 is not a valid value for the argument QueryExpression of the method GetElementList".  I messed around with the string format, but couldn't get it.  It might not be a real ASAM date as you mentioned...  

I used your first suggestion and it works great.  It's a few more lines of code, but it's better than going through the Navigator.  I'll just use that instead of GetElementList.  Thanks for the suggestion, you've been very helpful. 🙂

'My code for reference. 

dim query : set query=oMyDataStore.CreateQuery
Call query.Conditions.Add("Measurement","iStartTime",">=",StartDate)
Call query.Conditions.Add("Measurement","iStartTime","<=",EndDate)
Call oMyDataStore.Search(query)
MsgBox(oMyDataStore.Results.Count)

[AndreasK]

I am sorry used the wrong format string.

dim query : query = "datetime>=" & RTT(StartDate,"#yyyymmddhhnnss") & "; datetime<=" & RTT(EndDate,"#yyyymmddhhnnss")
dim MyElementList : Set MyElementList = Navigator.Display.CurrDataStore.GetDataStore.GetElementList("TestRun",query)

 Accidentially it worked on my machine because all values had number in front.

2012128000000 interpreted in asam time means that it is the 80. of december 2012. So it has to be

20121208000000. The leading zero is missing because of using "D" instead of "d".

But once again this only shows the problems with string representation. Please stay with the CreateQuery solution.

[RSenior]

How do I load data using results from create query method?  The only way I can find is Navigator.LoadData(Element).  Problem is getting the Element.  If I have a results list from the data store query method, I can seem to figure out how to load the data.  Do I have to do another search with the navigator display object? 

I tried

Navigator.LoadData(oMyDataStore.Results(1)) 

Navigator.LoadData((oMyDataStore.GetElementList("Measurement","Id = " & oMyDataStore.Results(1).Properties("Id").Value))

+ others I have deleted

These give me this error: "The different data models cannot be merged."

I also tried 

Call Navigator.LoadChannelsByName(oMyDataStore.GetElementList("Measurement","Id = " & oMyDataStore.Results(1).Properties("Id").Value).Item(1),Array("AVL_INDEP_TIME"))

But I get "An error has occurred in the LoadChannelsByName method"

Thanks in advance.