NDU Internship - CSC 480

bahrainiancrimsonΛογισμικό & κατασκευή λογ/κού

13 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

132 εμφανίσεις


NDU
Internship - CSC 480
Final Report

Prepared for
Dr. Hoda Maalouf,
Chairperson of Computer
Science Department


By
Pedro Maroun Eid,
Senior Computer Science Student


29 January 2002

1
TABLE OF CONTENTS

ABSTRACT
.............................................................................................................................................................2

INTRODUCTION
............................................................................................................................................3

P
URPOSE
............................................................................................................................................................................3

P
ROBLEM
............................................................................................................................................................................3

PHASE DESCRIPTION
..........................................................................................................................5

T
HE
R
ESEARCH
P
HASE
.....................................................................................................................................................5

S
ETUP
P
HASE
....................................................................................................................................................................6

L
INKING
P
HASE
...............................................................................................................................................................10

E
DITING AND
F
INALIZING
P
HASE
.................................................................................................................................13

CONCLUSION
.................................................................................................................................................15

REFERENCES
...................................................................................................................................................16

APPENDIX
.............................................................................................................................................................17






LIST OF FIGURES

FIGURE 2-1..........................................................................................10

2
ABSTRACT


This report summarizes the whole 60-hours internship accomplished at
Profiles SARL., Dbayeh. The objective of this Internship Program given by
Profiles General Manager himself was to “convert an existing application,
namely FollowUp, from flat-table storage to an SQL-based database.” The
project as Mr. Hatem required has to reuse the existing code written under
Delphi. FollowUp should just be edited in order to support the new database
system.

The project done was not completed in 60 hours as was specified; it took
about 100 hours of serious work excluding personal research. However, the
results went as expected and the new program works perfectly. In this
experience, many explorations were achieved such as working on the Delphi
Compiler, object Pascal and the Microsoft SQL Server 6.0 database engine.

The final presentation to the company’s Development Department will
take place on Friday the 8
th
of February. In that presentation, it is required to
pass over all the code and specify the changes and edits done with a valid
logical reason for each. The company was more than thankful of the work
accomplished. This new project has opened a new door in realizing the famous
PIMS Software under SQL-Database Engines and proved the compatibility and
integrity of Delphi (object Pascal).

3
INTRODUCTION


Purpose


The purpose of this Internship was to test and ensure the integrity and
compatibility of Object Pascal programming language with SQL database
engines such as Microsoft SQL Server 6.0. The study made has a much deeper
role than just changing “FollowUp”. The company’s interest is in the possibility
of changing its whole conceptual model of its most selling product, PIMS,
which is using flat-table storage to the much more common SQL format.

To prove and test the cooperation of the above described system, an old
program, namely FollowUP, which is used to follow up support activities made
by personnel to a specific company, was to be worked on. The work was
changing the existing code which was using Paradox tables into one that will
benefit from the specifications of an SQL database. The database engine
chosen was Microsoft SQL Server 6.0.


Problem



4
The project describes 2 Delphi programs which use Paradox flat-table
database System that need to be “redirected” to an SQL-database engine. The
same Delphi program (FollowUp) should use the SQL tables instead of the
Paradox ones. Delphi is a compiler for Object Pascal programming language.
The program is made by Profiles itself and is a medium-to-large size
application. It keeps track of clients, actions made to them and personnel
status with a particular action. FollowUp uses data tables made with Paradox
and need to be converted to SQL tables. Along with the Database creation, the
information stored in the tables also needs to be transferred to the new tables.
However, instead of filling the new database with existing information, sample
records were created and worked upon.

The project describes 3 phases. The first is the research and setup of all
necessary software that will be used in the project such as Microsoft SQL
Server and client software. Second, setting up and creating the database on
the SQL Server along with the declaration of permissions, ownerships and
rules. Third, relating the Database tables with FollowUp Delphi code. FollowUP
should be made to successfully use the new tables instead of the old ones.
Fourth and last, editing the code until it runs and operates successfully. This
phase includes the substitution of logically impossible tasks to SQL databases
and the test of the whole system. These phases will be discussed in details all
through the report.

5
PHASE DESCRIPTION

The Research Phase


The first part of the project was to make a research on how to operate
and what are the procedures for transferring the database from Paradox to MS
SQL Server.

