---- One. Interoperability between Lotus Domino and relational database
---- In the process development process, the first tricky problem encountered is: How to import all the contents of the original relational database into Domino? Because all the data of the company now focus on a relational database, I hope that the Notes library can interoperate with the old relationship library without having to waste more manure on the database conversion.
---- In addition, due to Lotus Notes is a non-structured database, the relational database is a structured database, so the interoperability or data import / export potential to implement it is necessary to need a special conversion program, and this program generally Both are attached, or by Lotus, or by the database manufacturer, such as the conversion between Lotus Notes and Oracle databases, there is a special program (PUMP). Lotus does not provide such products when purchasing Lotus products, if you need to purchase separately. For enterprises, if enterprise data is placed in more than one relationship library, then you need to purchase several such products, and it is not necessarily bought, so you can write a universal database converter through Lotus Script. Become a difficult point in this development.
---- Solving this problem with three Lotus Notes object classes provided in Lotus Domino R5: ODBCCONNECTION (ODBC connection), ODBCQUERY (ODBC query), and ODBCRESULTSET (ODBC result set). Applying these three classes to implement interoperability with relational databases in Lotus Script language.
---- The specific solution is as follows:
---- The first step is established in the control panel -> 32-bit ODBC data source.
---- Step 2 Newly burse a database try in Domino R5, and create a blank form connection, this form does not have any content, then create a "action" on the form, name "Read";
---- Step 3 Select the programming language in the programming window of the "Read" action.
---- Step 4 Click "Option" in the Object window of the programming window and write it as follows:
---- Usersx "* lsxodbc" // uses the ODBC class in the Lotus Script extension object
---- Step 5 Select "Declare" event in which it is written:
DIM session as notessession
DIM DB AS NotesDatabaseDoase
DIM DOC AS NotesDocument
DIM QRY as odbcquery
Dim Result As ODBCRESULTSET
DIM Con As ODBCCONNECTION
Define the various objects used in the program.
Step 6 Select the "Click" event in which it is written:
Sub Click (Source As Button)
'Set New Value
Set session = new notessession
Set con = New ODBCCONNECCONNECTION
// New ODBCConnection object instance
Set Qry = New ODBCQUERY
// New ODBCQuery object instance
SET RESULT = New ODBCRESULTSET
// New ODBCRESULTSET object instance
'Get the current database information
Set db = session.currentDatabase
Set doc = new notesdocument (db) // New documentation
DOC.FORM = "Connection"
/ / New Document Form Pointing Connection
Call con.disconnect ()
/ / Ensure that the CON object is currently not connected to other data sources
IF Con.Connectto ("Test") THEN
// If the connection is successful
Set QRY.CONNECTION = Con
// will be handed over to the Query object
QRY.SQL = "SELECT * from Table1"
// SQL statement
Set result.query = QRY
// will already connect the data source and write SQL
Query object of the statement assigns the Result object
Call Result.execute () // Exercise SQL statement
DO / / loop until the result is empty
Call result.nextrow () // Pointer pointing down the next record
For i = 1 to result.numcolumns
// NumColumns Property records the number of fields in the relational library
Field = Result.fieldName (i)
/ / According to the index value of the field value to the name of the field
Value = Result.GetValue (field)
// Take a field value
If Isdate (Value) THEN
// Special treatment for 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 library to the current form of the Notes current library
NEXT
Call Doc.save (True, True)
/ / When all the fields of a record are written
Save this document after the Notes library
Set db = session.currentDatabase
Set doc = new notesdocument (db) // New documentation
DOC.FORM = "Connection"
Loop unsult.isndofdata
Call con.disconnect () // Disconnects to the data source
Else
MessageBox ("Could Not Connect To Server)
END IF
End Sub
---- Finally, save the form and run. After clicking the READ operation with the mouse, the content in the relational database is taken in the Notes document database.
---- A brief description of the above code:
---- 1. NEW. The NEW keyword is used to create a Notes object. NEW is later is the Notes object to be created and the required parameters. The point you need to pay attention to using the new key is: You can't use the new key when you declare the Notes object, otherwise it will be wrong when you compile; * .Nsf ") formatted.
---- 2. The ODBCConnection object is a Notes object that is connected to ODBC. It uses its ConnectTo method to establish a connection with any of the data sources defined in the ODBC user data source, and return some related information, such as: Number of tables, fields in the table Number.
---- 3. The ODBCQUERY object is an object of the user who writes a standard SQL query statement. Its connection attribute is an ODBCConnection object that has been connected to the ODBC data source; its SQL property is the SQL statement written by the user. ---- 4. The ODBCRESULTSET object is to store the query results after executing the SQL query statement. Its query property is an ODBCQuery object that contains the establishment of the ODBCConnection and writes the SQL statement.
---- 5. The NEXTROW method for the ODBCRESULTSET object is to point the ODBCRESULTSET's record pointer to the next record. Since the pointer to the ODBCRESULTSET object is empty after taking the record in the relational database, that is, it does not automatically pointing to the first record of the result set, the NEXTROW method implements the record pointer points to the first record. Function, and in the later loop process, this method will continue to point the pointer to the back record until the last one.
---- 6. Since the field in the previous relational database is more, the practice of writing the literal name directly in the program is not wise, so this program uses the field name of the ODBCRESULTSET object to get the field name through the number of the field. Then use the getValue (FieldName "function to get the domain value, and finally call the AppendItemValue (FieldName, Value) function of the Document class to add a domain named FieldName and assign a value.
---- After setting the properties of the above three objects, call the ODBCRESULTSET object's Execute method to read data from the relational library.
---- But when using the above code, when the actual database content is converted, it is not possible to identify the Chinese field name when the NOTE is connected to the relational database through the ODBC data source. If the field of the relational database is Chinese name, the ODBCRESULTSET will be empty, and the solution is to change all the fields in the relational database to English name. After repeated test, it is not recognized that all Chinese literals Notes are not identified. It is related to the driver of the relationship library. Since this development is connected to FoxPro's DBF database, use the FoxPro2.6 database driver, so it cannot pass ODBC Gets Chinese field name, if the connection is the relationship library is Microsoft SQL Server 7.0, you can identify Chinese literals through the ODBC data source.
---- The problem also discovered simultaneously during the conversion process of the actual database: the program cannot take all the contents of the relational database after the program is completed. Since the structure of the relational database that needs to be converted is more complicated, there are more than 40 fields, including characters, strings, date, numbers, etc., and more records in the library (more than 3,000 records), After this problem, there are two guess, one is related to the system buffer set by ODBC driver, one is related to the memory space opened by Notes, however, through the experiment, ultimately the possibility of Notes is the largest. This is, first, Notes set a cache for all the classes, which also include odbcresults, and find the implementation of the Lotus Script script and multiple steps to track the execution of the script, and finds that only the same database record (for example Section 3328 Records, ODBCRESULTSET believes that the data set has been in the head, and the following records are lost. So use the relational database software to open the database, and reduce the fields, after several bars, you can read all more than 3,000 records at a time. As for how many fields that need to be reduced can be read at a time, all records are related to the structure of the original relational database, and the specific situation is required, and it is necessary to conclude. However, one that can be affirmed is that Notes itself has set a cache for each object class; and Notes can change the size of the cache based on the current memory usage. Second, when the user is running, it is not immediately running other programs, and then running Notes, the third 3328 database record mentioned above will change, which may change to Article 3112 records, or may Changes to Article 3218 Records, this number will become unique, and how much is unknown, but is related to the running application. ---- The above code implements the content written to the Lotus Notes library through the ODBC data source reading relational database, and the above method can also be implemented in the Lotus Notes program to write back to the relational database through the ODBC data source. The method is similar to the above process, so it is only attached to the source code:
(1) Option incident:
Usersx "* lsxodbc"
(2) Clicked incident:
Sub Click (Source As Button)
DIM session as notessession
DIM DB AS NotesDatabaseDoase
DIM DC AS NotesDocumentCollection
Define the object class used in the program
Set session = new notessession
Set db = session.currentDatabase
Take information from the current database
DIM Con As New ODBCCONNECCONNECTION
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
// Get the document set from all documents in the current database
If dc.count = 0 Then // If the document is empty, then exit
Result.close (DB_Close)
Con. Disconnect
EXIT SUB
END IF
For i = 1 to dc.count // cycle,
Until all documents are written to the relationship library
SET DOC = DC.GetNTHDocument (i)
// get the article I text
Call result.addrow ()
/ / Add a record in the relational library
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 specials for each document
Notesitem object is used to identify system information
Value = doc.getitemvalue (str_name)
Call Result.SetValue (str_name, value (0))
/ / Assign a value for fields in the relational library
End if End Fortl
Result.Updaterow // Update Relational Library
NEXT
Result.close (DB_Close)
Con. Disconnect
Else
MessageBox ("Could Not Connect Server")
END IF
End Sub
---- II. Date processing in Lotus Domino
---- In the code that interacts with the ODBC object class and the relational database, there is a 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
---- This section of this code is to determine if the field read from the relational library is the date field. If so, then determine the value of this date field, see if it is empty, if it is When you write a Notes library, you must ensure that the write is the empty date type. If the value of this field is not written directly to the NOTES library; if this field is not a date field, no processing is made. Maybe someone will ask: Why do you have to do this for a date field?
---- The answer is: If you do not do the above processing, the value of the date field removed by the ODBCRESULTSET class is empty, and a special date will appear when writing the Notes library: "December 30, 1899". Why did this date, it is still unknown. However, in the Lotus Script, since there is no separate Date data type, Notes's date variable exists in the form of 8 bytes of floating point numbers, and its integer part represents a certain day, and its fractional part indicates a small time. Second, count from midnight.
---- The type of date can be expressed from January 1, 9994, 9958465), from 31 December 1999, and then NOTEs will be integrated on December 31, 1999. Date and all the date exceeding the above-mentioned date ranges to December 30, 1899. Why does not use this method to handle a date-type variable, and why don't have a better interpretation of the default value of December 30, 1899 as a default value, but as a database conversion program, it should guarantee that the conversion before and after The consistency of content in the two databases, so since Notes itself does not solve this problem, then it can only be solved by the program. The idea of solving is that if the value of the date type variable is empty, then write an empty date to the Notes library, so there is the code above. ---- In the code, use a DateValue (String) function that the function of this function is to convert the string parameter into a date data type, if the content represented by the string does not find the legal date data, Then this function will retain the value of the string unchanged, but only convert its data type into a date type, that is, the function of the mandatory type conversion function is implemented. The "0:00:00" string in the code is a special string. When reading the record of the relational database via the ODBC data source, if the date field value in the relational library is empty, then the value is "0 : 00: 00 "Date of form (this may be related to Microsot's ODBC driver and related procedures, because not only Notes takes out the empty date for this value, other software even includes Microsoft's own MS Query components read the same relationship database through ODBC The same problem occurs when the date type variable is available). Use the DateValue ("0:00:00") statement to determine if the value of the removed date variable is empty. If you assign the value of the VALUE into an empty string, then write to the Notes library, then Avoid this special date on December 30, 1899.
---- Although there is no special date data type in Lotus Script, Lotus provides a NotesDateTime object class, including related processing of date data. If developers must handle date data in the program, Lotus Script If the function provided is not enough, you can consider using the NotesDateTime object.
---- three. Notes in view of the view and form
---- During the development process, the relationship skills of NOTES and forms in Notes were found in the development process. NOTES sets two properties for each form, both of which can identify the form. During the development process, the library developed has two forms, named Form1 and Form2, respectively, its alias is set to Document, and each form has a view and corresponding view of Form2 Although the columns in the specified view are associated with the domain in Form2, when running from the view, the form is switched to the form, but the form is form1, and multi-party lookups cannot be solved. This problem, so, the psychological change of the trial is reported to change the alias of the form, which is Document1 and Document2, and the problem is solved. However, the situation from the appearance of Form1 cannot be switched to the form, and then the aliasing of FORM1 is changed back to Document, and both views can correctly switch to their form.
