ASAM-ODS sql queries

[Lukas_Doubek]

Hello,

im using connector to oracle database via Diadem Navigator. I use it like this:

Call oQueryForm.Conditions.Add("MeaResult", 		"Name", 								"=", "Channels")

But im unable to filter all parameters of the test, which is saved in some different tables.

My question is if i got the model of the database, can i use pure SQL statements to Select and Join  the concrete data i need?

make query + fetch the data

[Brad_Turpin]

Hi Lukas,

I would not recommend that you go at an ASAM-ODS data base with atomic SQL commands.  At any rate I can't help you if you try that, other than to see if I can point you to the official ASAM-ODS table schema and wish you luck.

The "parameters of the test" that you mention, are these attributes of the aotest level of the ASAM-ODS data tree?  Or are you referring to linked ParameterSet contents?

In the query you listed in your post, what is the ASAM-ODS level being returned?  Is it aotest/aosubtest, aomeasurement, or aomeasurementquantity?

You say you're unable to "filter all parameters of the test".  Do you mean that you can't use these parameters as query conditions, like the one you showed in your post, or do you mean something else?  It is possible to traverse the ASAM-ODS data tree programmatically, starting with any returned element, such as a measurement.  We might be able to get at the parameters you care about that way.

Brad Turpin

Senior Technical Support Engineer

National Instruments

[Pesmith8]

HI Lukas, 

Sounds like  a nice idea!

I have ran SQL queries from DIAdem to  ms SQL Server and this should work to oracle as well.    When it comes to ASAM that should allow you to access the meta data that is stored in attributes/tables. 

If this is to a AVL ASAM, setup then should be able to access the meta data, but not the actual data stored in blobs.

Paul

[AndreasK]

Your first try should be to use the GUI to do your query.

If you are capable to connect to your database you can use the interactive Query form in the DataNavigator to build and check queries.
There you should be able to pick the different attributes ... in the control.

When this works out you can put the cursor in an condition and press "CTRL + SHIFT + C"

Option Explicit  'Forces the explicit declaration of all the variables in a script.

Dim oMyDataStore, oMyConditions, oMyResults
 
'--- Search with GUI
Dim oMyQueryForm
Set oMyDataStore = Navigator.Display.CurrDataStore
Set oMyQueryForm = oMyDataStore.QueryForm
Call oMyQueryForm.Clear
oMyQueryForm.Mode = eAdvancedQueryForm
oMyQueryForm.ReturnType  = "MeaResult"
oMyQueryForm.ResultsMode = eResultsModeElements
Set oMyConditions = oMyQueryForm.Conditions
 
'--- Search without GUI
'Dim oMyQuery
'Set oMyDataStore = Navigator.ConnectDataStore("FEV1")
'Set oMyQuery = oMyDataStore.CreateQuery()
'oMyQuery.ReturnType = "MeaResult"
'Set oMyConditions = oMyQuery.Conditions
 
'--- Fill the query
Call oMyConditions.Add("TestStep","TestEquipment.EngTestBedFlowMeter.AirMeter","=","rotary_meter")
Call oMyConditions.Add("MeaResult","Name","=","L1000000")
oMyConditions.Logic = "C1 AND C2"
 
 
'--- Search with GUI
oMyDataStore.ResultsList.Settings.MaxCount = 200
Call oMyQueryForm.Search()
Set oMyResults = oMyDataStore.ResultsList.ResultsElements
 
'--- Filter the Data-Browse-Tree
'oMyQueryForm.IsFilterActive = False
 
'--- Search without GUI
'Call oMyDataStore.SearchElements(oMyQuery, 200)
'Set oMyResults = oMyDataStore.ResultsElements

There is no explicit SQL like query language. The joins are added by following the relations like in object oriented thinking.

If you do not know your model/schema you can right klick into the Data Navigator Browse tree and select: "Display Data Model...".

There are two additional methods

GetElementProperties
GetElementList

with an almost undocumented textual query language.

dim insts
set insts = store.GetElementList("MeaQuantity", "measurement.Name=TL2600", false)
set insts = store.GetElementList("MeaQuantity", "Name=Time;or;Name=Speed", false)
set insts = store.GetElementList("MeaQuantity", "measurement.Name=TL2600;and;Name=Time;or;Name=Speed", false)
set insts = store.GetElementList("MeaQuantity", "measurement.Name=TL2600;and;(;Name=Time;or;Name=Speed;)", false)
set insts = store.GetElementList("MeaQuantity", "(;Name=Time;or;Name=Speed;);and;measurement.Name=TL2600", false)

