A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
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:
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.
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.
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"
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.
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.
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:
The two source files are:
Server side: server.asp
Start building with 50+ products and up to 12 months usage for Elastic Compute Service