How to access a relational database through ODBC in LOTUS NOTES

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

1 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 reliable security and thus be 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.

2 ODBC Overview
ODBC (open database connectivity Open DATABASE connection) is a component 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 improves efficiency and software reliability.

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:
3.1 The first layer is the ODBC application software. It sends SQL statements to the database by calling the ODBC function and processes the SQL return results. Lotus notes implements this function through NDODBC.
3.2 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;
● Provide ODBC function entry points for each driver;
● Checks the ODBC call parameters and sequence.
3.3 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 of 3.4 is the data source. Data sources refer to the data to be accessed and related operating systems, database management systems, and network systems.
4. SQL statements supported by the ODBC interface
In fact, there are two types of ODBC drivers (Layer 3), one containing SQL statement processing power, and the other.
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.

5. Create an ODBC Data Source
Lotus Notes uses 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 the OK button; ⑤ enter the data source name and description, and the required information. 6. Some drivers need other information, enter the required information, and press OK. 7. Press CLOSE.

6. Use functions to access heterogeneous databases through ODBC
In lotus notes 4.5, three functions are provided to access heterogeneous databases and a value or list value is returned:
● @ 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.
7. Use LotusScript to access external databases through ODBC
The LSX compatibility module 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 feature 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.