Index  Up  >>  

SQL support via DBI

The configuration of the DBI database is done 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, you can instead 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, you would typically 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 just fine. (The minivend database must already exist, of course.)

This is the same as the DBI_DSN environment variable -- if you don't set the DSN parameter, then the value of DBI_DSN will be used to try and find the proper database to connect to.

USER
The user name you log into the database with -- same as the environment variable DBI_USER. If you don't need a user name, just don't set the USER directive.

PASS
The password you log into the database with -- same as the environment variable DBI_PASS. If you don't need a password, 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 MiniVend fields should be of the fixed-length character type, something like char(128). In fact that is the default if you do not choose a type for a column. You can have as many lines as needed. This is not a DBI parameter, it is specific to MiniVend.

NAME
A space-separated field of column names for a table. Normally not used -- MiniVend should resolve the column names properly upon query. Set this if your 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 MiniVend. All columns must be listed, in order of their position in the table.

NUMERIC
Tells MiniVend to not quote values for this field; allows numeric data types for SQL databases. Placed as a comma-separated field of column names for a table, in no particular order. This should be defined if you are to use an numeric value, as many DBD drivers do not yet support type queries.

UPPERCASE
Tells MiniVend 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 MiniVend delimiter type - one of TAB,CSV,PIPE,%%,LINE or the corresponding numeric type. The default for SQL databases is TAB -- use DELIMITER if you wish to import another type. This is not a DBI parameter, it is specific to MiniVend.

KEY
You can change the keying default of code in the first column of the database with the KEY directive. Don't use this unless you know exactly what you are doing and are 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 MiniVend 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 MiniVend 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 Minivend 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 NUL 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

You must have mySQL, DBI, and DBD::mysql completely installed and tested, and have created the database minivend for this to work. Permissions are difficult on mySQL -- if you have 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 your 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 and appropriate ODBC driver must be installed and tested.

Index  Up  >>