Welcome to DBExtractor!

Copyright © 2005-2006 SQLExec LLC. All rights reserved.

DBExtractor Home Page

DBExtractor Program Updates

Technical Support

DBExtractor is an ODBC database export utility. It gathers execution parameters from the DBExtractor Specifications File , which is then used to connect to specific files or databases via an ODBC driver and execute user-defined queries or special metadata queries . It is designed to work as a data refresh utility (see Date Driver) invoked as a windows scheduled task , where new or updated data is found and extracted to disk. This program runs on all Windows operating systems, and can work with any ODBC-compliant database driver. DBExtractor is licensed, not sold. See the license agreement for details.

DBExtractor can also specify files as input sources in addition to databases. See the ODBC Text Drivers section for more details.

Footprint

DBExtractor has a relatively small application footprint. It takes up very little space in the computer's memory. It uses a minimum of disk space . CPU utilization is also minimal. File I/O may be intensive at times depending upon the amounts of data being extracted from a database, and output file sizes also vary in this respect depending upon the number of rows being returned from database query calls.

Auditing/Reporting/Logging

DBExtractor also has a robust logging facility , where informational, warnings, and errors are recorded to the program log file. There is no auditing feature per se, but the log file does keep track of every SQL statement that was executed and how many rows were returned.

Security

DBExtractor has security features involving the capacity to encrypt passwords used when connecting to a database. DBExtractor requires only a READ ACCESS to tables from which it will execute queries.

Date Driver

Date Driver refers to the logic employed to get new or updated data from the source database. It involves updating the DBExtractor Specifications File with the current system date, but only when the program runs completely without errors. This date is then used as the start point for successive executions of this program. The current system date is used as the end point. SQL queries defined in this input file have special keywords designating start and end date where they are replaced with real-time values at execution time.

SETUP Procedures

The setup procedures are straightforward to get the program up and running as a scheduled DOS batch job.

Execution Parameters

DBExtractor takes only one optional parameter, the location of the DBExtractor Specifications File. If not provided, it will look for a specific file, DBEXTRACTOR.INI in the DBExtractor program directory.


License Agreement

END-USER LICENSE AGREEMENT FOR THE SOFTWARE PRODUCT known as DBExtractor