As stated in the previous reports, the work started by viewing the
application and its contents. Mr. Hatem was very useful in an overview of
'FollowUp' and a brief explanation of its way of use and condition terms.
FollowUp is a GUI (Graphical User Interface) that has connections with the
Paradox Database. The application has not to be touched. Paradox Database
system is structured of flat-storage database tables each in a single file. The
relationships of the tables along with the database name and specifications are
put in a definition file independent from the data files. According to Paradox,
Mr. Balian says, a Database is a folder. On the contrary, MS SQL Server puts
the whole database in a single file. So making the GUI interact with MS SQL
Server needs a link connection from Delphi to the MS SQL Server. According to
Mr. Balian, creating an ODBC (an object representation of any data source
object under Windows) that points to the database by an object and then
making 'FollowUp' refer to that name whenever it needs it is the best and
easiest way for connection under Windows. Delphi can then be made to point
to the ODBC object by its built-in Database Engine. This task was done by
using 'DBAdmin.exe', a tool that Borland Delphi provides to manage

6
databases. MS SQL Server has an advantage over Paradox by being much
faster, reliable, and error free.
According to Mr. Balian, the Database has to be kept as it is. In other
words, table relations, attribute's names and types, table names are to be kept
the same without any change, even letter case change. This is because the
Delphi program refers to these by their names and types. Changing them
would affect the program results and most probably will end with illegal
operations. To transfer the existing records in the Paradox Database to the MS
SQL Server, Mr. Balian suggested looking for an import command in the SQL
DBE (Database Engine) that transfers information directly into the database.
However, that task was later omitted under authority.

After doing all the above, Mr. Balian said that if the Delphi program is
done in a professional way, it should accept the differences and compatibly
cooperate with the new DBE. If not, the project should be extended to
changing the code to SQL statements instead of Pascal procedures. Changing
the whole code is unfeasible, however some statements were added and
edited in order to successfully cooperate with the SQL Server.


Setup Phase


This phase started with the malfunction of MS SQL Server 6.0 to achieve
wanted tasks. After installing the client software, 3 hours of reading and
research were spent figuring out the problem of the client “not seeing” the

7
server or the server instance. Although a physical connection existed and the 2
computers could ping each other on the network, the client software of MS
SQL 6.0 could not resolve the alias of the server software running on the
server. Upon research, many technical setups were found but all failed to
succeed in solving the problem. After all this work and finding that the server
software was installed since more than 2 years, it was desperate of continuing
in the same method in fear of loosing more time. Talking with Mr. Fadi Eid, a
program developer in NDU computer center, it was found that the best way to
solve such a problem is reinstalling the software again. He also said that
changing to MS SQL Server 2000 will facilitate things and automatically solve
complex problems. Profiles Development Officer, Joseph, researched the idea
with Mr. Hatem and granted the acceptance. Ordering the software took about
2 days. In the mean time, research and notes on installing MS SQL Server
2000 from the internet were studied and a process was scheduled. Next was
the setup of MS SQL Server 2000 and client. The client was setup first since
there was work to be done by the company on the NT Server. The installation
of the client was easy enough but so long that a nice 10 minutes sleep in front
of the monitor was like 10 hours. Finally, I was awakened by colleagues on
99% of total installation time. The installation took about 20 minutes on
Windows 98(the client workstation) to complete. Installing MS SQL Server
2000 was as hard as installing a new operating system. The server,
PROFILESNT, runs on Windows NT 4.0 with Service Pack 4. Upon installation of
the Server software, an error message occurs and warns that installation
cannot proceed without Service Pack 5 or above. A simple solution was
upgrading to the latest service pack (SP6). Upon upgrade, another error
occurs: “upgrade cannot open or modify SETUP.LOG.” Consulting Mr. Joe

8
Hatem and after research on news groups and the internet, the problem was
found and Mr. Hatem declared to be guilty in erasing the “repair” folder under
Windows NT main directory. However, 2 solutions were found to possibly solve
the problem. The first, creating and faking the missing files; easy but didn’t
work. The second, reinstalling Windows NT under a FIX directory; a little
complicated. In consulting the Mr. Mattar, he chose to suspend the matter to
see if there was a possibility of setting up the Server software on another
Server station, namely SERVER, with Windows 2000 Server OS. On the second
day, Mr. Mattar reported that the company cannot, for the time being,
consecrate with either of reinstalling Windows NT or installing MS SQL Server
2000 on Windows 2000 Server because both are currently in use by the
company employees. A last chance was trying to setup the server software on
Windows 95 or 98; this either didn’t work. There was no more choice but to
switch back to MS SQL Server 6.0!

