Board index » delphi » ADO Migration Questions and Issues

ADO Migration Questions and Issues


2003-08-13 02:46:48 AM
delphi146
I am trying to migrate my FlashFiler local area network client/server apps
to MS SQL using ADO. I recently bought, and have gone through, the book
called Advanced Delphi Developers Guide to ADO but still am having problems
putting it all together since I am new to both ADO and MS SQL. Here are my
questions:
1. I want a software app client on computer B to use the MS SQL tables
located on computer A (the server). Apparently the TADOConnection component
is used in some way to do this via the connection string, although I may be
wrong here. The book leaves this unclear. Exactly how can I find and
communicate with MS SQL tables which are located somewhere on a LAN other
then where the client machine is located?
2. Anyone have an example app with src on how this is done using the ADO
components which come with Delphi?
I am using Delphi 7 Pro on a Windows XP Pro machine.
Thank you for any assistance you can give on this matter.
Regards;
Bob Dalton
 
 

Re:ADO Migration Questions and Issues

Bob,
This might help (article is BDE specific, but it is the concept that is
important)
bdn.borland.com/article/0,1410,28160,00.html
Essentially, your TADOConnection points to the database (ConnectionString
contains user/password, etc, not specific tables), TADODataSet point to your
TADOConnection (Connection property), write some SQL (CommandText property)
and Open (Active := True). that is it.
Once your comfortable with that...
community.borland.com/article/0,1410,22571,00.html
then maybe
community.borland.com/article/0,1410,20847,00.html
community.borland.com/article/0,1410,16123,00.html
or perhaps
community.borland.com/article/0,1410,20563,00.html
Good luck,
krf
"Bob Dalton" <XXXX@XXXXX.COM>writes
Quote
I am trying to migrate my FlashFiler local area network client/server apps
to MS SQL using ADO. I recently bought, and have gone through, the book
called Advanced Delphi Developers Guide to ADO but still am having
problems
putting it all together since I am new to both ADO and MS SQL. Here are my
questions:

1. I want a software app client on computer B to use the MS SQL tables
located on computer A (the server). Apparently the TADOConnection
component
is used in some way to do this via the connection string, although I may
be
wrong here. The book leaves this unclear. Exactly how can I find and
communicate with MS SQL tables which are located somewhere on a LAN other
then where the client machine is located?

2. Anyone have an example app with src on how this is done using the ADO
components which come with Delphi?

I am using Delphi 7 Pro on a Windows XP Pro machine.

Thank you for any assistance you can give on this matter.

Regards;

Bob Dalton


 

Re:ADO Migration Questions and Issues

Quote
You are correct. The "Data Source =" parameter of connection string
should
point to the server machine.
or Data Source = ComputerA
if ComputerA is the name of your server computer.
Thanks for your reply.
Is there anyway to find this automatically for MS SQL on a LAN? Since my
applications are sold "off the shelf", and not done on a contract basis, I
have no way to know what this would be in advance to allow it to be hard
coded. The only other alternative I see would be for the customer to have to
provide this to my apps via some form of configuration dialog. I would
prefer to avoid that if possible since configuration allows too many chances
for customers to{*word*222}things up......
In my FlashFiler apps this automatic finding is easy since the FlashFiler
Server broadcasts on the network making it easy to locate. I am just
learning the ins and outs of the MS SQL server....
Regards;
Bob Dalton
 

Re:ADO Migration Questions and Issues

Thanks for the reply but none of the links you provided answered my
question. Perhaps I didn't phrase it clearly enough. I am not new to
database work (been doing it for many years now) but simply new to ADO and
MS SQL.
My big question is how do I automatically find the MS SQL server on a network
so that I can generate a connectionstring to have a client connect with it
on a different machine on the same LAN?
Thanks anyway!
Regards;
Bob Dalton
 

Re:ADO Migration Questions and Issues

In article <3f39c20e$XXXX@XXXXX.COM>,
XXXX@XXXXX.COM says...
Quote
Thanks for the reply but none of the links you provided answered my
question. Perhaps I didn't phrase it clearly enough. I am not new to
database work (been doing it for many years now) but simply new to ADO and
MS SQL.

My big question is how do I automatically find the MS SQL server on a network
so that I can generate a connectionstring to have a client connect with it
on a different machine on the same LAN?

Thanks anyway!

