Database Mashables Responses

Responses for database mashable operations may be a single return value, a result set or a combination of parameters, return value, and result sets. In many cases, MashZone NextGen determines the structure of the response when you register a database mashable.

The different structures and names that can occur in database responses include:

Well-Known Result Sets: this response structure is used for all custom finders and all built-in finders, with the exception of the built-in selecttable-name finder. The complete structure of the result set is well defined when the mashable is registered.

{ "sid": database-service-name
"oid: finder-name
...
"response": {"records": {
"record": [
{ "mashable-column-name": columln-value,
"mashable-column-name": columln-value,
...},
{ //objects for each row in result set}, ... },
...
]
}
}
}

records contains a property named record that is an array of row objects.

Each row object contains properties for each column in the result set.

Table, view and column names are from the database mashable, not from the database.

For examples of responses with well-known results sets, see the findtable-nameAll finder in the Default Operations Available for Database Mashables topic.

Dynamic Result Sets: this may be the entire response for a finder, such as the built-in selecttable-name finder, or one result set returned along with parameters or a return value from a stored procedure. With dynamic result sets, MashZone NextGen does not know what the result set structure is when you register the database mashables.

Result sets for stored procedures are commonly dynamic. For the selecttable-name finder, a parameter dynamically defines the columns for the result set.

{ "sid": database-service-name
"oid: finder-name
...
"response": {"rows": [
{ "sql-column-name": columln-value,
"sql-column-name": columln-value,
...}
{ //objects for each row in result set}, ... },
...
]
}
}
}

The result set is enclosed in an object named rows which contains an array. The array contains objects for each row and those objects have properties for each column in the result set. Column names are from the database – not from the database mashable.

See the sample response for selecttable-name finder in the Default Operations Available for Database Mashables topic for an actual example.

REF CURSORs: are available only for Oracle databases. REF CURSORs may be the entire response for a packaged function or they can be one or more parameters returned from a stored procedure.

REF CURSORs are treated exactly like dynamic result sets. The REF CURSOR is enclosed in an object named rows which contains an array. The array contains objects for each row and those objects have properties for each column in the records for the REF CURSOR. Column names are from the database – not from the database mashable.

INOUT or OUT Parameters: stored procedures may also return parameters with simple values if they are INOUT or OUT parameters. Currently for Oracle databases only, OUT parameters may also be REF CURSORs.

Parameters are returned in the response in a property with the same name. See Stored Procedure Examples for examples.

Return Values: from stored procedures are simple integers. They are returned in the response as a property named returnValue. See Stored Procedure Examples for an example.

Unknown Return Type: this return type happens with stored procedures when JDBC metadata defines the return type as unknown. These are handled very much like dynamic result sets, but the structure of the response includes a DynaBean element. See Stored Procedure Examples for an example.

The following example shows a dynamic response with a return value, two parameters with simple values and a result set:

{ "sid":"someDBSvc",
"oid":"executeSomeProcedure",
...
"response": {
'returnValue':1,
'OUT_PARAM':'abc',
'INOUT_PARAM':'def',
'rows':[
{ 'ID':15, 'PART_NO':'ABC123', 'QTY':0 },
{ 'ID':167, 'PART_NO':'ABC456', 'QTY':120 },
{ 'ID':201, 'PART_NO':'ABC789', 'QTY':34 }
]
}
}

The next example shows a response from an Oracle package function with a REF CURSOR return type:

{ "sid":"someDBSvc",
"oid":"executePkgSomeFunction",
...
"response": {
'rows':[
{ 'ID':15, 'PART_NO':'ABC123', 'QTY':0 },
{ 'ID':167, 'PART_NO':'ABC456', 'QTY':120 },
{ 'ID':201, 'PART_NO':'ABC789', 'QTY':34 }
]
}
}

The next example shows REF CURSORS returned from an Oracle procedure in OUT parameters:

{ "sid":"someDBSvc",
"oid":"executeAnotherProcedure",
...
"response": {
'OUT_PARAM1':{
'rows': [{
'ITEM1':'value 1',
'ITEM2':'value 2',
'ITEM3':'value 3'
}]
},
'OUT_PARAM2':{
'rows': [{
'ITEM7':'value 7',
'ITEM8':'value 8',
'ITEM9':'value 9'
}]
}
}
}

The final example shows a response for a stored procedure with an unknown return type. It actually returns both a return value and a result set:

{ "sid":"someDBSvc",
"oid":"executeSomeProcedure",
...
"response": {
'DynaBean': {
'returnValue': 'value 0',
'rows':[ {
'ITEM1':'value 1',
'ITEM2':'value 2',
'ITEM3':'value 3'
} ]
}
}
}