The re-setup of MS SQL Server 6.0 was a success. No problems
happened maybe because of reading the setup notes and instructions
carefully. The software was put on WinNT server station 4.0 service pack 3
with 10 user licenses. After setting up the server, network configuration was
set up on TCP/IP and Banyan Vines protocols. SQL Server and SQL Executive
services were setup to run automatically on startup. SQL Server enables
network support to the server and SQL Executive acts as an administrator
(manages users, databases, etc…). Both need accounts on the system to run
at startup and these accounts should be defined as users with administrator
privileges. After setting up the Server, a database device, named
“flwupdbdev”, that is used to access the database files was created, defined
and configured. Then, the database, “flwupdb” was created and waiting for

9
tables and relationships to be put in. The database was assigned a 20
Megabyte space of which 5 were for log files to operate and store on. The
Client station succeeded the test of pinging the server and the Enterprise
Manager tool was able to detect domain SQL 6.0 running on the Server. SQL
6.0 contained the database device in which the actual database files were to
be implemented. Enterprise Manager can delete, edit, and create databases
and database devices as long as a certain SQL Server is detected in the
Servers window. This tool also can manage users and create tables, rules and
definitions on the actual Server. For sure the Enterprise Manager has one of 2
options to enable a user to access the Server, either logon with windows
authentication or using its built-in authentication. However, both methods
require a user definition in the server’s Operating System user account
manager. Later, users will be given certain permissions to access the database
on the server.

This phase also includes the setup and the creation of the database that
will be used in FollowUp. The Database creation was researched in parallel to
the previous work done. Physically, nothing was done but theoretically, the
Database ER was set up and structured. Since there are 3 existing tables, the
ER Model was simple and the attributes of each entity have been kept the
same. The database structure was copied as it is from the old Paradox tables
with a slight change in column names to make the attributes more
understandable. Refer to the data attributes and table structure section in the
Appendix for more information about the structure of the tables.

More than one version of the entire database was done because of
unsuccessful or bugged solution. MS SQL 6.0 does not support changing data

10
types, lengths of fields, NULL values, or even column names after saving the
tables on the Server. This resulted in corrupt files and runtime errors upon test
of SQL-based commands. After the fourth trial, the database was a success
and add-ons were implemented such as rules, defaults, and conditions. The
database was tested using various SQL queries/commands like insert, delete,
and select statements and was found to obey all local and referential integrity
constraints. The database ER is represented in figure 2.1. A person is a worker
in the company. Exactly one person does an action to exactly one client.
However, one person can make many actions and many actions can be done
to the same client. A person may perhaps not perform an action and there
may be clients that do not need actions. Afterwards, The database has been
filled with valid test records in order to operate on.



Figure 2.1: ER Model of FollowUp’s Database.



Linking Phase


In this phase, the database had to be closely tested and obeying all
possible logical queries. The test was successful under all environments and
ACTION
PERSONEL
CLIENT
Does
Is done
to
1
N
N
1

11
the database was found to be reliable and obeying all constraints required.
There were rules set to enhance the allowed input of data. Some of these rules
were found to interfere with the application, “Follow Up”. One of 2 was done:
either changing the code to obey the rule in the case of simple procedures or
deleting the rule if it was an essential part in the application. An example rule
is that a customer may be one of three types: a client (C), a prospect (P), or
terminated (T). The code was set up to insert values A, B, or C in the status
field of the client. In this case the rule was edited for the field to accept only A,
B, or C instead of C, P, or T. This was done because the code had so much
implementation on the field and it was easier to change the rule instead. Next
was the linking of the application to the database as assigned. After the
success and test of the database, the best way to connect to the database
remotely was by using the ODBC drivers included with Windows. The Driver
set was called SQL Server Connection. Drivers specific to MS SQL Server 6.0
were installed upon installation of either the Server or the client. ODBC is to be
set on each of the stations wanting the remote connection in addition to the
Server. Then, a tool called Delphi DBE-Administrator that comes with Delphi
Compiler was used to set up the connection from the Delphi code to ODBC.
The referred connection that could be seen directly as the database was called
SQLDATABASE. This name will be used in Delphi code to refer to the database.
In this way, Delphi code was directly connected to the database through
ODBC. Hence, the compiled code or executable was also connected. Upon
connection to the Database, the user does not have to enter his/her username
and password unless Windows User Authentication fails. After making the
connection to Delphi, “Follow Up” code was to be changed to comply with the
new Database. First, the tools used for the connection to the old database

