Surviving Client/Server:
InterBase Or SQL Server?
by Steve Troxell
ll Delphi developers have had conform at the entry level, with bits forwards through a result set with
Athe opportunity to work first and pieces of the higher levels SQL and are available in both
hand with at least one SQL server thrown in (along with some ven- InterBase and SQL Server. These
backend: Local InterBase Server. dor-specific enhancements as can be either read-only or modifi-
For many of you, this is your first well). Let s take a look at the major able. SQL Server cursors can also
look at a client/server system. areas where InterBase and SQL be scrollable (you can move back-
Also, as I ve stated many times in Server differ. Features indicated wards, to first, to last, or jump to a
the course of this column, func- below by (SQL92) are part of the specific row in the cursor) and can
tionality of client/server systems higher levels of the SQL 92 stand- be made sensitive or insensitive to
varies widely from vendor to ard, other features are vendor changes made by other users to
vendor. For these reasons, this enhancements. the underlying rows.
month I want to take a close look at Domains (SQL92) Both prod- Datatypes Both servers sup-
InterBase (which should be good ucts support domains. These are port most of the standard SQL 92
common ground for everybody) just user-defined data types for col- datatypes:CHAR, VARCHAR, INTEGER,
and Microsoft SQL Server (because umn definitions in tables. However, DATETIME, SMALLINT, FLOAT and
it s the only other client/server SQL Server allows domains to be REAL. SQL Server extends the avail-
RDBMS I have direct experience used with stored procedure pa- able datatypes to include TINYINT
with!) in order to illustrate just how rameters and local variables (one byte unsigned integers),
different two seemingly similar whereas InterBase restricts them BINARY and VARBINARY (hexadecimal
products can be once you look to table column definitions only. strings),MONEY and SMALLMONEY (spe-
under the hood. Flow Control Both products cial instances of the numeric
This is not an exhaustive com- provide simple flow control state- datatype with currency related dis-
parison by any means. We will fo- ments such as IF THEN ELSE branch- play formatting), BIT (binary on-off
cus primarily on the features ing, WHILE loops and exits from the value), and TIMESTAMP (special
having the greatest impact on day middle of a stored procedure. auto-incrementing value used for
to day development. The basis of InterBase s flow control state- optimistic locking schemes).
our comparison will be InterBase ments are only valid within the Arrays InterBase tables can
Workgroup Server v4.0 and SQL body of a stored procedure or trig- include arrays wherein any column
Server v6.0. At the time of writing, ger definition. SQL Server s flow can be designated as an array of up
SQL Server v6.5 has just been re- control statements can also be to 16 dimensions of any single data
leased and the next InterBase is used within script files. For exam- type. Individual array elements, or
under development. ple, a script that creates a stored array slices, can be accessed
procedure could check to see if the though SQL statements. However,
ANSI SQL92 Conformance stored procedure already existed referencing such structures
Despite the fact that SQL is a sup- and drop it before the CREATE through third-party tools or even a
posedly  standardized language, PROCEDURE statement is run. This Delphi program is problematic at
in reality it s about as portable allows the same script file to create best. SQL Server does not support
from vendor to vendor as C. Con- new procedures or update existing array structures.
cepts such as indexes, triggers, and ones. Without flow control, the BLOB Support (SQL92) SQL
stored procedures are not even CREATE PROCEDURE statement would Server supports BLOB data fields
included in the SQL 92 standard. In produce an error if the procedure through separate TEXT and IMAGE
actuality, the standard can be im- already existed, and the DROP datatypes. However, it allocates
plemented at three levels: entry, PROCEDURE statement would pro- these fields in fixed 2048 byte incre-
intermediates and full. Each level duce an error if the procedure did ments. InterBase supports both
builds upon the previous levels not exist. With InterBase you text and binary BLOB fields
and adds functionality. As such, would have to switch between the through a single datatype called
vendors claims of conformance to CREATE PROCEDURE and ALTER BLOB and gives you explicit control
any SQL standard should not be PROCEDURE statements, so no single over the disk allocation of the field.
taken too seriously. script would work for both creating InterBase also provides BLOB
Neither InterBase nor SQL Server and updating the same procedure. filters which are DLL functions you
conform to the SQL 92 standard at Cursors (SQL92) Declared cur- write and link into InterBase
the full level. They both really only sors allow you to navigate (except for Novell servers). With
22 The Delphi Magazine Issue 11
these filters you can perform tasks SQL 92) as well as functions to add
SELECT Address, City, State
INTO #TempTable
such as converting a bitmap from and subtract datetimes, extract
FROM Authors
the application into a JPEG com- datetime portions (month, day,
pressed image in the database and year, hour etc), a whole slew of
Figure 1: Temporary tables
vice-versa. SQL Server does not math functions, substring search-
in SQL Server
provide BLOB filters. ing (similar to POS in Delphi) and
Temporary Tables (SQL92) A functions to compute and compare
temporary table can be created Soundex values. The alert manager can also be tied
such that it is invisible to other User-Defined Functions Both into the NT Performance Monitor
users and hence there would be no servers provide the ability to intro- to trigger an alert when a selected
chance of collision if other users duce user-defined functions into performance threshold is crossed.
happen to use a temporary table the SQL language. Within InterBase However, the only thing the alert
with the same name. Alternatively, (except for Novell servers), you manager can do in response to the
temporary tables can be made vis- can import a function from a DLL event is to send an e-mail, a pager
ible to other users to share data. In and use it as you would any other notification, or execute a task such
either case, temporary tables auto- system-supplied function in the as an SQL script file or NT program.
matically drop themselves at the SQL context. The function can have
end of the process or session. zero or more input values and can Triggers
SQL Server supports temporary return one output value of any type InterBase provides more flexible
tables, although not with the SQL as the function result. This capabil- trigger support than SQL Server.
statements specified by the SQL92 ity allows users to overcome the Both servers provide the standard
standard. InterBase does not spartan built-in function library, abilities to bind triggers to insert,
support temporary tables at all. but you have to write the functions update, and delete operations on a
While the CREATE TABLE state- yourself. table. InterBase allows you to have
ment can be used to create tempo- SQL Server provides a similar ca- multiple triggers bound to the
rary tables, SQL Server provides a pability through extended stored same event and table; all firing in a
convenient shortcut with the procedures. Here a stored proce- specified sequence. One advantage
SELECT statement. Figure 1 shows dure call can be made to an exter- to having separate triggers for dis-
how you can add the INTO clause to nal DLL procedure. The interface tinct tasks on the same operation
a SELECT query and have the server to SQL is just like any other stored is that it is easier add and remove
automatically create a temporary procedure call. While this is not as individual tasks than to modify the
table containing the result set of clean as the true function interface trigger code if it were all in one
the query. This results in a tempo- of InterBase, it does allow for mul- place.
rary table named #TempTable con- tiple output parameters in the InterBase also allows triggers to
taining three columns: Address, stored procedure argument list be bound either before or after the
City and State. Other users can and for returning multi-row result data operation occurs. For exam-
also have temporary tables named sets. Some extended stored proce- ple, you can have a trigger on a
#TempTable (of completely different dures that are provided by SQL table firing before an update that
structures if desired) without fear Server allow you to execute any validates the field contents prior to
of colliding with each other. Windows NT command string (re- changing the row in the table. You
Within SQL Server, temporary turning any output as rows of text), can also have another trigger on
tables are stored in a special data- as well as send and read e-mail the same table firing after an
base called TempDB. This includes through the SQL Server mailbox. update that adds a row into a dif-
temporary work tables created ferent table, possibly a change
during the server s normal course Alerts history table.
of processing queries. SQL Server InterBase triggers or stored proce- SQL Server only allows one
allows you the option of storing the dures can raise user-defined  event trigger per table per operation (in-
TempDB database within server alerters which then notify other sert, update and delete) and they
RAM, greatly improving perform- applications that have registered fire after the operation has been
ance of these activities. their interest in the alert with the applied to the table. However, al-
Functions (SQL92) When it InterBase server. This is a simple though the table is modified before
comes to providing built in func- notification message; no data can the trigger fires, if an error is raised
tions to assist the SQL developer, be transmitted to or from the lis- within the trigger the table change
InterBase pales in comparison to tening clients. can be rolled back.
SQL Server. InterBase provides a SQL Server does not offer built-in InterBase also has a more con-
paltry 8 built-in functions, while support for inter-application com- venient syntax for examining and
SQL Server provides more than 75. munication like this. However, it modifying columns within a trig-
Some of the more interesting addi- does have an alert manager which ger. InterBase provides two con-
tions to SQL Server s library in- can monitor the NT event log look- text variables called Old and New
clude the LOWER, LTRIM/RTRIM and ing for any particular event (which that contain the columns of the
SUBSTRING string functions (part of can be fired from an SQL query). affected row before and after the
24 The Delphi Magazine Issue 11
change (Old is not available for in-
CREATE TRIGGER OnEmployeeInsert FOR Employee
BEFORE INSERT
serts, New is not available for de-
AS
letes). You can manipulate the row
BEGIN
contents by directly examining or /* Set the DateEntered field to the current date */
New.DateEntered = Today;
modifying these context variables.
END
Figure 2 illustrates how this is done
in InterBase.
Figure 2: InterBase trigger
SQL Server provides two virtual
tables, Deleted and Inserted, that
serve the same function as CREATE TRIGGER OnEmployeeInsert ON Employee
FOR INSERT
InterBase s Old and New variables.
AS
However, because these are actu- BEGIN
/* Set the DateEntered field to the current date */
ally tables, you must use regular
UPDATE Employee SET DateEntered = GetDate()
SQL statements to manipulate
FROM Inserted
WHERE Emp_No = Inserted.Emp_No
them. What this means is that you
END
have to issue SELECT and UPDATE
statements to read or change col-
Figure 3: SQL Server trigger
umn contents. Figure 3 shows how
this is done with SQL Server. Since
the rows have already been added
CREATE PROCEDURE Get_Employees_ByDept(Dept char(3))
to the table, we use an UPDATE state- RETURNS(Emp_No smallint, First_Name varchar(15),
Last_Name varchar(20), Phone_Ext varchar(4))
ment to modify the affected rows
AS
by joining with the Inserted table.
BEGIN
FOR
Another difference between the
SELECT Emp_No, First_Name, Last_Name, Phone_Ext
two concerns when a given SQL
FROM Employee
statement affects more than one WHERE Dept_No = :Dept
INTO :Emp_No, :First_Name, :Last_Name, :Phone_Ext
row (for example, an UPDATE with a
DO SUSPEND;
WHERE clause for all the employees
END
within one department). In this
case, an InterBase trigger fires
Figure 4: InterBase stored procedure
once per row affected, an SQL
Server trigger fires once per set
affected. This makes SQL Server
CREATE PROCEDURE Get_Employees_ByDept(@Dept char(3))
triggers a bit tough to work with: AS
BEGIN
you must allow for the possibility
SELECT Emp_No, First_Name, Last_Name, Phone_Ext
that the Inserted and Deleted
FROM Employee
WHERE Dept_No = @Dept
tables may contain more than one
END
row. Notice the UPDATE statement in
Figure 3: because of the join in the
Figure 5: SQL Server stored procedure
WHERE clause, this will modify all
rows that exist in the Inserted
table. with the corresponding column all columns of a table with SELECT *
value. Figure 4 shows an example FROM, it involves an additional layer
Stored Procedures of such a stored procedure. of stored procedure maintenance
Procedures that do not produce In contrast, SQL Server allows for by requiring much more code in
result sets (ones that simply add or  implicit result sets from stored terms of parameters and mecha-
change data or return simple out- procedures. In this case, no output nisms to populate those parame-
put parameters) are handled sub- parameters are necessary to pass ters. However, although SQL 92
stantially the same way by both back column values: the stored does not cover stored procedure,
vendors, although there is a slight procedure itself has the result set they are planned to be introduced
difference in the calling convention implicitly bound to it. The stored in the next standard currently un-
for output parameters. procedure behaves transparently, der development (SQL3). InterBase
Stored procedures returning there is no difference in the output conforms more to the proposed
results are significantly different. of the stored procedure than if the standard than does SQL Server. We
With InterBase, each column to be underlying SQL queries were sent covered this issue in more detail in
returned must be declared as a standalone. Figure 5 shows the the March 1996 Issue (No 7).
separate output parameter passed equivalent SQL Server stored pro- Other differences include how
back by the stored procedure. As cedure for the InterBase version transaction control is handled
would be expected, within the shown in Figure 4. through stored procedures.
body of the procedure each output Not only does the InterBase con- InterBase does not allow transac-
parameter must be explicitly set vention negate the use of returning tion control statements (START
July 1996 The Delphi Magazine 25
DECLARE @N INT
how you can trap the native SQL
DECLARE @Dept CHAR(3)
error produced when attempting
SELECT @Dept =  623
SELECT @N = COUNT(*) FROM Employee WHERE Dept_No = @Dept to add a record with a duplicate
IF @N = 0
primary key and return your own
BEGIN
message in place of the default
RAISERROR ( No employees found for department %s , 16, -1, @Dept)
RETURN /* RAISERROR doesn t terminate by itself */
server message.
END
Begin-end blocks can be nested,
each having when-do error handlers
Figure 6: SQL Server error handling
for specific errors. If an inner block
produces an error, InterBase pro-
gresses through the levels until it
CREATE EXCEPTION NoEmployees  No employees found for this department. ;
... finds a matching when-do handler
DECLARE VARIABLE N INTEGER;
for that error  just like Delphi s
DECLARE VARIABLE Dept CHAR(3);
multiple levels of Try-Except
Dept =  623 ;
SELECT COUNT(*) INTO :N FROM Employee WHERE Dept_No = :Dept
blocks.
IF (N = 0) THEN
EXCEPTION NoEmployees;
Integration With Delphi
InterBase works quite well with
Figure 7: Basic InterBase error handling
Delphi, which is not surprising
since they both come from the
same company. However, there is
CREATE EXCEPTION DupKey  Don t enter something twice!
...
no direct support in Delphi for ar-
BEGIN
ray structures and some configura-
INSERT INTO Table1 VALUES (20, 512);
WHEN SQLCODE -803 DO tions of NUMERIC and DECIMAL fields
EXCEPTION DupKey;
translate in Delphi as integers in-
END;
stead of real numbers (truncating
the fractional part). Also, you can-
Figure 8: InterBase s structured exception handling
not use the TStoredProc component
for stored procedures returning
TRANSACTION, COMMIT or ROLLBACK) ger, RAISERROR does not in and of more than one row, you must use
within stored procedures, the itself end execution of the code, SELECT * FROM
transaction must be handled by you must explicitly call RETURN. in a TQuery.
the calling application. However, While this is a bit clunky, it does The big problem with SQL Server
any exception or error raised give you a means of placing debug- and SQL Links v2.51 (shipping with
within the transaction will auto- ging statements within stored Delphi 1) is that it only supports
matically cause a rollback of the procedures. You can also use SQL Server v4.21A and won t di-
entire transaction. With SQL RAISERROR to post messages in the rectly support new features in ver-
Server, transaction control state- NT event log (which would not nec- sions 6.0 or 6.5. For the most part,
ments can appear anywhere essarily be an action you would this can be overcome by encapsu-
(within the calling application or want to terminate execution of the lating v6.x-specific functions
within a stored procedure) and code). within stored procedures. Stored
transactions can be nested. This In InterBase, the text of an excep- procedures called through
gives you greater flexibility in tion message is statically defined TStoredProc do not work when
encapsulating code within stored and bound to a name. You then connecting though ODBC because
procedures. raise the exception in a block of of a change to the parameter
Finally, there are significant dif- code (usually within a stored pro- checking in SQL Server v6.0 (that
ferences in error handling capabili- cedure): see Figure 7. Unlike SQL the BDE violates because it is only
ties within stored procedures. Server, the mere act of raising the designed for SQL Server v4.21A).
With SQL Server, the error han- exception terminates the block of However, you can still call stored
dling mechanism is the RAISERROR code and returns to the caller. procedures using straight SQL syn-
statement, which will send a user- However, within stored proce- tax in a TQuery. Although I have not
defined message back to the client. dures InterBase provides struc- had a chance to work with it at the
The message may be defined in tured exception handling similar to time of writing, SQL Links v3.01
code, or may be defined in a lookup the Try-Except block in Delphi. (shipping with Delphi 2) supports
table and referenced by number. When an exception is raised (or an SQL Server v6.0, but messages on
RAISERROR also provides the con- SQL statement produces an error), the CompuServe fora indicate that
venience of substitution parame- execution of the begin-end block is it is notoriously slow.
ters to allow you to insert text terminated and any actions per-
relevant to the specific instance of formed within it are rolled back. It Development Tools
the error (see Figure 6). If used then looks for a when-do statement When comparing the development
within a stored procedure or trig- to handle the error. Figure 8 shows tools provided with these two
26 The Delphi Magazine Issue 11
products, SQL Server clearly wins load/save functions so handling is just a night and day difference in
hands down. SQL Enterprise SQL script files is as easy as a click ease of use. Here at TurboPower
Manager is your one-stop resource on a load or save button. There is we have had the opportunity to
for nearly all the development and no syntactical difference between work with both products and
administration functionality need- SQL code contained in a script file consider productivity to be signifi-
ed to manage as many concurrent and interactive SQL code. cantly higher with SQL Server s
SQL Servers as you can access Within any query window, you tools than with InterBase s. This is
from the workstation. This in- can highlight any portion of the strictly from the developer s point
cludes multiple servers on the LAN code and execute only the high- of view, before you even get into
and even remote servers accessi- lighted code. This is a great aid to the need for such tools at the data-
ble via modem through NT s development as you can have a base administrator s level. Delphi
Remote Access Services. workspace with more than one 2 s Database Explorer overcomes a
Enterprise Manager not only per- query and quickly run any of them lot of the limitations of InterBase s
forms SQL queries to manipulate by simply highlighting and execut- Windows ISQL, but it won t per-
data or metadata, it allows you to ing the one you want. As another form the administration tasks and
set up users and security, perform example, you could have a script it won t work with script files.
backups and restores, and monitor file containing several related A significant difference in devel-
server connections, activity, and stored procedures, load it into the opment between these two prod-
locking for any server it has access query editor, change one of them, ucts is the handling of changes to
to. Tasks can be scheduled to exe- and select only that one to execute table structures. InterBase s ALTER
cute automatically at certain inter- without having to execute the TABLE statement allows you to add
vals. These can include any NT entire script file. or drop any column from an exist-
application or SQL statement and The equivalent capabilities for ing table. If the new columns don t
e-mail or pager notifications can be InterBase can be found in its allow nulls, InterBase fills it with a
sent to any user upon the success Windows ISQL and Server Manager reasonable default value (zeros for
or failure of any task. programs. Server Manager covers numeric columns, empty string for
All of the functions of the GUI the administration functions: back- character columns). This is ex-
front end can also be performed as ups and restores, users, security, tremely convenient for online
SQL queries. SQL Server encapsu- etc. There is no facility to interac- changes to tables (and begs for a
lates a great number (over 180 tively monitor server activity or front end GUI tool to change table
actually) of routine functions and schedule automated tasks. structures). It allows structures to
services within system-supplied To execute SQL queries with be modified without losing existing
stored procedures (basically ex- InterBase you use the Windows data, but problems may still arise
tending the SQL command set). For ISQL program. You can only run because the existing data won t
example, adding or deleting users one query at a time in a tiny 1 inch have proper values in the newly
or even changing a user s pass- by 5 inches non-resizable edit added columns.
word can all be performed through window. There is no provision for SQL Server s ALTER TABLE state-
SQL by calling system-supplied multiple simultaneous query win- ment only allows you to add col-
stored procedures. This opens the dows or workspaces. However, umns, and the columns you add
door for you to write and deploy you can call up previously exe- must allow nulls because SQL
your own system administrator cuted queries through a Previous Server makes no assumptions
application to seamlessly integrate button. Script files must be written about what data belongs in the new
these functions in a turnkey with an external editor and run column. To remove columns you
database system. InterBase does through ISQL separately. If you re must drop the entire table and
not supply any SQL statements or debugging the script, you must recreate it from a script with the
procedures to access its constantly switch back and forth columns omitted.
administration functions. between ISQL and the editor. This is further complicated by
The basic query writing features Compounding this irritant, the the fact that in order to drop a
of SQL Server include a fully scrol- file open dialog used to load the table, you must first remove all for-
lable and resizable text editor for script file isn t even smart enough eign key references to it.
writing any number of queries at to remember the last directory you Obviously, you must then restore
one time (the server can perform loaded from. You cannot selec- the foreign key references after rec-
multiple queries in one execution, tively run portions of the script file reating the table. The same issue
producing multiple result sets if  it s all or nothing. Because of the exists with InterBase, except it
needed). need to redefine SQL terminator provides better ways of working
Separate query windows can be characters in script files, the around the problem. Because of
opened for different servers and syntax of a SQL query within a this, you may not want to physi-
each window can have multiple script file isn t quite the same as an cally define foreign key relation-
query workspaces for the same or interactive SQL query. ships until well into the
different databases. The text editor Working with queries or scripts development process when the
provides standard text file between SQL Server and InterBase table structures have stabilized.
July 1996 The Delphi Magazine 27
Transportability the same pages and from reading Microsoft does a much better job
In developing a database, there them. All this is done to ensure in this department. Administration
probably comes a time when you consistency of data and that no tasks are self-contained within a
need to move it somewhere. For reads pick up a partially modified single task-oriented manual. Tasks
example, you may need to work on (and therefore possibly inconsis- are laid out step by step with ample
the project at home, on a laptop, or tent) result set. The problem is that screen shots, and every field,
deliver it to another site for demo because the locks are placed at the switch, radio button and option is
purposes. With Interbase, this is page level, unrelated data may be meticulously documented. SQL
extremely easy: you just copy the blocked as well. programming tasks are self-
.GDB file to whatever machine InterBase employs a Multi- contained in an alphabetical refer-
needs it. With SQL Server, it s an- Generational Versioning Engine ence manual covering all SQL state-
other story entirely. The preferred which I understand is an exclusive ments and concepts, including
mechanism is the SQL Transfer in the industry. Contention is han- over 180 system-supplied stored
Manager program, which moves a dled by making separate copies of procedures. The text is very clear
database (or parts of it) from one affected rows in the database and and examples are abundant. The
database to another on the same or managing them for each user ma- examples usually reference the
different SQL Servers (through a nipulating the same rows. This supplied example database, so you
LAN or modem). Unfortunately, ensures there is virtually no block- can experiment freely. SQL Server
Transfer Manager is a bit buggy. ing of processes. There is still an also provides a Database Devel-
For example, it usually refuses to optimistic locking scheme such oper s Companion which is an
move triggers. Other than that, it that if user A changes a row and excellent and comprehensive in-
seems gets the job done but it de- user B tries to change the same troduction to client/server pro-
pends on there being a direct link row, user B will get an error mes- gramming and design issues.
between the two machines. sage to the effect of  record has Finally, the complete SQL Server
A backup and restore operation been changed by another user . So documentation set can be installed
is more reliable, however there are although there are multiple copies to hard disk (or read from
problems with that too when mov- of the rows they are being managed CD-ROM) for online access.
ing between two different SQL Serv- such that data is not lost by InterBase s documentation falls
ers. Because of how SQL Server multiple simultaneous changes. a bit short in completeness and
manages user lists, the user access Nothing is free, however. All organization. The Language Refer-
and permissions from your source these extra copies of rows eventu- ence details the syntax and briefly
database won t necessarily coin- ally become  dead and must be covers the usage of each SQL state-
cide with the user list in the target cleaned up. InterBase has a ment, but you usually have to refer
SQL Server. What this means is that  garbage collection routine that to the Data Definition Guide for a
you usually have to go through periodically goes through and dis- complete explanation (minus the
some manual steps or batch proc- cards these dead rows. It s possi- complete syntax). In the Data Defi-
esses to keep the user lists in sync. ble that this garbage collection nition Guide you ll find information
could put a noticeable drag on your about declaring arrays within your
Locking Schemes system when it occurs. You can tables, but you ll have to turn to the
There is a drastic difference in how configure the interval at which this Programmer s Guide to find out how
these servers handle record lock- happens, but it is based on transac- to move data between the array
ing. In SQL Server, the lowest tion load not clock time, so you and an application. There is lots of
granularity of locking is the page can t force it to happen at a specific example code, but it rarely refers
(2Kb). Record locking per se is not time when the system is not in use. to the sample databases supplied
possible: if a lock is imposed at You can disable the garbage collec- with InterBase. In a few cases the
least the whole page containing the tion altogether and perform it usage defined in the text doesn t
row is locked (and consequently all manually through Server Manager. quite coincide with the usage in the
other rows on that page). However, examples. There seems to be some
true record locking is supposed to Documentation amount of duplication and disper-
available with version 6.5. Both products provide manuals sal of material between the manu-
Locks occur when reading or covering administration, installa- als and you have to read them all to
modifying data. SELECT statements tion, SQL programming, API refer- get complete coverage of any one
will hold a lock until all the rows ences and tools. SQL Server s topic. For example, complete infor-
selected have been read and re- manual set is certainly a lot more mation about BLOB filters is
turned. This will not prevent other voluminous (by a factor of 2), spread across three manuals.
users from reading the same rows, which is partly explained by its
but will prevent them from modify- much larger feature set and Conclusion
ing the rows (see the TTable administration capabilities. There is much more to both of
Revisited sidebar). While modify- While any evaluation of docu- these products than I have covered
ing data, locks are placed that pre- mentation quality is going to be here. My intention was to show
vent other users from modifying subjective at best, I have to say that Delphi client/server developers
28 The Delphi Magazine Issue 11
who may only be familiar with one
InterBase v4.0 SQL Server v6.0
platform just how diverse the
Rows per Table 2 billion Limited by table size
client/server world is. Both prod-
Columns per Table 16,000 250
ucts have their strengths and
weaknesses and which one is right
Row Size (excl. BLOBs) 64Kb 1962
for you will depend on what kind of
Size of Table Limited by resources 1Tb
project you re undertaking.
The table at the right summa- Number of Databases Limited by resources 32,000
rises the capacities of the two
Number of Tables/DB 64,000 2 Billion
systems. More detailed info can be
Number of Active Users Limited by resources 32,000
found on the World Wide Web at:
Memory per User 250Kb 40Kb
SQL Server:
http://www.microsoft.com/sql Server capacities
InterBase:
TTable Revisited
http://www.borland.com/
Back in the January issue, we talked about the usage of TTable in
Product/DB/InterBase/
client/server. Because with TTable you don t have control over the
ibasmain.html
queries being used to fetch data from the server and because of the
locking scheme used by SQL Server (and most other servers besides
Next month I plan to take a look at
InterBase), a particularly nasty problem can arise. Let s say you re
the new database features in
using a TTable and a TDBGrid to provide a browser for a customer table.
Delphi 2.0 from a client/server
When you open the TTable, an unrestricted SELECT statement is sent to
perspective.
the server which selects all columns and all rows in the table. This will
place a lock on at least part of the table until the query is closed or
flushed (all rows are sent back to the client). This will block other users
Steve Troxell is a software engi-
from updating (but not reading) the locked part of the table. Because
neer with TurboPower Software
SQL Server uses page-level locking, rows that aren t even being directly
where he is developing Delphi
viewed by the browser could be locked. While part of this problem is
client/server applications for the
due to the architecture of the locking scheme, it is also due to the
casino industry. Steve can be
architecture of TTable and browsers in general within a client/server
contacted at [e-mail]
RDBMS.
or on CompuServe at 74071,2207