Link to home
Start Free TrialLog in
Avatar of alshaikh
alshaikh

asked on

How can I access the MS-SQL server using HTTP protocol?

How can I access the MS-SQL server using HTTP protocol from Delphi application?


Microsoft SQL 2000 supports data access using HTTP protocol and XML format. This means it is possible to access the MS-SQL server from any computer that supports HTTP protocol without the need to install the MS-SQL client on this computer.

For example, I can user this query from a URL:
http://www.myserver.com/nwind?sql=select+*+from+employees+for+XML+auto

Delphi with Midas components allows an application to connect to many data base servers using the web connection component and remote data module. This however requires an application server to be in middle to act as broker between the data base server and the thin client.

My objective is to be able to connect to MS-SQL server from Delphi over HTTP protocol using a ClientDataSet like component directly without a need for an application server in the middle.  

Does any one now of a method or a component available in the market to achieve this?



Note:  I am a new member in the Experts-Exchange community and I used all the points given to me for this question.
Avatar of Epsylon
Epsylon

You can use ADO or ODBC to connect to an SQL-server directly (with proper accountname and pasword).
Avatar of alshaikh

ASKER

Thanx Epsylon on your comment.

Both ADO and ODBC will require the MS-SQL client to be installed on the computer to establish the connection.

My objective is to connect to the MS-SQL server without having the MS-SQL client installed on the PC. (i.e. thin client connection).

I hope this clarifies the requirement.


Regards,
What do you mean with 'MS-SQL client'?
Thanx Epsylon on your comment.

MS-SQL Client is part of MS-SQL server.

Once installed on a Windows 95 , 98 or 2000, it will install the SQL ODBC driver (SQLSRV??.DLL) and Microsoft OLE DB Provider for SQL server as well.

With the thin client concept, you should not need any of the above. Your program should be able to connect to the data base server using other methods, such as the MIDAS or HTTP connection.

I want to avoid the MIDAS connection because it requires and application server to be in the middle and therefore I am exploring the HTTP connection, which is new with MS-SQL 2000.

For more information, you can examine the MIDAS examples of Delphi 5.


Regards,
When using Delphi 5's ADO components you don't need the MS-SQL Client...
I compiled a test program with ADO and it worked fine on my PC (my pc has Win2K and SQL DLL files). But when I tried it on other PCs (Win95) it failed.

Are saying that to deploy an ADO Delphi application, all you need is copy the EXE file to the user?s PC?


Regards,
Epsylon, of course the SQL client is needed. In fact, it is automatically installed with many MS products (Office etc.), but it still is a separate component. However, since it is so widely spread, it should not be too much of a problem to expect users to have it or install it. On the other hand, doing the SQL queries through HTTP would also be an elegant way of eliminating ANY client component needed, but I don't know of any existing Delphi interface for this.
Wouldn't installing MDAC be enough?
MDAC is needed anyway if you want to use recordsets...
Hi Epsylon,

We develop Intranet/Extranet based applications for a wide range of users on our corporate network. The only common factor we can assume is having Win9X on their PCs.

Some of the users also connect from outside the corporate network, from home or other places and they must be able to connect to the Extranet.

Not all of them have MDAC installed on their PCs. In addition users who are connecting to our Extranet from outside will not be able to access the database server even with MDAC installed.

Again, as highlighted in my question, MIDAS components do provide a solution for this problem but requires a middle layer to act as a broker.

My objective is to be able to connect to MS-SQL server from Delphi over HTTP protocol using a ClientDataSet like component directly without a need for an application server in the middle.  
With MIDAS you need to deploy some dll's along with your app, so it doesn't make any difference. You can also deploy MDAC or whatever with it using Install Shield or other install builders.
Next to that, MIDAS requires expensive licences for each client.

What I know is that the Open method of ADODB.RecordSet can have a URL as parameter, to retrieve records in XML format. I don't know if this feature is implemented in the Delphi ADO components.
Hi Epsylon,

You are right MIDAS needs one dll (midas.dll) to be installed along with the application and also requires expensive licences for each client, thats why I am searching for other alternatives.

The installation of midas.dll need not however to be installed manualy when your application is ActiveForm for example. Once the user opens the page that contains the ActiveForm using his browser, the brouser will prombt him/her to install the active form. Once the ActiveForm is installed it will automaticaly install with it the midas.dll

