BizTalk Utilities CV ,   Jobs ,   Code library
 
Home Page


Add/Edit your code items
Search the code library
Browse for the code library

WCF, WS, SOAP

WCF, WS, SOAP

WCF, WS, SOAP Contents

Binding XML Data Island to Web Forms
Security Model for Web Services
What is a XML Web Service?
 
 

<< UncategorizedXALAN >>

By lianbo
First Posted 08/16/2002
Times viewed 368

Remote Database Administer via XMLHTTP

This post contains attachments
v20020816093716.zip 

Summary This article describes how to administer remote database via XMLHTTP and ADOX. It is useful to webmasters who use virtual hosting. Source codes is downloadable at the end.

A Review of Remote Database Administer Several years ago, CGI-BIN modules were used to administer remote database. But recently, CGI-BIN was used rarely due to the fact that it runs slowly and is hard to maintenance. These few years, Component Object Model (COM) was widely used and affected 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 database, for example, using XMLHTTP and ADO/ADOX. How RDBA works RDBA's working flow is as follows: 1 Client ask server to implement a query. 2 Server receive the request and implement it, then return the results to client. 3 Client receive the results from server and show 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. For more details please read the article linked below. 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 Method: 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 codes 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 xmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 'sending FORM data 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 a 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 chose to use depend 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 requests: 　Produce dynamic SELECT menu, in which exist the databases name according to a given path. 　Produce dynamic SELECT menu, in which exist the tables 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 handle of the both 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 "" 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 "" & .Tables(i).Name &"" End If Next End With Response.Write "" ' 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 Next End With ' List the fields name With catDB.Tables(j) For i = 0 To .Columns.Count -1 If .Columns(i).Properties("Autoincrement") = True Then 'If is primary key, then check it. Response.Write "" & .Columns(i).Name & " " 'Display the field name Else Response.Write "" & .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: /superviosr/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 any problems or suggestions, please feel free to write me: lianbo@gois.ws, or leave your message in my site: http://eng.gois.ws Enjoy programming! Any suggestion and question please feel free to write me: lianbo@gois.ws Welcome to my site - GOIS Programmer: http://eng.gois.ws

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote :  

<< UncategorizedXALAN >>



Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown:

 
 

    Email TopXML