DirList2ODBC - General Information
Introduction
The DirList2ODBC driver enables easy access to DirList2 databases by translating standard SQL statements into the numbers and letters that the DirList2 server's web interface understands. With this functionality, one can use ODBC-compliant applications such as Microsoft Access to work with DirList2 databases.
Because SQL semantics are quite different from DirList2 server's, considerable effort was put into DirList2ODBC's development. With this driver, everything in the DirList2 database system becomes a collection of tables and fields, allowing you to use the DirList2 system as if it were a generic database system.
Here are the tables that DirList2ODBC provides to ODBC clients as well as the equivalent DirList2 system object:
DirList2ODBC Table DirList2 Server Object tblDirEntry Directory entries tblClass Classifications tblClassValue Classifications tblClassData Directory entries tblSortTable Sorting tables tblIndexInfo Global variables tblUser User database For more information on what the server objects represent, please refer to the DirList2 Server documentation.
Each table has its own set of SQL statements that it supports for ODBC clients. Here is a list of tables and their supported SQL statements (items in brackets [] are optional and can be left out of the statement if desired):
Table SQL Statements Clauses Notes tblDirEntry SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblDirEntry [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] NOT is not supported right before an opening parenthesis _ _ [ORDER BY field1, field2, ...] The fields cannot be literals _ INSERT INTO tblDirEntry [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblDirEntry [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition NOT is not supported right before an opening parenthesis _ DELETE [*] _ _ _ FROM tblDirEntry _ _ _ [IN "index"] _ _ _ WHERE condition NOT is not supported right before an opening parenthesis tblClass SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblClass [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] Condition can only involve ClassID; parenthesis are ignored _ INSERT INTO tblClass [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblClass [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition The condition can only involve ClassID; parenthesis are ignored _ DELETE [*] _ _ _ FROM tblClass _ _ _ [IN "index"] _ _ _ WHERE condition The condition can only involve ClassID; parenthesis are ignored tblClassData SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblClassData [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] Condition can only involve ClassID, UserName, or both; parenthesis are ignored _ INSERT INTO tblClassData [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblClassData [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition The condition can only involve ClassID, UserName, or both; parenthesis are ignored _ DELETE [*] _ _ _ FROM tblClassData _ _ _ [IN "index"] _ _ _ WHERE condition The condition can only involve ClassID, UserName, or both; parenthesis are ignored tblClassValue SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblClassValue [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] Condition can only involve ClassID, ValueID, or both; parenthesis are ignored _ INSERT INTO tblClassValue [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblClassValue [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition The condition can only involve ClassID, ValueID, or both; parenthesis are ignored _ DELETE [*] _ _ _ FROM tblClassValue _ _ _ [IN "index"] _ _ _ WHERE condition The condition can only involve ClassID, ValueID, or both; parenthesis are ignored tblSortTable SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblSortTable [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] Condition can only involve SortTableID; parenthesis are ignored _ INSERT INTO tblSortTable [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblSortTable [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition The condition can only involve SortTableID; parenthesis are ignored _ DELETE [*] _ _ _ FROM tblSortTable _ _ _ [IN "index"] _ _ _ WHERE condition The condition can only involve SortTableID; parenthesis are ignored tblUser SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblUser [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] Condition can only involve UserID; parenthesis are ignored _ INSERT INTO tblUser [(field1, field2, ...)] _ _ _ [IN "index"] _ _ _ VALUES (value1, value2, ...) All values must be literals _ UPDATE tblUser [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ WHERE condition The condition can only involve UserID; parenthesis are ignored _ DELETE [*] _ _ _ FROM tblUser __ _ _ [IN "index"] _ _ _ WHERE condition The condition can only involve UserID; parenthesis are ignored tblIndexInfo SELECT field1 [AS alias1], ... Field qualification is optional; use * to select all fields _ _ FROM tblIndexInfo [AS] [alias] _ _ _ [IN "index"] _ _ _ [WHERE condition] This clause, if specified, is completely ignored; this table only has one row _ UPDATE tblIndexInfo [IN "index"] _ _ _ SET field1=value1, ... All values must be literals; conditions and field names are not supported as values _ _ [WHERE condition] This clause, if specified, is completely ignored; this table only has one row
While the IN clause is always optional, it can be used if you want to access an index other than the one you're logged into. Note, however, that the same credentials that you used to login to the current index will be used to access the index that you specified in the IN clause. If you don't have sufficient access rights in that index, the server will return an appropriate error to the driver.
When you want to change your current session's administrative mode, use the following DirList2ODBC-specific statement in SQLExecDirect or SQLPrepare/SQLExecute:
Statement Parameters Notes ADMINMODE 0 Disable the administrative mode for this recordset (default if you do not have the CanViewHiddenEntries priviledge) 1 Enable the administrative mode for this recordset (default if you have the CanViewHiddenEntries priviledge, fails if you do not have CanViewHiddenEntries) Release Note on String Searches:
When specifying strings for search criteria, equal-to, not equal-to, less than [or equal to], greater than [or equal to], and LIKE (including ESCAPE) are supported. At this time, only equal-to is supported in tables other than tblDirEntry.
Here is a list of the tables and their fields, with the primary keys in bold:
Table Field Description tblDirEntry UserName Username on the host system FirstName First name LastName Last name Exclude Exclude user from all non-administrative listings Exists User's home directory exists on the host system CheckName Check the host system's user database for the user's first and last names on the next update CheckWebSite Check the user's web site on the next update HasWebSite The user currently has a valid web site WebSite Web site address HasEMailAddress The user has an active e-mail address EMailAddress E-Mail address HasTelephone The user has a telephone Telephone Telephone number tblClass ClassID Unique identifier for this classification Name Name HasDefaultValue This classification has a default value DefaultValue The default value tblClassData ClassID Classification identifier UserName Username that this row applies to ValueID Value for this classification for this user HasOtherValue None of the predefined values for this class apply to this user OtherValue The other value if HasOtherValue is set tblClassValue ValueID Unique identifier for this value within this classification ClassID Classification identifier Value The string representation of this value tblSortTable SortTableID Unique identifier for this sort table Order The fields, order, and orientations for this sort table NumEntries Current number of entries in the sort table (usually equal to the number of rows in tblDirEntry) tblUser UserID Unique identifier for this database user Name Name Password Password Description Description of this user UserLevel Level of user edit ability SearchLevel Search rights based on a predefined list in the server Audit Audit the actions of this user CanViewHiddenEntries User has administrative access to tblDirEntry CanGetEntries Read access to tblDirEntry and tblClassData CanModifyEntries Write access to tblDirEntry and tblClassData CanDeleteEntries Delete access to tblDirEntry and tblClassData CanGetClasses Read access to tblClass and tblClassValue CanModifyClasses Write access to tblClass and tblClassValue CanDeleteClasses Delete access to tblClass and tblClassValue CanGetSortTables Read access to tblSortTable CanModifySortTables Write access to tblSortTable CanDeleteSortTables Delete access to tblSortTable CanGetIndexInfo Read access to tblIndexInfo CanModifyIndexInfo Write access to tblIndexInfo CanCreateIndex Can create new databases CanDeleteIndex Can delete the database CanLock Can lock the database (required for successful write access in a future release of DirList2ODBC) CanGetUsers Read access to tblUser CanModifyUsers Write access to tblUser CanDeleteUsers Delete access to tblUser CanNoOp User can ping the server and/or log in. The server specifically ignores CanNoOp during authentication AuditGetEntries Audit reads on tblDirEntry and tblClassData AuditModifyEntries Audit writes on tblDirEntry and tblClassData AuditDeleteEntries Audit deletes on tblDirEntry and tblClassData AuditGetClasses Audit reads on tblClass and tblClassValue AuditModifyClasses Audit writes on tblClass and tblClassValue AuditDeleteClasses Audit deletes on tblClass and tblClassValue AuditGetSortTables Audit reads on tblSortTable AuditModifySortTables Audit writes on tblSortTable AuditDeleteSortTables Audit deletes on tblSortTable AuditGetIndexInfo Audit reads on tblIndexInfo AuditModifyIndexInfo Audit writes on tblIndexInfo AuditCreateIndex Audit index creations AuditDeleteIndex Audit the index deletion (after this, the database has to be recreated in theory for further audits on this database) AuditLocking Audit locking (not recommended due to the number of messages involved when it will be implemented) AuditGetUsers Audit reads on tblUser AuditModifyUsers Audit writes on tblUser AuditDeleteUsers Audit deletes on tblUser AuditNoOp Audit pings and logins (this can generate a lot of messages!) tblIndexInfo Version Minor version of the index file LowestVersion Lowest minor version of DirList2 Server that the index file requires HasSequenceNumber File maintains sequence numbers (always enabled on DirList2 Server 2.1 and higher) SequenceNumber The sequence number of the file (basically the file's internal version; it is incremented on each write and delete operation to the database); it can also be helpful for ordering multiple backups HasLocking Locking capabilities are enabled for the database EncryptFile Actual database file on the server is encrypted Locked Database is currently locked Audit Enable auditing for the database AuditEMail The e-mail address to send audit messages to when auditing is enabled WebDocsDir The subdirectory to poke around when looking for web sites within users' home directories BaseDir Base directory upon which the users' home directories appear in CachePasswd Cache the /etc/passwd file for fast name lookups. Note that this only works on servers compiled for UN*X platforms, and only on versions 2.4 and higher (this flag is stored correctly in previous versions but has no effect) HasEMailDomain Use a default e-mail address domain when automatically creating new records during index updates. This requires DirList2 Server 2.6 to have a meaning (this flag is stored correctly in previous versions but has no effect) EMailDomain Use this e-mail address domain when automatically creating new records during index updates. This value is not stored with any versions of DirList2 Server below 2.6 HasDefaultURL Automatically redirect to a URL if no index file is specified in the web query string or if the query string is simply blank. This requires DirList2 Server 2.6 to have a meaning (this flag is stored correctly in previous versions but has no effect) DefaultURL Redirect to this URL if no index file is specified in the web query string or if the query string is simply blank. This value only means something when in the dirlist_indexes/users.idx2 file, since that's what dirlist2.cgi looks for when it needs a default URL. This value is not stored with any versions of DirList2 Server below 2.6 While tblIndexInfo does not have a primary key, the setup instructions for the generic Access database require that you set something such as EncryptFile as the primary key so that Access can create updatable recordsets.
Here is a list of the data types that DirList2ODBC can work with:
SQL Type Internal DirList2 Type C Data Types BIT* BOOLEAN BIT* CHAR STRING CHAR VARCHAR STRING CHAR LONG VARCHAR* STRING CHAR* INTEGER* NUMERIC LONG, SLONG, and ULONG* TINYINT NUMERIC TINYINT, STINYINT, and UTINYINT SMALLINT NUMERIC SHORT, SSHORT, and USHORT * Native data type in the actual data stored by DirList2 Server.
Note that every data type above can be converted into any data type listed above with no restrictions except that negative values are not supported when converting strings to signed numbers. If a data type was not listed above, then it is not supported.
When connecting to a data source using SQLDriverConnect or SQLBrowseConnect, you must specify a DSN parameter. Here is a list of parameters that SQLDriverConnect, SQLBrowseConnect, and ConfigDSN support:
Parameter Description DSN Data source name to connect to. This is the only required parameter when connecting to a data source. DESC Description of the data source. This is ignored when connecting to a data source. UID Username to login as. This value corresponds with a Name field in tblUser in valid logins. PWD Password for the user. This value corresponds with the Password field in tblUser for the user specified in the UID parameter. DATABASE The index file to use on the server. SERVER The hostname or TCP/IP address of the server to connect to. PORT The TCP/IP port to connect to on the server. This is usually 80, the standard port for web servers. PATH Path to the dirlist2.cgi program. This is usually /cgi-bin/dirlist2.cgi Passing these parameters to ConfigDSN will set the default values to those you specify. This can be useful when writing a customized installer for a database product. All persistent values used by DirList2ODBC are stored in each DSN's key in the system registry. If you want to make a quick connection and don't care about the other parameters besides DSN, use SQLConnect.
This driver conforms to ODBC 2.0 Level 1 and implements many Level 2 functions such as SQLExtendedFetch.
The SQL engine conforms closely to the core SQL89 standard, but many features such as the entire set of DDL statements are not implemented because their functions are not consistent with DirList2's design.
Copyright (c) 2000 Steven Lawrance
Copyright (c) 1999-2000 Bryant College
Written by Steven Lawrance
DirList2 Server Version: 2.6
DirList2ODBC Version: 1.51
Last Revision: 10-08-2000