ODBC Query

Back to

Related topics

Looking for Winshuttle Composer Plugins?

The ODBC Query plug-in allows you to work with SQL databases by creating, updating, and deleting database records.

Value Pair

Description

connectionstring

String to use to connect to the database.

Note:

  • The user must have permissions to access the database.
  • If Integrated Security=SSPI is set in the connection string, then the logged in user has permissions to access the database. For example:

    Integrated Security=SSPI;Provider=sqloledb;database= <databasename>; server=<path to SQL server database>

  • Otherwise, you can specify a user in the connection string that has rights to access the database. For example:

    Provider=sqloledb;Data Source=WID101\SQLEXPRESS;Initial Catalog =<databasename> ;User Id = <userid>; Password = <password>

    Note: Integrated Security will only work if the SQL server is on the same box as Workflow, otherwise you either need to have Kerberos authentication, or specify a SQL username and password due to a server double-hop issue.

escapesql

A Yes/No value that indicates whether or not the plug-ins should escape apostrophes in the field values used in substitutions in the query supplied.  The default is Yes if omitted.

query

Query to run on the database with the form data.

Example 1: Insert a Record

  • connectionstring – DRIVER={SQL Server};SERVER=localhost;DATABASE=ws;UID=svdb;PWD=ws;OPTION=3
  • query – set quoted_identifier off; insert into EnvironmentalLog (ID, EquipName, ScrubFlow, PressDrop, Comments) values (&quot;[formId]&quot;, &quot;[EquipName]&quot;, &quot;[ScrubFlow]&quot;, &quot;[PressDrop]&quot;, &quot;[Comments]&quot;)

Example 2: Update a Record

  • connectionstring – DRIVER={SQL Server};SERVER=localhost;DATABASE=ws;UID=svdb;PWD=ws;OPTION=3
  • query – set quoted_identifier off; update EnvironmentalLog set EquipName = &quot;[EquipName]&quot; where ID = &quot;[//my:titleNum]&quot; update EnvironmentalLog set PressDrop = &quot;[PressDrop]&quot; where ID = &quot;[//my:titleNum]&quot; update EnvironmentalLog set distComments = &quot;[Comments]&quot; where ID = &quot;[//my:titleNum]&quot;

Example 3: Delete a Record

  • connectionstring – DRIVER={SQL Server};SERVER=localhost;DATABASE=ws;UID=svdb;PWD=ws;OPTION=3
  • query – set quoted_identifier off; delete fromEnvironmentalLog where ID = &quot;[//my:titleNum]&quot;