Lotus Domino R5 development experience

Lotus Domino R5 development experience
---- Lotus Domino is one of the mainstream development platforms for today's office automation systems. At present, most enterprises and organizations are using Lotus Domino to develop their own paperless office systems. Not long ago, an OA system development project I participated in was developed using Lotus Domino R5. During the development process, I have accumulated some tips and I hope to provide some help to relevant people.

---- 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:
[Copy to clipboard] Code:
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:

[Copy to clipboard] Code:
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:

---- 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 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:

[Copy to clipboard] Code:
Uselsx "* lsxodbc"

(2) In the clicked event:

[Copy to clipboard] Code:
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:

[Copy to clipboard] Code:
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.