OLEDB Query

Back to

Related topics

Looking for Winshuttle Composer Plugins?

The OLE DB Query plug-in allows you to work with 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.

isstoredprocedure

A Boolean expression indicating whether or not the value specified in the query argument is a database stored procedure.

Example: If you use the isstoredprocedure command, your query will look similar to the following:

EXEC dbo.StoredProcedure

parameters

A comma-separated list of parameters in the query.

parametertypes

A comma-separated list of parameter types in the query.  Can be one of int16, uint16, int32, uint32, int64, uint64, decimal, double, bool, single, float, date, datetime, or string.

query

The query string.

values

A comma-separate list of values to bind to the parameters.

Example

  • connectionstring – Provider=sqloledb;Data Source=myServer;Initial Catalog=myDatabase;User Id=svadmin;Password=sharevis;
  • isstoredprocedure – false
  • parameters – @vendorcolumn,@vendoramount
  • parametertypes – string,double
  • query – insert * from Invoices where vendor = ? and amount < ?
  • values – [VendorName],[Amount]

This example queries the database specified by the connection string.  It runs the query specified against the database, and binds the values in the SharePoint fields VendorName and Amount to the parameters in the query.