Remote Database Administer via XMLHTTP

• Download source files - 143 Kb

A Review of Remote Database Administer

Several years ago, CGI-BIN modules were used to administer remote databases. But recently, CGI-BIN is used rarely due to the fact that it runs slowly and is hard for maintenance.

These few years, Component Object Model (COM) was widely used and worked well. Unfortunately it is not easy to register COM components on virtual hosting.

Yes, on .NET or on J2EE platform, we can make very well n-Tier Web applications. But for a web site, we have other easy ways to administer remote databases, for example, using XMLHTTP and ADO/ADOX.

How RDBA works

RDBA's working flow is as follows:

1. Client asks server to implement a query.
2. Server receives the request and implements it, then returns the results to client.
3. Client receives the results from server and shows them.

Two important aspects for RDBA are:

1. The channel between client and server to send commands and receive results. It is XMLHTTP.
2. The middle tier between the front-end of server and database to retrieve data. It is ADO/ADOX.

RDBA's working flow showed as Fig. 1

Using XMLHTTP

Just as its name implies, a XMLHTTP control can be used to send and receive XML information using HTTP requests.

In fact XMLHTTP does more than that. It can send commands to the server with XML, string, stream, or an unsigned array. Commands can also be the parameters of URL. It can send result to the client with XML, string, stream, or an unsigned array.

Using XMLHTTP on client site is quite simple, only 5 steps:

1. Create XMLHTTP object.
2. Open XMLHTTP to the server with specifying method, URL and authority. As the same with HTTP, open method can be "POST" or "GET".
3. Send request information to the server.
4. Wait until the result has been received from the server.
5. Free XMLHTTP object.

XMLHTTP Methods

Open bstrMethod, bstrUrl, varAsync, bstrUser, bstrPassword

• bstrMethod: HTTP method used to open the connection, such as GET or POST.
• bstrUrl: Requested URL on the server. This must be an absolute URL, such as: http://Myserver/Mypath/Myfile.asp.
• varAsync: Boolean. Indicator as to whether the call is synchronous. The default is True (the call returns immediately). But usually set it to False to wait for results from the server.
• bstrUser: Name of the user for authentication.
• bstrPassword: Password for authentication.

Send varBody

• varBody: The acceptable VARIANT input types are BSTR, SAFEARRAY of UI1 (unsigned bytes), IDispatch to an XML Document Object Model (DOM) object, and IStream.

setRequestHeader bstrHeader, bstrValue

• bstrHeader: HTTP header name to set.
• bstrValue: Value of the header.

If you need to POST data you can add a header that tells the receiver you’re sending FROM data: xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

XMLHTTP Properties

• onreadystatechange: Specifies the event handler to be called when the readyState property changes.
• responseBody: Response entity body as an array of unsigned bytes.
• responseStream: Represents the response entity body as an IStream.
• responseText: Response entity body as a string.
• responseXML: Response entity body as an XML document.

Following is the snippet from my downloadable sample source:

Function GetResult(urlStr)
  Dim xmlHttp
  Dim retStr
    
  Set xmlHttp = CreateObject("Msxml2.XMLHTTP")    'create object
  On Error Resume Next               'error handling
  xmlHttp.Open "POST", urlStr, False 
          'open connection using "POST" method, asynchronously
  'sending FORM data
  xmlHttp.setRequestHeader "Content-Type", _
          "application/x-www-form-urlencoded"
  xmlHttp.Send        'send HTTP request to the server

  If Err.Number = 0 Then      'if no error occurred
    retStr = xmlHttp.responseText 'wait for receive response from server
  Else
    retStr = "Url not found"      'error message
  End If
  Set xmlHttp = nothing      'free the object
  GetResult = retStr      'return the response to the caller
End Function

The parameter in GetResult() function is an URL string, it is the request page URL on the server. You can add sub-parameters along with it, for example:

• urlStr = "server.asp?cmd=" & cmd & "&db=" & db & "table=" & table
• cmd: command, such as query, modify, delete, etc.
• db: the database name on the server.
• table: the table name on the server.

If no error occurred, the only thing that client needs to do now is to sit there and wait until the response come bake from the server (retStr = xmlHttp.responseText).

At last, GetResult() returns the response to the caller to show the results.

Which type of data to use?

As told above, the acceptable data types transferring within XMLHTTP pipe are BSTR, SAFEARRAY of UI1 (unsigned bytes), IDispatch to an XML Document Object Model (DOM) object, and IStream. The most data type used is XML DOM and STRING.

Which data type you chose to use depends on your application goal.

For retrieving data from the remote server, it is better to use XML data on both server and client sites in order to treat with a great deal data and request the server to add/remove/modify/query records, as well as to sort/filter data on the client. This article does not focus on XML COM.

The sample in this article is for the webmasters who uses virtual hosting. So it needs two basic capabilities:

1. Administer remote database: add/remove/modify/query remote databases/tables/fields.
2. Administer data in remote database: add/remove/modify/query records.

So this sample focuses on sending/receiving STRING through XMLHTTP.

The advantages are obvious:

• Easy to program like classic ASP (is the same as common ASP on server site, a little addition on client site - add several commands about XMLHTTP),
• On line implement RDBA,
• Refresh page data without reload page.

Due to response via STRING, it is simple to use script command "Response.Write" to return the results trough XMLHTTP pipe, nothing changed on the server. Once the server finishes to response, the client then uses "xmlHttp.responseText" method to get the whole results, returns it to the caller for refreshing and display page data.

Some delay, however, will come into being on client site after push the button to send request if you use asynchronous model. Client will sit and wait for results coming back from the server. It can be avoided by using XML DOM object and synchronous model.

Using ADOX

