2.1. SQL Support via DBI

The configuration of the DBI database is accomplished by setting attributes in additional Database directives after the initial defining line as described above. For example, the following defines the database arbitrary as a DBI database, sets the data source (DSN) to an appropriate value for an mSQL database named minivend on port 1114 of the local machine:

   Database arbitrary arbitrary.asc SQL
   Database arbitrary DSN           dbi:mSQL:minivend:localhost:1114

As a shorthand method, include the DSN as the type:

   Database arbitrary arbitrary.asc dbi:mSQL:minivend:localhost:1114

Supported configuration attributes include (but are not limited to):

DSN

A specification of the DBI driver and its data source. To use the DBD::mSQL driver for DBI, use:

            dbi:mSQL:minivend:othermachine.my.com:1112

where mSQL selects the driver (case IS important), minivend selects the database, othermachine.my.com selects the host, and 1112 is the port. On many systems, dbi:mSQL:minivend will work fine. Of course, the minivend database must already exist.
This is the same as the DBI_DSN environment variable, if the DSN parameter is not set. Then, the value of DBI_DSN will be used to try and find the proper database to connect to.

USER

The user name used to log into the database. It is the same as the environment variable DBI_USER. If a user name is not needed, just don't set the USER directive.

PASS

The password used to log into the database. It is the same as the environment variable DBI_PASS. If a password is not needed, just don't set the PASS directive.

COLUMN_DEF

A comma-separated set of lines in the form NAME=TYPE(N), where NAME is the name of the field/column, TYPE is the SQL data type reference, and N is the length (if needed). Most Interchange fields should be the fixed-length character type, something like char(128). In fact, this is the default if a type is not chosen for a column. There can be as many lines as needed. This is not a DBI parameter, it is specific to Interchange.

NAME

A space-separated field of column names for a table. Normally not used. Interchange should resolve the column names properly upon query. Set this if a catalog errors out with "dbi: can't find field names" or the like. The first field should always be code. This is not a DBI parameter, it is specific to Interchange. All columns must be listed, in order of their position in the table.

NUMERIC

Tells Interchange not to quote values for this field. It allows numeric data types for SQL databases. It is placed as a comma-separated field of column names for a table, in no particular order. This should be defined if a numeric value is used because many DBD drivers do not yet support type queries.

UPPERCASE

Tells Interchange to force field names to UPPER case for row accesses using the [item-data ...], [loop-data ...], [item-field ..., etc. Typically used for Oracle and some other SQL implementations.

DELIMITER

A Interchange delimiter type, either TAB,CSV,PIPE,%%,LINE or the corresponding numeric type. The default for SQL databases is TAB. Use DELIMITER if another type will be used to import. This is not a DBI parameter. It is specific to Interchange.

KEY

The keying default of code in the first column of the database can be changed with the KEY directive. Don't use this unless prepared to alter all searches, imports, and exports accordingly. It is best to just accept the default and make the first column the key for any Interchange database.

ChopBlanks, LongReadLen, LongTruncOK, RaiseError, etc.

Sets the corresponding DBI attribute. Of particular interest is ChopBlanks, which should be set on drivers which by default return space-padded fixed-length character fields (Solid is an example).
The supported list as of this release of Interchange is:

          ChopBlanks
          CompatMode
          LongReadLen
          LongTruncOk
          PrintError
          RaiseError
          Warn

Issue the shell command perldoc DBI for more information.

Here is an example of a completely set up DBI database on MySQL, using a comma-separated value input, setting the DBI attribute LongReadLen to retrieve an entire field, and changing some field definitions from the default char(128):

 Database   products  products.csv  dbi:mysql:minivend
 Database   products  USER          minivend
 Database   products  PASS          nevairbe
 Database   products  DELIMITER     CSV

 # Set a DBI attribute
 Database   products  LongReadLen   128

 # change some fields from the default field type of char(128)
 # Only applies if Interchange is importing from ASCII file
 # If you set a field to a numeric type, you must set the
 # NUMERIC attribute
 Database   products  COLUMN_DEF    "code=char(20) NOT NULL primary key"
 Database   products  COLUMN_DEF    price=float, discount=float
 Database   products  COLUMN_DEF    author=char(40), title=char(64)
 Database   products  COLUMN_DEF    nontaxable=char(3)
 Database   products  NUMERIC       price
 Database   products  NUMERIC       discount

MySQL, DBI, and DBD::mysql must be completely installed and tested, and have created the database minivend, for this to work. Permissions are difficult on MySQL. if having trouble, try starting the MySQL daemon with safe_mysqld --skip-grant-tables & for testing purposes.

To change to ODBC, the only changes required might be:

   Database products  DSN         dbi:ODBC:TCP/IP localhost 1313
   Database products  ChopBlanks  1

The DSN setting is specific to a ODBC setup. The ChopBlanks setting takes care of the space-padding in Solid and some other databases. It is not specific to ODBC. Once again, DBI, DBD::ODBC, and the appropriate ODBC driver must be installed and tested.