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
connectionname	The ODBC/OLEDB connection name to the database. It includes a list of all the OLEDB and ODBC connections defined at the global level or current app level in a drop-down menu.	Yes
query	The query string. Example: Direct Value: insert into aEmp(NAME, ID) values(?,?) or StoredProcedureName Value from form field: [/my:myFields/my:field2] Value from solution field: [field1]	Yes
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
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
values	A comma-separated list of values to bind to the parameters. Example: Direct Value: [/my:myFields/my:field1],[/my:myFields/my:field2]	No
isstoredprocedure	A boolean value indicating whether or not the value specified in the query argument is a database stored procedure. The default value is No.	No
escapesql	A boolean value that indicates whether or not the plug-ins should use escape apostrophes in the field values which are used for substitutions in the supplied query. The default value is No.	No

Parameter examples:

• 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]'