Hi Brian,
Thanks for a very full explanation. I'll need to read through it a few
more times to take it all in but I think I will be heading down the
Access/SQL path but still keeping DataPerfect very much at hand too.
Thanks again.
Regards,
Michael
Brian Hancock wrote:
Hi Michael,
Most of my work comes from Access development but I am still active
with DP development.... I actually like both environments, they are
as different as chalk and cheese... I think Access is Microsoft's best
retail application. I think SQL Server is an excellent backend
database... I love working with DP.
DP has a particular paradigm that stops it having the flexibility of
other products. Both the presentation of data (ie its user interface),
its programming interface, and its data store are intimately bound
together. This paradigm makes it exceptionally good for rapid
development, and it makes for a consistent user interface and
programming environment so it makes it a very stable consistent
platform to develop with and use. Its lockless multiuser editing of
the same record makes it incredibly good for high volumes of users and
in particular where there are stong possibility of users editing the
same or related records.
SQL Server is much closer to DP that you would think. SQL Server is a
backend product in that it does not have any user interface at all...
I use DP for web applications, and I treat it in a similar vein to
someone using SQL Server. I have had my sporting web application
working in a club for over three years with about 200 current users,
Onloy a small fraction of those users use the application
concurrently, however I have had at times 10 copies of DP being
executed simulataneously... I have server in the vicinty
of 200,00 database interactions without the slightest problem or donw
time .
DP is nowhere near as powerful as SQL Server, and SQL Server can be
scaled up for far more concurrent users. SQL Server runs as a service
on the host machine meaning it is always in memory and ready to serve
requests, whereas each user interaction of DP means a sequence of
applications need to be loaded into memory before the application can
do anything. DOS, DP.EXE, then loading of the application STR, and
after it has finished the applications need to be unloaded from
memory. So why do I persist with developing in DP when there is so
much overhead? well firstly for small numbers of user on average
webserver hardware, it has only a slight performance penalty so the
comparison to SQL Server is not so bad. But the main reason for using
it is that I can develop robust complex applications far faster.
In DP developing the data rules, occur via the definition of the user
interface, so the data rules are less abstract, they have real and
obvious meanings. For instance, take the Keep A Total function which
allows you to define a field whose total can be added or subtracted to
fields in related tables when a change is made to the child record.
Firstly I have to say, that there is no equivalent whatsoever in
Access (you have to do all that by programming at the client end, and
you must do it each and every time you modify that record. This makes
Access extremely prone to error, as you have to remember to code that
interaction any time you write a piece of code or form that users that
table. SQL Server (and other similar DB's eg MySQL, Informix, Oracle
etc) have what are called Triggers. Triggers are pieces of programming
that are attached at the data end of things and not in the client
application, (as it effectively is in DP). You can write a Trigger to
say that any time a change, addition or deletion of a record occurs
that another action will be performed on another data table,and that
like DP can be used in a cascading effect. The true advantage of that
is that the client application developer does not need to think about
what happens, all he does is add, delete or change the record he is
interested in, and the consequently changes like Keep A Total happen
in the background without his or her knowledge. Triggers in these
types of databases are ultimately far more powerful than in DP. DP has
a particular default interaction such as Keep A Total, whereas
triggers for example can be used to ensure far more complex events.
Ror example deleting a record, my trigger and action to copy it to an
archive table, updating the username, timestampe etc, or it might act
on multiple other records in multilpe other tables.
One of DP's major deficiencies is its inability to truely interact
with other records when you are making an edit. For example for many
DP developer they have had times when they might like to check for a
duplicate record before adding a record, however the issue of
duplications is not always straightforward. DP allows you do do it in
a fashion with indexes and exception lists, however sometimes the
interaction needs to be far more complicated, For example I need to
add new players into my sporting database, but I do not want to create
duplications, so I want it to fail if the first and last names are the
same, and additioanlly if any of the conditions such as phone number,
address, date of birth, or email address are identical. In SQL Server
that can be performed in a trigger. In Access you can perform that by
program code on the client application, in DP the way most people
interact with it, it can't be directly done, and with a web enabled DP
it has to be done via the a Report (which makes it very similar to
what you have to do with Access). In this area SQL Server is by far
the winner.
Programming multuser in Access is far more complicated. If you only
have a few users, or users do not frequently edit and update the same
or closely related records, then it is no problem, but soon you will
run into locking issues and inconsistent updates. SQL Server is far
better than Acecss because it is not the multiple instances of the
client application manilpulating the data but one instance of the data
engine serving multiple users, so it can manage muliple users updates
far better, although you still need to add programming and
administration consideration into it when in DP you will be oblivious
to the potential problems as DP handles it so elegantly. When you web
enable DP, rather than using DP interactively, you are then confronted
with multiuser issues. Never in regards to locking, but more about
inconsistent updates. For example if DP is asked to provide data to
fill in an web form, you run the report, and once it is finished you
are disconnected from DP, DP does not know what you might do with this
data. If you simply save that data and any edits back , you might
inadvertently overwirte new data in another field where because they
had updated before you. The programming in DP and the web application
is immensely complicated to avoid that happening.
I use XML as my output data from DP. XML is self describing, and can
be used by so many client applications. Although Access can export
XML, it can only do it in a very crude fashion, with almost no control
on things like names spaces, entitites and CDATA. SQL Sever can
deliver SQL statement results in XML form, with far great control that
Access but without the flexibility of DP. The reason for this is that
DP does not understand XML so you need to do it very primitively
within the report writer. however becase it is primitive and because
the report writer in DP is absolutely amazing in is capabilities, you
have total and absolute control of your output in ways that Access
and SQL Server can't. In fact for SQL Server you really need a
middleware application layer to do things like this. On the other
hand things coming out from Access or SQL Server are standards based,
and middleware layers can expose incredibly simple yet powerful web
services interfaces that DP and complex CGI middleware programming I
suppose could do if you ever had the time to write it.
DP does not slow down with large volumes of data. Access does, and in
fact Access is limited to 500mb in a single MDB file which might be
where all the tables are housed.. SQL Server can outgrow both Access
and DP.
My web applications with DP give users everything they expect from a
GUI web application. They do not know that the backend is DP, I would
write the same user interactions if I were doing it with SQL Server.
My applications include conections with otehr databases, extracting
information from other websites, sending emails and sending mobile
phone text messaging, but all of this processing occurs in my
middleware layers on the server.
One of the really great things aobut Access is that you can replace
the Access tables with data from SQL Server... This means
applications can be scaled up... Although it generally is not as
straightforward as it sounds. Acecss has the cabailities to use Visual
Basic programming code in queries on Access data. If you use those
same functions with SQL Server then you have to bring all the data
into the client application rather than have the server produce the
results. You can write stored procedures on the server, and you can
pass SQL statements thru to the server but then you can only use the
vocabulary of SQL. In many cases complex Access applications run
better as Access applications run that upsizing them to SQL.
I actually have written various mini tools for managing the migration
of DP to Access or the administration of DP. For example, I have
updated the STE Manager application so that it can document DP panels
and output the results as an XML file, where I can use various XSLT
transformations to view varying reports about the application. I also
have it outputting an SQL DDL (data definition language) statement so
that I can reproduce the structures in SQL Server or MySQL, it is not
by any means perfect, actyually they are pretty kludgey and usually
the only reason I want to do that is because I can import them into ER
Diagram software so I can produce ER diagrams of my DP application.
The tools are pretty rough and ready as they are for my own use so I
am guessing that you might not find them all that useful, although you
are welcome to try them. On the other hand Access and SQL have a
wealth of high quality administrative tools, from a variety of vendors.
It is a shame more people are not giving the DP web applications a try
because I am sure if people did they would probably think that they
already have a Windows version of DP.
At the conference in Los Angeles in 2004 I discussed a large
DP application that I had been using for many years. Just after I
arrived back in Australia the company was sold to a much larger public
company who were horrified that DP and WordPerfect were being used by
the largest supplier of this product and service in Australia, they
thought we had far "better" technology for the results had achieved.
They put our success down to dumb luck. They immedaitely set on a plan
to replace the application, and to increase its functionality into
being a webapp, and the timetable for that was 3 months, and $300,000
... That was almost 4 years ago, and rumours are that the transition
to VB.NET and SQL Server has cost nearly 1.5 million dollars, and is
still does not have the much vaulted functionality they had
planned and has only in the last few months come up to have a similar
functionalist to the application they replaced albeit slower, and with
less operator satisfaction.
I have been involved in a patent dispute because a company managed to
get a patent through a few years back but which my original 1990's DP
and WP application combination had, one of the barristers who was
documenting my aplication had to learn intimately how it worked, and
said he could not believe how easy it was to use DP, and downloaded a
copy of it and documentation from the DataPerfect.nl website so he
could play with it some more himself.
I hope this helps with your migrationm whichever way that is
Regards
Brian
----- Original Message -----
*From:* Michael Iannantuoni <mailto:[EMAIL PROTECTED]>
*To:* Dataperfect Mailing List <mailto:[EMAIL PROTECTED]>
*Sent:* Friday, February 22, 2008 9:00 PM
*Subject:* [Dataperf] DataPerfect V's Access & SQL
I have someone who wants to change from DataPerfect to Access/SQL
for no good reason other than it's Windows and modern!
Can someone please help me list the pro's & cons of DataPerfect
v's Access/SQL and the DP approach v's the "all data can be
represented in a table" approach.
Thanks,
Michael
------------------------------------------------------------------------
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
------------------------------------------------------------------------
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
