Data source

The Data source provides DataPipe with a database connection string that gives the name of the database and how to connect to it. We recommend using the Build button unless you are familiar with setting the connection string manually.

More examples of connecting to a database (online)

Build

The Build button allows the connection information to be constructed. A username and password can be specified in the database connection string if required. A file containing the connection information can also be specified.

SQL query string

The SQL query string is a SQL command that tells DataPipe which rows of data to retrieve. This SQL command is sent directly to the target database for evaluation, so be aware that there are minor variations between databases as to how they treat names with embedded spaces, how they quote strings (single or double quote), how wildcards are specified in WHERE ... LIKE statements (* or %) etc. Click the Parse query button to check if the syntax of your SQL query string is correct, or see your database documentation if you need precise documentation. The SQL command must be a SELECT statement - if it is not the dataset returned may not be updateable (it cannot be modified). DataPipe will warn you if the dataset is not updateable when you click the Preview data button.

(*) Only select the fields you need to modify. Performance suffers as more fields are returned. e.g 'select * from TableName' returns all fields, and is slower than 'select field1, field2 from TableName'.

Note: MySQL's MyODBC does not support the updating of record sets that contain BLOB fields.

How do I use multiword table / field names (spaces in Table or Field name)?
Enclose multiword names in backquotes:
SELECT `Last Name`, `First Name` FROM `Address Book`

For ODBC links to an Excel spreadsheet, the Sheet names should be specified as
SELECT * FROM [Sheet1$]

You can also limit the number of records displayed by using the Rows to Display field on the Sample data tab.

Preview data

The Preview data button uses the SQL query string to retrieve a group of sample records from the Data source. The data is then displayed on the Sample data tab.

Parse query

Click the Parse query button to check if the syntax of your SQL query string is correct. The syntax is parsed by the relevant database driver for the target database.