IMPORTANT - READ CAREFULLY: This DBExtractor End-User License Agreement ("EULA") is a legal agreement between you ((to include affiliates and subsidiaries), hereby referred to as the LICENSEE, and SQLEXEC LLC, the PRODUCT OWNER of the SOFTWARE PRODUCT identified above and hereafter referred to as the LICENSOR.  The specific type of LICENSE (unrestricted site, restricted site, seat, etc.) is based on the INVOICE or CONTRACT that was delivered to the LICENSEE along with the SOFTWARE PRODUCT.  This SOFTWARE PRODUCT runs on a COMPUTER, which consists of computers, workstations, terminals, or other electronic or digital devices that run on the operating systems noted in this license agreement.

The SOFTWARE PRODUCT consists of all files and software distributed with the SOFTWARE PRODUCT.  These components are distributed as part of this license agreement by the LICENSOR to the LICENSEE.  The source code used to compile or produce the binary output files are not distributed as part of this license agreement, and in no way are any rights to this source code implied by this license agreement to the LICENSEE.  Only the LICENSOR can provide maintenance of this SOFTWARE PRODUCT.

The LICENSOR guarantees the product to be free of defects, known or unknown at the time of this LICENSE agreement, for the duration of the LICENSE agreement.  The LICENSOR will, at the LICENSOR's expense, fix any defect brought to its attention by the LICENSEE in a timely manner befitting the nature of the defect and the business impact on the LICENSEE.  Any updates to the SOFTWARE PRODUCT by the LICENSOR as a result of fixing defects, are also covered by all provisions of this license agreement.

By installing, copying, downloading, accessing, or otherwise using the SOFTWARE PRODUCT, you agree to be bound by the terms of this EULA. If you do not agree to the terms of this EULA, do not install or use the SOFTWARE PRODUCT.

The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. The SOFTWARE

PRODUCT is licensed, not sold.

SOFTWARE PRODUCT LIMITATIONS

1. The SOFTWARE PRODUCT license only applies to the following Windows operating systems:

Windows NT 4

Windows 2000 (all versions)

Windows XP   (all versions)

2. The SOFTWARE PRODUCT is guaranteed to be ODBC-compliant and only works with ODBC drivers.  As such it is guaranteed to work without defect as long as the ODBC driver required by this SOFTWARE PRODUCT is without ODBC defects in any way used by this SOFTWARE PRODUCT.

3. The SOFTWARE PRODUCT does not work with BLOB/CLOB or other binary database formats.

Reservation of Rights. All rights not expressly granted are reserved by the LICENSOR of the SOFTWARE PRODUCT.

GRANT OF LICENSE. This EULA grants you the following rights:

Applications Software. The LICENSEE may make additional copies of the SOFTWARE PRODUCT for backup purposes only.

Unlimited instances of this SOFTWARE PRODUCT may run or be executed on a single COMPUTER, where this license is in effect.

VALID LICENSE TYPES

The following forms of license agreements are associated with this SOFTWARE PRODUCT.  The INVOICE for this SOFTWARE PRODUCT contains information about what type of license is granted to the LICENSEE of this SOFTWARE PRODUCT.  Here are the current license types available:

Unrestricted Site License:

An unrestricted site license allows any user within your company to use the SOFTWARE PRODUCT or any number of instances of the SOFTWARE PRODUCT on any workstation that they prefer.  Unrestricted Site Licenses are limited in duration and must be renewed according to the terms of the purchase agreement.  Unrestricted Site Licenses do not convey or imply a Corporate Distribution License (CDL). A CDL is where the LICENSEE may combine a SOFTWARE PRODUCT with other products not part of this license agreement or distribute this SOFTWARE PRODUCT as a sublicense to others.

Restricted Site License:

A Restricted site license allows a limited number of users within your company to use the SOFTWARE PRODUCT concurrently on any COMPUTER within that company.  Restricted Site Licenses are limited in duration and must be renewed according to the terms of the purchase agreement.  Restricted Site Licenses do not convey or imply a Corporate Distribution License (CDL). A CDL is where the LICENSEE may combine a SOFTWARE PRODUCT with other products not part of this license agreement or distribute this SOFTWARE PRODUCT as a sublicense to others.

Seat License:

Seat licenses grants one concurrent user to interact with the SOFTWARE PRODUCT on a limited number of COMPUTERS that are also owned by the LICENSEE.  This limited number of COMPUTERS can be no more that three additional COMPUTERS.  There is no time limit on a seat license agreement so the LICENSEE is not required to renew this license agreement.  The seat license grants free upgrades to the LICENSEE for a limited period of time.  This period is stipulated in the INVOICE agreement entered into by the LICENSEE.  This period is not less than one year from the receipt of this SOFTWARE PRODUCT.

* Limitations on Decompilation, and Disassembly. You may not decompile, or disassemble the SOFTWARE PRODUCT, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation.

* Trademarks. This EULA does not grant you any rights in connection with any trademarks or service marks of SQLEXEC, or the SOFTWARE PRODUCT.

* Rental. You may not rent, lease, or sell the SOFTWARE PRODUCT as a stand-alone product.

* Termination. Without prejudice to any other rights, the LICENSOR may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the SOFTWARE PRODUCT and all of its component parts.

EXPORT RESTRICTIONS. You agree that you will not export or re-export the SOFTWARE PRODUCT, any part thereof, or any process or service that is the direct product of the SOFTWARE PRODUCT to any country, person or entity subject to U.S. export restrictions. You specifically agree not to export or re-export any of

the Restricted Components (i) to any country to which the U.S. has embargoed or restricted the export of goods or services, which currently include, but are not

necessarily limited to Cuba, Iran, Iraq, Libya, North Korea, Sudan and Syria, or to any national of any such country, wherever located, who intends to transmit or transport the Restricted Components back to such country; (ii) to any person or entity who you know or have reason to know will utilize the Restricted Components in the design, development or production of nuclear, chemical or biological weapons; or (iii) to any person or entity who has been prohibited from participating in U.S. export transactions by any federal agency of the U.S. government. You warrant and represent that neither the BXA nor any other U.S.

federal agency has suspended, revoked or denied your export privileges.

If you acquired this SOFTWARE PRODUCT in the United States, this EULA is governed by the laws of the State of Virginia.  If this SOFTWARE PRODUCT was acquired outside the United States, then local law may apply.

LIMITATION OF LIABILITY. To the maximum extent permitted by applicable law, in no event shall the LICENSOR or its suppliers be liable for any special, incidental, indirect, or consequential damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of

business information, or any other pecuniary loss) arising out of the use of or inability to use the SOFTWARE PRODUCT or the provision of or failure to provide Support Services, even if the LICENSOR has been advised of the possibility of such damages. In any case, the LICENSOR's entire liability under any provision of this EULA shall be limited to the greater of the amount actually paid by you for the SOFTWARE PRODUCT or U.S.$5.00; provided, however, if you have entered into a SOFTWARE PRODUCT Support Services Agreement, the LICENSOR's entire liability regarding Support Services shall be governed by the terms of that agreement. Because some states and jurisdictions do not allow the exclusion or limitation of liability, the above limitation may not apply to you.


Date Driver

