Solved

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

Posted on 2001-07-01
21
1,103 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:alshaikh
21 Comments
 
LVL 13

Expert Comment

by:Epsylon
ID: 6242112
You can use ADO or ODBC to connect to an SQL-server directly (with proper accountname and pasword).
0
 

Author Comment

by:alshaikh
ID: 6242136
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,
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6242182
What do you mean with 'MS-SQL client'?
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:alshaikh
ID: 6242205
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,
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6242209
When using Delphi 5's ADO components you don't need the MS-SQL Client...
0
 

Author Comment

by:alshaikh
ID: 6242235
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,
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 6242243
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.
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6242283
Wouldn't installing MDAC be enough?
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6242289
MDAC is needed anyway if you want to use recordsets...
0
 

Author Comment

by:alshaikh
ID: 6242972
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.  
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6243004
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.
0
 

Author Comment

by:alshaikh
ID: 6243690
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,
 
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6243729
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.
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6245040
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;
0
 

Author Comment

by:alshaikh
ID: 6247388
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,

0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6247617
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.
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6264207
Hello?
0
 

Author Comment

by:alshaikh
ID: 6264311
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".



 

0
 

Author Comment

by:alshaikh
ID: 6264312
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".



 

0
 
LVL 26

Expert Comment

by:Russell Libby
ID: 8701366
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
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 8816332
Per recommendation,

PashaMod
Community Support Moderator @Experts Exchange
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Printing problem 2 91
Delphi 2 59
delphi parse string to params 3 122
PHP preg_replace code convert to Delphi 14 55
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question