where the path always start at the ReturnType which is "MeaQuantity" in the example.

[Lukas_Doubek]

thanks for replies.

I tried to use the filter and then CTRL+SHIFT+C and it works like on other places in diade, f.e. in report. good to know.

1) Im in situation i dont have the db model, so i tried ur advice and looked the db model.

But i think theres is the problem, cause in the model i can see node called "TestSequence", where is a parameter called laboratory. ok then, so i want to filter that.

2) but it the result list, i cant see the node "TestSequence". i only got 5 nodes (Project, StructureLevel, Test, TestStep, MeaResult), thats why i started this thread, cause i dont see parameters to filter the test.

But i must confess that i have experiences only with relational databases (mysql, postgre), so maybe its standard to  have only these 5 nodes, but i would like to ask how i can use the other parameters in search, as the laboratory parameter for example?

Thanks

[AndreasK]

The 5 nodes are the main nodes where an ASAM ODS database stores its measurement data.
The main hierachy of your openMDM model.

In an openMdm model the MeaResults holds relations to TestSequence, UnitUnderTest and TestEquipment.

So you can pick the element to query in the relation tree of MeaResult.

Does this work for you?

[Pesmith8]

HI Lukas,

It is nice that you are working with Andreas, he is my go to person for ASAM-ODS issues.

Just a little background that helped me, as I also came from a DB-SQL environment. Andreas will correct anything that I have misspoke on.

ASAM-ODS is a linked list inside of a Database. (That is why you can export the Data to ATFX file and it works just like it did when inside of a DB)  When the linked list is mapped to a Table, the Attributes(ODS) will map to fields(db)

So when You are looking at the data, you can traverse the tree, by following the branches from the Root Node to the other nodes.  Then you can go look at each of the attributes of that level, and some of these nodes, will have children, and others will just be attributes.

Generally you can traverse to a level that has many attributes,and this will be the most useful level for querying. 

I would do some playing around with the data in DIAdem just do some exploring taking good notes. Eventually you will need to build somekind of Model(if you can not get it from who made it.)  With ASAM-ODS the Model is a big Deal.

Once you find the level that has interesting Attributes, that level would be great for doing a query in DIAdem. to pull out useful data into data portal.

Anyway I must go now.

Paul

[AndreasK]

Adding some info:

• If a store is opened in DIAdem Navigator you can run
  

  Option Explicit
  ScriptInclude ProgramDrv & "Libr\Documents\Utils\ODS\DataStoreDialogs.vbs"
  
  call ODS_DataStoreDialogs_Content(nothing)
  ​

  to run queries on the ASAM ODS Interface to follow those linked objects.

  
  or just replace the nothing in the call by a store you opened in the script to do some debugging. The dialog is a scripting example how to use DIAdem ASAM ODS interface.
  You will find it in
  

  ProgramDrv & "Libr\Documents\Utils\ODS\DataStoreDialogs.vbs"
  ProgramDrv & "Libr\Documents\Utils\ODS\DataStoreDialogs.sud"​

• There are some additional scripts that will help you load Data from ASAM ODS databases.
  If you run 
  

  Scriptstart ProgramDrv & "Libr\Documents\Utils\ODS\ni_ods2dia\Ni_ods2dia_check.vbs"​

  to add it temporary or

  Scriptstart ProgramDrv & "Libr\Documents\Utils\ODS\ni_ods2dia\Ni_ods2dia_install.vbs"

  to add it permanently, you will get an additional dialog when importing from DIAdem Navigator when dragging a measurement from Navigator into portal.

  
  

  If you pick "Load ODS Data" you will find the related OpenMDM info in DIAdem portal

  
  

  this is also based on an example script solution delivered at

  ProgramDrv & "Libr\Documents\Utils\ODS\LoadDataHelper.vbs"

  that shows how customize load from ASAM ODS.
  The entries in the Load Dialog and context menu entry can be extended by adding own scripts in

  ProgramDrv & "Libr\Documents\Utils\ODS\ni_ods2dia\custom_import_scripts"

  that currently contains two examples