Using XMLHTTP to implement remote management database in ASP

xml| Data | database

A Review of Remote Database administer
A few years ago, the Cgi-bin module was used as a remote management database. Now, Cgi-bin is being used more and less because it runs slowly and is difficult to maintain.
In recent years, the Component Object Model (COM) has been widely used, unfortunately, the registration of COM components on the virtual host is a very cumbersome thing.

On the. NET or Java EE platform, we can easily develop n-tier structure WEB applications. But for the site, we have a more convenient way to remotely manage the database, for example: Using XMLHTTP and Ado/adox.

How RDBA Works
The workflow for Rdba ' s is as follows:
1 The client issues a query request to the server.
2 The server accepts and executes it, and then returns the value to the client.
3 The client receives the result from the server and displays it.
RDBA relies on two technologies:
1 channel for client and server interaction (Send command, return result): XMLHTTP.
2 The middle tier on the server to get the data: Ado/adox.

Rdba ' s work Flow diagram is as follows:

Using XMLHTTP

XMLHTTP is used to send or accept XML information through HTTP requests.

In fact, there are many other uses for XMLHTTP.
You can use it to send commands to the server (available in XML, string, stream, or unsigned array). command can also be used as a parameter to a URL
You can also send result information to the client (XML, string, stream, or unsigned array)
For more information, please refer to:

Using XMLHTTP on the client is simple, only 5 steps:
1. Create the XMLHTTP object.
2. Specify methods, URLs, and validations to open XMLHTTP to the server. The Open method can be either "POST" or "get".
3. Send request information to the server.
4. Wait until the result is received from the server.
5. Release the XMLHTTP object.

XMLHTTP Method:
Open Bstrmethod, bstrURL, Varasync, Bstruser, Bstrpassword
Bstrmethod: HTTP method for opening a connection, such as Get or POST
bstrURL: URL on the requested server, such as: http://Myserver/Mypath/Myfile.asp.
Varasync: Logical type. Indicates whether to synchronize calls. The default is True (the call returns immediately). However, it is usually set to false to wait for the server's return value.
Bstruser: User name information for authentication.
Bstrpassword: Authentication password information.

Send Varbody
Varbody: Types can be: BSTR, SAFEARRAY of UI1 (unsigned bytes), IDispatch interface for XML DOM objects, or IStream.

setrequestheader Bstrheader, Bstrvalue
Bstrheader: The name of the HTTP header to set.
The value of the bstrvalue:http header.
If you want to POST data, you can add an HTTP header to tell the recipient that the data is being sent:
Xmlhttp.setrequestheader "Content-type", "application/x-www-form-urlencoded"

XMLHTTP Properties:
onreadystatechange: Specifies the event-handler function that is invoked when the ReadyState property changes.
responsebody: in the form of unsigned byte arrays response
Responsestream: in the form of IStream response
responsetext: in the form of a string response
Responsexml: response in XML document

The following is a code fragment:

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 the connection asynchronously with the "POST" method
   'Send form data
   xmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
   xmlHttp.Send 'send an HTTP request to the server

   If Err.Number = 0 Then 'If successful
     retStr = xmlHttp.responseText 'waiting to accept data from the server
   Else
     retStr = "address not found" error message
   End If
   Set xmlHttp = nothing 'release object
   GetResult = retStr 'return response to caller
End Function
————————————————
Copyright statement: This article is the original article of CSDN blogger "inelm", following the CC 4.0 BY-SA copyright agreement, please reprint the original source link and this statement.
Original link: https://blog.csdn.net/inelm/article/details/16281

The parameter of the GetResult () function is the URL to request, and you can add the child parameters, such as:
Urlstr = "server.asp?cmd=" & cmd & "&db=" & DB & "table=" & Table
CMD: command type, such as: query, modify, delete, etc.
DB: Database name on the server
Table: Tables Name

What type of data do you use?

As mentioned above, there are many types of data that can be transmitted through the XMLHTTP channel. The most used are the XML DOM and STRING.

What type of data to choose depends on the needs of the program.

In order to get data from the server, both the client and the server use XML data as a good method. Because this can handle a large amount of data, request the server to add/delete/modify/Query records, or to sort/filter data on the client.

The purpose of this article is to remotely administer a database on a virtual host, so this requires the following two features:
1. Remote Management database: Add/delete/modify/Query remote database/table/field.
2. Data in remote Management database: Add/delete/modify/Query Records

So here we use XMLHTTP to send/accept strings

The benefits of doing this are obvious: You can refresh the page without blinking

Because it takes a string, you can simply use "Response.Write" to return the result. Once the server is response, the client uses the "Xmlhttp.responsetext" method to obtain the entire result and returns it to the caller to refresh the page data.

If asynchronous, then the client presses the button, there will be a certain delay, the client must wait to return data. This can be avoided if asynchronous or XML DOM is used.

Using ADOX

ADO cannot be used to remotely administer a database, and we can use ADOX.

ADOX is an extension of ADO, which provides more functions for working with databases. Once you are authenticated and your role is a database administrator, you can do anything with ADOX.

This sample requests:
Generates a dynamic Select menu that displays the name of the database under the specified path.
Generates a dynamic Select menu that displays the name of the table in the database selected in the above menu.
Lists the field names dynamically, based on the selected database and table.

Depending on the path of the virtual directory and the change of select, instant changes are displayed and there is no blinking refresh of the page.

Use Scripting.FileSystemObject to obtain the database name for the given path. Use ADOX to get the table name and field name. The code is as follows:

Gets the table name in a database:

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 Handling

  catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=" & Server.MapPath(DBName) 'Open the connection
                           'Add username and password if needed
  If Err.Number = 0 Then 'Successful
    ' Dynamically generate 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 it is a table
  'Add table name to select
         Response.Write "<option value=" & .Tables(i).Name & ">" &_
                        .Tables(i).Name &"</option>"
       End If
     Next
  End With
  Response.Write "</select>" ' select output is complete
  Else
   'Error message
   Response.Write "Error: Can't open database - " & DBName
  End If
  Set catDB = Nothing ' release ADODB.catalog object
End Sub

Get the field name in the table:

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 Handling

  catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & Server.MapPath(dbName) 'Open the connection
   'Add a username and password here if necessary

  If Err.Number = 0 Then ' Success
    'Found matching 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 field names
    With catDB.Tables(j)
      For i = 0 To .Columns.Count -1
        'If it is the primary key, select 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 field name
        Else
          Response.Write "<input type='radio' name='field' value=" &_
                         .Columns(i).Name & ">" & .Columns(i).Name & ""
                                                   'Display field name
        End If
      Next
    End With
  Else
    'Error message
    Response.Write "Error: Can't open database - " & dbName
  End If
  Set catDB = Nothing ' release ADODB.catalog object
End Sub

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:
/superviosr/database。

The two source files are:
Server side: server.asp
Client: supervisor.asp