This section contains basic information on using the ODBC extensions. Three example macros included in WBODBC.DOT illustrate the use of the functions. The sample form, INVOICE3.DOT, which shows how you can automate Word forms to take advantage of the ODBC extensions, is described at the end of this section.
To indicate that the ODBC extensions are located in the WBODBC.WLL add-in library, you must declare the functions prior to calling them in a macro. All of the Declare statements for the WBODBC.WLL functions documented in this appendix are included in the AllDeclarations macro stored in the WBODBC.DOT template. Copy these declarations into every macro that uses the WBODBC.WLL functions.
To retrieve data, you must first establish a connection with a data source by
using SQLOpen. A successful call to SQLOpen returns a unique connection identification number. The connection ID identifies the specified data source until the connection is closed by using SQLClose or SQLCloseAll. The connection ID is used by SQLExecQuery or SQLQueryExecto send a query, and the SQL retrieve functions then use the same connection ID to retrieve the query results.
You can use the following SQL retrieve functions with SQLExecQuery: SQLRetrieveColumns, SQLRetrieveFlush, SQLRetrieveItem$, SQLRetrieveRows, SQLRetrieveToDocument.
You can use the following SQL retrieve functions with SQLQueryExec: SQLQueryFetch, SQLQueryRetrieve, SQLRetrieveColSize, SQLRetrieveColumns, SQLRetrieveFlush, SQLSetRowPos.
Before sending a query with SQLExecQuery or SQLQueryExec, you might want to determine the underlying structure, or schema, of a DBMS. This is called mapping the database. For example, you might want to know whether the database is organized as "tables" or "files." Once you've mapped the database, you can more easily write macros to manipulate the data.
You map a database by using the SQLGetSchema and SQLGetSchemaItem$ functions. With SQLGetSchema, you specify the type of information, or property, that you want to learn about the DBMS. You then use SQLGetSchemaItem$ to retrieve a specific description of the property. The third example macro described later in this section shows how to use these functions.
After a call to any ODBC extension that returns a numeric value, you should check for a returned error condition, which is indicated by a 0 (zero) or a negative integer. The ODBC extensions include functions for examining errors. First you call SQLCountErrors, which returns a count of the number of lines of error information stored in memory after a preceding WordBasic ODBC function call returned an error value. If SQLCountErrors returns a positive integer, you then call SQLErrorText$ to return each line of error message text. The returned error message text can then be evaluated by an error trap or displayed to the user.
The macros shown in the following examples are available in WBODBC.DOT, the template provided on the Microsoft Word Developer's Kit disk. Only portions of the macros are included here; to review the complete code for an example, open the macro in a macro-editing window.
When you run the Exec example macro, you type the SQL query in a dialog box. If the query string results in an error, the ParseErrors subroutine is called to display the error message. The query is then redisplayed in a dialog box so that you can edit the query string. Successful queries are stored in a new document so that you can reuse them in other macros.
While quit = 0
s$ = InputBox$("SQL> ", "SQL Interactive Interpreter", prompt$)
If Len(s$) = 0 Then
quit = 1
Else
ret = SQLExecQuery(connect_num, s$)
If ret <= 0 Then
prompt$ = s$
x$ = "Error: " + Str$(ret) + "," + Str$(SQLErrorCount)
MsgBox x$
ParseErrors
Else
If FirstLog <> 0 Then
FileNew
FirstLog = 0
EndIf
LogString(s$)
prompt$ = ""
EndIf
EndIf
Wend
And here is the ParseErrors subroutine:
Sub ParseErrors
nerrors = SQLCountErrors()
For i = 1 To nerrors
MsgBox "error: class(" + SQLErrorClass(i) + ") code(" + \
Str$(SQLErrorCode(i)) + "): " + SQLErrorText(i)
Next
End Sub
The Report example macro opens a Microsoft Access data source named "test.mdb," a data source set up from the Microsoft Access database TEST.MDB that is provided on the Microsoft Word Developer's Kit disk.
The SQL query string sent with the SQLExecQuery function selects each row, or record, of information for which there is an entry in the "name" field. (Note that the Quote2$() user-defined function, not shown here, returns the string "name" preceded and followed by Chr$(34), the ANSI character code for a double quotation mark.) Next, the macro creates a table in a new Word document and inserts each data item retrieved by SQLRetrieveItem$ into the appropriate table cell. If the amount in any account field is less than 1, that amount is formatted as bold. If an ODBC extension returns an error value, control is passed to the MyError error handler.
connect_num = SQLOpen("DSN=test.mdb", output_ref$, 0)
If connect_num <= 0 Then Goto MyError
ret = SQLExecQuery(connect_num, "Select * from table4 " + \
Quote2$("name"))
If ret <= 0 Then Goto MyError
col = SQLRetrieveColumns(connect_num)
row = SQLRetrieveRows(connect_num)
If col <= 0 Or row <= 0 Then Goto MyError
FileNew
TableInsertTable .ConvertFrom = 0, .NumColumns = col, .NumRows = row
For i = 1 To row
For j = 1 To Col
item$ = SQLRetrieveItem$(connect_num, j, i)
If (j = 1) Then
Insert item$
Else
v = Val(item$)
s$ = Str$(v / 100)
Insert s$
If (v < 100) Then
ParaUp 1, 1
Bold(1)
CharRight 1, 0
End If
End If
If j <> col Or i <> row Then NextCell
Next
Next
ret = SQLClose(connect_num)
If ret > 0 Then Goto MyEnd
The Schema example macro displays a dynamic dialog box in which you can
select an option corresponding to a database property to see a description of that property. The macro uses SQLGetSchema and SQLGetSchemaItem$ to build a shared array of the strings describing the database properties. The array, called combobox1$(), is then displayed in the dialog box.
In the main subroutine (not shown here), the connection information for SQLOpen — the name of a data source, the database name, and any other required connection string arguments — is entered in response to dialog boxes.
After the custom dialog box is defined, it is displayed. The following subroutine, GetDBInfoWithQualifier, is called by the dialog function (or by the intermediate function GetDBInfo if there is no value for the qual$ argument) every time the user selects a different property in the dialog box. The dialog function then uses the shared array combobox1$() to update the custom dialog box.
Function GetDBInfoWithQualifier(itemid, qual$)
i = SQLGetSchema(connect_num, itemid, qual$)
If i > 0 Then
For j = i - 1 To MaxItem
combobox1$(j) = ""
Next
For j = 0 To i - 1
s$ = SQLGetSchemaText(connect_num, j + 1)
combobox1$(j) = s$
Next
Else
MsgBox "No Information"
End If
GetDBInfoWithQualifier = i
End Function
The INVOICE3.DOT template included in the WBODBC folder on the Microsoft Word Developer's Kit disk contains an integrated collection of macros similar to the INVOICE2.DOT macros described in Chapter 9, "More WordBasic Techniques," in Part 1, "Learning WordBasic." The primary difference between the two templates is that INVOICE2.DOT uses dynamic data exchange (DDE) to retrieve data from and write data to a data source in a running application (a workbook open in Microsoft Excel), while INVOICE3.DOT uses the ODBC extensions to retrieve data from and write data to a data source in a DBMS (an .MDB file created in Microsoft Access).
The advantages of using the ODBC extensions to automate Word forms are simple: the ODBC functions are faster than comparable DDE commands; the ODBC functions require only a data source, not both a data source and the application that was used to create it; and the ODBC functions can be used to read and modify data sources created by DBMSs that do not support DDE. In general, the ODBC error reporting functions make debugging and handling ODBC errors more flexible than handling and debugging comparable DDE errors in WordBasic.
The following table shows a comparison of the steps that macros using DDE versus macros using the ODBC extensions must carry out to automate a form.
DDE macro steps | ODBC macro steps |
Determine whether required application is running; start the application if it is not running. | Not required. |
Locate the file that will act as the data source and open it in the running application. | Connect to an established ODBC data source (previously set up using the ODBC Administrator program). |
Execute a query in the form of the application's programming language with DDEExecute. | Execute a query in ODBC SQL using SQLExecQuery or SQLQueryExec. |
Retrieve data as strings using DDERequest; parse as required by the application's DDE and database functionality. | With SQLExecQuery, retrieve data items as strings using row and column notation with SQLRetrieveItem$, or retrieve the entire query and insert it as a table with SQLRetrieveToDocument. With SQLQueryExec, move through rows using SQLQueryFetch or SQLSetRowPos and return data from columns using SQLQueryRetrieve. |
Close the DDE channel using DDETerminate. | Close the data source channel using SQLClose. |
For comparison, open the macros in the two templates on the Microsoft Word Devleoper's Kit disk: INVOICE2.DOT in the WRDBASIC folder and INVOICE3.DOT in the WBODBC folder. Looking at the areas in the macros that retrieve and write data, you can compare and contrast the amount of code, the data parsing routines, and the error handling routines required by ODBC and DDE. Create a new document based on each form and use it to run the macros and demonstrate the differences in speed.
For complete information about the purpose and functionality of the Invoice form itself and the use of form fields to trigger WordBasic macros, see "Automating Forms" in Chapter 9, "More WordBasic Techniques," in Part 1, "Learning WordBasic."
Note
To run the macros in the INVOICE3.DOT template, you must have access to the NWIND.MDB or NORTHWIND.MDB file (shipped with Microsoft Access version 2.0 and version 7.0, respectively) and you must create a data source called "Northwind" associated with that database. For information about creating ODBC data sources, see "Setting Up Data Sources," earlier in this appendix.