Database Query Plug-in
The Database Query plug-in allows you to work with OLEDB and ODBC database connections by creating, updating, and deleting database records.
Properties
|
Name |
Description |
Mandatory |
Group Name | Modes |
|---|---|---|---|---|
|
connectionname |
The ODBC/OLEDB connection name of the database. It lists the OLEDB and ODBC connections defined at the global level or current app level. |
Yes |
Query Details | NA |
|
query |
The query string. Example: Direct Value: insert into aEmp(NAME, ID) values(?,?) or StoredProcedureName Value from form field: [/my:myFields/my:field2] |
Yes |
Query Details | NA |
|
parameters |
A comma-separated list of parameters in the query. Example: Direct Value: @name, @id. Value from form field: [/my:myFields/my:field2] Value from solution field: [field1] |
No |
Parameter Details | NA |
|
parametertypes |
A comma-separated list of parameter types in the query. Can be any of these parameter types: int16, uint16, int32, uint32, int64, uint64, decimal, double, bool, single, float, date, datetime, or string. Example: Direct Value: string, int. Value from form field: [/my:myFields/my:field2] Value from solution field: [field1] |
No |
Parameter Details | NA |
|
values |
A comma-separated list of values to bind to the parameters. Example: Direct Value: [/my:myFields/my:field1],[/my:myFields/my:field2] |
No |
Parameter Details | NA |
|
isstoredprocedure |
A boolean value indicating if the value specified in the query argument is a database-stored procedure. The default value is No. |
No |
Query Details | NA |
|
escapesql |
A boolean value indicating if the plug-ins must use escape apostrophes in the field values used for substitutions in the supplied query. The default value is No. |
No |
Query Details | NA |
Parameter example
- connectionname – DatabaseConnection1
- isstoredprocedure – false
- parameters – @vendorcolumn,@vendoramount
- parametertypes – string,double
- query – select * from Invoices where vendor = ? and amount < ?
- values – [VendorName],[Amount]
Usage examples
Example 1: insert a record
- connectionname – DatabaseConnection1
- query – set quoted_identifier off; insert into EnvironmentalLog (ID, EquipName, ScrubFlow, PressDrop, Comments) values ('[formId]', '[EquipName]', '[ScrubFlow]', '[PressDrop]', '[Comments]')
Example 2: update a record
- connectionname – DatabaseConnection1
- query – set quoted_identifier off; update EnvironmentalLog set EquipName = '[EquipName]' where ID = '[//my:titleNum]' update EnvironmentalLog set PressDrop = '[PressDrop]' where ID = '[//my:titleNum]' update EnvironmentalLog set distComments = '[Comments]' where ID = '[//my:titleNum]'
Example 3: delete a record
- connectionname – DatabaseConnection1
- query – set quoted_identifier off; delete fromEnvironmentalLog where ID = '[//my:titleNum]'