(Pro version only)

The database connection filter connects to an OLE DB/ODBC database and retrieves data. It assumes that its input text is a sequence of SQL statements that can be run against the specified database. The data (if any) returned after executing each statement is formatted according to the output style. The filter assumes that the input text is a series of semi-colon (;) terminated SQL statements. Semi-colons that are inside single or double quotes are ignored.

The data output can then be manipulated by subsequent filters to

• generate a database extract (which can be converted, modified or reformatted)
• update data from the original table after performing name and address cleansing (requires a subsequent database connection filter)
• insert data into a new table (requires a subsequent database connection filter)

Sample extracting from a database

Here's a quick example of how to use a database filter to extract data from a database:

1. Add a Special Menu\Database filter
2. Configure it to point to your database using the Build button e.g. to the Northwind database that comes with MS Access
3. Set the database filter output options, e.g. for Delimited Output
4. In the Trial Run area, type
   
   select * from customers
   
   or use
   
   select * from <your table name>
   
   For an Excel spreadsheet (first setup an ODBC connection to point to the Excel file, then point the TextPipe database filter at the ODBC connection), use
   
   SELECT * FROM [Sheet1$]
    
5. Click the [Trial Run] button, to see data extracted and populated in the Trial Run Output area. You can also set the Input Filter to use the Trial Run area by default, and then just click the [Go] button. To save this data to a file, set the Single File Output option of the Output Filter.

Database connection string

The database connection string gives TextPipe the name of the database and tells it how to connect to it. We recommend using the Build button unless you are familiar with setting the connection string manually.

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.

Command Timeout

The command timeout (in seconds), allows you to set the time after which a query will be automatically failed. A setting of 30 will abort a query with an error message if it runs for longer than 30 seconds (the default). The command timeout setting is not implemented by all database drivers. The maximum value is 2147483647.

Output Style

The output style determines how the data will be output:

Output header row/DTD

When enabled, an XML document type definition (DTD) is output for XML files, or a header line with column titles is output for Delimited or Fixed width output formats. TextPipe forces all column heading to be unique, and to conform to XML naming conventions without any special characters. If TextPipe doesn't automatically generate the header you need to can easily add your own using an Add Header filter.

Delimited

The field delimiter appears between each column of data, and the text qualifier appears at the start and end of string or character type fields. If the string field contains the delimiter character it is escaped by repeating the delimiter character. A tab character (\t or \009) may be used to separate columns to generate tab delimited data. The right-click (or context) menu on this field supports Undo, Cut, Copy, Paste, Delete, Clear Entire Field, Select All and Select All And Copy and entering special characters. If Output Header Row/DTD is checked TextPipe automatically generates a Header Row with column names (shown in italics at the start of the example below).

"company","productid"
"Dushkin/MCgraw-Hill",100503
"Programma Electric AB",131686
"Ohio University",131686
"DSL Shops",131686

Fixed

The data is output left justified in a column width determined by the database (specific to each field). If the column width is greater than 4096 then the field is output with no padding. If Output Header Row/DTD is checked TextPipe automatically generates a Header Row with column names (shown in italics at the start of the example below).

company                    productid
Dushkin/MCgraw-Hill        100503
Programma Electric AB      131686
Ohio University            131686
DSL Shops                  131686

XML

TextPipe outputs the data in XML format, with each column output an an entity. All XML-special characters are escaped. If Output Header Row/DTD is checked TextPipe automatically generates an XML schema (DTD) for the data being extracted (shown in italics at the start and end of the example below). You can use an Add Header filter to add your own DTD at the start of the file.

<?xml version="1.0" standalone="yes" ?>

  <!-- generator="TextPipe Search/Replace" -->
  <!-- companyURL="www.datamystic.com" -->
  <!-- SQLquery="select company,productid from shareit" -->

  <!-- event="DTD start" -->
  <!DOCTYPE TextPipeRows [
    <!ELEMENT TextPipeRows (TextPipeRow*)>
    <!ELEMENT TextPipeRow (company, productid)>
    <!ELEMENT company (#PCDATA)>
    <!ELEMENT productid (#PCDATA)>
  ]>
  <!-- event="DTD end" -->

  <TextPipeRows>

  <TextPipeRow>
    <company>Dushkin/MCgraw-Hill</company>
    <productid>100503</productid>
  </TextPipeRow>

  <TextPipeRow>
    <company>Programma Electric AB</company>
    <productid>131686</productid>
  </TextPipeRow>

  <TextPipeRow>
    <company>Ohio University</company>
    <productid>131686</productid>
  </TextPipeRow>

  <TextPipeRow>
    <company>DSL Shops</company>
    <productid>131686</productid>
  </TextPipeRow>

</TextPipeRows>

  <!-- rowCount="4" -->

Insert script for table <tablename>

With this mode setting TextPipe will generate a SQL Insert Script that can be applied to any database. You must specify the name of an existing table to insert the data into. If Output Header Row/DTD is checked, TextPipe includes the column names to insert into (shown in italics in the example below). You can use an Add Header filter to add a drop/create table statement at the start of the script.

insert into existing_table ( company, productid ) values ( "Dushkin/MCgraw-Hill", 100503 );
insert into existing_table ( company, productid ) values ( "Programma Electric AB", 131686 );
insert into existing_table ( company, productid ) values ( "Ohio University", 131686 );
insert into existing_table ( company, productid ) values ( "DSL Shops", 131686 );

BLOB data

If your query returns BLOB (Binary Large Object) data (SQL Server Binary fields), a sub filter can be used to encode the binary data. The most common method to use is a mime base64 encoding.

Notes and Errors

We cannot SELECT data from bigint, decimal & numeric fields. However, you can work around this with a cast e.g. select cast(bigint as varchar) from tablename. Also note that data from Binary fields often contain ASCII 0's - use a Map to replace these nulls with space or similar.

If you have difficulties using this filter, you may need to update the database drivers and the corresponding system components on your system. Click here to download the Microsoft MDAC (Data Access Components) library.

 Contact Us   Support   Community   Tutorials and User Guides (online)
 Copyright © 1999-2006 DataMystic. All rights reserved.