perhaps the following code snippet will help:
function TdmMain.BuildADOConnectString(ADOConnection : TADOConnection)
: String;
begin
result := ADOConnection.ConnectionString;
dbADO.Close;
// calling EditConnectionString displays the dialog, and
// sets the ConnectString property to the resulting value.
if EditConnectionString(ADOConnection) then
result := ADOConnection.ConnectionString;
end;
procedure TdmMain.ConfigADODatabase;
var
IniFile : TIniFile;
ConnectString : String;
begin
IniFile := TIniFile.Create('SomeIniFile.ini');
try
ConnectString := BuildADOConnectString(dbADO);
IniFile.WriteString('ADODATABASE','CONNECTSTRING',ConnectString);
finally
IniFile.Free;
end;
end;
function TdmMain.ConnectADODatabase : Boolean;
var
IniFile : TIniFile;
ConnectString : String;
begin
result := False;
IniFile := TIniFile.Create('SomeIniFile.ini');
try
ConnectString := IniFile.ReadString
('ADODATABASE','CONNECTSTRING','');
dbADO.ConnectionString := ConnectString;
finally
IniFile.Free;
end;
try
dbADO.Connected := True;
result := True;
except
if MessageDlg('Cannot connect to ADO database.'+#13+#10+'Configure
ADO Connection?', mtError, [mbYes,mbNo], 0) = mrYes then
begin
ConfigADODatabase;
ConnectADODatabase;
end
else
Result := False;
end;
end;
Remember to include AdoConEd in your "uses" clause and replace
SomeIniFile, dbADO and the IniFile sections with something that
reflects your space...
If your user selects OLE DB Provider from the list that gets displayed
when you click Build in the dialog presented by the call to
EditConnectionString you will then get a dialog that will present servers
running MS-SQL and then the db's that they are serving up.
An observation: if you are moving into the MS-SQL server space as a
backend, your end user is usually a cut above your previous client base
and this construct is well within their expertise. If you take the time
to give them a complete step by step procedure with annotated pix, then
it should work fine.
I use the above in a system that uses Access (being moved to Firebird)
and the old oilpatch hands that run it have no problem configuring
connections.
--
John
Life is complex. It has real and imaginary components.
 

Re:ADO Migration Questions and Issues

You could use the Lan Manager API (Jedi conversion), see
sqldev.net/misc/EnumSQLSvr.htm for a C++ example.
Alternative methods using ODBC (SQLBrowseConnect) or OleDB also exist.
Danny
---
"Bob Dalton" <XXXX@XXXXX.COM>schreef in bericht
Quote
>You are correct. The "Data Source =" parameter of connection string
should
>point to the server machine.
>or Data Source = ComputerA
>if ComputerA is the name of your server computer.

Thanks for your reply.

Is there anyway to find this automatically for MS SQL on a LAN? Since my
applications are sold "off the shelf", and not done on a contract basis, I
have no way to know what this would be in advance to allow it to be hard
coded. The only other alternative I see would be for the customer to have
to
provide this to my apps via some form of configuration dialog. I would
prefer to avoid that if possible since configuration allows too many
chances
for customers to{*word*222}things up......

In my FlashFiler apps this automatic finding is easy since the FlashFiler
Server broadcasts on the network making it easy to locate. I am just
learning the ins and outs of the MS SQL server....

Regards;

Bob Dalton


 

Re:ADO Migration Questions and Issues

You could also use SQLDMO (SQLDMO.Application.ListAValailableSQLServers
returns a SQLDMO.Namelist object).
Danny
---
"Bob Dalton" <XXXX@XXXXX.COM>schreef in bericht
Quote
>You are correct. The "Data Source =" parameter of connection string
should
>point to the server machine.
>or Data Source = ComputerA
>if ComputerA is the name of your server computer.

Thanks for your reply.

Is there anyway to find this automatically for MS SQL on a LAN? Since my
applications are sold "off the shelf", and not done on a contract basis, I
have no way to know what this would be in advance to allow it to be hard
coded. The only other alternative I see would be for the customer to have
to
provide this to my apps via some form of configuration dialog. I would
prefer to avoid that if possible since configuration allows too many
chances
for customers to{*word*222}things up......

In my FlashFiler apps this automatic finding is easy since the FlashFiler
Server broadcasts on the network making it easy to locate. I am just
learning the ins and outs of the MS SQL server....

Regards;

Bob Dalton


 

Re:ADO Migration Questions and Issues

Thanks for the code snippets which were extremely helpful!
Based on your code and reply statements then it appears that there is no
automatic way to do this and the customer must provide that information
correct? Disappointing but if that is got to be the way it is done then that's
going to have to be the way it is done I guess.
On reflection I agree that those using MS SQL and installing my apps in
combination will probaby be the system adminstrator and have the required
expertise to do it right.
Thanks for your assistance!
Regards;
Bob Dalton
 

Re:ADO Migration Questions and Issues

Thanks for your reply and the information!
Regards;
Bob Dalton
 

Re:ADO Migration Questions and Issues

Thanks for your reply and assistance!
Regards;
Bob Dalton
 

Re:ADO Migration Questions and Issues

Thank you for the reply and information!
Is the a Delphi demo available on this by chance? Being new to both ADO and
MS SQL I am grappling with the issues involved with programming and deploying
such apps as you would expect. it is quite a bit different in many ways from
FlashFiler or BDE programming.....
Also is there information that just discusses SQLDMO? I looked at your link
and it seems to involve just the installation aspects and not necessarily an
overview.
Regards;
Bob Dalton
"Brian Bushay TeamB" <XXXX@XXXXX.COM>writes
Quote
>
>Is there anyway to find this automatically for MS SQL on a LAN? Since my
>applications are sold "off the shelf", and not done on a contract basis,
I
>have no way to know what this would be in advance to allow it to be hard
>coded. The only other alternative I see would be for the customer to have
to
>provide this to my apps via some form of configuration dialog. I would
>prefer to avoid that if possible since configuration allows too many
chances
>for customers to{*word*222}things up......


Yes you can use SQLDMO to find servers.

You must import the SQLDMO type library then use code like this


var
LSQLServer: _SQLServer;
begin
LSQLServer := CoSQLServer.Create;
LSQLServer.Start(true, 'MyServerName', 'sa',''); // if you want login
inside or
LSQLServer.Start(false, 'MyServerName', '',''); // if you want only
start
server
// your code here
LSQLServer := nil;
end;


SQLDMO is not installed by MDAC. It does install with SQL Client
utilities.
This article explains manual installation
support.microsoft.com/default.aspx
--
Brian Bushay (TeamB)
XXXX@XXXXX.COM