The basic purpose of the date driver logic is to get data that is new or changed since the last run. It could also be used to get all of the original data on the first run for a particular site. In that case, you would not use BEGIN_DATE or END_DATE keywords. The DBExtractor program does not validate that you are using the keywords or using them correctly. It just does global replacements for them if found. It replaces the BEGIN_DATE with the LASTCOMMITDATE and the END_DATE with the local, system date.

The SQL statements must be coded so that the queried date is greater than or equal to the BEGIN_DATE and less than the END_DATE. The LASTCOMMIT DATE is only updated if the previous DBExtractor execution completely successfully without any fatal errors.

The format of the LASTCOMMITDATE determines how the interim period is defined. For the Date format, updates are found based on a day units, whereas updates are found for time units using the DateTime format.

Date Format: YYYYMMDD, YYYY/MM/DD, or YYYY-MM-DD

This interim period are the days greater than or equal to the last commit date (LASTCOMMITDATE value in the DBExtractor Specifications File) and less than the local, system date.

DateTime Format: YYYYMMDDHHMMSS

This interim period is the time greater than or equal to the last commit datetime (LASTCOMMITDATE value in the DBExtractor Specifications File) and less than the current system datetime.

The SQL statements provided by the user must have the placeholders for start and end date embedded in the SQL statements. These placeholders for start and end date are %%BEGIN_DATE%% and %%END_DATE%% respectively. They must be enclosed in quotes since they are treated as constant character strings within the SQL statement. Consider the following example in Oracle:

select * from linxdata.temp_ccso_arrest where TO_CHAR(addtime, 'YYYYMMDD') >= '%%BEGIN_DATE%%' and TO_CHAR(addtime, 'YYYYMMDD') < '%%END_DATE%%';

Below are some sample functions to use for these date predicates based on certain database vendors. These examples use the DATE format.

Oracle

TO_CHAR(<date field>, 'YYYYMMDD') >= '%%BEGIN_DATE%%' and TO_CHAR(<date field>, 'YYYYMMDD') < '%%END_DATE%%'

MS SQL Server

convert(char(8), <date field>, 112) >= '%%BEGIN_DATE%%' and convert(char(8), <date field>, 112) < '%%END_DATE%%'

CAST(FLOOR(CAST(<date field> AS float)) AS datetime) >= '%%BEGIN_DATE%%' and CAST(FLOOR(CAST(<date field> AS float)) AS datetime) < '%%END_DATE%%'

Adaptive Server Anywhere (Sybase ASA or SQLAnyWhere)

convert(char(8), <date field>, 112) >= '%%BEGIN_DATE%%' and convert(char(8), <date field>, 112) < '%%END_DATE%%'

Adaptive Server Enterprise (Sybase ASE or Sybase SQL Server)

convert(char(8), <date field>, 112) >= '%%BEGIN_DATE%%' and convert(char(8), <date field>, 112) < '%%END_DATE%%'

MS ACCESS

Format(<date field>, 'yyyymmdd') >= '%%BEGIN_DATE%%' and Format(<date field>, 'yyyymmdd') < '%%END_DATE%%'

INTERBASE/FIREBIRD

WHERE <date field> >= CAST( '%%BEGIN_DATE%%' AS DATE) AND <date field> < CAST('%%END_DATE%%' AS DATE)


Logging

DBExtractor writes error and logging information to the file specified as the DBExtractor log file. By default, this log file is DBExtractor.log in the DBExtractor program directory if not specified otherwise on the LOGFILE parameter in the DBExtractor Specifications File.

DBExtractor provides a robust logging feature. You can specify the location of the log file in the DBExtractor Specifications File, or it defaults to DBExtractor.log in the DBExtractor program directory. Records are appended to the logging file. The DBExtractor logging file contains a timestamp for each record entry. A record entry record is identified by one of the following severity codes:

DEBUG

INFORMATION

WARNING

NON FATAL ERROR

FATAL ERROR

A text string follows the severity code with a detailed description of the DBExtractor logging entry. The log file is automatically deleted when the file size exceeds 500Kbytes.


Oracle SQL Loader Considerations

This section documents some notes related to the loading of data using Oracle's SQL Loader that had been previously extracted using DBExtractor.

DBExtractor does not place double-quotes around output fields, so you must remove the following phrase if found in your Oracle SQL Loader control file:

optionally enclosed by '"'

This phrase is usually found in control files to detect string fields and to avoid problems with embedded delimiters. DBExtractor detects and reports embedded delimiters in the output log file as a fatal error message.

SQL Loader Peculiarities:

1. For CLOB columns, you must specify CHAR (40000), or you may get data field exceeded max limit errors.

2. You can qualify date column values with a string mask in one of the following ways:

<column name> DATE 'YYYY/MM/DD HH24:MI:SS',

<column name> "to_date(:<column name>, 'YYYY-MM-DD HH24:MI:SS')",


