Tutorial: Using xfodbcusr.c as an example

This tutorial guides you through the steps needed to create user-defined data routines. As you follow the tutorial, you’ll modify xfodbcusr.c to include routines that do the following:

Convert addresses to mixed-case characters as they’re input and all uppercase characters as they’re retrieved by an application.
Change dates to the fifteenth of the month as they’re output, and the first of the month as they’re written by an application.
Store customer limits for Oregon customers to $2000.75, no matter what number you enter, and add $200.00 to customer limits for Oregon customers as the limit is retrieved from the database.

Once you’ve modified the code in xfodbcusr.c, you’ll compile and link the modified file as xfodbcusr.dll (Windows), XFODBCUSR.so (UNIX), or xfodbcusr_so.exe (OpenVMS). You can then watch it work as you use xfODBC to read and write data to the sample database supplied with the xfODBC installation.

Note that these routines are designed to work only with the sample database that’s included with the xfODBC installation. Like all user-defined data routines, they can’t change the size of a field, only the contents.

Open Repository and do the following:

Set the xfODBC sample database as the current repository. The repository files for the sample database are located in the connect\synodbc\dict subdirectory of the main Synergy/DE installation directory.
Change the CUST_STREET field in the CUSTOMERS structure from type alpha to type user, and set the class to alpha.
Change the CUST_LIMIT field in the CUSTOMERS structure from type decimal to type user, and set the class to numeric.
Change the OR_ODATE field in the ORDERS structure from type date to type user, and set the class to date.
Save your changes as you exit Repository.

For help with any of the tasks in this step, see the Repository User’s Guide.

Run dbcreate with the -x or -c option (overwrite or create). Either option will work. For information on dbcreate options, see dbcreate utility.

Open xfodbcusr.c in a text editor. (You’ll find this file in the connect\synodbc\user subdirectory of the main Synergy/DE installation directory.) Remove or comment out the #ifdef and #endif lines from all four of the functions: user_to_alpha(), user_to_number(), alpha_to_user(), and number_to_user(). For client/server configurations, uncomment the section for your host machine’s operating system. Save your changes and close the file.

Run one of the following (located in the connect\synodbc\user subdirectory of the main Synergy/DE installation directory):

makeusr.bat on Windows
makeusr on UNIX
makeusr.com on OpenVMS

These files compile xfodbcusr.c, link it, and save it as one of the following:

xfodbcusr.dll on Windows
XFODBCUSR.so on UNIX
xfodbcusr_so.exe on OpenVMS

To compile and link using Microsoft Visual Studio, create a new DLL, add xfodbcusr.c, and then build.

Copy the DLL, shared library, or shared image you just created to the connect subdirectory of the main Synergy/DE installation directory. This will overwrite the file of the same name that’s already there. For client/server configurations, copy the file to the connect subdirectory of the main Synergy/DE installation directory on the server.

On OpenVMS, the XFODBCUSR_SO logical must be set to the path and filename of the xfodbcusr_so.exe shared image. See Other environment variables used by xfODBC for more information.

Start an ODBC-enabled application, and access the data in the sample database. (For help with this step, see Accessing a Synergy Database.) Note the following:

All text in the CUST_STREET column is capitalized.
All dates in the OR_ODATE column are set to the first of the month.
CUST_LIMIT values for customers who live in Oregon (OR) are $200.00 greater than other CUST_LIMIT values.

To verify that addresses are converted to mixed-case characters as they’re input and all uppercase characters as they’re retrieved by an application, do the following:

From the ODBC-enabled application, INSERT or UPDATE a row, changing the CUST_STREET value to all uppercase characters.
Return to Repository, and change CUST_STREET back to type alpha.
Generate a system catalog for the sample database by running dbcreate.
Finally, return to the ODBC-enabled application and access the sample database. The text in the CUST_STREET column should be in mixed case.

To verify that dates are stored as the fifteenth of the month and display as the first of the month when retrieved, do the following:

From the ODBC-enabled application, INSERT or UPDATE a row, changing the OR_ODATE value to any date other than the fifteenth. This date will be displayed as the first, but stored as the fifteenth.
In Repository, change for OR_ODATE back to type date.
Generate a system catalog for the sample database by running dbcreate.
Finally, return to the ODBC-enabled application, access the sample database, and notice that the date in the OR_ODATE column is the fifteenth.

To verify that customer limits for Oregon customers are stored as $2000.75 and that $200.00 is added when they’re retrieved, do the following:

From the ODBC-enabled application, INSERT or UPDATE a row, changing CUST_LIMIT to any value.
In Repository, change CUSTOMER_LIMIT back to decimal.
Generate a system catalog for the sample database by running dbcreate.
Return to the ODBC-enabled application, access the sample database, and notice that if you changed a CUST_LIMIT for a customer from Oregon, the numeric_to_user() function changed the limit to $2000.75, no matter what you entered. If the customer isn’t from Oregon, the limit you entered is stored.

You can also use the following methods to debug user-defined data routines:

Create an alpha overlay column on the user-defined column and compare the values in an ODBC-enabled application.
Create a separate system catalog and connect file that use the same data but without user-defined fields. Then run the ODBC-enabled application with both system catalogs, and compare the output.