BorlandTalk.com

Glenn Alcott · Guest

I am the developer for a vertical market application with hundreds of users,
which is now in D5 with Paradox. We are contemplating a move to MS SQL
Server (either the free MSDE or the upcoming 2005 Express version) which
will of course require major changes in the application. The product does a
lot of complicated reports that now involve multiple queries, creation of
temporary tables, etc. I am thinking that a lot of this code could be moved
into stored procedures on SQL Server, but since stored procedures are in the
database the problem becomes how to distribute them, and how to update the
users when they are changed. My best solution so far is to actually recreate
the procedures in the Delphi code each time, using an ADOCommand object with
code like this:

adocommand1.commandtext:= 'IF EXISTS(SELECT name FROM sysobjects WHERE
name = ''testproc'' AND type = ''P'') DROP PROCEDURE testproc';
adocommand1.Execute;
adocommand1.commandtext:= 'create procedure testproc as '
// insert procedure code here
adocommand1.Execute;
adocommand1.commandtext:='exec testproc';
adocommand1.Execute;

I know that in a multiuser situation this might be a bit tricky if two users
are trying to run the same procedure simultaneously, but this isn't too much
of a problem with our user base. Other than that, is there a better approach
to doing this?

Also, I'm wondering if anyone else has dealt with a similar database
migration with a widely distributed product. It seems like a very
complicated task in terms of migrating existing data, SQL Server
installations, etc. (aside from actual changes in the app) and I'd
appreciate insights from anyone who's done it.

Glenn

Arno Brinkman · Guest

Hi,

<snip>

*Lysander* · Guest

In article <42704e55$1 (AT) newsgroups (DOT) borland.com>, [email]galcott (AT) compuserve (DOT) com[/email]
says...

Glenn Alcott · Guest

"Arno Brinkman" <bnewsgroups @ REMOVE_ME abvisie.nl> wrote

Betsy · Guest

Hi Glenn,

I have a couple of questions... I was thinking of using mdse where I needed
to use an application on a laptop or other situations where they (the users)
would not have access to the network and be working offline... I haven't
really gotten a look at it yet and am completely unaware of it's
capabilities and limitations.

Here is where I'm at. I'm upgrading D5 pro with Paradox + bde applications
for my company using D7 pro with MS-SQL2000 via ADO connectivity. I am not
ever dealing with the number of users that you are... max maybe 10 on the
outside with any particular application... rarely even that. I am WAY
impressed with the speed and responsiveness of the finished product. I am a
HUGE fan of the ability to push off work, restrictions, etc to the server
via good database design and relationships. I am futher impressed with my
ability to clean up slop in my code which was partly because of me and
partly because I was trying to fill in hole (pot holes) that bde and paradox
had left me.

(don't tell on me)
I was told some time ago to deliver my executables and other related files
to the users machines... I tried it for about a week... who wants to go to
their desks everytime there is a change... NOT ME! I go once and set them
up a shortcut to an appropriate location on the network... this location
usually mimicks my source directory that I work out of for development ie
developing in
q:sourcedelphidelphi7legalsomeSillyApplicationForLegal... would become
q:datalegalsomeSillyApplicationForLegal... from here I will have a help
folder, a report folder, a query folder, etc... anything that isn't acually
produced by & in the project itself... then I'll use references to them in
the code and project like .helpwhatever.hlp and .reportwhatever.rtm, or
..querywhatever.txt... then I make sure and put these folders out in the
destination directory together with the executable and these files are
referenced there and can be swapped out without swapping out the exe at any
time. (note the rtm files are my saved report templates from reportBuilder
Pro)

Now... upgrading... I've ported all of my query files to views or stored
procedures... depending entirely on whether or not they require a
parameter... but the reporting I'm handling the same way...

Having the views and stored procedures on the server is sweet on sweet...
it's fast, and it's compartmentalized... if you got something wrong in you
sql statement you can fix it without even opening delphi and wallah...
everyone is fixed. I rarely ever build a query statement in my code any
more.

Similar with the reports... you can open them and modify them... save the
modifications to the appropriate destination directory and all is well.

I did initially start working with the TADOTable components and other
similarly named components as before... have recently switched to the
TadoDataset as recommended by others and found it is much much better...

there, what a blabber mouth... I'll shut up... hope it helps,
b
"Glenn Alcott" <galcott (AT) compuserve (DOT) com> wrote

Bob Dawson · Guest

"Glenn Alcott" wrote

Glenn Alcott · Guest

Betsy,

You said you had questions but you didn't really ask any. So what are your
questions?

Your situation seems very different from mine because apparently you are
creating in-house apps. Mine is a commercial app with users all over the
country so things like stored procedures have to be handled differently.
It's a complicated app written in a very Paradox-specific way (not by me
originally) and converting it is a pretty scary proposition. But our main
competitor just converted from Paradox to MSDE so we sort of need to keep
up.

Glenn

"Betsy" <betsy.a.tainer (AT) lesschwab (DOT) com> wrote

Mr. John A. Jackson · Guest

I do quite a bit of these migrations for small to mid-sized companies. The
key is not to get fancy, yet apply properly tiered code.

First, most Delphi/Paradox applications make heavy use of the BDE, fully
open tables and Paradox specific coding. You will need to change coding
style otherwise any relational database will appear slow. Access everything
with a query when using SQL Server, MSDE, Firebird, etc.

Second, separate your GUI code from your data access code from your business
logic code. For example, I have one function that returns all US states.
My GUI code calls that function to load US states. It does not matter if my
GUI component is a listbox or a combobox, it reuses the same data access
code.

