A collection of interoperability knowledge between Lotus Domino and relational databases

Interoperability Between Lotus Domino and relational databases
---- 1. Interoperability Between Lotus Domino and relational databases
---- The first tricky problem encountered during project development is: how to import all the content in the original relational database into Domino? Because all the data of an enterprise is concentrated in a relational database, it is expected that the Notes library can interoperate with the old relational database without wasting more manpower on Database conversion.
---- In addition, because Lotus Notes is a non-structured database and relational database is a structured database, a dedicated Conversion Program is required to implement interoperability between the two or import/export data, this program is generally an additional product, either provided by Lotus or provided by database vendors. For example, there is a dedicated program (Pump) for the transformation between Lotus Notes and Oracle databases ). Generally, Lotus does not provide such products when purchasing Lotus products. If necessary, you can purchase them separately. For enterprises, if enterprise data is stored in more than one relational database, they need to purchase several such products, which are not necessarily available yet, therefore, writing a common database conversion program using Lotus Script becomes one of the difficulties in this development.
---- Solve this problem by using the three Lotus Notes object classes provided in Lotus Domino R5: ODBCConnection (ODBC connection), ODBCQuery (ODBC query), and ODBCResultSet (ODBC result set ). The application of these three classes, coupled with the Lotus Script language, can achieve interoperability with relational databases.
---- The specific solution is as follows:
---- The first step is to create a user data source Test in the control panel --> 32-bit ODBC data source;
---- Step 2 create a new database Try in Domino R5 and create a blank form Connection. This form does not have any content, and then create an operation on the form ", the name is "Read ";
---- Step 3 select the programming language Lotus Script in the "Read" Operation's programming window;
---- Step 4 click the "Option" event in the Object window of the programming window and write the following script:
---- Uselsx "* lsxodbc" // use Lotus Script to extend the ODBC class in the object
---- In step 5, select the "Declare" event and write it to it:
Dim session As NotesSession
Dim db As NotesDataBase
Dim doc As NotesDocument
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim con As ODBCConnection
Define various objects used in the program.
Step 6 select the "Click" event and write it to it:
Sub Click (Source As Button)
'Set New Value
Set session = New NotesSession
Set con = New ODBCConnection
// Create an ODBCConnection object instance
Set qry = New ODBCQuery
// Create an ODBCQuery object instance
Set result = New ODBCResultSet
// Create an ODBCResultSet object instance
'The current database information is obtained
Set db = session. CurrentDataBase
Set doc = New NotesDocument (db)
// Create a document
Doc. form = "connection"
// The form of the new document points to connection
Call con. Disconnect ()
// Ensure that the con object is not currently connected to other data sources
If con. ConnectTo ("test") Then
// If the connection is successful
Set qry. connection = con
// Assign the con with which the connection is established to the query object
Qry. SQL = "SELECT * FROM Table1"
// SQL statement
Set result. Query = qry
// Write the connected data source and SQL
The query object of the statement is assigned to the result object.
Call result. Execute () // Execute the SQL statement
Do // loop until the result set is empty
Call result. NextRow () // pointer to the next record
For I = 1 To result. Numcolumns
// Numcolumns attribute records the number of fields in the relational database
Field = result. FieldName (I)
// The Name Of The field is worthy of the field according to the index of the field
Value = result. GetValue (field)
// Obtain the field value
If Isdate (value) Then
// Special processing of the Date Field
If value = Datevalue ("0:00:00") Then
Value = ""
Else
Value = Format (value, "mm-dd-yyyy ")
End If
End If
Set item = doc. AppendItemValue (field, value)
// Write the value in the relational database to the current form in the current library of Notes.
Next
Call doc. save (True, True)
// When all fields of a record are written
Save this document after the Notes Library
Set db = session. CurrentDataBase
Set doc = New NotesDocument (db) // create a document
Doc. form = "connection"
Loop Until result. IsEndOfData
Call con. Disconnect () // Disconnect from the data source
Else
Messagebox ("cocould not connect to server ")
End If
End Sub