Metadata Queries

DBExtractor can execute queries which return database metadata information. This information is also referred to as the database system tables, where information about tables, columns, column attributes, indexes, and primary/foreign keys are stored. These queries might be userful in gathering information that will help you construct the SQL statements to retrieve the data you want to extract.

Key phrases are used instead of actual SQL statements after the keywork, SQL=.

SQL=SYSTEMTABLES;FILE=SYSTEMTABLES.DAT

SQL=USERTABLES;FILE=USERTABLES.DAT

SQL=COLUMNS:mytablename;FILE=USERTABLES.DAT

SQL=TABLESTATS:mytablename;FILE=TABLE.DAT

SQL=PKEY:TABLE1;FILE=TABLE1PKEY.DAT

SQL=FKEYS:TABLE1;FILE=TABLE1FKEYS.DAT

SYSTEMTABLES returns a comma-delimited list of all system tables in the database.

USERTABLES returns a comma-delimited list of all user tables in the database.

COLUMNS returns a tab-delimited list of all column names for a particular table.

TABLESTATS returns index information for a particular table.

PKEY returns a tab-delimited list of primary key information.

FKEYS returns a tab-delimited list of foreign key information where the table named has foreign keys and where other tables have foreign keys referencing the named table.


ODBC Text Drivers

In addition to extracting data from a database source, you can also extract data from a text file as your database source. This section explains how.

1. Create a System Data Source (DSN). Open .

Select the System Tab, and click ADD. Scroll down the driver list until you come to the one named,

Microsoft Text Drivere (*.txt; *.csv). Select it, and press FINISH. Give it a name, and press OK.

2. Modify your DBExtractor .INI file. Create a Connection entry using the name you just gave to your DSN, and your sql statements pointing to those source files. In the example below, MSTXTDSN is the DSN name, and c:\temp\dept.txt, and c:\temp\dept.csv are the text files to be used as the source files. Note that you must fully-qualify the file with the complete directory path.

[DSN=MSTXTDSN]

UID=

PWD=

SRV=

DBQ=

CONNECTSTRING=

SQL=SELECT * from c:\temp\dept.txt;FILE=dept.DAT

[DSN=MSTXTDSN]

UID=

PWD=

SRV=

DBQ=

CONNECTSTRING=

SQL=SELECT * from c:\temp\dept.csv;FILE=dept.DAT

3. The final step is to configure a file, schema.ini, which will reside in the same directory as the Dbextractor.exe file. This file is used by the DSN to understand the format of the source text file(s). You can specify multiple formats for each text file. For our example we are using both a tab-delimited (TXT) and comma-delimited (CSV) text files, DEPT.TXT and DEPT.CSV respectively. Theyt contain 3 columns and the first row is a header row, giving a column name to each column: deptno, dname, loc. It has 4 rows of data that follow (this example shown is the DEPT.CSV file, the DEPT.TXT file has tabs instead of commas separating the values):

    deptno,dname,loc

    10,ACCOUNTING,NEW YORK

    20,RESEARCH,DALLAS

    30,SALES,CHICAGO

    40,OPERATIONS,BOSTON

For our example, here are the contents of our schema.ini file:

[dept.csv]

ColNameHeader=True

Format=CommaDelimited

MaxScanRows=0

CharacterSet=OEM

Col1=DEPTNO Integer

Col2=DNAME Char

Col3=LOC Char

[dept.txt]

ColNameHeader=True

Format=TabDelimited

MaxScanRows=0

CharacterSet=OEM

Col1=DEPTNO Integer

Col2=DNAME Char

Col3=LOC Char

It is this easy to set up a file as a data source instead of a database! You can also specify a schema.ini file that can handle fixed length format source files. In these cases, you always specify the following for each column:

TEXT WIDTH <precision>

Consult the Schema.ini File section for more details.


Setup

DBExtractor setup procedures involve the following steps:

1. Defining DBExtractor query rights on the database host or server .

2. Installing an ODBC driver, and then defining a local Data Source Name for that driver.

3. Entering parameters in the DBExtractor Specifications File.

4. Setting up DBExtractor to run as a scheduled batch job.

Optionally:

5. Define DBExtractor Security, which involves encryption of passwords.


Database Server

DBExtractor needs a valid database profile (userid and password) with which to connect to the database server. The minimal DBExtractor rights necessary are SELECT authority with respect to certain tables.


ODBC

DBExtractor requires that an ODBC driver is installed and a Data Source Name (DSN) is created under the Microsoft ODBC/Data Source Administrator interface that is used to map a DSN to a specific ODBC driver. The DSN name is then put into the DBExtractor.ini file to identify a database profile to use during program execution.

To add a DSN, follow these steps:

Start the ODBC Administrator, Data Sources (ODBC), which is located under Administrative Tools. Select the System tab, and click ADD.

