MiniVend now provides complete external
SQL database support via the Perl
DBI and
DBD modules. This allows transparent access to any database engine that is supported by a
DBD module. The current list includes mSQL, mySQL, Solid, Postgres, Oracle, Sybase, Informix, Ingres, Qbase,
DB2, Fulcrum, and others. Any
ODBC (with appropriate driver) should also be supported.
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 must be defined if you are to use an numeric value, as
DBI does not yet have standard type queries.
- DELIMITER
-
A MiniVend delimiter type - one of
TAB,CSV,PIPE,%%,LINE or the corresponding numeric type. It can also be a custom delimiter as specified with
FieldDelimiter
and RecordDelimiter. The default for
SQL (and Msql) 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 release of MiniVend 3.02 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:localhost:3333
Database products USER mike
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 price=float, code=char(20), discount=float
Database products COLUMN_DEF author=char(40), title=char(64)
Database products COLUMN_DEF nontaxable=char(3)
Database products NUMERIC price, 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.
|
