Delphi app, connection to DB over Internet
Harry
Hi all,
i just wondered how to solve this and have no idea at all. I have a simple Desktop application (developed with Delphi6 enterprise) and now need access to a MS-SQL-Server that is somewhere else. The only connection i have is an Internet-connection.
So, which approach should i use to read and write some records to that remote database?
- Can i directly connect to the DB by using the RemoteServer and RemoteProvider Parameters in ADO's connection string? And how about the security?
- Do i need to write a WebModule or something else.
As you can see, i really would be glad about any help.
Thanks,
Harry
Roland Van Waas
Roland
"Harry" <harald...@gmx.net> wrote in message news:3b9f1b82$1_1@dnews...
Ed Dressel
Alessandro Federici
> i just wondered how to solve this and have no idea at all. I have a simple
Desktop application (developed with Delphi6 enterprise) and now need access
to a MS-SQL-Server that is somewhere else. The only connection i have is an
Internet-connection.
> So, which approach should i use to read and write some records to that
remote database?
You have plenty of ways to do these things.
Check www.msdelphi.com and see how you can get XML for one.
Using SQL 2k XML features is another one.
Using SOAP is a third.
> - Can i directly connect to the DB by using the RemoteServer and
RemoteProvider Parameters in ADO's connection string? And how about the
security?
I think those are MIDAS related properties but I just woke up ;-)
> - Do i need to write a WebModule or something else.
Not necessarily.
Tell me a little bit more about your application.
Harald Feigel
thanks for your posting. Now, a little more details about the app. Like
already mentioned, it's a pure desktop application (no web application).
Using that app, i am encoding WindowsMedia Files. Now, somewhere in the
evening, i have to upload all the Windows Media Files to another machine. I
solved that already using FTP file transfer. Additionally, i do now have to
send data (patient identification data, medical diagnoses) for each file
that i have transmitted via FTP. The machine that receives that data will
have to write it into MS-SQL tables. The amount of data is actually very
small.
It's up to me, to decide, how the receiving machine will handle that. So if
e.g. a WebServer is necessary, it would be installed.
So if you have any recommendations, i would be glad to here them. Now, i
will read your articles on msdelphi.com and hopefully get a bit trained on
that subject.
Thanks,
Harry
"Alessandro Federici" <alef@[nogarbage]bigfoot.com> wrote in message
news:3b9f8948$1_2@dnews...
Jim Elden
We do this all the time.
First add the IP address and server name to your HOSTS file. This is
not necesary but makes things more readable..
Then use the SQL Server Client Network Utility to set up an alias to
the server using TCP/IP. If you skipped the HOSTS entry then use the
server IP for the Server Name.
Then build the connection string for the ADO components just like you
would on your local network.
RemoteServer and ProviderName properties are for MIDAS/DataSnap,
so leave them blank.
HTH,
Jim
Jim Elden
TCP/IP...
Alessandro Federici
news:3b9f90de_2@dnews...
> thanks for your posting.
No problem. A note about Jim's solution: you may need open a port on the fw
and that may be bad...
Back to your question.
> The machine that receives that data will
> have to write it into MS-SQL tables. The amount of data is actually very
> small.
> It's up to me, to decide, how the receiving machine will handle that. So
if
> e.g. a WebServer is necessary, it would be installed.
I see. Easy ;-)
Here's how I would approach this:
1) Build a COM object with the methods you need. Let's assume the interface
of the obj looks like this:
IPatientStorage = interface
function GetPatients(const aFilter : string) : string;
function StorePatient(const aName, anAddress, aCity [etc etc] : string;
Age : int; [etc etc]);
OR
function StorePatient(XMLPatientData : string);
end;
Two things you should pay attention to:
A) Methods should be stateless. Basically they work in a
call/execute/nomore fashion. There's no state between a method call and the
other. So for instance, no Init and then GetPatients. You pass all the
parameters in one shot, get the results and you are done.
This is because of two reasons: scalability in the first place and how the
SOAP works in the second (related to the first).
If you need more than one result (imagine some errors) use out parameters.
For instance, GetPatients(aFilter : string; out Errors : string): string;
BTW the GetPatients would return XML generated the way I explained in the
article http://www.msdelphi.com/Articles/DSOAP_ADO_XML/DSOAP_ADO_XML.html
B) The StorePatient method. I wrote two possible ways to handle it. The
first time uses a method with parameters. Nice to read and use BUT, in case
of frequent changes, not so good. Option 2 (function
StorePatient(XMLPatientData : string) ) is less OO but more practical. Pick
your way. Interfaces should not be changed.
IN this object you will then access your db the way you know already.
2) Run the WSDL Export wizard like I explain in
http://www.msdelphi.com/Articles/SOAP/Using%20SOAP%20with%20Delphi.htm
and create the SOAP infrastructure
3) Run my DSOAP wizard and create the Delphi interfaced class
4) Use the thing ;-)
Enjoy and lemme know how it goes
Harald Feigel
Harry
Alexis Rios
response.
http://www.yourserver.com/query.asp?param1=value¶m2=value
Do not write any html to the response just simple XML
then with MSXML you can load the remote url and get the results.
This shows how powerful and simple XML is..
James Poll
Or use SQL Server 2000. It has an XML option
that you can use over the internet.
If you need help, go to www.delphizine.com. They are currently
running articles on this.
Jim
"Alexis Rios" <ale...@ariox.com> wrote:
>Create an .asp page at the web server that creates an XML document as the
>response.
>
>http://www.yourserver.com/query.asp?param1=valueśm2=value
Alessandro Federici
news:3ba0db73$1_2@dnews...
> Or use SQL Server 2000. It has an XML option
> that you can use over the internet.
You can get the same using 7 as well.
JW
client-server model using ADO as the database connectivity. steps involve:
a - server
1. enable tcp/ip with default port# 1433 on your ms/sql server box. this
allows internet package (yours) to be captured by sql listener
2. enable mixed-mode authentication on sql server
3. your server ip must be defined/registered on a dns server (try ping
to make sure it replies)
4. install ms/ado 2.x on your server.
b - clients
1. install ms/ado 2.x (2.61 is recommended) on every client pc (win2k
not needed) who uses your app. if you install ms/ado that is less than 2.5,
then you have to deploy sql client (such as DBMSSOCN.DLL) also.
2. on your delphi TADOConnection.ConnectionString, assign the following
values to it:
Provider=SQLOLEDB.1;
User ID=sqluserlogonid;
Password=sqllogonpassword;
Persist Security Info=True;
Initial Catalog=yoursqldatabasename;
Data Source=www.yoursite.com (can be ip too);
Network Library=DBMSSOCN (this shipped with ado2.5 or above);
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=ClientPcName;
Use Encryption for Data=False;
Tag with column collation when possible=False
the above parameters are defualt by delphi ide. you may not need all of
them. however, it never bother me to leave them in.
the above solution is fantastic and fast if your clients are all ms/windows
and that you do not need load balancing. otherwise, use multi tiers.
in addition, if your clients are all windows 2000 pro., you do not have to
do anything on their pc other than deploying your app only.
hope this helps
jw.
"Harry" <harald...@gmx.net> wrote in message news:3b9f1b82$1_1@dnews...