Going crazy to create Data structures for REST JSON objects

scott kent

Question

I am sure the answer will be wait for 9 but I can not wait I am behind on a deadline now.

I am trying to create a data structure and populate it with Data.
Probem is there is no clear way to map a query output to a Structure. Its just basically Guess and Prey.

Where can I control the mapping of a Query Output to a Structure.
Below is the stucuture Sample:

{

"name" : "AwesomeApp Decision Response Bulk Import",

"updateRule" : "always",

"fields" : {

"emailAddress" : "{{Contact.Field(C_EmailAddress)}}"

"identifierFieldName" : "emailAddress",

"syncActions" : [

0 : {

"destination" : "{{DecisionInstance(123)}}",

"action" : "setStatus",

"status" : "yes"

}

"isSyncTriggeredOnImport" : false,

"isUpdatingMultipleMatchedRecords" : false,

"uri" : "/contacts/imports/6",

"createdBy" : "DocsExample",

"createdAt" : "2014-03-06T13:59:00.6600046Z",

"updatedBy" : "DocsExample",

"updatedAt" : "2014-03-06T13:59:00.6600046Z"

}

I created 3 Tables I have fields as a One to One I have synaction as on to many.
I created a Structure that mimics the structure above.

I created my Structure with last all the attribute fields and 1 of Record type field the other of record list type syncAction.

Problem is the Output Query keeps giving me errors about data missmatch.

I assume that is because OS mapps simply by the Column ordering from my testing.
What I am worried is that I can not Populate fields in my list of Fields and my List of syncAction objects because they are one to many relationships.

I went over the Advanced Query Tutorial but it uses only simple db access it does not address well how to get data into a structure that is more complex.

I am very confused why is there no way to map query to a Structure, I would have thought you would have a build in ORM of some sore?

Or was Outsystems never intended to be used much with objects oriented in mind. I am trying like hell just to populate a simple object with a few attributes.

All I want to do is build a simple object illustrated above. It was easy to build the entity but it has ID fields that can not be used in the JSON object. So I need to use a Structure but now I can not not populate that structure with a query.
My Query
SELECT
{BULKImportQueue}.[name],
{BULKImportQueue}.[updateRule],
{BULKImportQueue}.[identifierFieldName],
{BULKImportQueue}.[isSyncTriggeredOnImport],
{BULKImportQueue}.[isUpdatingMultipleMatchedRec],
{BULKImportQueue}.[uri],
{BULKImportQueue}.[createdBy],
{BULKImportQueue}.[createAt],
{BULKImportQueue}.[updatedBy],
{BULKImportQueue}.[updatedAt],
{field}.[ContactID],
{field}.[EmailAddress],
{syncAction}.[destination],
{syncAction}.[action],
{syncAction}.[status]
FROM (({BULKImportQueue} Inner JOIN {syncAction} ON ({syncAction}.[BULKImportQueueId] = {BULKImportQueue}.[Id])) Left JOIN {field} ON ({field}.[Id] = {BULKImportQueue}.[Id]))

Im really upset I got this fare and got stuck on something so simple as mapping???? Why would you allow advanced queries and provide no mapping?

I must be missing something? To say I am upset is a mild undertatement. This tool is almost unusable with its inablity to Abstract between the Database and Objects.

Please tell me I am missing something. I was loving this tool so much until that brick wall. I am so convinced I am missing somethign because how could anyone use OS for anything remotely serious if you can not easily create Data Structures map data betwewen those structures and Entities?

Scott

ErrorCasting.jpg

11 Aug 2014

Isaac Blaise

Staff

Hi Scott,

I'm sorry but your post is confusing. What is it you are trying to accomplish, from a high level? Do you have the option of using restful services vs running an advanced query?

The Advanced query does not do any 'smart' mapping between what is selected and what is assigned to your output structure (or structures), so perhaps that is not a good alternative for you. However, if you have a json string with your data, then you can use the ardo json component that will convert it to your structure. I think Rui Barbosa wrote an example for you that already does this?

Cheers,
Isaac

11 Aug 2014

scott kent

Rui's example did not deal with anything other than a simple flat JSON file. I have this 100% Down both Clien and Server, its when there is a more complex JSON object that leads me to beleive OS does not handle storing OOP Data Structures like Classes in a Database very well?

Problem is the JSON needs to be generated from OS Database Query, or static variables.
Even If I was recieving this object from a JSON Request I could use adroJSON to save this to a Structure but, I need to create that structure manaualy and prey they match up. Even if I figured this part out, I would still run into a similar issue with now udating the database with the JSON as I am having generating the JSON from the Database.

When I create the Joins to generate the data for this JSON Structure I can not get the data to mapp properly and I am struggling to debug it as there is no visibility on how map this info from a DB query.

I need to create a REST Asynch Websevice that:
1. Recieves a List of Contacts Responds 204
2. Then Populates the JSON Data structure I posted above with data from the database.
3. Performs an HTTP Post of that Data Structure
4. Recieves a 200 Response with that same JSON data sructure returned only filled in with more field details
5. Then I perform a Second Post with my List of Contacts
6. Then I Perform a 3rd Post and Synchronize my Import with the DB

Basically I reciee Contacts, Create the Import Object using the JSON, then I perfore

I am Stuck on #2.
I have no problem Creating Simpler JSON Webservcies where the JSON is FLAT, but as soon as I want to create a more complex Class I fail to see any means to populate that Class with Information from the database?

I created a Structure that Represents the JSON I posted above.
I created 3 Tables 1 for the Import Object, one for the Fields Lists(one to one), one for the syncActions(one to many)
I created a advanced query the Joins all the data, I tried a simple query this failed.

I swore I even Matched up the Column Names of the SQL Column Outputs to the the Structure field names if all 3 relationship were flattened, Like the results of a Right Join.
(I did this through Column Order vs Order of Fields in the Structure.)

I get an error message that says my columns are not all used, which is weird because I count the number fo fields in Mystructure vs. in my Query and they are identical.

The other error I get is when I TEST My query it give me a casing Error, which is strange because most all my fields are Text the Columns it is thoughing the error seem to be the correct Type for the data, but interesting the columns it is having an issue with updating the Structure with are the ones related to fields.contactID If it was because of mismatch coumns I would have thought the error would have been for one of the last 2 or 3 Columns.

I attached My Project. I have an Edit and View page for my BULKImportQueue object.

PPSPrivacyDecision.oml

11 Aug 2014

João Rosado

Staff

Hi Scott,

The Advanced queries have an implicit mapping with the structure. Each attribute of the structure maps directly to a column from the query, in order.
Your main problem there is that you are trying to get a non-tabular structure from a query and that is not possible.

You should get the data from your query on a tabular format and then inside your action convert it into the structures you need with applicational logic.

(a bit oftopic: the "syncActions" you have something in the "[ 0 : { ... }]" format. That is not valid json)

Regards,
João Rosado

11 Aug 2014

scott kent

Yes that was a poory copied exampled of the JSON, ignor the stupid Array syntax.

The is no way do do what you are talking about wthin an Action that I can see. In order to take 3 tables and combine them into one Object you would need a Local Variable of Type of that Object.
OS does not allow Local variable to be Structures. As fare as I can see structures can only be used for the output of Advanced Queries?

So my question is how does one convert the tabular to a structure?

11 Aug 2014

scott kent

FYI It is possible to get non-tabular structure from an SQL query I do it all the time.
IF I could mapp to my Stucture feilds and access the Related Objects like I can in every single other platform, this would not be a problem.

11 Aug 2014

scott kent

Sorry I am panicing because I committed to OS as a my platform and now even If i switch to .NET and work 3 days straight I may not have a new SSL cert setup in time for me to meet my deadline for a Proof of Concept that got pushed back one week already.

11 Aug 2014

scott kent

I Have even Tried teh JSON to WSDL tool that did not help.
All it did is create the exact same Structures I already have.

I still can not populate those structures with Data from the database nor can I populate it with Variable data.
Nor do the reverse.

602-740-7994

11 Aug 2014

scott kent

{
"name": "",
"updateRule": "always",
"fields": {
    "ContactID": "{{Contact.Id}}",
    "emailAddress": "{{Contact.Field(C_EmailAddress)}}"
},
"syncActions": [
    {
      "destination": "",
      "action": "setStatus",
      "status": ""
    }
],
"identifierFieldName": "emailAddress"
}

11 Aug 2014

João Rosado

Staff

Hi again Scott,

You can declare variables of Structure types, Set the data type to "Record" and then you can select a structure as Record definition.

See if that helps.
If you want post the oml and I'll help you making the action for it.

Regards,
João Rosado

11 Aug 2014

scott kent

Let me give it a stab I feel so dumb I knew it had to be me!

OK let me eat some humble pie and try and solve this with local variables.

I Owe you a beer!!!

Scott

11 Aug 2014

Isaac Blaise

Staff

Scott,

Was this issue resolved? Can you provide more detail on the resolution if so?

18 Aug 2014

scott kent

Yes, it did.

My end solution was to basicaly:
1. Create Strucure(s) that resemble the JSON desired.
2. Create a variable of type Record and use the Structure definition.
3. Create a variable of type Record and use the Database Table(s) definition. (Multiple variable for joins...)
4. Using Assign, map the data from the Structure variable to and from the Database variable(s)

Advanced SQL queries were a dead end, using variables to create a ORM was the better approach.

19 Aug 2014

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines