...
| Code Block | ||
|---|---|---|
| ||
SELECT * FROM PEOPLE
SELECT FIRSTNAME, LASTNAME FROM PEOPLE WHERE LASTNAME = 'Dawes' ORDER BY LASTNAME
SELECT FIRSTNAME, LASTNAME FROM PEOPLE WHERE LASTNAME LIKE '%Daw%'
DELETE FROM PEOPLE WHERE LASTNAME = 'Dawes'
INSERT INTO PEOPLE (FIRSTNAME, LASTNAME) VALUES ('Chris', 'Dawes')
UPDATE PEOPLE SET FIRSTNAME='Christopher' WHERE FIRSTNAME = 'Chris' AND LASTNAME = 'Dawes'
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY lastname) AS rn, People.* FROM People) AS tbl WHERE rn BETWEEN 1 AND 25 ORDER BY lastname
SELECT * FROM PEOPLE WHERE FIRSTNAME IN ('Chris','Marty','Ken'); |
Hopefully from this list you can start to see how this extension can be used. If you need to perform more complex queries it is possible but it would require the transport to be modified.
...
Revision HistoryAnchor Revision History Revision History
v1.7 - Sept 2018
- Now supports queries using WHERE IN
v1.6 - May 11 2017
- Queries are now case insensitive
...
| Type | Parameter | Description | |||||
| Input | dbName | The name of the WAS dataSource registered in Resources...JDB...Data Sources. i.e. jdbc/SampleDB *Deprecated* If dbType = ODBC then this should be the name of the configured DSN. | |||||
| Input | dbTable | The name of the table. | |||||
| Input | action | The type of SQL query to be executed. Valid values are insert, select, update, delete. *Disclaimer* - Be careful with delete and update operations. If you do not properly supply a valid where clause then you could update or delete ALL the rows of the table you are accessing. | |||||
| Input | dbType | Valid Value is DataSource. *Deprecated* the previous values, since the individual database drivers are no longer included in the jar file: Oracle, DB2, MySQL, ODBC, MsSQL | |||||
| Input | dbServer | The host name of the database server. | |||||
| Input | dbPort | The port of the database server. (i.e. 50000) | |||||
| Input | dbUser | The User name of the user to connect to the database. *Deprecated* - Use WAS-J2CAlias instead | |||||
| Input | dbPassword | The password of the user to connect to the database. *Deprecated* - Use WAS-J2CAlias instead | |||||
| Input | WAS-J2CAlias | The alias for the user that should be used to connect to the database. Note: You can configure this in the WAS Admin Console: - Security...Global Security - Java Authentication...J2C Authentication Data... - New...Enter the Alias, username and password | |||||
| Input | assign_<columnName> | Defines the columns that will be used in the assignment clause of applicable SQL queries. For example, creating the following parameters assign_name, assign_age would result in : Select name, age from person If you don't supply any then * will be used for the query: Select * from person | |||||
| Input | where_<columnName> | Defines the columns that will be used in the where clause of applicable SQL queries. For example, creating the following parameters where_name would result in: Select * where name = <value> Note: Any parameter that has a value will automatically be added to the transport outbound parameter list. So if you want to return the values of the where parameters as single items outside of the result set then add individual outbound parameters to your outbound mapping. (Added in v 1.3) | |||||
| Input | whereOp_<columnName> | Valid value is LIKE, case insensitivevalues are LIKE (Added in v1.5), STARTSWITH, and IN (Added in v1.7). Only specify this for the column name if you want to use wild card (LIKE '%x%') instead of an equality (=) search. (Added in v1.5) | |||||
| Input | whereOperator | This determines the relationship between multiple where clauses. There can only be one relationship. Valid values are AND and OR | |||||
| Input | orderBy | The column name to use to sort the results (SELECT only). Required if paginating the results. (Added in v1.5) | |||||
| Input | orderByOp | Valid values are ASC or DESC. Optional, defaults to ASC. (Added in v1.5) | |||||
| Input | pageNum | Paginates the query results. Page number. Pagination may not work with all databases (I have only tested it against DB2), it is using the SQL query like:
(Added in v1.5) | |||||
| Input | itemsPerPage | Paginates the query results. Specify Items per page. (Added in v1.5) | |||||
| Input | includeBlankValues | Valid values are true or false, default is false. All of the "assign_" parameters will be used to build the query even if they are blank. This parameter is critical to a select action where you defined all the "assign_" parameters that you want returned in the query but none of them are actually being used as inputs to the service (where the fields contain values). | |||||
| Input | includeWhereInOut | Valid values are true or false, default is false. If true, the parameters that are defined as "where" parameters will automatically be included in the service results. | |||||
| Output | <columnName> | Each column of the result set will be provided (in uppercase) as an output parameter. | |||||
| Output | success | Returns true if the query is processed without error, otherwise false. | |||||
| Output | rowsAffected | The number of rows affected by the INSERT, DELETE or UPDATE query. For SELECT will contain the number of rows returned. | |||||
| Output | queryResults | The results of the SELECT query. This is a list object where each row contains the columns that were defined by the assignment portion of the query. The returned parameters are in upper case, so your mapping might look something like:
| |||||
| Output | state | The SQL state, only returned if the SQL query fails. | |||||
| Output | message | The SQL message, only returned if the SQL query fails. | |||||
| Output | error | The SQL error code, only returned if the SQL query fails. |
...