Using ODBC to implement Domino and relational database interoperability

dom|odbc| Data | database
Using ODBC to implement Domino and relational database interoperability

Lotus Domino is one of the main development platforms for OA Systems today, Domino has a non relational database-a document database, and most of the enterprise information is now stored in relational databases such as SQL Server, so In the process of developing OA system, the problem of data exchange between Domino and relational database is bound to be involved.


One, Lotus Domino and relational database interoperability


Three Lotus Notes extension classes are available in Lotus Domino R5: OdbcConnection (ODBC connection), Odbcquery (ODBC query), and Odbcresultset (ODBC result set). Using these three classes, supplemented by the Lotus script language, enables interoperability issues with relational databases.

The specific solution is as follows:

1th Step: Set up user data source in Control Panel → 32-bit ODBC data source test;

Step 2nd: Create a new database try in Domino R5 and create a blank form con-nection, there is nothing in the form, and then an "action" is created on the form, named "Read";

3rd step: In the "Read" operation of the programming window to select the programming language for Lotus Script;

4th step: Click the "Option" event in the object window of the programming window and write the following script:

Uselsx "LSXODBC"//using Lotus Script to extend an ODBC class

Step 5th: Select the "Declare" event in which to write:

Dim session as Notessession

Dim DB as NotesDatabase

Dim Doc as NotesDocument

Dim Qry as Odbcquery

Dim result as Odbcresultset

Dim con as OdbcConnection

Step 6th: Select the "click" event in which to write:

Sub Click (Source as Button)

′set New Value

Set session=new notessession

Set con=new OdbcConnection

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 ()

Columns=result. Columns

Todo

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 Until result. Isendofdata

Call Con. Disconnect ()

Else

Messagebox ("Could not connect to server")

End If

End Sub

　　

Finally, you save the form and run it, and after you click the read operation, the content in the relational database is taken to the Notes document database.


Second, the problem of the procedure existence and solution


But using the above code to convert the actual database content, we found that the Chinese text segment name was not recognized when notes connected to the relational database through an ODBC data source. If the fields of the relational database are Chinese names, then Odbcresultset will be empty, and the solution is to change all fields in the relational database to English names.

In the actual database conversion process also found in the problem: The program after the completion of a relational database can not be taken out of all the content. By debugging the Lotus script and stepping through the execution of the script multiple times, you find that each time you execute to the same database record, Odbcre-sultset thinks the dataset is over, and the following records are lost. Then you can read all of the records at once by opening the database with the relational database software and reducing the number of fields in it. As to how many fields need to be reduced in order to read all the records from the original relational database structure, the specific situation needs specific experiments.


Third, the date processing in Lotus Domino


The procedure above has the following procedure:

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 function of this piece of code is: determine if the field read from the relational library is a date field, and if so, determine the value of the Date field, see if it is empty, and if it is null, do the processing when writing to the Notes library, guaranteeing that the null date type is being written. Writes the notes library directly if the value of this field is not null, and does nothing if this field is not a date field.

If the above processing is not done, then the value of the date-type field taken out by the Odbcresultset class is empty, and when written to the Notes library, it is not known why it was written to the "December 30, 1899" date.

In code, a datevalue (string) function is used to convert a string parameter to a date data type, and if the string represents a value that does not find a valid date data corresponding to it, this function preserves the values of the string. The function of coercion type conversion function is realized by simply converting its data type into date type. The "0:00:00" string in the code is a special string that, when reading records from a relational database through an ODBC data source, if the date-Type field value in the relational library is empty, the value taken is the "0:00:00" date. You can use the DateValue ("0:00:00") statement to determine whether the value of a date-type variable is empty, and if NULL, assign the value of the variable value to an empty string, and then write to the Notes library to avoid the day of "December 30, 1899."


Four, Notesitem


Notesitem is an object that can represent all of the form elements, simply that all elements that can be placed on a form can be represented and manipulated in a Lotus script using a Notesitem object. Doc in the code above. The role of the Appenditemvalue (Fieldname,value) function is to append a field (that is, a field in a relational library) to a form based on its value.

In fact, attentive readers must have found, in the above on the empty date of the processing code, why should the value of the first assignment to empty, and then write into the storage? Why not directly to the library to write the value of it? The crux of the problem lies in the Notesitem.

Notesitem has many properties, the two most important of which are the name and Text properties. The Name property holds the names of the form elements (that is, FieldName), while the Text property holds the value of the form element (value). In the program execution to Doc. Appenditemvalue (Fieldname,value) function, notes writes the domain name (fieldname) and the corresponding value (value) to the Notesitem object. Execute Call Doc. After the Save () function, all Notesitem objects in memory are stored in the associated form.

In the process of studying how to deal with an empty date, the initial idea was to write the empty string directly into the Notesitem, but after viewing the help and stepping through the execution of the script, it was found to be unworkable. The reason is that Notesitem's Text property is read-only and cannot be assigned a value at all, and there is no corresponding assignment function, apparently the function doc. Appenditemvalue (fieldname,value) is a function that assigns values specifically to the Text property.


V. The relationship between views and forms in notes


During the development process, due to an error, we accidentally discovered the skills associated with the view and form in notes. Notes sets the name and alias two properties for each form, and each of these two names identifies the form. In the development process, we developed a library with two forms, named Form1 and Form2 respectively, whose aliases have been inadvertently set to docu-ment, and each form has a view corresponding to it. When you design a Form2 view, although the columns in the specified view are associated with the fields in Form2, but when you double-click a document to edit it at run time, the form you switch to is FORM1, and multiple lookups don't solve the problem, so you try to change the alias of the form. , Document1 and Docu-ment2 respectively, and the result is solved. But then again the Form1 view cannot switch to the form, and then the FORM1 alias is changed back to document, and two views are switched correctly to their own form.

As it turns out, notes, when it comes to associating a view with a form, first look at whether the form has an alias, and if so, associate it with an alias, and once you have changed the alias of the form, you have to redesign the view, and you can't change the alias of the form if you don't want to Of course, you can also use this technique to implement the ability to switch from view to different forms. If the form does not have an alias, it is associated with a name.