Default Operations Available for Database Mashables

MashZone NextGen creates the following operations in database mashables by default. With custom database mashables, MashZone NextGen adminsitrators can remove any of these default operations when they register database assets in MashZone NextGen. With simple database mashables, all default operations are generated.

Note: MashZone NextGen administrators can also disable or change the default availability for specific operations in MashZone NextGen 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.

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