Novell is now a part of Micro Focus

How to Use the ODBC Driver with NDS, Part 4: Example Application

Articles and Tips: article

Joshua Parkin
Software Engineer NamSys Inc.
jparkin@home.com

01 Jun 2001


This AppNote, the fourth in a series, demonstrates the creation of an application that takes an SQL query through the ODBC driver for NDS and build an XML file. The article includes an example application.

Introduction

The previous article in this series dealt with the theory of XML, including namespaces and well-formed XML (http://support.novell.com/techcenter/articles/ana20010405.html). This article will demonstrate how to create an application that takes an SQL query through the ODBC driver for NDS and builds an XML file with the resulting set.

Remember that an XML file is built in a pseudo-tree structure. You should have a root tag followed by a container tag, which should contain element tags. You will see examples of XML files throughout this article. Later on, I will discuss well-formed XML in greater detail.

One of the main advantages of XML is the ability to use XML files to send and merge data. The only major disadvantage to using XML files this way is tag name conflicts. To solve the problem use namespaces. Namespaces are used to segregate your XML tags from those of someone else, even if they have the same name. Namespaces can be anything you choose, such as your favorite food or your Web site.

Expanding on Well-Formed XML and More Review

If you plan to convert anything to XML, you must carefully plan the XML file. Make certain it is well-formed and conforms to the XML rules, so the data is interchanged among the platforms without error.

Remember what has been learned about XML. You know you need to have the <?XML version="1.0"?> included in the first line of the file. At this time, I will not be concerned with international languages. I will assume that everything will be kept in English and that there is no need to put in any language features. Now, you need to plan a root node. An XML document needs this root. I'll have the root node named <NDSTree>. This is the file layout so far:

<?xml version="1.0"?>
<NDSTree>
    -information from the NDS tree
</NDSTree>

Now, I will talk about node names. For now, it is easiest to use the actual node names from the tree. That means you need to decide which nodes you will use. For the purpose of this article, I will assume that you are going to make an XML file that contains a corporation's contact information on a Web page:

  • NDS_FullName

  • Title

  • Postal Address_Street

  • Postal Address_POBox

  • Postal Address_State

  • Postal Address_Street

  • Postal Address_ZIP or Postal Code (This is up to you and where you are).

  • Telephone Number

The XML File

So far, you have designed an XML file with all the important parts. The following is an example XML file.

<?xml version="1.0"?>
<NDSTree>
     <NDS_FullName>info</NDS_FullName>
     <Title>Info</Title>
     <Postal Address_Street>Info</Postal Address_Street>
     <Postal Address_POBox>Info</Postal Address_POBox>
     <Postal Address_State>Info</Postal Address_State>
     <Postal Address_Street>Info</Postal Address_Street>
     <Postal Address_ZIP>Info</Postal Address_ZIP>
</NDSTree>

Writing a Visual Basic program to Populate the XML File

The file you have created is missing something. There is a root object and some leaf objects, but, since there will be multiple entries, there must also be a leaf object that contains all the individual's data. Call it <NDSObject>. You have just planned what may be your first XML file. It is well-formed and contains all the important information, but the file is useless until it is filled with the appropriate data. To avoid entering all the employee's information in the fields yourself, you need to build a conversion program that will grab the important information and store it in the file.

Starting a New Visual Basic Project

In order to develop this conversion program, you need to run Visual Basic and start a new project. First, go to the Project References and select DAO Library 3.51. I cannot stress this step enough. You may spend hours debugging if you don't select this option. Also, if this option is not selected, the help button on the error screen will not tell the user to look at the DAO Library option. Save yourself a headache and do this first. Figure 1 shows this option selected.

Selecting the Microsoft DAO 3.51 Object LIbrary.

Designing the Form

Next, design the form layout. For this project, you need to add three command buttons, two list boxes , one combo box, one text box, and some labels that will give some indication of what each control will do. The layout could look something like the one shown in Figure 2.

Sample Form Layout.

One other thing you need to add is a common dialog box. This is used for finding out where the user wants to save the files. The textbox is for the user to enter a field name. The combo box will be populated with the object in the tree. One of the list boxes, list1, will be populated with the objects contained in the object the user chooses in the combo box. The other list box, list2, will display the selected objects to be used in the file. One command button, command1, will be used to add the selected objects from list1 to list2. Command2 will be used to allow the user to build the application. The command3 button will allow the users to clear list2.

Writing the Form's Code

Now, program the form. First, disable the buttons. This will stop the user from being able to use them when the buttons have nothing to do. A data control was not used because the PullTree function created in part 2 of this series will be used instead. (You can find "How to Use the ODBC Driver with NDS, Part 2" in the December 2000 issue of Novell AppNotes at http://developer.novell.com/ research/howto.htm.)

The function's code is as follows:

Public Function PullTree(sqlQuery As String, tmpTree As String, tmpDSN As String)
     
'See if the database is already open and if so close it
If Not (NDSTree Is Nothing) Then
  NDSTree.Close
End If
     
If tmpDSN = "" Then ' See if DSN -less connection
Connect = "ODBC;Driver={Novell ODBC Driver for NDS};DSN=;DBQ=" + tmpTree + ";"
Else ' DSN connection
Connect = "ODBC;DSN=" + tmpDSN + ";DBQ=" + tmpTree + ";"
End If
     
Set NDSTree = OpenDatabase("", False, True, Connect)
Set NDSrs = NDSTree.OpenRecordset(sqlQuery)
     
End Function

The PullTree function can be placed in a separate module or right into your frame. The choice is yours, depending on how your application works. In this tutorial, you can simply put it in your frame. Next, create the variables needed in order to use the PullTree function. They are as follows:

Public NDSTree As Database
Public NDSrs As Recordset
Public recCount As Integer

These global variables are used by the function and need to be passed throughout the application. They are to be defined in the Declarations portion of your code.

In the form load section you must open the database and pull out the fields needed to populate the combo box, combo1.

Private Sub Form_Load()
     
Connect = "ODBC;Driver={Novell ODBC Driver for NDS};DSN=;DBQ=gennexx;"
Set NDSTree = OpenDatabase("", False, True, Connect)
     
' Fill the Table combo Box
     
For I = 0 To NDSTree.TableDefs.Count - 1
 Combo1.AddItem NDSTree.TableDefs(I).Name
Next I
     
End Sub

Open the database using a DSN-less connection and use the tabledef property of the NDSTree object to pull out field (object) information. There is no need to run an SQL query because you are simply doing a general scan of the database and grabbing the appropriate properties. You do not need to create a recordset at this time because you are not pulling field values out of the tree. You are simply grabbing the fields contained in the tree.

If the program is run now, you will see the combo box populated with all the objects contained in that tree. Now you are able to browse through and select the appropriate object from which you want to grab info for the XML file . However, in order to make it worthwhile, you will need to code the combo box to do something when chosen. Use the click event.

Private Sub Combo1_Click()
Dim varQuery As String
Dim varTree As String
Dim varDSN As String
Dim ndsTableDef As TableDef
     
'dsnless connection
varDSN = ""
     
'sql query
varQuery = "SELECT [NDS_Name] FROM " & Combo1.Text
     
'tree name
varTree = "gennexx"
     
'calls the function to open the database to play
Call PullTree(varQuery, varTree, varDSN)
     
'populates the list box
Set ndsTableDef = NDSTree.TableDefs(Combo1.Text)
For I = 0 To ndsTableDef.Fields.Count - 1
   List1.AddItem ndsTableDef.Fields(I).Name
Next I
End Sub

Note: Here you will use the PullTree function that was created in part 2 of this series. You are going to use DSN-less connection. For the SQL statement you will use the combo1.text property to make sure you grab the appropriate information from the right object in the tree. Since the function opens the database and builds an appropriate recordset, use the tabledef property again in order to grab the field names inside that object and put them into List1.

Now, functionality must be given to the command buttons.

Private Sub Command1_Click()
'add button
List2.AddItem List1.Text
     
End Sub
     
Private Sub Command2_Click()
     
'build button
'If text1 is blank then notify user and make them enter a name.
If text1.text = "" then 
     Msgbox "You need to enter a file name, no extension please."
Else
     Call buildFile()
End If
     
End Sub
     
Private Sub Command3_Click()
     
'clear button
List2.Clear
     
End Sub

Now, for the piéce de résistance: the buildFile function. This function needs to do several things, but first get the location and filename to write to. Otherwise, you will get an error when you try to open and write. Also, run the query from the recordset (from pullTree) and then build the file as laid out above.

Start thinking about how you are going to build this function. First, name it.

Public Function buildFile()
    
End Function

Okay, that was the easy part. Now you need to call upon the common dialog box that was put on the form. If you have not done so, you should do that now. I named mine NDScmdlg. Therefore, I have to add the following lines of code:

On Error Goto CancErr
With NDScmdlg
     .CancelError=True
   .Filter = "XML files|*.xml|"
   .ShowSave
End With
On Error Resume Next
     
CancErr:
     If err.number=32755 then Exit Function

Note: I put in an error checker in case the user canceled the operation. If the user does cancels the operation, it will send back an error (#32755).

Next, you will access the file system object and open the file for writing.

Dim fso, f, ts
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(NDScmdlg.FileName)
Set ts = f.OpenAsTextStream(ForWriting, TristateFalse)

Write the first lines to the file. These is the <?XML?> tag and the root node tag.

Ts.WriteLine "<?XML version=""1.0""?>"
Ts.WriteLine "<NDSTree>"

Note the use of double quotations in the <?XML?> tag. The reason for it is that VB will now know to insert into the string a single quotation mark where each of the double quotations exists. Otherwise, VB is looking for a variable named 1 with property name 0, which you know cannot exist. This generates a really nasty error.

The file is now open, and you have started to write to it. So, now what? You can call upon the PullTree function to retrieve the recordset. First thing you need to do is DIM the appropriate variables needed for the pulltree function.

Dim varTree As String
Dim varSQL As String
Dim varDSN As String

Since you are going to need to know the number of records pulled from the query, there must be another variable.

Dim numofRecs As Integer

Next, you will build the query.

varSQL = "Select "
     
If List2.ListCount > 1 Then
     
   For i = 0 To List2.ListCount - 2
    varSQL = varSQL + "[" + List2.List(i) + "], "
   Next
   varSQL = varSQL + "[" + List2.List(List2.ListCount - 1) + "] "
   varSQL = varSQL + "From " + Combo1.Text
Else
     
   varSQL = varSQL + "[" + List2.List(List2.ListCount - 1) + "] "
   varSQL = varSQL + "From " + Combo1.Text
End If

Check to see how many items are selected. If there is only one selected, then build the statement with one entry. Otherwise, there needs to be a comma between the fields in the SQL query. That is why there is the routine that puts the last field in the list without a comma at the end. If this were not there, you would get an SQL syntax error.

Next, define the rest of the variables used for the pullTree function and call the function.

varTree = "[treename]"
varDSN = ""
     
'call the PullTree function to run the SQL query
Call PullTree(varSQL, varTree, varDSN)

Here, you are going to use a DSN-less connection again. The choice is yours. All you have to do is put in a tree name. Next, count the number of records returned from the query and loop to write that many records into the file. For each object returned, start a new object in the XML file. Before the information regarding that object is written to the file, write the <NDSObject> tag. Write the field names as the tags for the returned objects. When it is all done, close with a </NDSObject> tag.

NDSrs.MoveLast
numofRecs = NDSrs.RecordCount
NDSrs.MoveFirst
     
'loop for the number of records
For x = 1 To numofRecs
 ts.WriteLine "   <NDSObject>"
 'loop for the number of fields pulled
 For i = 0 To List2.ListCount - 1
  If NDSrs(List2.List(i)) = Null Then
    ts.WriteLine "      <" + List2.List(i) + "></" + List2.List(i) + ">"
  Else
    ts.WriteLine "      <" + List2.List(i) + ">" + NDSrs(List2.List(i))
	+ "</" + List2.List(i) + ">"
  End If
 Next
 ts.WriteLine "</NDSObject>"
NDSrs.MoveNext
Next
NDSrs.Close
ts.Close

There you go. You have written an XML converter for the NDS tree. It's pretty simple, isn't it?

So, what is next? Well, in case you didn't notice, there are some problems with the program. For instance, the tags have spaces in their names. This must be fixed, and the easiest way is to replace the spaces with underscores. However, to make the program much more functional, you can allow users to name their own XML tags instead.

To allow for this, add a new function named tagNames. Pass a variable to it with the number of fields in the list. The function code will look like this:

Public Function tagNames(tmpNum As Integer)
    
End Function

Also, create a variable to store the new tag names to. Limit the total number of tags to 1000. That means there is a limit to the total number of objects you can pull for each entry in the tree. One-thousand should be sufficient for now. You can increase or decrease the limit amount as you see fit. The variable, varListCount, is there only to keep track of how many records were selected.

Private vartagNames(1000) As String
Public varListCount

Now, you can build the function. The function will use the input box function to grab the input from the user.

Public Function tagNames()
     For i = 0 To varListCount -1 
       tmpName = InputBox("Enter the tag name to represent" + List2.List(i))
         if tmpName = "" then msgbox "You must enter a tag name!"        
         vartagNames(i) = tmpName
     Next
End Function

Figure 3 shows an example input box.

Example Input Box.

Now, call it from the BuildTree function. You will need to add the tmpNum as well. Since this is used for the loop to build the appropriate number of tagnames, use the number of records chosen for the field (list2.listcount) and add that to varListCount.

VarListCount = list2.listcount
Call tagNames

Of course, this begs the question: How do you put the tagnames into the file when the buildTree function just puts in the list2.text values? You will need to change that module slightly. Look at the loop and make the appropriate changes.

For x = 1 To numofRecs
  ts.WriteLine "   <NDSObject>"
  'loop for the number of fields pulled
  For i = 0 To List2.ListCount - 1
    If NDSrs(List2.List(i)) = Null Then
       ts.WriteLine "      <" + vartagNames(I) + "></" + vartagNames(I) + ">"
    Else
        ts.WriteLine "      <" + vartagNames(i) + ">" + NDSrs(List2.List(i))
		+ "</" + vartagNames(i) + ">"
    End If
  Next
  ts.WriteLine "</NDSObject>"
NDSrs.MoveNext
Next

Now you have implemented code that will allow the user to create whatever tags he or she wishes as the file is being created.

Notes on Usage

Keep in mind the size of the returning recordset. If it is excessively large, you may need to create a significantly large XML file. Remember, you can break down the tree using SQL queries to fit whatever criteria you are looking for. (See the AppNote series on "How to use the ODBC driver for NDS," as previously mentioned.)

Currently, the program does not check to see if the file chosen already exits. If it does, the existing file will be automatically overwritten. To change this feature, implement a check using the File System Object's check FileExists.

Another thing the program does not handle is NameSpaces. This means that if you were to generate two files with the same node names and merged the two, you would run into some problems. The XML parser (whichever you used, be it DOM or XSL) wouldn't know which one to use. This is easily remedied. You should implement a check for the special characters that were mentioned before. This check can be done by doing an INSTR function and replacing the text using the MID function. This would be important if you did not know what the fields contained. If you were doing a query that you were certain did not have such character as <, >, &, or ", then you wouldn't have to worry about it. But you never know, and that is why I have brought it to your attention.

Conclusion

XML is being implemented everywhere. More and more tools arrive on the scene each day. To keep up with everything, I recommend http://www.xml.com/. This site provides the most unbiased up-to-date information regarding XML. They keep track of what's going on with XML, including what the W3 consortium has implemented, their working papers, and more. It also examines the new technology that is coming out regarding XML and how it works. There are also examples and definitions of the different features of XML. Quite often, you will find tutorials on how to use EJB (Enterprise Java Beans) with IBM's WebSphere product and how to use Microsoft's DOM to generate web pages. You will also find a regular column discussing what's new in parsers and how each stands up to the standards set by the consortium. The web site is updated weekly, so you are not left in the dark regarding any of the developments. This is always a good resource and a terrific place to start.

WROX is coming out with a Professional XML book that is supposed to be huge! I haven't seen it, yet, but word on the street is that it is excellent and covers everything regarding XML to date.

Keep up to date regarding this exciting development by using the aforementioned resources, and let your creative juices flow. This is the ground floor and there is lots of room to grow with this exciting new technology.

* Originally published in Novell AppNotes

Disclaimer

The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.

© Copyright Micro Focus or one of its affiliates