Create Query:
Code Block |
---|
language | xmlruby |
---|
title | Create DataModel Query |
---|
linenumbers | true |
---|
|
<Query>#
Query:
<Create> Create:
<VendorInfo> VendorInfo:
<vendorName>Phidget</vendorName> vendorName: "Phidget"
<vendorTitle>Sensor Provider</vendorTitle> vendorTitle: </VendorInfo>
</Create>
</Query>"Sensor Provider" |
Create Query Result:
Note that VendorSysId of type Sid is automatically generated without specifying the value in Create Query Statement.
Code Block |
---|
language | xmlruby |
---|
title | Create DataModel Query Results |
---|
linenumbers | true |
---|
|
<Create#
Create(Status=: "Success">):
<VendorInfo>VendorInfo:
<VendorSysId>KNTJ5MYXAAAAUAABA7EIY2FH</VendorSysId>
VendorSysId: "KNTJ5MYXAAAAUAABA7EIY2FH":
<vendorName vendorName(Status=: "Success+Created:1:1457715589911;", Value=: "Phidget"/>)
<vendorTitle Status=vendorTitle(Status: "Success+Created:1:1457715589914;", Value=: "Sensor Provider"/>
</VendorInfo>
</Create>) |
Find Query:
Code Block |
---|
language | xmlruby |
---|
title | Find DataModel Query |
---|
linenumbers | true |
---|
|
<Query>#
Query:
<Find Find(format=: "all">):
<VendorInfo>VendorInfo:
<vendorName eq=vendorName(eq: "Phidget"/>
</VendorInfo>
</Find>
</Query>) |
Find Query Result:
Code Block |
---|
language | xmlruby |
---|
title | Find DataModel Query Result |
---|
linenumbers | true |
---|
|
<Find#
Find(Status=: "Success", Format=: "all">):
<Result>Result:
<VendorInfo QName=VendorInfo(QName: "SimpleModel.MyModels.VendorInfo">):
<VendorSysId>KNRX3TBUAAAAUAABA4LIWHP2</VendorSysId>
VendorSysId: "KNRX3TBUAAAAUAABA4LIWHP2"
<vendorTitle vendorTitle(Value=: "Sensor Company", Known=: "Sensor Company", Version=: "1", Timestamp=: "1457663102018", DateTime=: "2016-03-10 18:25:02.018", QName=: "SimpleModel.MyModels.VendorInfo.vendorTitle", FName=: "vendorTitle"/>)
<vendorName Value=vendorName(Value: "Phidget", Known=: "Phidget", Version=: "1", Timestamp=: "1457663102004", DateTime=: "2016-03-10 18:25:02.004", QName=: "SimpleModel.MyModels.VendorInfo.vendorName", FName="vendorName"/>
</VendorInfo>
</Result>
</Find> |
Update Query:
Code Block |
---|
language | xml |
---|
title | Update DataModel Query |
---|
linenumbers | true |
---|
|
<Query>
<Find format="version">
<VendorInfo>
<vendorName eq="Phidget"/>
</VendorInfo>
</Find>
<SetResponseData>
<Key>Message: "vendorName") |
Example of Find Filter on Attributes of type Datetime
Code Block |
---|
language | ruby |
---|
title | Datamodel definiton |
---|
linenumbers | true |
---|
|
#
DataModel(Name: "MyTasks"):
Sid(Name: "TaskID")
String(Name: "TaskName")
String(Name: "TaskDesc")
DateTime(Name: "TaskTime", Format: "$SimpleDateFormat(yyyy-MM-dd'T'HH:mm:ss'Z')")
# TaskTime is a DateTime of SimpleDateFormat
Integer(Name: "TaskStatus")
Integer(Name: "RemindBeforeXMinutes") |
Datetime with simple Greater Than filter
Code Block |
---|
language | ruby |
---|
title | Datetime filter |
---|
linenumbers | true |
---|
|
#
Query:
Find:
MyTasks:
TaskID(ne: "")
TaskTime(gt: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ'):]") # Get the time in SimpleDateFormat |
Datetime with Range-based filters
Code Block |
---|
language | ruby |
---|
title | Datetime filter |
---|
linenumbers | true |
---|
|
#
Query:
Find(format: "Version"):
MyTasks:
TaskID(ne: "")
TaskTime(ge: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ', +PT15M):]")
TaskTime(lt: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ', +PT6M):]")
RemindBeforeXMinutes(le: "15")
RemindBeforeXMinutes(gt: "0") |
Update Query:
Code Block |
---|
language | ruby |
---|
title | Update DataModel Query |
---|
linenumbers | true |
---|
|
#
Query:
Find(format: "version"):
VendorInfo:
vendorName(eq: "Phidget")
SetResponseData:
Key: Message.Value.Find.Result.VendorInfo.vendorName</Key>vendorName
<Value>Phidget Inc</Value>
</SetResponseData>
<Update>Value: "Phidget Inc"
Update:
From: Result
<From>Result</From> Include: <Include>$Response$findResponse.Message.Value.Find</Include>
</Update>
</Query>Find |
Update Query Result:
Note that the Version number is incremented automatically if the update is successful.
Code Block |
---|
language | xmlruby |
---|
title | Update DataModel Query |
---|
linenumbers | true |
---|
|
<Find#
Find(Status=: "Success", Format=: "version">):
<Result>Result:
<VendorInfo>VendorInfo:
<VendorSysId>KNTMS6F7AAAAUAABA7SVACBP</VendorSysId>
VendorSysId: "KNTMS6F7AAAAUAABA7SVACBP"
<vendorTitle vendorTitle(Value=: "Sensor Provider", Version=: "1"/>)
<vendorName>Phidget Inc</vendorName>
</VendorInfo>
</Result>
</Find>
<Update Status="Success" Format="version">
<VendorInfo>vendorName: "Phidget Inc"
Update(Status: "Success", Format: "version"):
VendorInfo:
<VendorSysId>KNTMS6F7AAAAUAABA7SVACBP</VendorSysId>
VendorSysId: "KNTMS6F7AAAAUAABA7SVACBP"
<vendorName vendorName(Status=: "Success=Updated:2:1457718402256;", Value=: "Phidget Inc", Version=: "2"/>)
<vendorTitle Status=vendorTitle(Status: "Success_NoAction:1:1457718393024;", Value=: "Sensor Provider", Version=: "1"/>
</VendorInfo>
</Update>) |
Delete Query:
Code Block |
---|
language | xmlruby |
---|
title | Delete DataModel Query |
---|
linenumbers | true |
---|
|
<Query>#
Query:
<DeleteAll> DeleteAll:
<VendorInfo> VendorInfo:
<vendorName vendorName(eq=: "Phidget"/>
</VendorInfo>
</DeleteAll>
</Query> |
...
DataModel with one Complex type.
Code Block |
---|
language | xmlruby |
---|
title | DataModel with one Complex Type |
---|
linenumbers | true |
---|
|
<Def#
Def(Name=: "PostalAddress">):
<String String(Name=: "Street"/>)
<String String(Name=: "City"/>)
<String String(Name=: "Zipcode"/>
</Def>
<DataModel Name=)
DataModel(Name: "VendorInfo">):
<SidSid name=: "VendorSysId"/>)
<String name=String(Name: "vendorName"/>)
<String name=String(Name: "vendorTitle"/>)
<PostalAddressPostalAddress name=: "VendorAddress"/>
</DataModel>) |
DataModel with one Attribute that is of Array defined via Cardinality.
Save Query Results to a CSV
In some cases the user may want to save the query results to a CSV file. This can be achieved easily using 3 step process:
- Find Data
- Convert result to comma separated lines
- Save the content
- View the content
For example: Let's say you have a DataModel VendorInfo as defined below.
Code Block |
---|
language | ruby |
---|
title | Sample project |
---|
linenumbers | true |
---|
collapse | true |
---|
|
#
Namespace(Name: "SimpleModel"):
Domain(Name: "MyModels"):
DataModel(Name: "VendorInfo"):
Sid name: "VendorSysId")
String(Name: "vName")
String(Name: "vTitle")
String(Name: "vLocation") |
Generic Macro to Save File:
You can make is as part of your project.
Note the content has to be part of ProcessData.
Code Block |
---|
language | ruby |
---|
title | Generic SaveFile Macro |
---|
linenumbers | true |
---|
collapse | true |
---|
|
#
Macro(Name: "SaveFile"):
Argument:
FilePath: ""
Result:
JexlScript:
var file = "[:$Macro.Argument.FilePath:]";
var data = sffContext.getProcessData(["toCSV","result"]);
sffLog.info("Data", + data);
sff:saveTxt("text", file, data);
return null; |
Code Block |
---|
language | ruby |
---|
title | FindConvertAndSave |
---|
linenumbers | true |
---|
collapse | true |
---|
|
#
Query:
Find:
VendorInfo:
vName(ne: "")
SetProcessData(key: "toCSV.result", value: "Name,Title,Location[:EOL:]")
foreach(value: $vendor, in: $findResponse.Message.Value.Find.Result.VendorInfo):
SetProcessData key: "toCSV.result", Value: "[:$ProcessData.toCSV.result:][:$TL.vendor.vName:],[:$TL.vendor.vTitle:],[:$TL.vendor.vLocation:][:EOL:]")
SaveFile:
FilePath: "./resources/querydata.csv" |
Code Block |
---|
language | ruby |
---|
title | Save Query Output to a File |
---|
linenumbers | true |
---|
collapse | true |
---|
|
#
Query:
Find:
VendorInfo:
vName(ne: "")
SetProcessData key: "toCSV.result", value: "Name,Title,Location[:EOL:]")
foreach(value: $vendor, in: $findResponse.Message.Value.Find.Result.VendorInfo):
SetProcessData key: "toCSV.result", Value: "[:$ProcessData.toCSV.result:][:$TL.vendor.vName:],[:$TL.vendor.vTitle:],[:$TL.vendor.vLocation:][:EOL:]")
SetLocalData key: "FILE", value: "#o'resources/querydata.csv'#")
logInfo = [:[:$TL.FILE:][:$ProcessData.toCSV.result:]:] |
Finally to View the file:
Code Block |
---|
language | xml |
---|
title | View OR Download File |
---|
linenumbers | true |
---|
collapse | true |
---|
|
http://<ip>:<port>/fid-tqlengineres/<nameoffile> |