Select the driver you want to use for the target database vendor. Then press FINISH.

The rest of the steps necessary for defining the DSN are based on the particular interface the ODBC driver provides.


Specification File

The Specifications file is used to drive the DBExtractor program. A sample one is provided. This section documents the details of each field in this file. DBExtractor can also use text files as a source instead of a database. See the ODBC Text Drivers section for more details.

2 forward slashes indicate a comment line which is ignored when the program reads from this file.

The file consists of 2 sections: one general input section and one or more database-specific sections.

The field keywords are case-sensitive and all are UPPERCASE. Values can be either upper or lower case.

General Parameters

PRODUCTKEY

This field contains a valid product key if the product was registered successfully. Otherwise, the program runs in trial mode and is limited in functionality (only 50 rows can be retrieved from a table).

ODBCVERSION

Users can control what ODBC version is set in the call to mvSQLDriverConnect. This is added for backward compatibility with older ODBC drivers. Valid values are 2 or 3.

DELIMITER

This field dictates what delimiter will be used to separate the data elements for each row in the resulting output file. You can use 0-4 characters to designate the delimiter. To designate a tab, simple tab over from the equals sign.

FIXEDWIDTH

Valid values are Y or N. If Y, then each data type field will be padded to the maximum length, otherwise fields are written to the output file as returned from the database driver. For certain drivers, character strings are returned as the maximum length with spaces appended to the end. Some other drivers return the same type of data type with the values trimmed on the right. If you want to make sure all spaces are removed from the end of strings, then set the TRIM parameter to Y. This will enable the program to override the default values returned from the database for string type data fields. You can then keep the fixedwidth settings for non-string type fields separate from how you treat string type fields when you use both the FIXEDWIDTH=N and TRIM=Y parameters. See the table below under TRIM description.

TRIM

Valid values are Y or N. If Y, then all character fields are trimmed left and right. If you want to make sure all spaces are removed from the end of strings, then set this parameter to Y. See the table below for how FIXEDWIDTH and TRIM work together.

FW    TR  Result

==    ==  ===========================================================

Y     Y   Invalid. You cannot specify Y for both FIXEDWIDTH and TRIM.

Y     F   Fixed width output for all data type fields.

F     T   All field values are returned as their actual value length

          In addition, strings are left and right trimmed.

F     F   All field values are returned  as their actual value length and

          strings are not trimmed if they have leading/trailing spaces.

HEADER

Valid values are Y or N. If Y, then a header row with the column names will be at the top of the output file.

CR

You can use zero characters to indicate not to replace the carriage returns or any 2 character string length value to specify the replacement values for all carriage returns found in text columns.

ORACLECTL

Valid values are Y or N. If Y, then Oracle SQL Loader control files are created for every SQL statement. CTL files parse fields as delimited or fixed width. If FIXEDWIDTH=Y, then CTL files use the fixed width format. Otherwise they use the DELIMITER value for parsing the fields in the output control files. Also, an Oracle SQL Loader batch file is also created (SQLDR.BAT) when this value is set to Y that invokes SQL LOADER on all CTL files that are created.

Sample SQLDR.BAT file:

sqlldr userid/password@orainst ERRORS=100000 ROWS=1000 DIRECT=FALSE CONTROL=<extract dir>\sample1.CTL

sqlldr userid/password@orainst ERRORS=100000 ROWS=1000 DIRECT=FALSE CONTROL=<extract dir>\sample2.CTL

Sample CTL file:

LOAD DATA

INFILE '<extract dir>\sample1.CTL'

TRUNCATE INTO TABLE sample1

FIELDS terminated by '*|*'

TRAILING NULLCOLS

(

RECNO,

ACCNUMBER,

ACCTIME,

)

VERBOSE

Valid values are Y or N. If Y, then additional messages are logged to the standout output terminal. This field does not affect what records are written to the log file.

DEBUG

Valid values are Y or N. If Y, then debugging information is also shown and logged.

LOGFILE

If specified, the path and name of the file where program messages are appended. If empty the logfile name defaults to DBEXTRACTOR.LOG in the program directory.

PASSWORDSENCRYPTED

Valid values are Y or N. If Y, then the password associated with each DSN defined are encrypted and need to be decrypted.

EXTRACTDIR

The name of the local directory where the output files are extracted. Make sure this directory contains nothing else besides files to be extracted, since this directory is flushed before every program execution.

EXTRACTDIRCLEAN

Valid values are Y or N. If N, then no files are deleted from the file extract directory before the files are extracted to that directory during program execution. If Y, then any previously existing files in the file extract directory will be deleted unless the EXTRACTDIRCHECK variable is also set to Y and the extension values specified in EXTRACTDIREXTENSIONS are found in the file extract directory.

EXTRACTDIRCHECK

