Using ODBC With RDM Server

When you retrieve data using ODBC, you are accessing the database outside of the Collect! interface. The names of fields are actual database names. Dates are shown in their raw format. Several settings, stored in flags, are represented with a numeric bit switch value. Additional information, such as notes, is stored in binary "blobs."

This document is intended to help you parse the data and use it in whatever way you need.

Date Formatting

Dates are shown in their raw format. The example below is taken from an MS Access table linked to the debtor record in the d01_collect database.

Example of Date Fields

1040905 will be the raw data. This date represents 09/05/2004 (MM/DD/YYYY)

The format of dates is as follows:

You add 1900 to the first two (or three) numbers to get the correct yyyy year.

• yymmdd for years in the 1900's
  
  Example:
  990729 represents July 29, 1999
  (99+1900 = 1999)



• 1yymmdd for years in the 2000's
  
  Example:
  1040905 represents September 5, 2004
  (104+1900=2004)

Add 19000000 to the entire number to put the year in a yyyy format. Then parse the string using your ODBC interface's date functions.

• yymmdd for years in the 1900's
  
  Example:
  990729 represents July 29, 1999
  (990729+19000000 = 19990729)



• 1yymmdd for years in the 2000's
  
  Example:
  1040905 represents September 5, 2004
  (1040905+19000000=20040905)

This should help you parse these dates with any of the programs you are using to develop your ODBC interface.

Time Formatting

As shown below in the Date/Time Stamp, the time is in 24 hour format with minutes and seconds displayed.

134754 indicates 1:47:54 P.M.
91053 indicates 9:10:53 A.M.

Date/Time Stamp

Several fields in the Debtor record are updated whenever a record is modified. You can use these as a date/time stamp. Shown below in MS Access, the fields are

de_modified_date
de_modified_time
de_modified_by

Date/Time Stamp Fields

Bit Switches

In Collect! there are many places where settings are stored in "bit switches." For instance, the setting Omit From Client Statement is stored in the Transaction record in a database field called tr_flags. It is necessary to parse the value found in these flag fields to determine the whether the setting is ON or OFF.

There are two types of bit switches where these settings are stored. If the switch is Exclusive, the value stored in the field is the actual binary value of the switch.

When the switch is Inclusive, the value in the field is a cumulative value of all the settings stored in that flag and switched ON. In these cases, it is necessary to use conditional expressions in your reports to determine the actual value of the setting in question.

Details of these settings are available to licensed users of CV11. Please request Database Schema Docs from Collect! Technical Services at support@collectsoftware.cc if you qualify as a licensed user of CV11.

See Also

- ODBC Topics