Solved

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

Posted on 2001-07-01
21
1,094 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
Comment Utility
You can use ADO or ODBC to connect to an SQL-server directly (with proper accountname and pasword).
0
 

Author Comment

by:alshaikh
Comment Utility
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
Comment Utility
What do you mean with 'MS-SQL client'?
0
 

Author Comment

by:alshaikh
Comment Utility
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
Comment Utility
When using Delphi 5's ADO components you don't need the MS-SQL Client...
0
 

Author Comment

by:alshaikh
Comment Utility
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
Comment Utility
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
Comment Utility
Wouldn't installing MDAC be enough?
0
 
LVL 13

Expert Comment

by:Epsylon
Comment Utility
MDAC is needed anyway if you want to use recordsets...
0
 

Author Comment

by:alshaikh
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:Epsylon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hello?
0
 

Author Comment

by:alshaikh
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Per recommendation,

PashaMod
Community Support Moderator @Experts Exchange
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now