Third, you are not using stored procedures now, so you don't really have to
use them in SQL Server. This allows you to move to any database (hint: you
have potential customers using Oracle?). However, using some stored
procedures make sense. You will have to make the call when analyzing the
application.

Fourth, MSDE has limitations, you should research this.

Fifth, SQL Server Express 2005 may be free, but it requires ADO.NET. I do
not know of any ADO.NET components for D5 (or D7), only for D2K5.

Thanks.
"Glenn Alcott" <galcott (AT) compuserve (DOT) com> wrote

Brian Malooley · Guest

Glenn,

Have you taken a look at Advantage Database Server? It can be run in
local mode which is free or remote (which does cost). But your not
locked in to any one server OS.

http://www.advantagedatabase.com

The move from Paradox to ADS is very easy. For the most part, it won't
take much to recompile your application.

Glenn Alcott · Guest

Yes, in fact we looked at Advantage before MS and it's still a possibility.
But my client, the vendor of the app, is looking at this as an opportunity
to market to larger businesses and thinks a Microsoft backend would offer a
marketing advantage, because his customers never heard of Advantage. I don't
know yet which way it will go.

I don't have the luxury of writing the app in a way that will work with any
engine. It's a very large and complicated program dating back to Delphi 1.0
(not written by me originally) and a redesign to accomplish that is way over
the client's budget. Also, there are too many difference between the various
flavors of SQL for that to even be realistically possible as far as I can
see.

Even moving to Advantage isn't that simple. I started playing around with it
a while ago and after 10 hours of work I probably hadn't done 10% of it,
partly because of the very Paradox-specific way the app was written. I've
gotten quite skeptical of these statements about how easy it is to migrate.
For simple apps it's probably true but not for something like this.

Glenn

"Brian Malooley" <bmaloole (AT) NOSPAM (DOT) twcny.rr.com> wrote

Bill Todd · Guest

Whatever amount of time it will take to convert an application written
for Paradox tables to Advantage you can triple as a minimum to convert
to SQL Server or any other SQL database server. Advantage has high a
high performance Table component that is virtually identical to TTable.
Usually, the biggest change you will have to make to convert to
Advantage is to find any place you set the IndexName property to a null
string for the primary index and change that to the primary index name
for your Advantage tables.

Moving to a SQL database server requires everthing to be changed to use
SQL and query components for access. This almost always requires some
U/I changes. See
http://community.borland.com/article/0,1410,28160,00.html for a more
detailed explanation.

--
Bill Todd (TeamB)

Mr. John A. Jackson · Guest

While I understand your need to deal with your client's marketing concerns,
Advantage Database would be a great alternative, if you can sell it. I
agree with Bill Todd, your conversion cost will be far, far less if you go
with Advantage DB. However, before you do, you will have to REALLY sell the
point. The ADB website has some very good talking points.

Otherwise, plan your conversion to SQL Server well before you code.

"Glenn Alcott" <galcott (AT) compuserve (DOT) com> wrote

Jon Robertson · Guest

Viatcheslav V. Vassiliev · Guest

SqlExpress 2005 can be used with ADO (provider name will be SQLNCLI, not
SQLOLEDB like for MS SQL Server 2000/MSDE) and ODBC. SqlExpress has
limitations similar to limitations of MSDE.

ADO.Net components for Delphi Win32 (D5-D7, D2005/Win32) are available in
Managed extensions for VCL ([url]http://www.managed-vcl.com)[/url].

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Mr. John A. Jackson" <[no]dd2x]spam[@lweresearch.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: news:42744a80$1 (AT) newsgroups (DOT) borland.com...

Betsy · Guest

Hey Glenn,

I did have questions using/getting msde... but looks like you've got enough
of your own. And, I missed it... your structure, delivery and client
distribution sounds way more complicated then mine. Also I'm upgrading D5
over pdox5/7 to D7 over sql... with data that is well structured and
doesn't need much restructure/reorg in the upgrade.

There comes a time when the process of upgrading is more complex the the
process of rewriting... I don't think that I would attempt to upgrade a D1
application over pdox... I think I'd catalog necessary
layout/functionality/etc and go back to the drawing board, fresh.

To upgrade my apps from 5 to 7 it IS just a matter of export/import + any
restructure... then open the original project, swap out the database
components for new ADO... then go after any procedures/functions that are
outdated due to functionality of the database... port over things from code
to server objects... etc... it's really not a big hairy deal... true, most
are not big hairy programs like you are describing yours. It's not a
'snap'.. there is some work involved and it does take some time depending on
how well structured the original program is... but it's not difficult and I
find it very satisfying... I'm surprised at the ease in which things come
together and how much nicer the programs are structured mainly due to new
capabilities OR my better understanding of how to make them work. I'm
thinking probably the biggest advantage to upgrading vs rewrite is you don't
have to rebuild the interface. When I'm upgrading essentially the only
thing that is left, for the most part, untouched, IS the interface. Every
other single part of the application has been completely gutted and rebuilt.

I'll bet your on a good path.. and I'd stick with the MS for your backend...
as much as I hate to admit it market share and name recognition go a long
way... the functionality etc are all there and I'm certain any of the other
hardsells would be perfectly fine... but sounds like your commercial with a
wide client base etc (blah de blah) MS is your tool.

I'd like to hear how you resolve your distribution issues etc... please keep
us posted!

b

"Glenn Alcott" <galcott (AT) compuserve (DOT) com> wrote