Valid values are Y or N. If Y, then any file in the file extract directory with an extension defined in EXTRACTDIREXTENSIONS will cause the program to end with a fatal error. This check helps to ensure that the user does not accidentally delete files in a wrongly specified extract directory. All files are deleted from the extract directory when the DBExtractor program executes.

If EXTRACTDIRCLEAN=N, then this field is ignored since no files are eligible for deletion.

EXTRACTDIREXTENSIONS

These are the extensions that will be looked for if both EXTRACTDIRCLEAN and EXTRACTDIRCHECK variables are set to Y. There must ot be any embedded spaces within the string. Each extension is defined as a period followed by an extension. For example:

EXTRACTDIREXTENSIONS=.cmd,.exe,.ini,.dll,.ocx,.drv,.com,.sys

DSNCOUNT

Specifies the number of DSNs that are defined below in the DSN sections. They must match or an error is returned.

DATEMASK

Specifies the LASTCOMMITDATE format for dates. This value is ignored for timestamp values.

Valid values are YYYYMMDD, YYYY/MM/DD, or YYYY-MM-DD. The DATEMASK and LASTCOMMITDATE formats must match.

DATEMASK=YYMMDD

LASTCOMMITDATE=20050115

LASTCOMMITDATE

The date when the last program ran successfully without any fatal errors. The format of the date is defined in DATEMASK. For timestamp values the DATEMASK is ignored. The LASTCOMMITDATE field is used to determine the start date of subsequent program executions. The DATEMASK and LASTCOMMITDATE formats must match.

DATEMASK=YY/MM/DD

LASTCOMMITDATE=2005/01/15

Database Profile Section

This section documents the database(s) to use for extracting the data. You can have one or more DSN sections as shown below. Currently, the maximum number of DSNs that can be specified is 20.

[DSN=<DSN Name>]

The name of the DSN that dictates what ODBC driver to use. This field must be enclosed in brackets as shown above.

UID

The user id used for connecting to the database.

PWD

The password used for connecting to the database. This value may be encrypted depending on the value of the general parameter, PASSWORDSENCRYPTED.

SRV

The server name of the database. This field usually remains empty.

DBQ

Specific to Oracle. This value allows the user to specify the Oracle Service Name directly. Certain older Oracle drivers must have this value specified on the connection string.

CONNECTSTRING

A user-specific connection string can be used to replace the values specified above. This string may be empty if the user

chooses to use the other fields provided above. If the string is not empty, then the values specified above for this DSN will not be used.

One or more lines like the following line that designate what SQL to run and to what output file. An SQL statement cannot be defined across multiple lines.

SQL= <sql statement>;FILE=<file name>

<sql statement> can consist of global replace keywords shown below that designate the start date and end date of the data to retrieve from the database. These keywords must be enclosed in single quotes.

%%BEGIN_DATE%%

%%END_DATe%%

See Date Driver for more details


Specification File Sample

[GENERAL PARAMETERS]

PRODUCTKEY=

ODBCVERSION=3

DELIMITER=*|*

FIXEDWIDTH=N

TRIM=Y

HEADER=N

CR=^^

ORACLECTL=Y

VERBOSE=Y

DEBUG=N

LOGFILE=c:\temp\dbextractor.log

PASSWORDSENCRYPTED=N

EXTRACTDIR=c:\temp\files

EXTRACTDIRCLEAN=N

EXTRACTDIRCHECK=Y

EXTRACTDIREXTENSIONS=.cmd,.exe,.ini,.dll,.ocx,.drv,.com,.sys

DSNCOUNT=3

DATEMASK=YYYY/MM/DD

LASTCOMMITDATE=1997/01/01

// SQLANYWHERE (ASA) example

[DSN=EAS Demo DB V9]

UID=dba

PWD=sql

SRV=

SRV=

DBQ=

CONNECTSTRING=

SQL=SELECT * from webdata;FILE=WEBDATA.DAT

SQL=select * from department;FILE=DEPARTMENT.DAT

SQL=select * from contact;FILE=CONTACT.DAT

SQL=select * from employee where convert(char(8), start_date, 112) >= '%%BEGIN_DATE%%' and convert(char(8), start_date, 112) < '%%END_DATE%%';FILE=EMPLOYEE.DAT

// INTERBASE example

[DSN=TAMUCC]

UID=SYSDBA

PWD=masterkey

SRV=

DBQ=

CONNECTSTRING=

SQL=select modified from citations;FILE=CITATIONS.DAT