ASP Web services generally uses ADO as a middle tier to retrieve data between the front-end and database. ADO objects implement add/remove/modify/query records by executing the queries. I won't discuss ADO here.

But ADO is not able to administer remote database, such as add/remove/modify/query remote databases/tables/fields. ADOX should be used instead.

ADOX is extended ADO, it provides more functions to deal with database. If you are authorized database administrator you can do anything inside the database, such as add/remove/modify/query the databases/tables/fields/index, add/remove/modify the group/user/password, and more...

This sample shows:

• Produce dynamic SELECT menu, in which exist the databases name according to a given path.
• Produce dynamic SELECT menu, in which exist the table name according to the selected database in the first menu.
• Dynamically list the fields according to the selected database and table.

Changing of the virtual path of database and the OnChange handler of the SELECT menu called will result in subsequently all different results. Refresh page data without reload page!

Using Scripting.FileSystemObject to get the database according to a given path. Using ADOX to get the tables name in a database and the fields name in a table. The codes look like:

Get the tables name in a database (parameter DBName is the database name):

Sub GetTables_Server(DBName)
  Dim i
  Dim catDB             'ADODB.Catalog object

  Set catDB = Server.CreateObject("ADOX.Catalog")  
          'Create ADODB.Catalog object

  On Error Resume Next  'Error handle

  catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & Server.MapPath(DBName)  
                              'Open connection
                           'Don't forget to add your user name and 
                           'password if needed.
  If Err.Number = 0 Then   'If no error occurred
    ' Produce dynamic SELECT menu
    Response.Write "<select name='selTable' onChange=ShowResult('fields')>"
    With catDB
      For i = 0 To .Tables.Count -1
       If .Tables(i).Type = "TABLE" then ' If is a table
     'Add the table name into the menu
         Response.Write "<option value=" & .Tables(i).Name & ">" &_
                        .Tables(i).Name &"</option>"
       End If
     Next
  End With
  Response.Write "</select>" ' End of menu produce
  Else
   'Error message
   Response.Write "Error: Can't open database - " & DBName
  End If
  Set catDB = Nothing ' Free ADODB.catalog object
End Sub

Get the fields name in a table (parameter tableName is the table name):

Sub GetFields_Server(dbName, tableName)
  Dim i, j
  Dim catDB    'ADODB.Catalog object

  Set catDB = Server.CreateObject("ADOX.Catalog")  
          'Create ADODB.catalog object

  On Error Resume Next  'Error handle

  catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & Server.MapPath(dbName)  'Open connection
            'Don't forget to add your user name and password if needed.

  If Err.Number = 0 Then        ' If no error occurred
    'Find the table matched the table name
    With catDB
      For j = 0 To .Tables.Count -1
    If (.Tables(j).Type = "TABLE") and (.Tables(j).Name = tableName) Then
          Exit For
        End If  
      Next
    End With

    'List the fields name
    With catDB.Tables(j)
      For i = 0 To .Columns.Count -1
        'If is primary key, then check it.      
        If .Columns(i).Properties("Autoincrement") = True Then
          Response.Write "<input type='radio' name='field' value=" &_
                      .Columns(i).Name & " checked=true>" & _
                      .Columns(i).Name & ""
                                  'Display the field name
        Else
          Response.Write "<input type='radio' name='field' value=" &_
                         .Columns(i).Name & ">" & .Columns(i).Name & ""
                                                   'Display the field name
        End If
      Next
    End With
  Else
    'Error message
    Response.Write "Error: Can't open database - " & dbName
  End If
  Set catDB = Nothing ' Free the ADODB.catalog object
End Sub

NOTE: All the tables in this sample use an Autoincrement field as the primary key. If the primary key in your tables is not Autoincrement type or there is not a primary key in your tables, you should rewrite above codes. In my opinion, use a Autoincrement field as primary key is a good habit.

Use the sample

A screenshot of GOIS RDBA is showed as Fig. 2.

System required:

To run this sample, the follows should be installed on your PC: Windows 98/Me/XP/2000 MSXML 4.0 MS Access 2000，IE 5.5 or above.

Install and execute:

1. Download the sample ZIP file - supervisor.zip.
2. Unzip the file under a path in your IIS, for example: C:\Inetpub\wwwroot\supervisor
3. Input "localhost/supervisor/supervisor.asp" in the url address on your IE, then GO.
4. By a little changing, such as change the path, authorize the user, you can upload this sample source to your virtual hosting to administer your own database.

There are several sample databases and two source files in the downloadable ZIP file. The sample databases will automatically unzip to a "database" path under the working path, for example: /supervisor/database

Two source files are:

• A service page codes runs on the server: server.asp
• A client page codes runs on client site: supervisor.asp

The functions of the buttons on the demo:

• Refresh: Refresh database path. Rewrite the path and push this button will result in subsequently all different results.
• List: List the selected field's contents.
• Max: Get the MAX value and display it in the ID editor. It is called automatically after select a table.
• Count: Count the selected field's number.
• Show: Get and show the content specified by ID and selected field.
• Modify: Modify the content specified by ID and selected field.
• Delete: Remove the record specified by ID.
• SQL: Execute SQL query which is input in the text editor(upper). The text editor below will show the result of execution.
• +: Expand/Contract the text editor.

Epilogue

This article describes how to remote administer database via XMLHTTP and ADOX. It is useful to webmaster who uses virtual hosting. I'll add other functions to it. If you have any problems or suggestions, feel free to write me: lianbo@gois.ws, or leave your message in my site: http://eng.gois.ws/

Enjoy programming!

Reference

• http://msdn.microsoft.com/library/en-us/wcexmlht/htm/ceconxmlhttpobject.asp