Microsoft Excel Datasources

Datasource properties should not be modified unless absolutely necessary as it can have dire effects on the Xephr® to database connections and performance.

Creating Microsoft® Excel Datasources

1. In the Explorer Tree, select the Datasource Information object.  

2. There are options to creating a datasource at this point.

• Right-click on the Datasource Information object and left-click on the New option in the pop-up menu.  Then left-click on the Datasource option.

• Left-click on the [Create Datasource] button in the Main Toolbar.

• Left-click on the File Menu.  Left-click on the New option. Left-click on the Datasource option.

• Press Alt+D on the keyboard.

3. The Create New Datasource screen will be displayed.

4. In the Category pop-list, select Database Datasource.

5. In the Type property, enter the database type for this datasource.  You cannot change this once the datasource is created.  Select Microsoft Access (via DSN-less ODBC).
   Choices are:
   IBM DB2
   Microsoft SQL Server
   Oracle 9i
   PostgreSQL
   Microsoft Excel (via DSN-less ODBC)
   Microsoft Access (via DSN-less ODBC)
   Generic ODBC (using DSN)

6. In the Name property, enter the name for the datasource.

7. In the Description property, enter a description for the datasource.

8. In the User property, enter the user id used to log into the database for this datasource.

9. In the Password property, enter the password used to log into the database for this datasource and user id.  The password will be hidden as a series of fourteen asterisks.  

10. In the Max Active property, enter the total number of connections that can be created in the connection pool.

11. In the Read Only property, select true or false.  When set to true, transactions can be read from the database, but not written to the database.  When set to false, transactions can be both read from and written to the database.

12. In the Default Catalog property, enter the catalog name that you wish to access in this datasource.

13. In the Full Path File Name property, enter the file name for the Microsoft Excel file, including the directory path to access the file from the Xephr server.

14. In the Default Directory property, enter the default directory for the datasource.

15. Click on the Ok button.  The property sheet will be opened for you to modify the remaining datasource properties.  

Editing Microsoft Excel Datasources

1. To edit an existing datasource, expand the Datasource Information section of the Explorer Tree and left-click on the desired datasource.

2. In the Name property, enter the name for the datasource.  This value was originally entered during datasource creation.

3. In the Description property, enter the description for the datasource.  This value was originally entered during datasource creation.

4. In the Database Type property, the datasource selected during creation is displayed and cannot be changed.

5. In the User property, enter the user id used to log into the database for this datasource.

6. In the Password property, enter the password used to log into the database for this datasource and user id.  the password will be hidden as a series of fourteen asterisks.

7. In the Max Active property, enter the total number of connections that can be created in the connection pool.

8. In the Max Idle property, enter the total number of idle connections that can exist in the connection pool.

9. In the Max Wait property, enter the length of time to wait for a connection in milliseconds.

10. Set the Test on Borrow property to true or false.  When set to true, when a connection is borrowed from the connection pool, it is tested to make sure it is a valid connection.  When set to false, it will not be tested.  When set to true, this property may have some impact on performance, but it verifies that the database connections are always valid.

11. Set the Test on Return property to true or false.  When set to true, when a connection is returned to the connection pool, it is tested to make sure it is a valid connection.  When set to false, it will not be tested.  When set to true, this property may have some impact on performance, but it verifies that the database connections are always valid.

12. Set the Test While Idle property to true or false.  When set to true, when a connection is idle in the connection pool, it is tested to make sure it is a valid connection.  When set to false, it will not be tested.  When set to true, this property may have some impact on performance, but it verifies that the database connections are always valid.

13. In the Tests Per Eviction Run property, enter the number of connections that can be tested for eviction each time an eviction run occurs.  This is the maximum number of connections that can be removed from the connection pool each time the eviction run occurs.

14. In the Min Evictable Idle Time property, enter the number of milliseconds for which a connection can be idle before it can be considered for eviction from the connection pool.

15. In the Time Between Eviction Runs property, enter the number of milliseconds between each eviction run.  During an eviction run, the connection pool is tested for idle connections.  The number of connections discarded is up to the number defined in the tests per eviction run property.

