Intercom V01n01 - Old Magazine Devoted To Interbase, 1997

,QWHU%DVH6RIWZDUH&RUSRUDWLRQ

,QWHU&RP 92/80(180%(5635,1*

InterBase Gets a New Home! InterBase is now a product of InterBase Software Corporation, a subsidiary of Borland International Inc. The leaders of Borland and the InterBase team together realized that to provide the best product and service to InterBase developers, a different model was needed for development, packaging, sales and service. InterBase has always been a strong product, and as an independent company, InterBase Software Corp. has the opportunity to be successful in new ways. One of the upcoming changes is that we are moving into a new home. As of July 17, 1997, we will be located at the following address. InterBase Software Corporation 1800 Green Hills Rd., Ste. 150 Scotts Valley, CA 95066 (408) 430-1500 (888) 345-2015 toll free (408) 430-1510 FAX Some of you who have been Borland customers for a long time may recognize this address, since it used to be Borland headquarters. We will be located right down the street from the Borland main campus. We will maintain ties with Borland’s sales team, so keep using the sales people, account managers, and distribu-

tors which you are accustomed to using. We will also have our email, phone, and mail forwarded to us from Borland as we go through our move, so you shouldn’t have any problems contacting us. Thank you in advance for your cooperation as we get settled into our new office.

Letter from the President Dear InterBase VAR Partners: By way of introduction I am Jim Weil, President of the new Borland subsidiary, InterBase Software Corporation. The InterBase team is very excited to have this opportunity to be an indepen-

dent operation, and we believe this is the best thing for you, for Borland, and for InterBase. The Borland company strategy is to build the best development tools on the market. InterBase wants to build the best database. Those paths require different development cycles,

CONTENTS InterBase Gets a New Home! . . . . . Letter from the President . . . . . . . . . Passing and Returning Dates to a Delphi-Created UDF . . . . . Q&A: Paul Ostler . . . . . . . . . . . . . . . The World Will Come to an End . . Customer Feedback . . . . . . . . . . . . .

1 1 2 2 3 4

different deployment platforms, and different skills. Yet, there are significant synergies that come from working together with Borland. By becoming an independent subsidiary we can continue to benefit from the partnership by being embedded in the Borland tools, while having the independence and flexibility to pursue other opportunities. In one of the notes I have received since the announcement of InterBase Software Corporation, the observation was made that this business changes every year and IB needs to find its own niche. We agree and believe that our only realistic chance to do this is as an independent company. We think, for example, that the opportunity surrounding Java will be huge, and we intend to look at this with an eye toward making it an important part of our ongoing strategy. Additionally, we are building a large network of VARs who have developed applications in their own niches utilizing IB technology. We need to be as responsive to this community as we have been to other Borland tool users. Again, we believe we can better accomplish this outside the Borland umbrella. I took this job because I believed that the IB team was capable of taking this superior product and building a substantial, successful business. I solicit your support; we will not and cannot achieve any measure of success without you!

Each date value is stored in two 32-bit integers: a signed integer for the date, and a unsigned integer for the time. Use this Delphi code to define this structure (ISC_QUAD) and the pointer to this structure (PISC_QUAD): type {InterBase Date/Time Record} ISC_QUAD = record isc_quad_high: Integer; // Date isc_quad_low: Cardinal; // Time end; PISC_QUAD = ^ISC_QUAD;

To protect the return value, declare a thread-safe ISC_QUAD variable outside of the function definition that will hold the return value (if the return value is a date).

Chris Levesque is a Quality Assurance Engineer for InterBase Software Corp.

threadvar tempquad: ISC_QUAD;

Then, write your function so that the result points to the threaded variable.

Q&A: Paul Ostler Architect of InterClient

// This function adds a number of days to an existing date. function DayAdd(var Days: Integer; IBDate PISC_QUAD): PISC_QUAD; cdecl; export; begin tempquad.isc_quad_high:= IBDate^.isc_quad_high + Days; tempquad.isc_quad_low:= IBDate^.isc_quad_low; Result:= @tempquad; end;

Retrieving the First n Records From a Result Set

Passing and Returning Dates to a Delphi-created UDF

There are times when you need to retrieve a fixed number of records from a result set using SQL – perhaps the top ten salespeople in a division, or the bottom five earned run averages in a baseball league. One way to do this is to create a stored procedure that performs the query and keeps track of how many records it has fetched. After the limit has been reached, the procedure should end.

You can use Delphi to create userdefined functions (UDFs) that extend the functionality of the InterBase server. Using a UDF that takes date values as parameters or returns date values may result in a Protection Exception or erroneous results if the DLL does not take special precautions to protect the date values.

Here's a procedure that returns the employee number, first name, and last name from the records in the employee table. The parameter rows specifies how many records the procedure returns, starting with those who have last names at the beginning of the alphabet, or the whole result set, whichever is smaller.

Jim Weil

create procedure getemp (rows integer) returns (emp_no smallint, firstname varchar(15), lastname varchar(20)) as begin if (rows < 1) then exit; for select emp_no, first_name, last_name from employee order by Last_name into :emp_no, :firstname, :lastname do begin suspend; rows = rows - 1; if (rows < 1) then exit; end end

,QWHU&20



A key InterBase personality will be interviewed as a regular feature in this newsletter. This month, we interview Paul Ostler. Q: What is InterClient? InterClient is an all Java JDBC driver for Java connectivity to InterBase. InterClient is a networked driver, meaning that it incorporates a JDBC remote protocol for exchanging and caching data between client and server. A networked driver synchronizes object and transaction state on both client and server, and transfers data between client and server in an efficient manner, usually via pipelining. This differs from a JDBC bridge which maps from the JDBC API to some native RDBMS vendor API locally. A JDBC bridge implementation relies on the RDBMS vendor's native API for the actual exchange and caching of data between server and client. One advantage of a networked driver over a bridge implementation is that the client is 100% pure Java, thereby providing for cross-platform, robust, and secure applets. Whereas a bridge implementation requires some binary code to be pre-loaded on each client machine, contrary to the notion of an applet.

Furthermore, the JDBC remote protocol employed by InterClient is streamlined for JDBC data access, and is database independent except in cases where proprietary InterBase features may be leveraged. This results in a much thinner and more efficient client than a Java client which would implement a legacy RDBMS remote protocol in Java. The disadvantages of a JDBC remote driver are that local connections, common for 3-tier applications, entail the added overhead of the JDBC remote protocol layer, and secondly, a middleware server, i.e. InterServer, is necessary to translate client requests to the RDBMS server.

protocol and one via the legacy remote protocol provided by the native C API. InterServer does not deprecate InterBase’s native remote protocol, but rather takes one step towards componentizing and streamlining the InterBase RDBMS solution for Java. The next phase of the InterClient project involves Java server components, including a JDBC bridge which could be used directly by a 3-tier application, or as the backend of a Java InterServer. A component model is going to be essential in a world where standards and technology are rapidly changing. For instance, we may at some time isolate the InterBase transaction manager as an implementation of the yet to be released Java Transaction Service interfaces (JTS). Moving towards a native Java API for InterBase will also allow us to plug into crypto and compression packages heretofore unavailable. Code reusability was never realized with C++, but it’s being realized rather rapidly with Java. It’s pretty clear that we can leverage our small footprint, technology, and ease of maintenance in the Java VAR space. The future looks good.

Q: Why is Java important for InterBase? InterBase is a start-up once again. As a start-up, we’ll need to evolve with the technology if we are to find new customers. A company such as Oracle could ride on legacy, but we can’t afford to be complacent. I believe the best way for us to evolve is to embrace a malleable architecture. InterBase is a lightweight, yet powerful, RDBMS. What better candidate from which to evolve plug-and-play embedded relational database components for Java. It was by no mistake that we chose to tailor our networked driver for JDBC remote. Such a choice positions InterBase with two alternatives for data access, one via InterServer and the new JDBC remote

You can currently download InterClient Beta 0.91 from KWWSZZZERUODQGFRP LQWHUEDVHLQWHUFOLHQW Biography Paul is the developer/architect of InterClient and InterServer, a Java client and middleware server for InterBase. Before joining Borland, he worked at Advanced Decision Systems as lead developer of a defense application for Rome Laboratories. Before that he worked at IBM in the Advanced Technology group of the Santa Teresa Laboratory where he developed prototypes in Smalltalk and Prolog for a database CASE tool and a natural language frontend. While at IBM, he collaborated with Professor F.L. Bauer at the Technical University of Munich to develop an

,QWHU&RP



expert system for program transformation. Paul has a B.S. in Computer Science, summa cum laude, from the University of Utah, and an M.S. in Computer and Information Science from the University of California, Santa Cruz.

The World Will Come to an End It will occur on December 31, 1999. That seems to be the idea that many software designers have had for the last thirty-odd years. Because of this certain cataclysm, it was thought unnecessary to design databases and applications to account for dates past the year 1999. Two digits were enough to record dates. Magnified by the millions of dates that had to be stored in databases, this has saved uncounted megabytes of hard disk space. It is becoming an increasingly accepted fact that the world will not end on that date. The heroes of yesterday’s bytethrift are the laughingstocks of today’s market of inexpensive storage. The year 2000 is a problem for the software industry. When string representations of years are abbreviated by using the last two digits of the year, rather than the full four digits, the century in which the date falls cannot be determined from just two digits. We naturally assume that two-digit years represent years in the 20th century. The problem with this assumption is that a 30-year mortgage starting in 1997 will end in 1927, and I will qualify for Social Security benefits 35 years before I was born. We need software to account for four-digit years to properly store and compare dates. There are two issues with this: date parsing and date storage. InterBase provides a reasonable default interpretation of two-digit years. The InterBase server interprets a two-digit year as the nearest year within a 50-year range. For example, 2/28/48 is interpreted as 1948, whereas 2/28/47 is 2047. This works out pretty well for most cases, but it’s no substitute for using four-digit years. For example, if you are

entering the birth date for a 52-year-old person, and you use a two-digit year for their birth, the database will interpret it as 2045. To override this, you should make sure that data entry operators are forced to enter four-digit years (reject year values less than 100). Alternately, write your applications to reformat date strings before inserting them into a database.

If you are confident that InterBase’s interpretation of two-digit years will yield the correct results for all of your legacy data or for all possible data that operators are entering, then you have no conversion work to do. Let InterBase convert the dates. Keep in mind that InterBase will interpret two-digit years slightly differently next year, because the 50-year frame of reference will have moved.

Some environments have to convert data in old formats that they cannot change, because the space allocated to store the two characters for the year is fixed. When interpreting this legacy data, it is the programmer’s responsibility to correctly interpret what the two-digit year numbers mean.

InterBase has no problem storing dates beyond the year 2000. Large year numbers can be stored, but after 32,768 A.D., the data conversion and display routines overflow. On the other hand, this range is adequate for most applications, except those involving geology or astronomy.

For example, suppose you are migrating a data set from a fixed-width format such as ASCII to InterBase. You write an application to read the ASCII data and insert it into an InterBase database. Between the reading and inserting steps, you may have to reformat two-digit years to four-digit years according to your own rules for interpreting the year.

The date is stored on disk as a structure of two 32-bit integers. The first is a signed integer, representing the number of days since 11/17/1858. This is the date that DEC technology traditionally uses for the date epoch; it is the founding date of the Smithsonian Institute.

This can be done with a trigger and a simple UDF: CREATE TRIGGER FIX_BIRTHDATES FOR PEOPLE_TABLE BEFORE INSERT AS BEGIN IF (NEW.BIRTHDATE > ’TODAY’) THEN NEW.BIRTHDATE = AddYear(NEW.BIRTHDATE, -100); END

The $GG
,17(5%$6(62)7:$5(&25325$7,21 %25/$1':$< 6&27769$//(<&$/,)251,$

The second number is an unsigned integer representing hundredths of seconds since midnight. InterBase stores time to this level of precision. The InterBase API provides two functions, isc_encode_date() and isc_decode_date() which convert InterBase’s internal date structure into the struct tm type that is familiar to C programmers. InterBase stores dates identically on all platforms and between versions 3.x and 4.x of the product. Once a date is stored into InterBase, the century is stored as

part of the data, and when you retrieve the value from the database you get the four-digit year. InterBase handles dates past the year 2000 very well. It is not difficult to override the default two-digit year interpretation that InterBase provides, and dates are stored correctly once they are in the database. I am more concerned about my VCR. January 1, 1900 falls on a Monday, but January 1, 2000 falls on a Saturday. My shows are going to record on the wrong night! Bill Karwin is Technical Publications Manager for InterBase Software Corp.

Customer Feedback That concludes our first issue of the InterBase newsletter. In future issues, we will expand our format, including articles from InterBase VARs and partners as well as ISC staff. We will use this medium to publish important news about InterBase, and to deliver insightful technical articles. We would like to hear your comments and suggestions regarding the first issue of our newsletter. InterBase questions from subscribers are welcome. Feel free to email your feedback to us at [email protected] Thank you very much.