ODBC access examples

This topic includes the following sections:

Using ODBC Test to test a query
Adding and using a data connection in Visual Studio

The following examples connect to the sample Synergy database provided with the xfODBC installation. These examples were created using Visual Studio 2019 and ODBC Test 2.7. Procedures may differ for other versions.

Before you start, follow the steps in Using the Sample Database As a Tutorial to prepare the sample database for ODBC access by generating a system catalog and creating a connect file, DSN, and users. The DSN you create for the sample database should specify the connect file.

For more examples, see the following Synergex KnowledgeBase articles:

1969—an example that accesses Synergy data with Microsoft Access
1970—an example that uses Microsoft Word to create a form letter from Synergy data
1972—an example that uses Crystal Reports to create a report with Synergy data

Using ODBC Test to test a query

ODBC Test (Odbcte32.exe) is an ODBC-enabled application distributed by Microsoft. It’s useful for testing your xfODBC setup and for testing SQL statements. If a query works with ODBC Test, the query and the setup (DSN, connect file, system catalog, data files, environment variable settings, and so forth) are working. If you find that the same setup and statement fail with another ODBC-enabled application, chances are the problem lies with the application (for example, with the way it generates SQL).

Open ODBC Test.

Select Full Connect from the Conn menu.

In the Data Source field of the Full Connect dialog box, select the DSN you created for the sample Synergy database, enter the username and password for the DSN (optional at this point), set the ODBC Behavior field to ODBC 3.0, and then click OK. (Leave the Cursor Library field set to Default.) To complete this example, the user you log in as must have read privileges.

If the xfODBC Info window opens, enter the username, password, or connect file—whatever information is missing—and click OK. This window opens if any one of these is not stored in the user DSN you selected and was not entered in the Full Connect dialog box.

If xfODBC isn’t able to connect to the sample database, you’ll get an error message ("Connection failed...") that should give you some idea about the problem. If you are unable to solve the problem from the information provided by the error message, see Troubleshooting data access.

If ODBC Test is able to connect to the sample database, a window with two panes will open. Enter the following SQL statement in the upper pane (see figure 1):

SELECT in_latin FROM plants 
    WHERE in_zone = 3 
        AND in_shape = 'tree'

Select Stmt > SQLExecDirect from the menu, and then click OK in the SQLExecDirect window. A message, which should include SQL_SUCCESS=0, is displayed in the lower pane of the window for the connection. (If you get an error, the SQL statement entered in step 5 is probably incorrect.)

1. The results of SQLExecDirect.

If step 6 resulted in SQL_SUCCESS=0, select Results > Get Data All from the menu. The results are appended to the information in the lower pane.

To disconnect, select Full Disconnect from the Conn menu.

There are many other ODBC API calls you can invoke from ODBC Test. See the documentation for ODBC Test for information.

Adding and using a data connection in Visual Studio

A data connection enables you to access your Synergy data from within Visual Studio. This example adds the data connection used in Using the Retrieve Data function and Using Query Designer below.

In Visual Studio, open the Server Explorer (View > Server Explorer) and select Tools > Connect to Database, or right-click the Data Connections node and select Add Connection from the context menu. (Data connections are independent of solutions and projects, so you don’t need to open a solution or project.) The Add Connection window or Choose Data Source window opens.

Do one of the following:

If the Add Connection window opens, make sure the data source is set to “Microsoft ODBC Data Source (ODBC).” If it isn’t, click the Change button and then, in the Change Data Source window, set the data source to “Microsoft ODBC Data Source” (which sets the data provider to “.NET Framework Data Provider for ODBC”) and click OK.
If the Choose Data Source window opens, set the data source to “Microsoft ODBC Data Source” (which sets the data provider to “.NET Framework Data Provider for ODBC”) and click Continue.

In the “Use user or system data source name” field of the Add Connection window, select the DSN for the sample database. If the DSN doesn’t specify a username and password, enter them here.

2. Entering data connection information in the Add Connection window.

Click the Test Connection button at the bottom of the Add Connection window to make sure the connection information is correct. Note that a “Data Source name is missing” error could indicate that the DSN does not specify a connect file. (For a DSN to work with the .NET Framework Data Provider for ODBC, it must specify a connect file.) For other errors (e.g., “Authorization failure”), check the information on the Add Connection window. If it appears to be correct, see Troubleshooting data access.

Once you’re able to connect with the Test Connection button, click OK to close the “Test connection succeeded” message, and then click OK in the Add Connection window. The Add Connection window closes, and a node for the data connection is added to the Data Connections branch of Server Explorer.

Using the Retrieve Data function

Once you’ve established a data connection, you can access the Synergy data from Visual Studio in various ways, including the Retrieve Data function, which displays the data for a table or view.

In Server Explorer, expand the data connection node added in step 5 of Adding and using a data connection in Visual Studio (above), expand the Tables node under that, and then right-click one of the tables (CUSTOMERS, ORDERS, PLANTS, or VENDORS).

From the context menu, select Retrieve Data. (See figure 3.)

3. Selecting Retrieve Data from the context menu for the CUSTOMERS table.

A tab with the name of the table is added to the editor pane of Visual Studio. The tab displays all the columns and rows for the table. (See figure 4.)

4. Results of Retrieve Data function for CUSTOMERS table.

Using Query Designer

Another Visual Studio tool you can use with a data connection is Query Designer, which enables you to enter SQL or use graphical tools to query a relational database.

In Server Explorer, right-click the data connection node added in step 5 of Adding and using a data connection in Visual Studio and select New Query from the context menu. If there is no New Query entry on the context menu, select Refresh from the context menu, and then right-click again and select New Query.

In the Add Table window (see figure 5), select the tables you want to query (you can use ctrl+click to select more than one) and click Add. The selected tables are displayed in the Diagram pane, the Criteria pane, and as part of the SQL pane.

5. Selecting tables in the Add Table window.

Click Close to close the Add Table window.

Select columns for the query by selecting columns in the Diagram pane (figure 6). The SQL pane displays the revised query.

6. Selecting columns in the Diagram pane.

Select Query Designer > Execute SQL to execute the query. The retrieved columns and rows are displayed in the Results pane.