Laserfiche WebLink
<br /> Appendix – Generic Database Data s tore - 1 <br /> <br />Appendix: Generic Database Data s tore <br />20 1 2 -1 1 -09 <br /> <br />Overview <br /> <br />The generic database datastore can be used to provide general access to database tables and views, for <br />example with the ReadTableFromDataStore() command. The trade -off is that although tables an d <br />views can be accessed, there is no application programming interface (API) to deal with the intricacies of <br />the database and converting tables to more complex data objects like time series . Consequently, <br />subsequent processing must operate on the returned tables . The TableToTimeSeries() command is <br />available to convert a table into time series. <br /> <br />The datastore internally corresponds to an Open Database Connectivity (ODBC) connection. The <br />connection can be defined one of two ways: <br /> <br />• Define an ODBC connection using Windows tools. The advantage of this approach is that <br />database authentication occurs through the ODBC connection. The disadvantage is that the <br />conne ction may use a generic database driver that does not perform as well as vendor drivers. <br />This appr oach is used when the DatabaseEngine and OdbcName configuration propert ies <br />are defined for the datastore . <br />• Provide connection information via DatabaseEngine , DatabaseServer , <br />DatabaseName , and potentially login configuration properties , and allow the softwar e to use a <br />vendor -specific JDBC (Java Database Connectivity) driver, which is generally optimized for the <br />database software. The dis advantage of this approach is that advanced authentication interfaces <br />have not been implemented (this may or not be an issu e depending on the security enabled for the <br />database). <br /> <br />Limitations <br /> <br />The following limitations app ly to the generic database data store: <br /> <br />• Database permissions control which tables and views are accessible and consequently protected <br />tables may not be visible in software or may generate errors if attempts are made to manipulate <br />outside of permissions . <br />• An attempt is made in the ReadTableFromDataStore() command to list tables and views <br />for selection . However, the ability to filter out system tables is limited b ecause some database <br />drivers do not implement required functionality. For example, the SQL Server JDBC driver does <br />not allow generic filtering of system tables and a work -around has been implemented to remove <br />known system table and view names from lists d isplayed to users. <br />• Table column properties in TSTool are de termined from database column metadata. Although <br />support for common data types has been implemented, some data types may not be fully <br />supported. If a database column type is not supported, the de fault is to translate the column data <br />to strings in the output table. <br />• Although database column properties can specify the width and precision for floating point data, <br />some database metadata is inaccessible, causing data -handling or visualization issues. F or <br />example, the SQL Server metadata defaults result in the precision of floating point numbers <br />(called “precision” in TSTool and “scale” in SQL Server column properties) to be set to zero. <br />27