16. Set the Read Only property to true or false.  When set to true, transactions can be read from the database, but not written to the database.  When set to false, transactions can be both read from and written to the database.

17. In the Default Catalog property, enter the catalog name that you wish to access in this datasource.

18. In the Isolation Level property, select the isolation level for the datasource.  The following choices are available:

• No Transaction Isolation: Transactions are not supported.  It is recommended that this option NEVER be selected.

• Read Committed: Dirty reads are prevented.  Non-repeatable reads and phantom reads can occur.

• Read Uncommitted: Dirty reads, non-repeatable reads, and phantom reads can occur.

• Repeatable Read: Dirty reads and non-repeatable reads are prevented.  Phantom reads can occur.

• Serializable: Dirty reads, non-repeatable reads, and phantom reads are prevented.

19. In the Allowed Table Query property, enter the select statement used to populate the list of tables available when creating a new block.  

20. In the Column Names Query property, enter the select statement used to select the column information for a table.  This is used to populate the selection list for simple query blocks for this datasource.  For example:
    
    SELECT c.name AS column_name, UPPER( t.name) AS data_type, c.length AS data_length, c.xprec AS data_precision, c.xscale AS data_scale, CASE c.isnullable WHEN 1 THEN 'Y' ELSE 'N' END AS nullable FROM sysobjects o, syscolumns c, systypes t WHERE o.name = ? AND c.id = o.id AND t.xtype = c.xtype ORDER BY column_name

21. In the Incoming Date Format property, enter the format in java in which the dates are stored in this datasource.  See the Format Masks section for choices.  Xephr expects dates returned from the datasource to be in this format.
    
    For example, if this property were set to yyyy-MM-dd then May 20th, 2007 would be stored as 2007-05-20.

22. In the Incoming Time Format property, enter the format in java in which the times are stored in this datasource. See the Format Masks section for choices.  Xephr expects times returned from the datasource to be in this format.
    
    For example, if this property were set to HH:mm:ss then, one thirty pm would be stored as 13:30:00.

23. In the Incoming Date/Time Format property, enter the format in java in which date and times are stored in this database, if they are in a field that combines the two.  See the Format Masks section for choices.  Xephr expects dates and times returned from the datasource to be in this format.
    
    For example, if this property were set to yyyv-MM-dd HH:mm:ss, then May 20th, 2007 at one thirty pm would be stored as 2007-05-20 13:30:00.

24. In the Outgoing Date Function property, enter the database specific code to format a date to pass it back to the datasource.  The values surrounded by the $$ is the java date format to use in the code to format the dates being passed to the database.
    
    The code within this property is used to convert the data in Date fields to be accepted by the datasource.

25. In the Outgoing Time Function property, enter the database specific code to format a time to pass it back to the datasource.  The values surrounded by the $$ is the java time format to use in the code to format the time being passed to the database.
    
    The code within this property is used to convert the data in Time fields to be accepted by the datasource.

26. In the Outgoing Date/Time Function property, enter the database specific code to format a date and time combined to pass it back to the datasource.  The values surrounded by the $$ is the java date and time format to use in the code to format the dates and times being passed to the database.
    
    The code within this property is used to convert the data in Date and Time fields to be accepted by the datasource.

27. In the Ignore Columns property, enter the names of the columns to be excluded from selection in tables when the column names query is executed during block creation.  Separate column names by commas.  These columns will be completely ignored by Xephr.

28. In the Passthrough Columns property, enter the names of columns to be excluded from selection in tables when the column names query is executed during block creation, but which will be passed through during transactions.  These are columns used for transparent record versioning schemes.

29. In the Path to File property, enter the file name for the Microsoft Excel file, including the directory path to access the file from the Xephr server.

30. In the Default Directory property, enter the default directory for the datasource.

31. In the Created on property, the date and time on which the datasource was created are displayed and cannot be changed.

32. In the Modified on property, the date and time on which the datasource was last modified are displayed and cannot be changed.

33. Press the Save button to save the changes to the datasource.

Xephr is a registered trademark of NDS Systems, LC.

Microsoft is a registered trademark of Microsoft Corporation.

Copyright © 2007 NDS Systems LC.