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.

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

Input Parameters	primaryKey
Return Value	none
Sample Request	{ "version":"1.1", "sid":"EmployeeTable", "svcVersion":"0.1", "oid":"deleteEmployees", "header":{ "resultFormat":"json" }, "params": [ {"primaryKey": 205} ] }

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" }, ... ] } }" }

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" } }

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%" ]
}

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%'" ] }

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" ] }

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" } ]

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" }, ... ] }, }

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" } ] }