---- Finally, save the form and run it. After you Click Read, the content in the relational database will be retrieved to the document database in Notes.
---- The above Code is briefly described below:
* ***** The relational database field must correspond to the name field of the lotus database.
---- 1. New. The New keyword is used to create a Notes object. New is followed by the Notes object to be created and the required parameters. Note that the New Keyword cannot be used when declaring the Notes object in the Declare event; otherwise, an error occurs during compilation; however, in the Click event, you can press Dim db As New NotesDatabase ("", "***. NSF format.
---- 2. the ODBCConnection object is a Notes object that establishes a connection with ODBC. Its ConnectTo method can be used to establish a connection with any data source defined in the ODBC user data source, and some related information is returned, such: the number of tables and the number of fields in the table.
---- 3. the ODBCQuery object is the object for users to write standard SQL query statements. Its connection attribute directs to the ODBCConnection object that has established a connection with the ODBC data source; its SQL attribute is to store user-written SQL statements.
---- 4. The ODBCResultSet object stores the Query result after executing the SQL Query statement. Its Query attribute is an ODBCQuery object that contains the established connection (ODBCConnection) and writes the SQL statement.
---- 5. The NextRow method of the ODBCResultSet object is used to direct the record pointer of ODBCResultSet to the next record. Because after the records in the relational database are obtained, the pointer of the ODBCResultSet object is null, that is, it does not automatically point to the first record in the result set, the NextRow method points the record pointer to the first record. In the future loop, this method will continue to point the pointer to the next record one by one until the last record.
---- 6. since there are many fields in the original relational database, it is unwise to directly write the field name in the program. Therefore, this program uses the FieldName (fieldindex) of the ODBCResultSet object) method to obtain the field name through the field number, then use the GetValue (fieldname) function to obtain the field value, and finally call the AppendItemValue (fieldname, value) of the Document class) the function adds a field named fieldname to the form and assigns a value.
---- After setting the attributes of the above three objects, call the Execute method of the ODBCResultSet object to read data from the relational database.
---- However, when using the above Code to convert the actual database content, it is found that the Chinese field name cannot be recognized when the Notes is connected to the relational database through the ODBC data source. If the field in the relational database is a Chinese name, The ODBCResultSet is empty. The solution is to change all fields in the relational database to English names. After repeated tests, it was found that not all Chinese field names and Notes are not recognized and are related to the driver of the relational database. As this development is connected to the dbf database of Foxpro, the Foxpro2.6 database driver is used. Therefore, you cannot obtain the Chinese field name through ODBC. If the connected relational database is Microsoft SQL Server7.0, you can identify the Chinese field name through the ODBC data source.
---- Problems found during the conversion process of the actual database are: after the program is executed once, all the content in the relational database cannot be obtained. Due to the complex structure of the relational database to be converted, there are more than 40 fields, including characters, strings, dates, numbers, and other data types, at the same time, there are many records in the database (more than 3000 records in total). Therefore, there are two guesses after this problem occurs. One is related to the System Buffer set by the ODBC driver, first, it is related to the memory space opened by Notes. However, through experiments, it is ultimately considered that it is most likely to be related to Notes. This is because first, Notes sets cache for all its classes, including ODBCResultSet. By debugging the Lotus Script and tracking the execution of the Script multiple times in one step, it is found that every time you execute the same database record (for example, 3,328th records), The ODBCResultSet considers that the dataset has arrived, and the following records are lost. Then, you can use the relational database software to open the database and reduce the number of fields, and then read and retrieve all more than 3000 records at a time. As to how many fields need to be reduced to read and retrieve all records at a time, it is related to the structure of the original relational database. The specific experiment is required, which is inconclusive. However, note that Notes indeed sets a cache for each object class, and Notes can change the cache size based on the current memory usage. Second, when the user does not immediately run Lotus Notes, but runs another program first, and then runs the Notes, the 3,328th database records mentioned above change, it may change to 3,112nd records or 3,218th records. This number is not unique, but is related to the running application.
---- The above Code allows you to read the content in the relational database from the ODBC data source and write it to the Lotus Notes database. You can also use the above method to write the content back to the relational database in the Lotus Notes program through the ODBC data source, the specific implementation method is similar to the above process, so only the source code is attached here:
(1) In the Option event:
Uselsx "* lsxodbc"
(2) In the Clicked event:
Sub Click (Source As Button)
Dim session As NotesSession
Dim db As NotesDataBase
Dim dc As NotesDocumentCollection
Define the object classes used in the program
Set session = New NotesSession
Set db = session. CurrentDataBase
Obtains information about the current database.
Dim con As New ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Define ODBC object classes
Set qry = New ODBCQuery
Set result = New ODBCResultSet
If con. ConnectTo ("test") Then
Set qry. Connection = con
Set result. Query = qry
Qry. SQL = "SELECT * FROM table1"
Call result. Execute ()
Set dc = db. AllDocuments
// Obtain the document set of all documents in the current database
If dc. Count = 0 Then
// Exit directly if the document set is empty
Result. Close (DB_CLOSE)
Con. Disconnect
Exit Sub
End If
For I = 1 To dc. Count // loop,
Until all documents are written to the relational database.
Set doc = dc. GetNthDocument (I)
// Obtain document I
Call result. AddRow ()
// Add a record to the relational database
Forall j In doc. Items
Dim str_name As String
Dim value As Variant
Str_name = j. name
If (str_name <> "FORM ")
And (str_name <> "$ UpdatedBy") Then
// Notes adds two special
The NotesItem object is used to identify system information.
Value = doc. GetItemValue (str_name)
Call result. SetValue (str_name, value (0 ))
// Assign values to fields in the relational database
End If End Forall
Result. UpdateRow // update the relational database
Next
Result. Close (DB_CLOSE)
Con. Disconnect
Else
Messagebox ("cocould not connect server ")
End If
End Sub
2. Date Processing in Lotus Domino
---- In the code that uses the ODBC family object class to interact with the relational database, there is the following section:

If Isdate (value) Then
If value = Datevalue ("0:00:00") Then
Value = ""
Else
Value = Format (value, "mm-dd-yyyy ")
End If
Set item = doc. AppendItemValue (field, value)
End if

---- The purpose of this Code is to determine whether the field read from the relational database is a date field. If yes, then determine the value of this date field, check whether it is empty. If it is empty, it will be processed when it is written to the Notes database to ensure that it is written to an empty date type, if the value of this field is not blank, it is directly written to the Notes library. If this field is not a date field, no processing is performed. Some people may ask: Why do I have to process the Date Field separately?
---- The answer is: if the above processing is not performed, if the value of the Date Field Retrieved through the ODBCResultSet class is null, a special date "May December 30, 1899" will appear when writing data to the Notes library ". Why is this date available. However, because there is no separate Date data type in Lotus Script, the Date variable of Notes exists in the form of 8-byte floating point numbers. The integer part of the variable indicates a day of a year or a month of a year, the fractional part indicates the hour, minute, and second, and starts counting from midnight.

---- The date type can be expressed in the range from January 1, January 1, 100 AD (-657434) to January 1, December 31, 9999 (2958465, then, Notes converts the integer date corresponding to January 1, December 31, 9999 and all the dates that exceed the preceding date range to January 1, December 30, 1899. Why does Notes use this method to process date-type variables and why may December 30, 1899 is the default value that cannot be converted to date? However, as a database Conversion Program, the consistency of the content in the two databases before and after the conversion should be ensured. Therefore, since the Notes itself does not solve this problem, it can only be solved by the program. The solution is: if the value of the date type variable is null, it is necessary to write an empty date to the Notes library, so the above Code is available.

---- In the code, a DateValue (string) function is used to convert string parameters to date data types, if the content indicated by the string cannot find the corresponding valid date data, this function will keep the value of the string unchanged, and only convert its data type to the date type, that is, the forced type conversion function is implemented. The "0:00:00" string in the Code is a special string. When reading records of a relational database from an ODBC data source, if the date field value in the relational database is blank, the retrieved value is a date in the form of "0:00:00" (this may be related to the ODBC driver and Related Programs of Microsot, because it is not just the Notes to retrieve an empty date for this value, other software may even encounter the same problem when Microsoft's MS Query component reads the date variables in the same relational database through ODBC ). The DateValue ("0:00:00") statement can be used to determine whether the Value of the retrieved date variable is null. If it is null, the Value of the variable Value is assigned as a null string, and then write it into the Notes library. This special date, January 1, December 30, 1899, will be avoided.

---- Although Lotus Script does not provide a special date data type, Lotus provides the NotesDateTime object class, including the processing of date data, if the developer must process date data in the program and feels that the functions provided by Lotus Script are insufficient, consider using the NotesDateTime object.

---- 3. association between views and forms in Notes

---- In the development process, due to an error, we accidentally discovered the association skills of visual charts and forms in Notes. Notes sets the name and alias attributes for each form. Both names can identify the form. During the development process, the developed database has two forms named Form1 and Form2 respectively, and their aliases are mistakenly set to Document, each form corresponds to a view. When designing a Form2 view, although the columns in the specified view are associated with the fields in Form2, however, when you double-click a document from the view to edit the document during running, the switch form is Form1, which cannot be solved by multiple searches, therefore, I reported that the form alias was changed to Document1 and Document2, and the problem was solved. However, when the Form1 view cannot be switched to the form, the Form1 alias is changed back to the Document. Both views can be switched to their own forms correctly.

---- As a result, Notes first checks whether the form has an alias when establishing an association between a view and a form. If so, it uses an alias for association, once the form alias is changed, you have to re-design the view. If you do not want to re-create the view, you cannot change the form alias. You can also use this technique to switch from a view to a different form. If the form does not have an alias, the name is used for association.

Abstract This article introduces the basic methods for activating ODBC in Lotus Notes based on MicroSoft's Open Database interconnection technology, and provides an example for converting relational database information to the NOTES database.

Key words: ODBC, DBMS, @ DB, ODBCConnection, ODBCQuery, ODBCResultSet
Introduction lotus development corp .) lotus Notes is a software product that greatly improves global communication, collaboration, and coordination within the company and with other companies and customers. It has a good email system, leading full-text retrieval and replication functions, as well as strong security measures, which can ensure security and is widely used.
However, it also has its own weaknesses in online transaction processing, such as data statistics, analysis, and chart generation, in this case, the traditional relational database management system is required. This article will discuss how to convert existing relational database information to the Notes database so as to utilize many databases with good performance sharing in Lotus Notes.
ODBC overview ODBC (open database connectivity Open DATABASE connection) is an integral part of the DATABASE in MicroSoft's OPEN Service structure (WOSA: MicroSoft Windows Open Service release echtrue, it stipulates that "using unified APIs to ACCESS heterogeneous database information" is an implementation of the CLI standard of SQL ACCESS GROUP, which is widely supported by world-leading database and application developers. Applications created using this unified API do not rely on any database management system for database operations and do not directly deal with any DBMS (Database Management System, in this way, applications can be directly shared with different DBMS.
Using ODBC technology, applications only need to care about data processing without considering data access. programmers do not have to understand the specific DBMS, which greatly reduces the workload of software developers, this shortens the development cycle and increases efficiency and software availability.
3 ODBC Working Principle
The ODBC interface uses SQL as the standard query language to access the connected data source. ODBC allows a single application to access multiple different database management systems, which allows application developers to develop, edit, and release applications regardless of the database management system (DBMS) they operate on. You can build a connection to a variety of databases by loading drivers connected to different databases.
Generally, ODBC calls consist of four layers:
The first layer is the ODBC application software, which sends SQL statements to the database by calling the ODBC function and processes the SQL return results. Lotus notes implements this function through NDODBC.
The second layer is the driver management software. The driver Manager manages drivers and loads drivers. Its functions are as follows:
Use the ODBC. ini file to map the data source name to a specific driver;
Process several ODBC initialization calls;
Provides ODBC function entry points for each driver;
Checks the ODBC call parameters and sequence.
The third layer is the driver software. Process ODBC function calls, submit SQL requests to a specific data source, and return the results to the application. If necessary, it modifies the SQL request applied to make the SQL request statement consistent with the syntax of the tddl database.
The fourth layer is the data source. Data sources refer to the data to be accessed and related operating systems, database management systems, and network systems.
The SQL statements supported by the ODBC interface are actually two types of ODBC-driven (layer-3) programs. One type contains SQL statement processing power, and the other type does not.
Therefore, we can see that for ODBC drivers without SQL processing (such as SYBASE's ODBC driver), the SQL statements that can be used in applications are data source databases (such as SYBASE) which SQL statements are supported?
This type of ODBC driver has two parts. One is to process the APPLICATION interfaces (such as API and application program interface) that support ODBC function calls ), the other is SQL grammer, which processes the supported SQL statements and SQL data types ). APIs are divided into three levels based on their functions:
4.1 Level 1 (Core): a set of API functions specified by X/OPEN and consumer CLI.
4.2 Level 1: contains all API functions of Level 1 and some extension functions.
Level 2 (Level 2): contains all the API functions of Level 1 and Level 2, and there are also some extension functions.
Lotus Notes 4.5 requires that ODBC-driven APIs support at least level 2.
Create an ODBC data source, Lotus Notes, and use the ODBC standard to access information of heterogeneous databases. Using the formula or Script language embedded in Notes, you can introduce non-Notes information in the Notes document to convert existing data into a Notes database. Before accessing external data, you must define a data source to let the ODBC driver manager know how to obtain data. A Data Source connects a specific ODBC driver with the database to be accessed, and includes the data you want to access. It is associated with the server or directory, and the background DBMS (Database Management System) the information is recorded in a registration file (ODBC in Windows 95. you can use a Windows management tool to register a data source. The ODBC driver manager is responsible for transmitting the SQL statements and other information of the application to the driver, while the driver is responsible for returning the result set to the application. The procedure in Windows 95 is as follows:
Open the WINDOWS control panel;
② Press the ODBC icon;
③ Press the ADD button;
④ Select the driver you need and press OK;
⑤ Enter the data source name, description, and required information. ⑥ some drivers need other information, enter the necessary information, and press OK.
7. Press the CLOSE button.
Using functions to access heterogeneous databases through ODBC provides three functions in lotus notes 4.5 to access heterogeneous databases and return a value or list value:
● @ DBCOLUMN (ODBC) returns all arrays of a column in the table, or all different values;
● @ DBLOOKUP (ODBC) returns the value selected by matching the keyword in a column in the table;
● @ DBCOMMAND (ODBC) transmits a command to an external DBMS and returns the result;
@ DBCOLUMN and @ DBLOOKUP can only extract data. They cannot add, delete, or modify data, or perform other operations. @ DBCOMMAND can restore data or send other SQL statements that can change data;
Their standard form is given below:
@ DbColumn ("ODBC": "NoCache"; data_source;
User_ID1: user_ID2; pass_word1; password2;
Table; column: null_handling; "Distinct": sort)
@ DbLookup ("ODBC": "NoCache"; "data_source ";
"User_ID1": "user_ID2"; "password1 ";
"Password2"; "table"; "column": "null_handling ";
"Key_column": "key"; "Distinct": sort)
@ DbCommand ("ODBC": "NoCache"; data_source; user_ID1: user_ID2;
Pas_sword1; password2; command_string: null_handling)
Note: If the user's NOTES. The INI file has the following statements:
NoExternalAPP = 1
All formulas are forbidden and no error information is displayed. The formulas cannot be executed.
Using ODBC to access the LSX compatibility module of the external database using Lotus script allows you to write programs that access external data using the LotusScript language, the ODBCConnection, ODBCQuery, and ODBCResultSet classes of Notes provide Notes with ODBC-standard access to external database attributes and operations.
Remember, you must place the following statement in the (OPTIONS) event of the (GLOBAL) object to access the ODBC class:
USELSX "* LSXODBC"
The ODBCCONNECTION class represents the odbc data access feature connected to the DATA source;
The ODBCQUERY class defines the ODBC database access features of an SQL statement. A query must be associated with a valid connection before it is used or validated.
The ODBCRESULTSET class represents the ODBC data access feature for performing operations on the set.
8. An implementation method for converting a relational database into a NOTES database
The following describes how to use Notes to access the Foxpro database.
The basic programming logic is: For a Foxpro database, create a form with the same structure in the Notes database according to its basic structure, in this way, the information of a field in Foxpro is converted and stored in the corresponding fields in the Notes form. Create an operation, write the Conversion Program in Lotus Notes, and run the program in the view, to convert external database information to the Notes database.
Assume that a Foxpro database (SU97.DBF) has the following structure:
Field name type width
Ordinal n 4
Comtime d 8
Comword c 18
Comno c 3
Recetime d 8
Receword c 18
Receno c 3
Recedepa c 24
Recetitl c 120
Seclev c 4
Handle c 12
Theme c 48
Remark c 60
Todepa c 40
Enddepa c 40
To convert all information of the database to the NOTES database, perform the following steps:
8.1 create a new database named ODBC. NSF in NOTES and create a form named TEST_ODBC in the database. The content of the form is as follows:
Domain Name class description
XUHAO digital editing
SHOUWENSHIJIAN
SHOUWEIZI
SHOUWENHAO files
LAIWENSHIJIAN
LAIWENZI Text Editor
LAIWENHAO file Compilation
LAIWENJIGUAN
LAIWENBIAOTI
MIJI Text Editor
BANFUQINGKUAN text compilation
ZHUTICI files can be edited
SONGBANDANWEI
WANCHENGDANWEI
In ODBC. create a standard view in the NSF database named TESTVIEW and define this view option as SELECT (form = "test_odbc"). Create an operation with the title "Conversion ", program the following in the CLICK event:
Sub Click (Source As Button)
Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim view As notesview
Dim r As Integer
Dim con As New odbcconnection
Dim qry As New odbcquery
Dim result As New odbcresultset
Set db = session. currentdatabase
Set view = db. getview ("testview ")
If con. connectto ("Foxpro25") Then
Set qry. connection = con
, Send a query request
Qry. SQL = "select * from su97"
Set result. query = qry
If Not result.exe cute () Then
Messagebox ("error :"&_
Result. geterrormessage (db_lasterror ))
End If
Columns = result. numcolumns
Do
Call result. nextrow ()
Set doc = New notesdocument (db)
Doc. form = "test_odbc"
To retrieve the result set and store it in the corresponding field.
Doc. xuhao = result. getvalue (1)
Doc. shouwenshijian = result. getvalue (2)
Doc. shouwenzi = result. getvalue (3)
Doc. shouwenhao = result. getvalue (4)
Doc. laiwenshijian = result. getvalue (5)
Doc. laiwenzi = result. getvalue (6)
Doc. laiwenhao = result. getvalue (7)
Doc. laiwenjiguan = result. getvalue (8)
Doc. laiwenbiaoti = result. getvalue (9)
Doc. miji = result. getvalue (10)
Doc. banfuqingkuan = result. getvalue (11)
Doc. zhutici = result. getvalue (12)
Doc. beizhu = result. getvalue (13)
Doc. songbandanwei = result. getvalue (14)
Doc. wanchengdanwei = result. getvalue (15)
Call doc. save (True, False)
Loop Until result. isendofdata
, Disconnected from the database
Call con. disconnect ()
Else
Messagebox ("cocould not connect to server ")
End If
End Sub
8.2 click the "convert" button in the view to convert the database (SU97) in FOXPRO. DBF) information is converted to the NOTES database at one time.
The above method has been debugged on the machine. The running environment is Windows 98, lotus notes CLIENT4.61, Lenovo PII/400, and 32 M memory.