Midas however requires expensive licences for each client as you said and has some disadvantages also, thats why I am searching for other alternatives.

Regards,
 
Just as a thought, have you considered using Sockets?  A simple EXE running on your server could intercept calls at a set TCP Port.  Your application could connect to the server EXE, pass it an SQL string and then return a recordset as a Variant Array (not a true recordset, but easy to manipulate).

I've done this before.  It's not lightning fast (compared with a direct connection), but it works really well.  You'd need to build a fair bit of logic into the server EXE, but the client side EXE wouldn't need much at all.

Alternatively, you could also look into using MTS (or COM+ if you're using W2K), although this is usually an excercise in futility.  I spent almost 6 months trying to develop a multi-tiered app which ran over the internet using DCOM/MTS and I was constantly hitting brick walls.  Don't think of using MTS/DCOM behind MS Proxy Server.  It doesn't work no matter what MS claim.


Stu.
This is what I was talking about:


procedure TForm1.Button2Click(Sender: TObject);
var rs: OleVariant;
   i: Integer;
begin
 rs := CreateOleObject('ADODB.recordset');
 rs.Open('http://www.myserver.com/nwind?sql=select+*+from+employees+for+XML+auto');
 rs.MoveFirst;
 while not rs.EOF do
 begin
   ListBox1.Items.Add(rs.Fields['text_field']);
   rs.MoveNext;
 end;
end;
Hi Stuart,

You are right this method works. I have actually developed two components, one on the server side and one on the client side that use HTTP to connect to database servers. I noticed that, the performance is not good and there seem to be a reliability issue when the number of hits/second exceeds 100.  


Epsylon,

Thanx for your excelent effort, I tried the code you posted, but I could not make it work. I noticed in the documentation of Microsoft, that recordset.open accepts a web resource but could not see a reference to a SQL statement as a parameter to the Open method of the recordset.

Have you tried this code?


Regards,

Yes I have tried it. Only not with that URL. I just copy-pasted that from your question  :o)


I have been using Access a lot and often did something like this (ASP):


<!--#include virtual="adovbs.inc" -->
<%
DSN='a connection string to a database'
response.expires = -1
Response.ContentType = "text/xml"
strSQL = "SELECT * FROM table1;"
Set conntemp = Server.CreateObject("ADODB.Connection")
conntemp.Open DSN
set rstemp=conntemp.execute(strSQL)
rstemp.Save Response, adPersistXML
rstemp.close
set rstemp=nothing
conntemp.Close
Set conntemp = Nothing
%>


As you may know this is an ASP page. When requested, this page returns the records (XML-formatted) from Table1. You can use the URL of this page to retrieve these records into a recordset.
Hello?
Hi Epsylon,

I tried connecting over HTTP using ADO components in Delphi6, but unfortunately no success yet.

I used RDSConnection and ADODataSet. It works Ok when I do not specify a Name in the RDSConnection.ComputerName. But when I specify a name, it gives this error message: "Business object cannot be created".


Here is what I did:

1. I added to the form an RDSConnection
2. Left ServerName property as RDSServer.DataFactory (Default)
3. Added an ADODataSet and set the RDSConnection to the one I added in step 1.

Now if I make the Connected property of the RDSConnection to TRUE it works fine. If I change the ServerName to any computer in our network, including my PC, it gives this error message "Business object cannot be created".



 

Hi Epsylon,

I tried connecting over HTTP using ADO components in Delphi6, but unfortunately no success yet.

I used RDSConnection and ADODataSet. It works Ok when I do not specify a Name in the RDSConnection.ComputerName. But when I specify a name, it gives this error message: "Business object cannot be created".


Here is what I did:

1. I added to the form an RDSConnection
2. Left ServerName property as RDSServer.DataFactory (Default)
3. Added an ADODataSet and set the RDSConnection to the one I added in step 1.

Now if I make the Connected property of the RDSConnection to TRUE it works fine. If I change the ServerName to any computer in our network, including my PC, it gives this error message "Business object cannot be created".



 

Avatar of Russell Libby
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

To be PAQ/Refund

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Thank you,
Russell

EE Cleanup Volunteer
ASKER CERTIFIED SOLUTION
Avatar of PashaMod
PashaMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial