Mashables : Types of Mashable Information Sources : Database Mashables : Default Operations Available for Database Mashables
Default Operations Available for Database Mashables
Presto creates the following operations in database mashables by default. With custom database mashables, Presto adminsitrators can remove any of these default operations when they register database assets in Presto. With simple database mashables, all default operations are generated.
Note:  
Presto administrators can also disable or change the default availability for specific operations in Presto Server configuration.
*Data Altering Operations: deletetable-name, inserttable-name and updatetable-name
Operations that directly alter data are enabled by default for tables that are included in database mashables.
*Finders: findtable-nameAll, findtable-nameByPrimaryKey, findtable-nameWhere, findtable-nameByWhereClause, findtable-nameWherecolumn-nameEquals and selecttable-name.
The finders selecttable-name, findtable-name Where and findtable-nameByWhereClause allow you to define arbitrary SQL queries. You can also define custom finders. See Arbitrary SQL Queries for Database Mashables for more information.
If tables or stored procedures support overloaded operations, second and subsequent variations generate a mashable operation with the same name plus a unique index number. For example, findEmployeeByPrimaryKey and findEmployeeByPrimaryKey0.
Note:  
Sample requests and responses in this topic are based on a database table with these names:
*Database table name = EMPLOYEES
*Mashable table name = Employees
*Mashable name = EmployeeTable
deletetable-name
Deletes one record from a database table based on a primary key.
Input Parameters
primaryKey
Return Value
none
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"deleteEmployees",
"header":{ "resultFormat":"json" },
"params": [ {"primaryKey": 205} ]
}
findtable-nameAll
Returns all records from this table. If this operation is called within a mashup that has previously set a maximum number of rows to return, the result set is limited to that maximum.
Input Parameters
None
Result Set
table-name_Array object with an array of all allowed records.
Sample Response
{ "version":"1.1",
"sid":"EmployeeTable",
"appId":"",
"oid":"findEmployeesAll",
"svcVersion":"0.1",
"header":{ "map":{ "serviceHeader":{ "map":{ } } } },
"error":null,
"errorCode":"",
"invId":"",
"response":"{ "Employees_Array": {
"Employees":[
{ "departmentId":90,
"hireDate":{"time":5.509116e11}
"email":"SKING",
"employeeId":100,
"jobId":"AD_PRES",
"phoneNumber":"515.123.4567",
"firstName":"Steven",
"lastName":"King"
},
{ "departmentId":90,
"hireDate":{"time":6.223644e11}
"email":"NKOCHHAR",
"employeeId":101,
"jobId":"AD_VP",
"phoneNumber":"515.123.4568",
"firstName":"Neena",
"lastName":"Kochhar"
},
...
] }
}"
}
findtable-nameByPrimaryKey
Returns one record or none that matches the specified primary key.
Input Parameters
primaryKey
Result Set
Object with matching record.
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"findEmployeesByPrimaryKey",
"header":{ "resultFormat":"json" },
"params": [ {"primaryKey": 205} ]
}
Sample Response
{ "version":"1.1"
"sid":"EmployeeTable",
"appId":"",
"oid":"findEmployeesByPrimaryKey",
"svcVersion":"0.1",
"header":{ "map":{ "serviceHeader":{ "map":{ } } } },
"error":null,
"errorCode":"",
"invId":"",
"response":{
"departmentId":50,
"hireDate":{"time":6.223644e11}
"email":"MWEISS",
"employeeId":120,
"jobId":"ST_MAN",
"phoneNumber":"650.123.1234",
"firstName":"Matthew",
"lastName":"Weiss" }
}
findtable-nameByWhereClause
Returns records that match the WHERE clause specified as a parameter. Dynamic parameters must be used in the WHERE clause with values specified in the second parameter as a comma-separated string.
This is an optional finder that is generated by default for tables and views. See Arbitrary SQL Queries for Database Mashables for more information on the implications of using this finder.
Input Parameters
*whereClause as a string with SQL code using ? for dynamic parameters.
*params as a string with a list of values, separated by commas, to substitute for each dynamic parameter. Values are listed in the order they should be placed in the WHERE clause.
Note:  
All substitution values must convert from string. Values that contain commas are not supported.
Result Set
table-name_Array object with all matching records.
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"findEmployeesByWhereClause",
"header":{ "resultFormat":"json" },
"params": [ "FIRST_NAME LIKE ?", "L%" ]
}
findtable-nameWhere
Returns records that match the WHERE clause specified as a parameter. No dynamic parameters can be used in the WHERE clause.
This operation uses raw SQL rather than a prepared statement which may be a security concern in some environments. It is disabled, by default. See Arbitrary SQL Queries for Database Mashables for more information.
Input Parameters
whereClause as a string with SQL code.
Result Set
table-name_Array object with all matching records.
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"findEmployeesWhere",
"header":{ "resultFormat":"json" },
"params": [ "FIRST_NAME LIKE 'L%'" ]
}
findtable-nameWherecolumn-nameEquals
One operation for each column that is configured for the tables or views in the mashable information source. Returns one or more records from a table where the column value equals the specified parameter.
Input Parameters
column-name specified in the type that matches this column
Result Set
table-name_Array object with all matching records.
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"findEmployeesWhereEmployeeIdEquals",
"header":{ "resultFormat":"json" },
"params": [ "205" ]
}
inserttable-name
Inserts one record to a database table.
Note:  
This operation only updates the columns that are configured in the mashable information source for the table.
Input Parameters
dataTransferObject with properties for each mashable column for the record to insert.
Return Value
The primary key of the new record
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"insertEmployees",
"header":{ "resultFormat":"json" },
"params": [ {
"departmentId":50,
"hireDate":{"time":6.223644e11}
"email":"NEWPERSON",
"employeeId":230,
"jobId":"ST_MAN",
"phoneNumber":"510.123.1234",
"firstName":"New",
"lastName":"Person" } ]
selecttable-name
Performs arbitrary queries on this table using the SELECT clause and optional WHERE clause specified as parameters.
This operation uses raw SQL rather than a prepared statement which may be a security concern in some environments. It is disabled, by default. See Arbitrary SQL Queries for Database Mashables for more information.
Input Parameters
*selectClause as a string with raw SQL code to select all or specific columns from records in this table
*optional whereClause as a string with raw SQL code to specify which records to return
Result Set
Rows object with an array of objects containing the selected columns for all records that match the whereClause.
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"selectEmployees",
"header":{ "resultFormat":"json" },
"params": [ "EMPLOYEE_ID, FIRST_NAME, EMAIL",
"FIRST_NAME LIKE 'L%'" ]
}
Sample Response
{ "version":"1.1"
"sid":"EmployeeTable",
"appId":"",
"oid":"selectEmployees",
"svcVersion":"0.1",
"header":{ "map":{ "serviceHeader":{ "map":{ } } } },
"error":null,
"errorCode":"",
"invId":"",
"response": { "rows":[
{ "FIRST_NAME":"Lex",
"EMPLOYEE_ID":"102",
"EMAIL":"LDEHAAN" },
{ "FIRST_NAME":"Luis",
"EMPLOYEE_ID":"113",
"EMAIL":"LPOPP" },
...
]
},
}
updatetable-name
Updates the record identified by primary key with the data object passed.
Note:  
This operation only updates the columns that are configured in the mashable for the table.
Input Parameters
*primaryKey for the record to update
*dataTransferObject with properties for each mashable column in the record to update.
Return Value
None
Sample Request
{ "version":"1.1",
"sid":"EmployeeTable",
"svcVersion":"0.1",
"oid":"updateEmployees",
"header":{ "resultFormat":"json" },
"params": [ {"employeeId": 101},
{ "departmentId":90,
"hireDate":{"time":6.223644e11}
"email":"NKOCHHAR",
"employeeId":101,
"jobId":"AD_VP",
"phoneNumber":"408.987.6543",
"firstName":"Neena",
"lastName":"Kochhar"
} ]
}
Copyright © 2013-2015 Software AG, Darmstadt, Germany.

Product LogoContact Support   |   Community   |   Feedback