==========================================

Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet

Set qry. Connection = con
Set result. Query = qry

Flag = con. ConnectTo ("User Data Source Name", "SQL user name", "password", "Database"

If flag = False Then
Msgbox "cannot connect to the SQL SERVER database. Please contact the system administrator! "
Exit Sub
End If

Qry. SQL = "SELECT * FROM form name"

Result. Execute

'Add a row
Result. AddRow

Result. SetValue ("field name", Doc. domain name (0 ))

Result. SetValue ("field name", Doc. domain name (0 ))

Result. SetValue ("field name", Doc. domain name (0 ))

'Update
Result. UpdateRow

'Close the connection
Result. Close (DB_CLOSE)
'Disconnect
Con. Disconnect

========================================================== ======================================

How to Use Notes to interact with relational databases

In the Notes environment, you can use formulas, functions, and scripts to interact with other relational databases.
1. Set ODBC Data sources
Notes can access relational databases through ODBC drivers.
1. Open "ODBC Data Source" in the control panel of the operating system ".
2. Click Add in user DSN to add a new data source.
3. Select the driver for the data source.
4. Define driver parameters such as data sources.

Ii. use scripts to read Databases
Note: The Uselsx "* LSXODBC" command must be added during the (Options) process to access the database using Lotus script.
On Error Goto err' if an Error occurs, it is forwarded to the corresponding Error handler.

Dim con As New ODBCConnection 'defines the ODBC connection object
Dim qry As New odbcquery' defines ODBC query objects
Dim result As New ODBCResultSet 'defines the ODBC result processing object

Set qry. Connection = con ': mount the ODBC query object and the corresponding Connection object *
Set result. Query = qry: mount the ODBC Query object and the corresponding result processing object *

Con. ConnectTo ("ABC") 'uses the ODBC Data Source ABC to connect to the corresponding database
Qry. SQL = "SELECT * from test" 'defines the SQL statement used by the ODBC query object
Result. Execute 'execute this SQL statement

Rows = result. MaxRows 'number of returned results records
Call result. firstrow' locate the first record

Do
A1 = result. GetValue ("1") 'gets the value in Field 1 of this record.
A2 = result. GetValue ("2") 'gets the value in Field 2 of this record.
Print a1, a2 will return the value, Print the display
Call result. nextrow'
Loop While maxrow> result. currentrow' if the last record is found, exit the Loop.

Result. Close (DB_CLOSE) 'closes the result set.
Con. Disconnect 'disconnect the database
Exit Sub

ErrorHandler:
Messagebox result. GetExtendedErrorMessage, and result. GetErrorMessage 'displays the message with an error.
Exit Sub

3. Use scripts to write data to the database
On Error Goto err' if an Error occurs, it is forwarded to the corresponding Error handler.

Dim con As New ODBCConnection 'defines the ODBC connection object
Dim qry As New odbcquery' defines ODBC query objects
Dim result As New ODBCResultSet 'defines the ODBC result processing object

Set qry. Connection = con ': mount the ODBC query object and the corresponding Connection object *
Set result. Query = qry: mount the ODBC Query object and the corresponding result processing object *

Con. ConnectTo ("ABC") 'uses the ODBC Data Source ABC to connect to the corresponding database
Qry. SQL = "SELECT * from test" 'defines the SQL statement used by the ODBC query object
Result. Execute 'execute this SQL statement

Result. addrow' adds a record row

Call result. SetValue ("1", "AA") 'assigns a value to field 1 of the record "AA"
Call result. SetValue ("2", 3) 'assigns 3 values to Field 2 of this record.

Result. UpdateRow 'writes the added records to the database.
Result. Close (DB_CLOSE) 'closes the result set.
Con. Disconnect 'disconnect the database
Exit Sub

ErrorHandler:
Messagebox result. GetExtendedErrorMessage, and result. GetErrorMessage 'displays the message with an error.
Exit Sub

Qry. SQL = "insert into table1 (namber, name1, age, xingbie) values ('" + a + "', '" + B + "'," + ")"