SQL=SELECT CIT_ID, TICKETNUM, CTYPE, WARNING, DOI, OFC_ID, BADGE, OFC, LOCATION, DIRECTION, ADDRESS, DESCRIPTION, TRESPASS, TRESPASS_EXP, SEARCHED, CONSENTED, NAME_ID, NAME, NADDRESS, NCITY, NSTATE, NZIP, NPHONE, NHEIGHT, NWEIGHT, NHAIR, NEYES, NRACE, NETHNICITY, NGENDER, NDOB, NSSN, NLICSTATE, NLICNUMBER, NLICTYPE, NLICRESTRICT, NIDSTATE, NIDNUMBER, VEH_ID, VLICENSETAG, VSTATE, VVEHYEAR, VCOLOUR, VMAKE, VMODEL, VVEHTYPE, VDECAL, VVIN, FINE, COURTDATE, JUDGE, COURT, DISPOSITION, COMMENT, CREATED, MODIFIED, CREATOR, ARRESTED, VIDEO FROM CITATIONS WHERE MODIFIED >= CAST( '%%BEGIN_DATE%%' AS DATE) AND MODIFIED <= CAST('%%END_DATE%%' AS DATE) ;FILE=citations_test.txt

// MS ACCESS example=

[DSN=PROCOP]

UID=admin

PWD=12345

SRV=

DBQ=

CONNECTSTRING=

SQL=SELECT RECNO, ACCNUMBER, ACCTIME, BADGE, OFFICERNAME, ETIME, UTIME, NARRATIVE FROM ACCIDENTS WHERE (Format(acctime, 'yyyymmdd') > '%%BEGIN_DATE%%' and Format(acctime, 'yyyymmdd') < '%%END_DATE%%') or (Format(etime, 'yyyymmdd') > '%%BEGIN_DATE%%' and Format(etime, 'yyyymmdd') < '%%END_DATE%%') or (Format(utime, 'yyyymmdd') > '%%BEGIN_DATE%%' and Format(utime, 'yyyymmdd') < '%%END_DATE%%') order by recno desc;FILE=ACCIDENTS.DAT

SQL=SELECT RECNO, ACCLINK, LNAME, FNAME, MNAME, AGE, DOB, SSN, HT, WT, RACE, SEX, HAIR, EYES, COMPLEX, BUILD, ADDRESS, CITY, STATE, ZIPCODE, HPHONE, WPHONE, WEXT, OCCUP, EMP, EMPADDR, EPHONE, NEXTKIN, KINADDR, KPHONE, DLN, DLSTATE, RESIDENT, ETIME, UTIME FROM ACCNAME WHERE (Format(etime, 'yyyymmdd') > '%%BEGIN_DATE%%' and Format(etime, 'yyyymmdd') < '%%END_DATE%%') or (Format(utime, 'yyyymmdd') > '%%BEGIN_DATE%%' and Format(utime, 'yyyymmdd') < '%%END_DATE%%') order by recno desc;FILE=ACCNAME.DAT

// Oracle example

[DSN=LSAXP_Oracle]

UID=MyUserID

PWD=MyPass

SRV=

DBQ=

CONNECTSTRING=DSN=LSAXP_Oracle;UID=MyUserID;PWD=MyPass;DBQ=LSAXP;MTS=T;

SQL=select * from CLINTYPES;FILE=CLINTYPES.DAT


Scheduler

DBExtractor can be run as a scheduled batch job. DBExtractor can be run as a scheduled task on any Windows operating system since there is a default Task Scheduler installed as part of the operating system. Task Schedulers show the exit status codes of programs they kick off.

While a scheduler provides some information on the reason for failed tasks, much more useful information can be found in the DBExtractor logging file. See the logging section for more details.

DBExtractor exit status codes:

0 - SUCCESS

1 - FAILURE


TroubleShooting

This section documents typical errors found during setup and program execution.


Security

DBExtractor provides a built-in encryption mechanism so that users can generate encrypted strings and use them as values in the DBExtractor Specifications File.

Setup would normally involve the following steps:

1. Generate encrypted passwords.

2. Place them in the Spec File.

3. Set PASSWORDSENCRYPTED=Y in the Spec file.

To create an encrypted value, run DBExtractor with these parameters:

DBExtractor ENCRYPT:<your value to encrypt>

DBExtractor then outputs to the standard output screen the newly created encrypted value for the given input value.


Schema.ini File

Click Here for Online Microsoft Help

Schema.ini File (Text File Driver)

When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file, which is always named Schema.ini and always kept in the same directory as the text data source, provides the IISAM with information about the general format of the file, the column name and data type information, and a number of other data characteristics. A Schema.ini file is always required for accessing fixed-length data; you should use a Schema.ini file when your text table contains DateTime, Currency, or Decimal data or any time you want more control over the handling of the data in the table.

Note The Text ISAM will obtain initial values from the registry, not from Schema.ini. The same default file format applies to all new text data tables. All files created by the CREATE TABLE statement inherit those same default format values, which are set by selecting file format values in the Define Text Format dialog box with <default> chosen in the Tables list. If the values in the registry are different from the values in Schema.ini, the values in the registry will be overwritten by the values from Schema.ini.