12
were left but redirected to the new database. The old Paradox database was
found in directory C:\tools\followup\testdata, all tables directing to the
Paradox tables were redirected to SQLDATABASE. For example, the table tool
that was directing to table client had database C:\tools\followup\testdata\ and
table name “client.db” in its properties; these were changed to database
SQLDATABASE with “dbo.client” as table name. The new reference tables were
still having the old datafield types and names. The main change in the
database was the change of some field names that were a little confusing for
writing SQL commands. For example, CODE was a field name used in all three
tables, rather the change made was that only table ACTION has field name
CODE. The other tables were given different field names like PCODE for
PERSON and CCODE for CLIENT. Unfortunately, computers do not detect this
change and it was to be done manually by deleting all used fields and making
the tool redetect all fields with their names and datatypes. This included fields
that had different datatypes like DURATION that was changed from integer to
time. “FollowUp” is a big application and working on it all would take much
more than a semester. The goal is to reach a reliable stage that makes Delphi
compatible with SQL Server. Under permission, all print units and reports used
in “Follow Up” have been discarded for timesaving. Removing these additions
took a lot more work than expected. Headers, procedures, variables and forms
were deleted from the project by using the simple method of building the
project and deleting the error-making units of the project. Only the important
part of the project was left to operate on. Many errors were solved by
correcting compilation errors. Another problem was found upon runtime,
Reference indexes to other tables written in the code were still pointing to the
old field names. These were fetched using the “call stack” tool provided with

13
the compiler upon runtime. The problems were solved by finding the object
responsible and changing the right code or Index value. Another main change
is that “Follow Up” was using a client record called “$LOST” to collect all lost
actions and link them to that specific client. After this step, an administrator or
manager could repair the clientless actions. This was made to enhance the
reliability of the Paradox tables. By the database ER and constraint rules, the
database will never allow an action without both a client and a person;
referential constraint on both PCODE and CCODE would be fired. Therefore,
since lost actions could never happen, the “$LOST” client record was cut from
the code. All objects used in “FollowUp” were made operable on the new field
names and datatypes. And the application ran with some errors. These
problems were dealt with in the final phase.


Editing and Finalizing Phase

In this last phase, the work done was editing and finalizing the
application, “FollowUp”, to run and operate reliably without any errors. A
major error was found when creating a new action. A dialogue box appears
and the date, duration, and version of software were to be entered in addition
to the operator code, PCODE, who is writing the action made to the client.
When OK was pressed, the database constraint that DESCRIPT cannot be NULL
was fired. The problem found was that the DESCRIPT field is being created
after the previous dialogue and the program is filling up the database with only
the dialogue fields resulting in a DESCRIPT=NULL. In this case, the solution
was to change the code so that all the requested (NOT NULL) values are
inserted at one time in the database. Another logical error was found upon

14
deletion of a person. Going to the code, it was found that a person is being
deleted without any care of the actions he has performed. After taking advice
from Mr. Hatem, a logical thinking was that a person should not be deleted
unless he has no actions done. Thus, to remove a person all action he did
should be removed manually prior to deleting the person. The code written in
that procedure was to test if a person already has actions. If he does, a
display message informing him that the person being deleted already has
action(s) was delivered. Else, the person is deleted. This is also shown in the
Appendix section in UconfDel.pas, page1 (end). Other minor errors were found
when saving to the database such as initially empty fields in the application.
These were changed by simply setting an initial value for the form variables.
The old application was set to inform all employees and especially managers of
major changes in the company such as deletion of employees, companies, and
other important changes. However, these were resulting in some errors
concerning the mail server and not the application. It was authorized to
remove their code.

15
CONCLUSION


As a conclusion to the work done, writing code to perform a detailed
report is the best way to prove the correct integration of FollowUp and SQL-
database. The form done prints all person actions done between 2 specific
dates specified in a dialogue box pop-up. All changed code in FollowUp is
commented by my name with an explanation of the edited part. Great thanks
to all who helped in the accomplishment of this project which include all the
references mentioned in the reference page. A special thanks to Profiles SARL
for its hosting to this internship project. From this experiment with the real
work environment, many new things were learned and exercised. A major
issue is the work under authority, which was never exercised before. Another
thing was teamwork cooperation and help. And a third is the charge of
responsibility that was given in working on the main server and the local
network itself. Hoping that this project would be of big help to the developers
at Profiles in converting their whole database system to SQL-based, this was a
great experiment in learning the basics of Delphi, Microsoft SQL Server 6.0,
and Microsoft ODBC.

16
REFERENCES



Mr. Armen Balian, NDU Instructor and Computer Center Programmer.


Mr. Joseph Mattar, Profiles SARL Development Department Manager.


Mr. Joe Hatem, Profiles SARL General Manager.


Mr. Fadi Eid, NDU Computer Center Programmer.


Profiles SARL staff, Profiles SARL.


17
APPENDIX



A- Hosting letter.
B- Paradox database structure.
C- Table Structures in Paradox.
D- Old given code of FollowUp’s main form.
E- The edited code of FollowUp2 (SQL version).