Using ODBC to implement interoperability of Domino and relational databases
Lotus Domino is one of the mainstream development platforms of today's office automation system. Domino comes with a non-relational database-document database, and most of the current information of most companies is stored in relational databases such as SQL Server, so Development of an office automation system is bound to involve data exchange issues in Domino and relational databases.
I. Interoperability of Lotus Domino and relational database
Three Lotus Notes extended classes are available in Lotus Domino R5: ODBCConnection, ODBCQUERY (ODBC query), and ODBCRESULTSET (ODBC results set). Applying these three classes to implement interoperability with relational databases in Lotus Script language.
The specific solution is as follows:
Step 1: Create user data source TEST in the control panel → 32-bit ODBC data source;
Step 2: Create a database try in Domino R5, and create a blank form con-nection, this form does not have any content, then create a "operation" on the form, named "Read";
Step 3: Select the programming language in the "Read" program window to Lotus script;
Step 4: Click "Option" event in the Object window of the programming window and write it as follows:
Usersx "LSXODBC" // use Lotus Script extension ODBC classes
Step 5: Select "Declare" event to write:
DIM session as notessession
DIM DB AS NotesDatabaseDoase
DIM DOC AS NotesDocument
DIM QRY as odbcquery
Dim Result As ODBCRESULTSET
DIM Con As ODBCCONNECTION
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
Set Qry = New ODBCQUERY
SET RESULT = New ODBCRESULTSET
'Get Current Database
Set db = session.currentDatabase
Set doc = new notesdocument (dB)
DOC.FORM = "Connection"
Call con.disconnect ()
IF Con.Connectto ("Test") THEN
Set QRY.CONNECTION = Con
QRY.SQL = "SELECT from table1"
Set result.query = QRY
Call result.execute ()
Column = result.columns
DO
Call result.nextrow ()
For i = 1 to result.numcolumns
Field = Result.fieldName (i)
Value = Result.GetValue (field)
If Isdate (Value) THEN
IF value = Datevalue ("0:00:00") THEN
Value = "" ELSE
Value = Format (Value, "MM-DD-YYYY")
END IF
END IF
Set item = doc.AppendItemValue (Field, VAL-UE)
NEXT
Call Doc.save (True, True)
Set db = session.currentDatabase
Set doc = new notesdocument (dB)
Doc.form = "TEST"
Loop unsult.isndofdata
Call con.disconnect ()
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 to the Notes document database.
Second, the problem and solvement of procedures
However, when using the above code to perform the actual database content conversion, we found that NOTEs cannot recognize the Chinese field name when connecting 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.
The problems discovered simultaneously during the conversion process of the actual database: the program cannot take all the contents of the relational database after each time it executes. By debugging the Lotus Script script and multiplexing the execution of the script multiple times, it is found that the ODBCRE-SULTSET is that the data set is already in the head. The following records are lost. So use the relational database software to open the database, and reduce the fields after several strips, you can read all records at a time. As for how many fields need to be reduced, you can read all records with the structure of the original relational database, and require specific examples.
Third, the date processing in Lotus Domino
There is a program in the above program:
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 code is to determine if the field read from the relational library is the date field. If so, determine the value of this date field, see if it is empty, if it is empty, write Notes library Time to do, to 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.
If the above is not processed, the value of the date field removed by the ODBCRESULTSET class is empty, and when writing the Notes library, I don't know why I will write "December 30, 1899".
In the code, use a DateValue (String) function, 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 The value of the string remains unchanged, and only the data type is converted 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. Use the DateValue ("0:00:00") statement to determine if the value of the obtained date type variable is empty. If you assign the value of the variable value into an empty string, then write to the Notes library, Avoid "December 30, 1899" this day. Four, Notesitem
NOTESITEM is an object that can represent all form elements, simply that all elements placed on the form, can be represented by a NotesIxt program in the Lotus Script program and operates. The role of the Doc.AppendItemValue (FieldName, Value) function in the above code is that the value of the value is a form of additional fields (which is the fields in the relational library).
In fact, careful readers must have discovered, in the above processing code, why should I first assume the value of Value first, then write in the library? Why can't you write a value directly to the library? The key to the problem is on Notesitem.
Notesitem has a lot of properties, the most important two are Name and Text properties. Name property stores the name of the form element (ie, fieldName), and the TEXT property stores the value of the form element. When the program is executed to the DOC. AppendItemValue (Value) function, Notes writes the domain name and the corresponding value (Value) into the NotesItem object. After executing the call doc.save () function, all NOTESITEM objects in all memory are deposited in the associated form.
In the process of studying how to handle the empty date, the initial idea is to write a null string into the Notesitem, but after the viewing helps and single step tracking scripts, it is found that the above method is not performed. The reason is that the text attribute of Notesitem is read-only. It is not assigned to it, and there is no corresponding assignment function. It is obvious that the function doc.AppendItemValue (FieldName, Value) is a function assigned to the Text property.
5. View of the view and form in Notes
During the development process, due to an error, we accidentally discovered the relationship skills of Notes and forms. NOTES sets two properties for each form, both of which can identify the form. During the development process, the library we develop has two forms, named Form1 and Form2, respectively, and its alias is set to Docu-Ment due to negligence, and each form has a view corresponding. When designing the view of the Form2, although the columns in the specified view are associated with the domain in the FORM2, when running from the view, when you want to edit it, it is Form1, Multi-party findings cannot solve this problem, so I have changed the mind's alias, which is Document1 and Docu-Ment2, and solve the results. But there is still a situation in which the Form1 cannot be switched to the form, and then the alias of Form1 is changed back to Document, and both views can correctly switch to their form. This findings, NOTEs are first looking at the form of the form and the form, if any, use the alias to associate, once the alias of the form changes, you have to redesign the view, if you don't want to re-do You cannot change the alias of the form. Of course, it is also possible to apply this technique to implement the function of switching from the view to different forms. If the form does not have an alias, use the name to associate.
