PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

Database Development

From information storage to SQL commands

I remember quite well the first time I started developing database applications. Back then I had heard a lot of different terms and names that I didn't understand, and I wasn't courageous enough to ask what those terms meant (yes, I was very young and shy). Now, several years later, everything is clear, so if you're in this same position and have just starting programming databases, this article may help you. If you are a database veteran, this article may help you understand the problems newcomers have when they're just starting as junior developers within your project. Okay, let's start.

What Is a Database?
Think of a database as an organized mechanism that is capable of storing information and through which one or more users are able to look at stored information in an effective and efficient manner. An example from everyday life is a phone book, which can be seen as a type of database. Its data consists of rows that contain attributes like individual names, addresses, and, of course, telephone numbers. The listings are alphabetized or indexed, allowing the user to reference a particular person with ease.

This data is stored in a database somewhere on a computer and is continually maintained as people move to different cities or states. Entries are added or deleted from the phone book and also modified as people change names, addresses, and/or telephone numbers. This is exactly what's done with data in a database. In very simple terms, a database is a collection of data.

What Is SQL?
If you ever come to Europe and visit the author of this article, you'll be surprised at how many different languages (at least dialects) are spoken just within a two-mile radius of where I live. Even I might have trouble ordering from a menu via my native tongue if the waiter speaks only his country's language. Imagine that the database is a foreign land in which you seek information. The language spoken there is called the Structured Query Language (SQL), which is what you'll have to speak to express your needs. Just as you'd order a meal from a menu in a different country or town, using this language you can request specific information from within a database in the form of a query. SQL is the standard language used to communicate with a relational database. The prototype was originally developed by IBM. SQL is pronounced either of two ways: as the letters S-Q-L, or as "sequel"; both pronunciations are acceptable. However, most experienced SQL users tend to use the latter pronunciation.

What Is ANSI SQL?
You may have heard the term ANSI or IS standard. The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQL is considered the standard language in relational database communication, originally approved in 1986 based on IBM's implementation. In 1987, the ANSI SQL standard was accepted as the international standard by the International Standards Organization (ISO). The standard was revised again in 1992 and called SQL-92. The newest standard is now called SQL-99; it's also referred to as SQL3. SQL-99 has five interrelated documents and other documents may be added in the near future. The five interrelated parts are:

  • SQL/Framework: Specifies the general requirements for conformance and defines the fundamental concepts of SQL
  • SQL/Foundation: Defines the syntax and operations of SQL
  • SQL/Call-Level Interface: Defines the interface for application programming to SQL
  • SQL/Persistent Stored Modules: Defines the control structures that then define SQL routines and also defines the modules that contain SQL routines
  • SQL/Host Language Bindings: Defines how to embed SQL statements in application programs that are written in a standard programming language

    The latest ANSI standard (SQL-99), which has replaced the SQL-92 standard, is about 2,000 pages, so if you're bored and don't know what to do check them out at www.sql-99.org.

    With any new standard, there are both advantages and disadvantages. First and foremost, a standard steers all interested vendors of products in the appropriate direction for development. In the case of SQL, a standard provides a basic skeleton of necessary fundamentals, which as an end result allows consistency between various implementations and better serves increased portability (not only for database programs, but databases in general and individuals who manage databases).

    Some may argue that a standard is not that good, limiting the flexibility and possible capabilities of a particular implementation. However, most vendors who comply with the standard have added product-specific enhancements to standard SQL to fill in these gaps.

    A standard is good, considering both the advantages and disadvantages. The expected standard demands features that should be available in any complete SQL implementation and outlines basic concepts that not only force consistency between all competitive SQL implementations, but also increase the value of a SQL programmer.

    Relational Databases
    A relational database is a database divided into logical units called tables, where tables are related to one another within the database. A relational database allows data to be broken down into logical, smaller, and manageable units, allowing for easier maintenance and providing more optimal database performance according to the level of organization. Tables are related to one another through a common key (data value) in a relational database. Again, tables are related in a relational database, allowing adequate data to be retrieved in a single query (although the desired data may exist in more than one table). By having keys, or fields, among relational database tables, data from multiple tables can be joined to form one large result set.

    Client/Server Technology
    In the past, the computer industry was predominately ruled by mainframe computers - large, powerful systems with both high storage capacity and high data-processing capabilities. Users communicated with the mainframe through dumb terminals that did not think on their own, but relied solely on the mainframe's CPU, storage, and memory. Each terminal had a data line attached to the mainframe. The mainframe environment definitely served its purpose and still does today in many businesses, but a greater technology was soon introduced: the client/server model.

    Modern database systems reside on various types of computer systems with various operating systems. The most common types of operating systems are Windows-based systems and common-line systems such as Unix. Databases reside mainly in client/server and Web environments. A lack of training and experience is the main reason for failed implementations of database systems. Nevertheless, an understanding of the client/server model and Web-based systems is imperative with the rising (and sometimes unreasonable) demands placed on today's businesses as well as the development of Internet technologies and network computing.

    An Introduction to Web-Based Database Systems
    Business information systems are moving toward Web integration. Databases are now accessible through the Internet, meaning that customers access an organization's information through an Internet browser such as Internet Explorer, Netscape, or Opra (just to mention the most used ones).

    Customers (users of data) are able to order merchandise, check inventories, check the status of orders, make administrative changes to accounts, transfer money from one account to another, and so forth. A customer simply invokes an Internet browser, goes to the organization's Web site, logs in (if required by the organization), and uses an application built into the organization's Web page to access data. Most organizations require users to register with them and will issue a login and password to the customer.

    Of course, many things occur behind the scenes when a database is being accessed via a Web browser. SQL, for instance, can be executed by the Web application. This executed SQL is used to access the organization's database, return data to the Web server, and then return that data to the customer's Internet browser.

    The basic structure of a Web-based database system is similar to that of a client/server system from a user's standpoint. Each user has a client machine that has a connection to the Internet and contains a Web browser. The network just happens to be the Internet (in the case of a Web-based database), as opposed to a local network. For the most part, a client is still accessing a server for information. It doesn't matter that the server may exist in another state, or even another country. The main point of Web-based database systems is to expand the potential customer base of a database system that knows no physical location bounds, thus increasing data availability and an organization's customer base.


  • More Stories By Berndt Hamboeck

    Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under admin@bhitcon.net.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.