Understanding Schema.ini Files

Schema.ini files provide schema information about the records in a text file. Each Schema.ini entry specifies one of five characteristics of the table:

The text file name

The file format

The field names, widths, and types

The character set

Special data type conversions

The following sections discuss these characteristics.

Specifying the File Name

The first entry in Schema.ini is always the name of the text source file enclosed in square brackets. The following example illustrates the entry for the file Sample.txt:

[Sample.txt]

Specifying the File Format

The Format option in Schema.ini specifies the format of the text file. The Text IISAM can read the format automatically from most character-delimited files. You can use any single character as a delimiter in the file except the double quotation mark ("). The Format setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis. The following table lists the valid values for the Format option.

Format specifier Table format Schema.ini Format statement

Tab Delimited Fields in the file are delimited by tabs. Format=TabDelimited

CSV Delimited Fields in the file are delimited by commas Format=CSVDelimited

Custom Delimited User specifies character (no double quotes) Format=Delimited(custom character)

Fixed Length Fields in the file are of a fixed length. Format=FixedLength

Specifying the Fields

You can specify field names in a character-delimited text file in two ways:

Include the field names in the first row of the table and set ColNameHeader to True.

Specify each column by number and designate the column name and data type.

You must specify each column by number and designate the column name, data type, and width for fixed-length files.

Note The ColNameHeader setting in Schema.ini overrides the FirstRowHasNames setting in the Windows Registry on a file-by-file basis.

The data types of the fields can also be determined. Use the MaxScanRows option to indicate how many rows should be scanned when determining the column types. If you set MaxScanRows to 0, the entire file is scanned. The MaxScanRows setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis.

The following entry indicates that Microsoft Jet should use the data in the first row of the table to determine field names and should examine the entire file to determine the data types used:

  ColNameHeader=True

  MaxScanRows=0

The next entry designates fields in a table by using the column number (Col n) option, which is optional for character-delimited files and required for fixed-length files. The example shows the Schema.ini entries for two fields, a 10-character CustomerNumber text field and a 30-character CustomerName text field:

  Col1=CustomerNumber Text Width 10

  Col2=CustomerName Text Width 30

The syntax of Col n is:

  Coln=ColumnName type [Width #]

The following table describes each part of the Col n entry.

Parameter Description

ColumnName The text name of the column. Enclose in double quotes for name with embedded spaces.

Type Bit, Byte, Short, Long, Currency, Single, Double, DateTime, Text, Memo, Date [date format]

Width Designates the width of the column in the next field (required for fixed-length files).

# The integer value that designates the width of the column (required if Width is specified).

Selecting a Character Set

You can select from two character sets: ANSI and OEM. The CharacterSet setting in Schema.ini overrides the setting in the Windows Registry on a file-by-file basis. The following example shows the Schema.ini entry that sets the character set to ANSI:

CharacterSet=ANSI

Specifying Data Type Formats and Conversions

The Schema.ini file contains a number of options that you can use to specify how data is converted or displayed. The following list shows these options.

DateTimeFormat

Can be set to a format string indicating dates and times. You should specify this entry if all date/time fields in the import/export are handled with the same format. All Microsoft Jet formats except A.M. and P.M. are supported. In the absence of a format string, the Windows Control Panel short date picture and time options are used.

DecimalSymbol

Can be set to any single character that is used to separate the integer from the fractional part of a number.

NumberDigits

Indicates the number of decimal digits in the fractional portion of a number.

NumberLeadingZeros

Specifies whether a decimal value less than 1 and greater than -1 should contain leading zeros; this value can either be False (no leading zeros) or True.

CurrencySymbol

Indicates the currency symbol to be used for currency values in the text file. Examples include the dollar sign ($) and Dm.

CurrencyPosFormat

Can be set to any of the following values:

Currency symbol prefix with no separation ($1)

Currency symbol suffix with no separation (1$)

Currency symbol prefix with one character separation ($ 1)

Currency symbol suffix with one character separation (1 $)

CurrencyDigits

Specifies the number of digits used for the fractional part of a currency amount.

CurrencyNegFormat

Can be one of the following values:

($1)

-$1

$-1

$1-

(1$)

-1$

1-$

1$-

-1 $

-$ 1

1 $-

$ 1-

$ -1

1- $

($ 1)

(1 $)

This example shows the dollar sign, but you should replace it with the appropriate CurrencySymbol value in the actual program.

CurrencyThousandSymbol

Indicates the single-character symbol to be used for separating currency values in the text file by thousands.

CurrencyDecimalSymbol

Can be set to any single character that is used to separate the whole from the fractional part of a currency amount.

Note If you omit an entry, the default value in the Windows Control Panel is used.