Accessing Interbase/Firebird Metadata in Delphi

Accessing Interbase/Firebird Metadata in Delphi

WARNING: Interbase and Firebird system tables are not for the faint hearted. It is not recommended that you alter anything in these tables if you ever want to use them again - and keep your job. Major stuff-ups can occur.

I was looking at some old Delphi 7 code of mine when I was attempting to get in behind the scenes of Interbase and Firebird tables and fields to learn a little more about them. I came across these little pieces of information I'd like to share.

The following will list all the tables in one grid and, for each table, list all the fields in another grid. Drop on your TIBDatabase and direct it at your Interbase or Firebird server.

Now drop a TIBTransaction, TIBQuery, TDataSource, and a TDBGrid and connect them all.

Enter the following query into the IBQuery1...

  SELECT DISTINCT RDB$RELATION_NAME as MyTable
 FROM RDB$RELATION_FIELDS
 WHERE RDB$SYSTEM_FLAG=0
 AND RDB$VIEW_CONTEXT IS NULL
 ORDER BY RDB$RELATION_NAME

and make IBQuery1 live. Now drop on another TDBGrid, TIBQuery and TDataSource and connect them. This time, select IBQuery2 and add TDataSource1 in the "DataSource" Property. This will ensure that the second query will look to the first query to fill in the parameters. That parameter will be the field MyTable.

Enter the following query string into the SQL property of IBQuery2...

  SELECT RDB$FIELD_NAME AS FIELDS
 FROM RDB$RELATION_FIELDS
 WHERE RDB$RELATION_NAME = :MyTable
 ORDER BY RDB$FIELD_POSITION

and make that query live. When you run that program, selecting tables in the first grid will show all the field information for that table in the second grid.

So far I have the following types in the field RDB$FIELD_TYPE. These can translate to...

• 8 = Integer
  
• 10 = Float
  
• 12 = Date
  
• 13 = Time
  
• 14 = Char
  
• 35 = TimeStamp
  
• 37 = VarChar
  
• 261 = Blob
  

There'll be a definitive list somewhere but that's all I have needed so far. I got that list from the internet somewhere but it was so long ago that I have forgotten where (thanks to whoever that was).

That will get you started, but here are some other queries that will return more metadata information...

Return indexes for a table

  SELECT RDB$INDEX_NAME
 FROM RDB$INDICES
 WHERE RDB$RELATION_NAME = :MyTable
 AND RDB$UNIQUE_FLAG IS NULL
 AND RDB$FOREIGN_KEY IS NULL

Return all Generators

  SELECT RDB$GENERATOR_NAME
 FROM RDB$GENERATORS
 WHERE RDB$SYSTEM_FLAG IS NULL

Return all Triggers

  SELECT * FROM
 RDB$TRIGGERS
 WHERE RDB$SYSTEM_FLAG IS NULL

Using XML as a data storage format

Introduction

Evolution of the web industry leads developers through sophisticated technologies waving and balancing between client-oriented and server-oriented approaches. New standards for browsers, protocols, and scripting languages replace each other. The next step is XML - extended markup language, which suppose to replace HTML and dominate in web applications as main communication format. XML has been designed to transfer data to clients separately from data's format. Web server or Internet client should combine XML data with a style sheet and generate well-known HTML code to display the data.

On the other hand you may use XML language to extend functionality of your programs. Consider XML as a universal data storage format, which as a matter of fact much more flexible than DBF or any other relational database format.

Why should you use XML as the only advanced way to create web pages? XML is more universal and more flexible than just only markup language. You may even create your own data format or network protocol by inventing your own tags and interface.

Below you will find one example of such non-standard XML usage, designed for use in Borland Delphi applications.

Analysis

Your program needs to store some data in a file or transfer the data to another computer. You can save your data in text or any proprietary format. You will need to write code to support such format. Should you change data structure, you will face necessity to modify the code. In any case you should not consider this format as a standard one, and other developers probably will not support the format.

Use XML as base for your data storage and transfer and you will gain the following advantages:

* Established and supported syntax standards;
* Common program interface for reading and writing XML text;
* Flexible data structure;
* Web enabled technology;

Using XML as a data format has some disadvantages too:

* Unlike usual database, XML text does not have fixed structure.
In most cases whole XML text stored in memory, so it should
be relatively small files;

* There are no built-in security in XML, so you have to encrypt
and decrypt XML files;

* XML is text format so you have to convert all fields to text
type before storing in text;

The most optimal and convenient way to implement this protocol in Delphi is to design an interface between Pascal objects and XML code. The goal is to store instances of Pascal classes in XML format with ability to restore the instances from the XML text.

It is a good idea to split "Pascal Object - XML" interface into two interfaces: "Pascal Object - Data object" and "Data object - XML" (see figure 1).

I do not see a dainty solution to extend any Pascal class with XML interface. Instead, let's create a new class to store and manage properties and data fields of an object. A developer will need to write additional code to move data between Pascal object and data object.

Figure 1. XML data interfaces



Design

Our task is to implement two independent interfaces: "Pascal Object - Data Object" and "Data Object - XML".

First interface will enable a developer to replicate data structure of an instance of any class and instance of the SPO class. "SPO" stands for Standard Pascal Object. SPO class will provide data container and set of methods to store and retrieve data.

Second interface will be implemented as a set of routines to convert SPO object to XML text and visa versa.

Design of SPO interface

The main purpose of the SPO class is to store and provide access to a data fields and properties. Because of that, it is much more important to create a convenient data access interface, than to optimize data storage method. After all, we will be able to change data storage method later without affecting the interface.

First of all let's define the data types, supported by our interface. We will consider only five data types in our model:

* String
* Numeric
* Date
* Boolean
* Object

The data type "object" allows us to build complex data structure by nesting one object to another. On figure 2 you may see an example of data object structure:

Figure 2 Data object model

To access data in our object we can use traditional method - to get property of main project, retrieve object from the property, then get the property of the retrieved object, etc:

var
O: TspoObject;
P: TspoProperty;
S: string;
begin
P := AnObject.PropByName['Career position'];
O := P.TheObject;
S := P.PropByName['Title'].Value;
end;

The same long sequence will be necessary to add new property to an object. To simplify access to nested objects and properties we will use path-style resource locator string. For example to get value of the "Start date" field you will use the following syntax:

AnObject['Career position/Start date'] := Now;

To create the object on figure 2 we can use only six lines of code:

with AnObject do begin
AddItem('First name','John');
AddItem('Last name','Smith');
AddItem('Date of birth',EncodeDate(1964,7,23));
AddItem('Career position',NULL,xtObject);
AddItem('Career position/Start date',EncodeDate(1998,9,1));
AddItem('Career position/Title','Director');
end;

Using collections

Sometimes you need to store several objects inside one property. You may index this collection of objects by a number or by keyword. To create a collection in object property you have to create an object and then add as many properties as you wish:

with AnObject do begin
AddItem('List',NULL,xtObject);
for i:=1 to 10 do begin
AddItem('List/'+intToStr(i),'Item'+IntToStr(i));
end;
end;

Design of XML interface

Now we are ready to design an interface to store our SPO in XML text (string). Because XML code is just a sequence of tags, we will design separate routine to format XML string as a XML text with indents. The XML dictionary will be quite simple. To store an object in XML text let's use keyword "object":




Object will contain only properties and methods:




Each property must contain a name, type and value. It can also include default value, and scope:



string

First name

Smith
<

The full list of used tags will looks like:

* Object
* Property
* Scope
* Type
* Name
* DefaultValue
* Value
* Param
* Method

Construction

There are two units. First unit - CPOM.PAS contains classes to create in-memory data objects: TspoObject, TspoProperty, and TspoMethod. Second unit - CxmlInterface.pas contains routines to convert TspoObject to XML text and vice versa.

Interesting Facts

Did you Know?

1. 10% of the world's population speak English as their mother tongue (Chinese 21%, Spanish 6%, Russian 6%, Malay 4%, Hindi 4%, Japanese 3%, Arabic 3%, Portuguese 3%, French 2%, German 2%)

2. Rains of many kinds of living creatures have actually been reported from earliest times and all over the world. On 28th May 1881, during a thunderstorm on the outskirts of Worcester, England, tons of periwinkles and small hermit crabs fell on Cromer Gardens Road and the surrounding fields.

3. The word "Christmas" comes from the Old English, "Cristes maesse" which means "Christ's mass" on which Christians celebrate the birth of Jesus Christ. It is traditionally a celebration of family and children.

4. The modern Christmas tree tradition came from western Germany, from a medieval custom, as a paradise tree -- a tree decorated with apples, wafers (or cookies), and candles representing the Garden of Eden, the host, and Christ.

5. The alarm clock was not invented by the Marquis de Sade, as some suspect, but rather by a man named Levi Hutchins of Concord, New Hampshire, in 1787. Perversity, though, characterized his invention from the beginning. The alarm on his clock could ring only at 4 am. Rumor has it that Hutchins was murdered by his wife at 4:05 am on a very dark and deeply cold New England morning.

6. If you went out into space, you would explode before you suffocated because there's no air pressure.

7. Only one satellite has been ever been destroyed by a meteor: the European Space Agency's Olympus in 1993.

8. 5th Century, Rome Mid February was traditionally the time of the Lupercian festival, an ode to the God of fertility and a celebration of sensual pleasure, a time to meet and court a prospective mate. In AD 496, Pope Gelasius outlawed the pagan festival. But he was clever to replace it with a similar celebration, although one deemed morally suitable. He needed a "lovers" saint to replace the pagan deity Lupercus. The martyred Bishop Valentine was chosen as the patron saint of the new festival.

9. Saint Valentine had been beheaded for helping young lovers marry against the wishes of the mad emperor Claudius. Before execution, Valentine himself had fallen in love with his jailer's daughter. He signed his final note to her, "From Your Valentine", a phrase that has lasted through the centuries.

10. Thumbing your nose (raising your thumb to your nose and fanning your fingers) is a sign of mockery throughout most of the world.

In Australia, it is rude to wink at women.

In Brazil, pulling down the lower lid of the right eye means that the listener doubts what you are saying.

In Korea, it is rude to keep your hands behind your back or in your pockets.

11. Mangos have been cultivated in India for about 5,000 years, and were originally small, fibrous fruits, somewhat like plums, with a taste like turpentine. There are now over 500 varieties grown there.
    
    More fresh mangos are eaten every day than any other fruit in the world.

12. A man named Sir Henry Wyat was sentenced to the Tower of London, at a time when prisoners generally starved to death. Sir Henry's kitty-cat seemed to understand the situation because she snuck into the Tower bringing him a freshly-killed pigeon every day. When the king heard of this, he must have felt sad for the kitty, because he immediately set Sir Henry free.

13. The word "ecology" derives from the Greek words "oikos", loosely translating as home and "logie", meaning science or doctrine. The dictionary defines ecology as the discipline that studies the relationships between organisms and their environment, that is to say, between an organism and its home. In a broader sense, ecology deals with the relation between living beings and the planet Earth: our great home, our only home. The word tourism developed from the Hebrew word Tora, which means to study, learn or search.

14. The Galapagos Islands have lived in virtual isolation for millions of years. In total, they consist of 61 islands and islets, with 13 main islands. Seeming like shadows upon the sea from one another, the 13 main islands are Baltra, Espanola, Fernandina, Floreana, Genovesa, Isabela, Marchena, Pinta, Pinzon, San Cristobal, Santa Cruz, Santa Fe, Santiago. In total land area, the islands are 4897 sq. miles (7880 sq. km) and in the total geographical area from Darwin Island to San Cristobal and Espanola, 28,000 sq. miles (45,000 sq. km)

15. Heathrow Airport handles more international passengers than any other airport in the world and offers flights to many international destinations including 33 flights to Paris and 23 flights to New York each day. The most popular country for flights from Heathrow is the United States of America. The busiest routes are New York, Paris, Amsterdam and Dublin.

16. One out of the ordinary punishment of the Elizabethan England was the drunkard's cloak. It was a punishment for public drunkenness; the name of it is somewhat misleading. The flaw in the name comes from the fact that the cloak is less a cloak and more a barrel. The drunk was forced to don a barrel and wander through town while the villagers jeer at him. Holes were cut in the barrel for the person's hands and head, causing it to become like a heavy, awkward shirt.

17. The Times Square in New York has been the center of worldwide attention for New Year for 96 years. In 1907, for the first time the Ball lowering ceremony was organized and this is now the symbol of New Year worldwide. This event is seen by over 500000 visitors at Times Square every year and over 100 Crore viewers on TV. The Times Square ball is 6 feet in Diameter and weighs over 400 Kgs. It has over 500 Crystals and is lighted with over 600 bulbs. The ball is lowered 77 feet in 60 seconds and the 60th second is at exactly 24:00 Hrs.

18. The White House is a freestone building in American colonial style and stands in Washington, DC, United States of America. It is the official residence of the President of the USA. The White House is the oldest federal building in the capital. It is officially called the Executive Mansion.

19. The White House receives approximately 6,000 visitors a day and has 6 floors (two are basements), 132 rooms, including 16 family-guest rooms, 1 main kitchen, 1 diet kitchen, 1 family kitchen, and 31 bathrooms

20. The people in the United States first decided to make their own currency when they needed money to pay for the Revolutionary War. Before the mid-1800's each dollar was worth a certain amount of gold or silver. Banks printed all the money. There were more than 10,000 different types of dollars that were printed. These bills (notes) were made in various sizes, colors and designs.

21. There are about twenty modern nations whose currency is called the "dollar." The word apparently derives from "taler," which in turn comes from "Joachimsthal," the name of a place in Bohemia where the taler (a silver coin) was created, with the "-thal" part presumably meaning "valley." (The modern German spelling, by the way, has been changed to "tal," which explains the new spelling of the English word "Neandertal.") So, we use dollars today because certain coins were once minted in a valley.

22. The flag of Britain, commonly known as the Union Jack (which derives from the use of the Union Flag on the jack-staff of naval vessels), embodies the emblems of three countries under one Sovereign. The emblems that appear on the Union Flag are the crosses of three patron saints: The red cross of St George, for England, on a white ground; The white diagonal cross, or saltire, of St Andrew, for Scotland on a blue ground; The red diagonal cross of St Patrick, for Ireland, on a white ground.

23. Wales is not represented in the Union Flag because, when the first version of the flag appeared Wales was already united with England. The national flag of Wales, a red dragon on a field of white and green, dates from the 15th century and is widely used throughout the Principality. The dragon as a symbol was probably introduced into Britain by the Roman legions. According to tradition, the red dragon appeared on a crest borne by the legendary King Arthur, whose father Uthr Pendragon, had seen a dragon in the sky predicting that he would be king.
24. Did you know that on an average day, drying paint releases more smog-forming compounds into the air than all of the area's oil-refineries and gas stations combined? Oil-based paints contain three to five times more polluting solvents than water-based, latex paints.

25. Businesses in European countries commonly use handwriting analysis in their employment practices. In France and Switzerland, approximately 80 percent of the large corporations use graphology in their hiring procedures.

26. Graphology is taught in psychology departments of several leading universities in Germany, France, Switzerland, Holland and Israel.

27. According to Phlegon, a Roman author of the 2nd century AD, the wreath of olive leaves was instituted as the prize for victors at Olympia in 752 BC, on the advice of the Oracle at Delphi. King Iphitos was told by the Delphic Oracle to plant an Olive Tree from which the victor' wreaths for the Olympic Games was cut.

28. In most TV series, the violence depicted shows no consequences. For example, no physical harm is depicted in 75% of series, no psychological trauma in 90%, and no judgment about the morality of the act in 87%. Positive and negative motives for violent actions were roughly equal (45 and 55 per cent, respectively). "Good guys" were slightly more likely than "bad guys" to be the instigators of violent activities (46 per cent vs. 41 per cent).

29. The world-famous Statue of Liberty standing in New York Harbor was built in France. It was France's gift to the American people. Its designer, a Freemason, was Brother Frederic A. Bartholdi (1834-1904) who conceived its design while on a visit to America. As his ship sailed into New York, Bartholdi had a vision of a woman standing on a pedestal, holding a torch and welcoming immigrants to a new life in a free land.

30. Some people believe that the aurora makes sound that accompanies the ripples and flow of the light. If the aurora does make sound, the sound would have to be generated here on Earth by some electromagnetic effect. Any noise generated by the aurora would take a long, long time to travel all the way to Earth, and the air up by the aurora is much too thin to carry sound.

31. World wide, about 1.5 million people are killed in road accidents every year. Road accident research has pointed towards driver error in the majority of cases. In the U.S. about 42,000 traffic fatalities occur every year and about 6.5 million injuries annually at a total cost of 200 billion dollars. Almost all of "driver error" can be traced to lack of emotional intelligence behind the wheel.

32. Taking tea has been a London tradition for more than 150 years. The practice was launched by the Duchess of Bedford in 1830 when she ordered a little something to ward off pangs between lunch and dinner. By the 1840s, wafer thin slices of bread spread with chopped cucumber along with light sponge cakes and freshly brewed pots of tea were being served up with tidbits of gossip all over London.

33. Alcohol is a depressant—not a stimulant as many people think. Alcohol slows down activity in the central nervous system, including the brain. Depressants affect concentration and coordination, and slow the response time to unexpected situations. In small quantities, depressants such as alcohol cause people to become relaxed and lower their inhibitions. They feel more confident and often act in a more extroverted manner. In larger quantities, depressants can cause unconsciousness and death. Benzodiazepines, heroin and cannabis are also depressant drugs.

34. There are now 6 million active divers worldwide. They engage in many different types of diving, of which wreck, cave, commercial, and military diving are just a few. The most common form of diving is sport diving, or recreational diving, which is practiced at depths of less than 130 ft (39 m). Diving beyond this limit requires advanced training. The amount of time a diver can remain underwater depends on several elements. The deeper the descent, the more rapidly the diver consumes air. In addition, some people consume air at a quicker rate than others. Several factors influence how efficiently a diver uses air, including diving experience, physical fitness, general relaxation, and a healthy lifestyle that limits tobacco and alcohol intake. Most divers can spend 45 minutes to an hour at 40 ft (12 m) below the surface.

35. Business letter is a formal document typically sent externally to those outside a company but is also sent internally to those within a company. It is estimated that close to 100 million business letters are written each workday.

36. In the year 1888, the Norwegian Fridtjof Nansen made the first crossing of Greenland, travelling from East to West on skis. The report on his expedition, Paa ski over Gronland, was published in 1890 in both Norwegian and English, and later in German. It aroused great interest in skiing in Europe and the United States, as well as creating a Norwegian national hero.

37. Lewis Carol who wrote "Alice's Adventures in Wonderland" and "Alice through the Looking Glass" lived in Christ Church college, Oxford as a student and lecturer. His real name was Charles L. Dodgeson and he became a lecturer in mathematics. Alice was the young daughter of the Dean of Christ Church and the book "Alice's Adventures in Wonderland" a Christmas gift for her.

38. Leonardo da Vinci left fewer than 30 paintings, and these aren’t even all finished.

39. Mona Lisa has no eyebrows in Leonardo da Vinci's painting because during that time, a woman was considered more beautiful if she shaved her eyebrows.

40. Leonardo became a vegetarian out of pity for animals and was even known to purchase birds at the market and set them free.

41. Power from the sun comes to the Earth as heat and light. This heat and light are the effect of the Sun's constant nuclear fusion of hydrogen nuclei. The process of fusion produces helium nuclei along with large amounts of energy. This energy is expressed as electromagnetic radiation (light is a specific frequency range of this radiation) as well as radiated temperatures of more than 6,100 degrees C. Only a small fraction of these extreme levels of energy that are released by the Sun come into contact with the Earth.

42. On 1 January 2007, Bulgaria and Romania have officially become part of the European Union. The European Union now boasts 27 nations and 490 million people.

43. Bulgaria will be the second EU country, after Greece, to use a non-Latin alphabet and the first to use Cyrillic, which originated in the medieval Bulgarian Empire.

44. 2007 marks the 50th anniversary of the Treaty of Rome, which established the European Economic Community (EEC), the forerunner of the European Union.

45. There are roughly 6,500 spoken languages in the world today. However, about 2,000 of those languages have fewer than 1,000 speakers. The most widely spoken language in the world is Mandarin Chinese. There are 885,000,000 people in China that speak that language.

46. St. Valentine's Day has connection with the term "fertility" because it is on the 14th of February when even before the birth of Saint Valentine, the birds used to mate with their partners.

47. Baba Marta (meaning Grandma Marta in English) is a holiday unique to Bulgaria celebrated on March 1 every year. This day is a celebration of the end of winter and the beginning of springtime. Bulgarians give each other "Martinitsi" (singular "Martenitsa") made of simply twisted red and white threads. They are either pinned on the clothes or worn like bracelets. The Martenitsa is a sign of health and good luck.

48. March 20 (March 21 in some years) is significant for astronomical reasons. On March 20, 2008 (March 20, 05:48 Universal Time), the Sun will cross directly over the Earth's equator. This moment is known as the vernal equinox in the Northern Hemisphere (vernal denotes "spring"). For the Southern Hemisphere, this is the moment of the autumnal equinox.

Data Exchange using XML and Delphi

Data Exchange using XML and Delphi Author: Deepak Shenoy Introduction Introduction to XML Where does XML fit in? XML and Delphi Stock Demo - Steps 1 to 4 Converting Existing Applications to use XML Data communication using XML Interoperability with other systems Communication Demo Technologies that use XML Internet Express BizTalk SOAP - Simple Object Access Protocol Conclusion References Appendices Listing 1-Stock View Application Listing 2-Stock Server Listing 3-XML in a Client Dataset ------------------------------------------------------------------------------------------------------- Introduction You have a client-server application. It's the age old concept - a set of forms on the client, a database on the server. Data access through ADO or BDE. Works great on a desktop or on a corporate intranet. Now, you have clients that want to access data "remotely" - across a WAN, or the Internet. So you're working on building a remote client for remote users. Then some specifications change, and you end up maintaining TWO versions - the remote app and the "local" application. Now someone wants a browser based client, and suddenly maintenance is a nightmare! You obviously need a solution that reuses the code on your client, yet allows you to remotely access your server. There are many solutions that currently exist: I will not delve into all of them. I'll talk about how you can use Extensible Markup Language (XML) to exchange data between your server and client - a solution that I will demonstrate to be scalable to remote access, with reduced code-maintenance. I will not demonstrate any proprietary code or even try to sell you any software - the aim of this article is to tell you how you can use XML to make your applications open and scalable. By open, I mean your application can "talk" to similar other applications, and by scalable I mean you can make remote clients, browser based clients etc. easier to develop. I will compare my idea with Internet Express - the solution provided with Delphi 5. As it stands today, Internet Express does the job of getting your data on to a browser - a remote access solution. But there is one thing you lose - the ability to define your XML yourself, to import data from other XML - one of the main benefits of XML. All of the source code accompanying this article is free for you to use, commercially or otherwise. Click here to download the source code. XML– Introduction to XML What is XML? It's a language. It is written is plain text - in a certain format. The "format" is used to structure the data so it makes it more readable than your average shopping list. Let's take an example: A list of books that you would want to display, for instance, could be shown in a paragraph listing the book name, the author and an abstract. In XML, you would describe such a listing as: <BOOK Name="Tomorrow won't be like yesterday"> <Author> Pres Enttense </Author> <Abstract> If you're worried about not getting enough done today, you're wasting your time. Don't lose your hair because your boss needs to play golf tomorrow. Golf can wait. It's a boring game anyway. </Abstract> </BOOK> Figure 1 The parts between the "<" and the ">" is a "tag". Which means it isn't something that's part of the data itself, but something that describes what the data means. The piece of XML above reveals that there's a book named "Tomorrow won't be like yesterday", whose author is Pres EntTense and there's an abstract given. Now you can extract the fields you want and present it in your application. But before we go ahead, let's discuss how XML is structured. XML Structure. The prolog. First, you need to be able to say "what follows is an XML document". Otherwise, a program wouldn't be able to tell it from anything else. You would add a prolog like so: <?xml version="1.0"?> The only difference between the prolog and an XML tag is the "?" that's between the angle brackets. The Document Element Each XML document describes something - and this something is depicted as a base tag inside which all the other data goes in. In our example above, a book was the document element, so the XML was surrounded by <BOOK> and </BOOK>. If we had two or more books we'd have to use: <BOOKS> <BOOK> ... </BOOK> <BOOK> ... </BOOK> </BOOKS> and <BOOKS> is the document element here. Every XML document needs one and exactly one document element. Document Content A document can various types of content 1. Elements 2. Attributes 3. Comments Elements A "tag" is an element. In our example above, BOOK, Author, Abstract are XML elements. Each element in XML should have starting and ending tags - for any tag X there must be a <X> and a </X> with the </X> coming after the <X>. In HTML, such a restriction is not imposed, so we sometimes find <p> tags which don't have a corresponding </p> tag. Text between the element start and end tags is called the CDATA of the element. In, <Soup> Today's special is the lobster soup </Soup> The Soup element is described by the text between the tags, which is the CDATA. Sometimes, it's not necessary to have CDATA, like in <BR> tags in HTML. So one has to write <BR></BR> everytime, which is quite redundant. XML provides an abbreviated way to handle this: for elements with no CDATA, it's valid to start and end an element with a slash before the ending >, like <BR/> Attributes Attributes are properties of elements, which come inside the element tag, just after the element name. In, <Product Name="Borland Delphi" Version="5"/> Name and Version are attributes of the element Product. The attributes have values, as seen above. This kind of granularity can also be achieved by using sub-elements, since the above XML could be written as: <Product> <Name>Borland Delphi</Name> <Version>5</Version> </Product> There's no real rule of thumb about which to use when. A general guideline would be to use attributes when the text is small and manageable, and sub-elements for larger texts or when the text may need to contain other sub-elements. Comments Comments in XML are just like HTML comments, starting with "<!--" and ending with "-->". Anything in the middle is ignored (even if there are any element tags in there, they will not be processed) Document Type Definition (DTD) XML, as we've seen, is just structured text. Since it is so, it needs to be parsed, and many parsers have been written for this purpose. Some ground rules need to be followed, such as: a) always end an element tag. You can use only <TAG/>, <TAG></TAG> or <TAG>content</TAG>. b) Maintain that there is only one document element surrounding the XML. c) XML is case sensitive. So <Element> is not the same as <ELEMENT> d) Use quotes when giving attribute values. Although <img src=myimg.gif> is valid in HTML, it is not in XML. It should be: <img src="myimg.gif"/> These ground rules form the basis for "well formed XML". But If you need to even define the structure of the XML, like which tag should follow which, what tags are allowed etc. you will need to describe that in a separate document, called a Document Type Definition (DTD). Document Type Definition (DTD). A DTD is also a text document. A DTD for our example above would be: <!ELEMENT BOOK (Author | Abstract)> <!ATTLIST BOOK Name CDATA> <!ELEMENT Author (#PCDATA)> <!ELEMENT Abstract (#PCDATA) #REQUIRED> The first <!ELEMENT part defines an XML Element, in this case the <BOOK> XML element. The latter part of the same line (Author|Abstract) says that a BOOK tag contains either Author or Abstract tags. The <!ATTLIST part defines the Name attribute of the BOOK tag.The rest of the tags define the other elements in the XML. An XML document can specify the DTD that defines it - like: <?xml version="1.0"?> <!DOCTYPE BOOK SYSTEM "book-structure.dtd"> <BOOK> ... </BOOK> Note:(book-structure.dtd is a text file containing the dtd). An XML that is well formed AND conforms to a DTD, it is said to be "valid XML". The XML that is used in this paper is not valid XML - DTDs have not been used anywhere, for clarity and space. XML Schemas An aside: DTDs were the first standard for defining XML. But a different parser has to be written for parsing a DTD, since a DTD does not conform to XML rules. XML schemas is a newer (although not yet a standard) approach, which uses valid XML to define another XML document. The format is different from a DTD, and supports some more data types. I won't delve into XML Schemas in this paper. XML and databases This demonstrates a structured set of data in plain text – so you can extract out the fields you want and present it in your application. Just like a table in a database, without the baggage of logging in, security, connection management and the like. And it's all plain text, so it's readable. XML is really extensible, unlike a table in a database - you can extend an XML to show master-detail relationships quite easily: <Customer name="International Operations"> <Order No="1110"> <Item Code="500-1100" Quantity="5"> </Item> <Item Code="200-1000" Quantity="10"> </Item> </Order> <Order No="400"> <Item Code="510-1100" Quantity="3"> </Item> <Item Code="200-1000" Quantity="25"> </Item> </Order> </Customer> Figure 2. This is a list of orders and the corresponding items. A file like this can be transmitted to a client over the Internet by email or using TCP/IP sockets. This can be used just like a database by the client, since the fields are neatly marked by tags. All of this, without the client having to own a license of the database server. Don't get me wrong: XML is not meant to replace database servers. It's going to complement them. You will still need optimized data storage, server side indexes, stored procedures and security - something XML will not address in the form it is now. (I wonder if this statement will remain sensible by the time this article is presented) XML data can easily be passed over a network to a client who could choose to show it in a set of TDBGrids in a fat-client application. The same information can be formatted and shown in a Web Browser. (Internet Express does this job really well) You can even send XML to a customer-his payment system can now integrate with yours, all he needs to do is parse the XML, get a list of orders/items, confirm receipt, and generate payment information - all the customer needs to do is to sign the check. (If it were so easy...) And finally, the customer sends the payment information to you - again in XML - so you can directly import the details into your application for your information and later reconcile it with your bank statements. Where does XML fit in? Your client-server application consists of data being exchanged between your client and server. Data is exchanged in a format that you would not be aware of, which limits your application's scalability. If you used XML as the data exchange format, you could easily scale your client to a remote client – by simply implementing a data transport mechanism. You could use TCP/IP and have users accessing the server over the Internet, for instance. In many enterprises, most user access consists solely of data reads. Instead of having all your users run client applications capable of reading and writing data, you can "web-enable"your server application by developing a web server application – an MTS component or an Enterprise Java Bean – that would connect to your server using TCP/IP or DCOM, get data in XML format and format it to emit HTML. This way all users can just log on to the Intranet site for reads. Later, you can even have other applications use this method to read data from the same server application and you will only need to understand the XML format returned. This makes it quite simple to build distributed applications. Does this require that you give up developing applications using the BDE/ADO ? Or clients using data-aware controls? No. You will still use BDE/ADO on your desktop and Intranet Multi-tier applications. I will show you how you can allow a client to access data on your server using XML. Which means that your server will need to have some components which can give information in XML and perhaps read information from a client in XML too. How do you do this in Delphi? Let's start looking at some code now. First, XML is plain text, so to make any real sense out of it, XML needs to be parsed. Delphi 5 comes with a basic XML parser (in InternetExpress, but you cannot use it for other purposes), and there are many free and commercial XML parsers available. I'll use the redistributable XML parser that comes with Microsoft Internet Explorer 5. It's not the best but it's definitely easy to start with since it supports COM interfaces. I've started off importing the Parser's interface from MSXML.DLL in the Windows System directory. A small introduction to the functions involved: 1. CreateOleObject('Microsoft.XMLDOM') - Creates the MSXML Object. We could even use CoCreateInstance, or the CoXMLDocument.Create in the imported Pascal file. 2. .Load(FileName), .Save(FileName) - Loads from/saves to the file named filename. 3. .LoadXML(XMLString) - Loads a string in XMLString and parses it. 4. .XML - The XML as a string 5. .createNode( type, nodename, namespace) - creates a node like <namespace:nodename> </namespace:nodename>. if the type is NODE_ELEMENT. You can specify NODE_ATTRIBUTE to add an attribute to a node like <item> can become <item code="111-0000">. "code" is an attribute. (Similar items: createElement, createAttribute ) Figure 3. I'll start with a demonstration here and extend it. First, I'll create an application that will show some stock quotes in a List View. (I've used some of the samples that Microsoft ships and converted them to Delphi. This is one of them, but I've extended it) The stock data is in an XML file. Stock Demo A small stock application that gets quotes from a server application - we'll use XML to communicate between the applications. Step 1. Let's now start with creating a small application that displays stock quotes to a user. The stock quotes are presumed to be in XML, such as: <quotes> <quote symbol="MSFT" price="90" change="61" open="29" volume="24370" lastupdate="12/28/99 18:57:07"/> // more quotes here... </quotes> Figure 4. We'll drop a listview on the screen that shows each quote in a different row.A bitmap indicates whether the stock is up or down. The form looks like this: Listing 1 shows the code that creates the XML Parser, opens an XML file, parses it using the XML parser and displays it on screen. The XML file that was loaded was : <quotes> <quote symbol="MSFT" price="90" change="61" open="29" volume="24370" lastupdate="12/28/99 18:57:07"/> <quote symbol="INFY" price="10" change="-10" open="6" volume="31101" lastupdate="12/28/99 18:57:07"/> <quote symbol="INPR" price="93" change="45" open="48" volume="19629" lastupdate="12/28/99 18:57:07"/> </quotes> Figure 6. The code that parses the document is : lstNodes := FDocument.selectNodes('//quote'); // all elements of type <quote> node := lstNodes.nextNode as IXMLDOMElement; // go through all the nodes while node<>nil do begin vVal := node.getAttribute('price'); // price is an attribute of <quote> liStock.SubItems.Add(vVal); ... end; Figure 7. We've now got our first XML based client screen up and running Step 2. Let's move to the server. Obviously, we're going to have to lookup these stock quotes someplace. I'll make a dummy quote server now, which just looks up stock tickers from a database and generates random values for the stock prices. I've created a table called Stocks with the structure in Figure 8. Column Type SYMBOL VARCHAR(10) PRICE FLOAT CHANGE FLOAT VOLUME FLOAT OPEN FLOAT Figure 8. This stock quote server will have a port 3580 open for requests. A server socket will listen to requests, which have to be in XML of the form: <quotes> <quote symbol="MSFT"/> <quote symbol="INPR"/> </quotes> Figure 9. The output will be XML (just as shown in Figure 6.), sent back to port 3580. This is pretty plain and simple. The server form is shown in Figure 1, and the code in Listing 2. Figure 10. The ADO Connection connects to an Access .MDB file that holds all the stock symbols qrySymbol is an ADO Query that does "SELECT * FROM STOCKS WHERE SYMBOL=:SYMBOL" The server socket listens for connections on port 3580. The code is given in Listing 2. Step 3. Let's now use a TClientSocket to send requests to this port at the server and receive the data. var szRec : array[0..99] of char; st : TWinSocketStream; begin szXML := FDocument.xml + #13#10 + #13#10; // like an End of input marker with ClientSocket1 do begin Open; Socket.SendText(szXML); st := TWinSocketStream.Create(Socket, 2000); if st.WaitForData(1000) then szXML := Socket.ReceiveText; st.Free; if szXML <> '' then FDocument.loadXML(szXML); ShowXML; CLose; end; end; Figure 11. A screen shot of the update process is shown, with the refreshed data in view (Figure 12.) 4. Showing the data on a Web browser : Here's code for an ISAPI DLL that will get requests from a web browser, and show the results to the user in a browser. var szRec : array[0..99] of char; st : TWinSocketStream; szXML : String; begin szXML := ' ' + #13#10 + #13#10; with ClientSocket1 do begin Open; Socket.SendText(szXML); st := TWinSocketStream.Create(Socket, 2000); if st.WaitForData(1000) then szXML := Socket.ReceiveText; st.Free; CLose; end; Response.StatusCode := 200; Response.ContentType := 'text/xml'; Response.ContentLength := Length( szXML ); Response.Content := szXML; Handled := TRUE; end; Figure 13. A screen shot will give you an idea of what kind of output you can expect: Figure 14. Note: This is Internet Explorer 5, which can show XML. This is a very simple example of what we can do with XML. If we have more stock servers that return quotes in the XML format in Figure 2, the client not depend on a particular server at all - just point the application to a different server if one is down for maintenance. What if we wanted multiple users to edit data? Just place some code at the server that will parse the input XML and update the database - You might need to add validations, security etc. but the model is in place. Here's the architecture of this application in a nutshell. How do you convert currently existing applications? You can't modify your existing applications to use this strategy, it'll take way too much time. Consider extending your current application to use XML. So you'll start off with exposing parts of your data using XML. A large part of enterprise applications consists of only reads - there are much fewer instances of writing data to a database than to read from it. For plain reads, just run a query on the database and make XML out of it. You can do that quite easily by : function MakeTag( TagName, Value : String ) : string; begin Result := '<' + TagName + '>' + Value + '</' + TagName + '>'; end; function MakeXML( Dataset : TDataset ) : string; begin Result := ''; if (not Dataset.Active) or (Dataset.IsEmpty) then Exit; Result := Result + '<' + Dataset.Name + '>'; Dataset.First; while not Dataset.EOF do begin Result := Result + '<RECORD>'; for i := 0 to Dataset.Fields.Count-1 do Result := Result + MakeTag(Dataset.Fields[i].Name,Fields[i].Text); Result := Result + '</RECORD>'; Dataset.Next; end; Result := Result + '</' + Dataset.Name + '>'; end; Figure 16. This doesn't cover BLOB fields or Memo fields etc. But for float fields (use a DisplayFormat), String/character fields and Integer fields this works quite well. There is a possibility of an XML Dataset that could emerge soon. A TDataset descendant that will give you a way to show parts of XML in datafields. Then, you can start using XML to display your data easily. Of course, since XML is plain text, we still have the issue of speed with large datasets - but since you control the interaction between the server and the client, you can send data in chunks or even split it into more manageable parts. Interoperability with other systems With an XML based implementation, you have one more advantage. Your applications can communicate with other packages on potentially different platforms. If two airline companies were to collaborate – so their customers could have a single point of purchase. Lets say they used different software for invoicing. To avoid hauling one airline company on to a common invoicing system, you could design an application that would convert their data to a common XML format – and use this format in the common ticket order system. There are innumerable advantages to this approach – a saving in time and money is only one of them. DEMO I'll demonstrate two applications that use different data formats for internal storage. I've added XML support to both the applications so they can communicate with each other – they also have support for more such applications to integrate with them. Note: All the source code is available with this document, suitably zipped. First, take a look at the screen shot of an Internet Site that shows the list of Customers of Company ABC, which is a reseller of Product T, made by Company XYZ. This is XML, formatted for browser viewing using XSL - Extensible Stylesheet Language . (I will not cover XSL in this paper) Figure 17. Company XYZ direct sells Product T too, but it needs to see all the customers of the product. I've built an application that reads data from XYZ's internal database and shows it in a Grid. In addition, it reads the XML from the Company ABC's web site, and shows the customers in the same grid, shown in Red. (I've sorted the list by Company Name). Figure 18. Technologies and Products that use XML InternetExpress InternetExpress is an XML solution by Inprise. With the enterprise version of Delphi 5, (and C++ Builder 5, I guess) you can create web clients for your multi-tiered applications. InternetExpress comes with special Javascript libraries which can be used on any browser that supports Javascript. These libraries contain an XML parser too, but you may not use these for non-MIDAS applications. You can create a regular Web application and drop a TXMLBroker on it. This acts like a ClientDataset, except it requests packets in XML instead. Then the XML is sent to the client, along with the Javascript XML Libraries. The XML is like this: <DATAPACKET Version="2.0"> <METADATA> <FIELDS> <FIELD attrname="OrderNo" fieldtype="r8"/> <FIELD attrname="CustNo" fieldtype="r8"/> <FIELD attrname="SaleDate" fieldtype="dateTime"/> ....more fields ... <FIELD attrname="OrderItems" fieldtype="nested"> <FIELDS> <FIELD attrname="OrderNo"fieldtype="r8"/> <FIELD attrname="ItemNo" fieldtype="r8"/> ...more fields... </FIELDS> <PARAMS DEFAULT_ORDER="16385" PRIMARY_KEY="1 2" LCID="1033"/> </FIELD> </FIELDS> <PARAMS MD_FIELDLINKS="22 1 1" LCID="1033"/> </METADATA> <ROWDATA> <ROW OrderNo="1014" CustNo="1645" SaleDate="19880525" ShipDate="19880526" EmpNo="144" ShipVIA="Emery" Terms="Net 30" PaymentMethod="Credit" ItemsTotal="134.85" TaxRate="0" Freight="0" AmountPaid="134.85"> <OrderItems> <ROWOrderItems OrderNo="1014" ItemNo="1" PartNo="7612" Qty="4" Discount="0"/> </OrderItems> </ROW> ...more rows... </ROWDATA> </DATAPACKET> Figure 19. The XML libraries at the client allow you to see the data, even modify (insert, update, delete) data without a round trip to the server. In effect, the XML libraries at the client cache these updates. You can then apply all the updates at one shot. You can change the Web page layout - the HTML. But you have no control over the XML format. You cannot add custom tags, data or attributes directly. Is it possible to connect to other systems or have other systems connect to your application? Not unless they use MIDAS too. The XMLBroker component works with the IAppServer interface, an integral interface of MIDAS. Yes, you could have a customer read XML directly from your web server application using the format given above. (I'm still testing this theory) But XML isn't at its productive best, in my opinion. If you are developing on MIDAS, try InternetExpress: it will save a lot of work if you want to have web clients. BizTalk In this context, Microsoft's Biztalk initiative (www.biztalk.org) is worth a mention. In Biztalk, you can define the XML definitions of your business objects. A worldwide definition library then allows to you to interact with other such publishers - now, with BizTalk server, you can have a PeopleSoft implementation connect to an SAP implementation etc. It's something worth looking at. SOAP - Simple Object Access Protocol There's a new kind of server coming up-The SOAP server. This have nothing to do with what comes on TV - it's much easier to understand, at the very least. This concept came about because one had to distribute client access across machine and location boundaries. When a firewall steps in, it's usually more than hell to configure a DCOM based multi-tier application. One way out is to use the HTTP port - port 80. This is generally let through by firewalls. In fact, MIDAS uses this for distributed applications using a special ISAPI Dll that they have written. - httpsrvr.dll. The DLL transfers the calls to the registered objects on the server using DCOM. A big problem - the data format is proprietary. Which says, pretty bluntly, that you can only use MIDAS clients to access the server. What about the VB and VC++ developers out there that want to use the service? We'll have to get back to you on that. SOAP is a protocol that defines a standard for passing data across to the server. The headers that come to the server are HTTP headers - but the body is XML. The XML is in a format defined by SOAP - but the only real limitation is that you need to enclose the XML in a certain set of tags and define your namespace. Currently, SOAP can be implemented using ISAPI Dlls on Windows NT, or through separate server programs. If you were to communicate with a SOAP program, you would need to format your XML and send it to the server - this is something InternetExpress currently doesn't allow you to extend its functionality to. With the architecture I've outlined in this presentation, you can format your XML, send it to a SOAP server, parse and integrate the results. By the time this article is presented, there may be commercial SOAP servers in place and there will be components to access them. Conclusion Adopt XML in your applications and you'll see the benefits in scalability. The technology has only recently become popular, but you'll find a lot more progress in this field – parsers, applications and servers that support XML. XML-Enable your applications as soon as you can – you need to start planning today. References 1. Microsoft Windows DNA XML Resource Kit CD 2. Microsoft XML site (msdn.microsoft.com/xml) 3. Newsgroups: - microsoft.public.xml @ msnews.microsoft.com 4. Mastering XML (Sybex) - Ann Navarro, Chuck White and Linda Burman, ISBN 81-7656-191-6 5. The XML Handbook (Addison Wesley) - Charles F. Goldfarb, Paul Prescod, ISBN 981-4035-87-4 Appendices procedure FormCreate(Sender: TObject); begin CoInitialize(nil ); // FDocument is a member variable of type IXMLDOMDocument OleCheck(CoCreateInstance(Class_DOMDocument, nil, CLSCTX_ALL,IXMLDOMDocument, FDocument end; Here's the code to load an XML Document and display it in the List View: procedure TForm1.OpenFile(FileName : string); begin if FDocument.Load(FileName) then ShowXML else ShowMessage('Could not load file : ' + FileName); end; procedure TForm1.ShowXML; var lstNodes : IXMLDOMNodeList; node : IXMLDOMElement; liStock : TListItem; vVal : OleVariant; szVal : string; begin //reset the list view StockList.Items.BeginUpdate; try StockList.Items.Clear; lstNodes := nil; // select the nodes <quote> to </quote> lstNodes := FDocument.selectNodes('//quote'); if (lstNodes <> nil) then while (true) do begin // traverse the nodes node := lstNodes.nextNode as IXMLDOMElement; if node = nil then // we're done break; // we have a ticker, add a row to the list view liStock := StockList.Items.Add; // now add each attribute to the list view liStock.Caption := node.getAttribute('symbol'); vVal := node.getAttribute('change'); szVal :=''; if VarIsEmpty(vVal) or VarIsNull(vVal) then liStock.ImageIndex := 2 // no value else begin szVal := vVal; if szVal[1] = '-' then // going down, show down arrow bitmap liStock.ImageIndex := 1 else liStock.ImageIndex := 0; end; liStock.SubItems.Add(szVal); vVal := node.getAttribute('price'); if not VarIsNull(vVal) then liStock.SubItems.Add(vVal) else liStock.SubItems.Add(''); vVal := node.getAttribute('open'); if not VarIsNull(vVal) then liStock.SubItems.Add(vVal) else liStock.SubItems.Add(''); vVal := node.getAttribute('volume'); if not VarIsNull(vVal) then liStock.SubItems.Add(vVal) else liStock.SubItems.Add(''); vVal := node.getAttribute('lastupdate'); if not VarIsNull(vVal) then liStock.SubItems.Add(vVal) else liStock.SubItems.Add(''); end; finally StockList.Items.EndUpdate; end; end; Listing 2 - Stock Server const QUOTELINE = '<quote symbol="%s" price="%d" change="%d" open="%d" volume="%d" lastupdate="%s"/>'; function TForm1.BuildResultXML(szInputXML: string): string; var doc : IXMLDOMDocument; lstNodes : IXMLDOMNodeList; node : IXMLDOMElement; vVal : Variant; begin Result := '<quotes>'; doc := CoDomDocument.Create; try doc.loadXML( szInputXML ); lstNodes := doc.selectNodes('//quote'); if (lstNodes <> nil ) then begin while (true) do begin node := lstNodes.nextNode as IXMLDOMElement; if node = nil then break; vVal := node.getAttribute('symbol'); if not VarIsNull(vVal) then FResult := FResult + GetSymbolInfo( vVal ); end; FResult := Fresult + '</quotes>'; end; finally doc._Release; end; end; function TForm1.GetSymbolInfo(szSymbol: string): string; begin Result := ''; qrySymbol.Parameters.ParamByName('SYMBOL').Value := szSymbol; qrySymbol.Open; if not qrySymbol.IsEmpty then Result := Format(QUOTELINE, [szSymbol, Round(FieldbyName('PRICE').AsFloat), Round(FieldByName('Change').AsFloat), Round(FieldbyName('Open').AsFloat), FieldbyName('Volume').AsInteger, FormatDateTime('mm/dd/yyyy hh:mm:ss', FieldByname('LastUpdate').AsDateTime)]); qrySymbol.Close; end; The server socket listens for a request, calls BuildXML and sends the returned string back to the client. Listing 3 - Data inserted into a client dataset WebLink.Get('localhost/istest/webcust.dll'); szXML := WebLink.body; OleCheck(CoCreateInstance(Class_DOMDocument, nil , CLSCTX_ALL,IXMLDOMDocument, Document)); Document.loadXML(szXML); lstNodes := Document.selectNodes('//CUSTOMER'); if (lstNodes <> nil ) then while (true) do begin node := lstNodes.nextNode as IXMLDOMElement; if node = nil then break; cdsCustomer.Insert; cdsCustomer.FieldByName('Src').AsInteger := 1; szVal := node.getAttribute('CUSTNO'); cdsCustomer.FieldByName('CustNo').AsString := szVal; compNode := node.selectSingleNode('COMPANY'); szVal := compNode.Text; cdsCustomer.FieldByName('Company').AsString := szVal; compNode := node.selectSingleNode('FNAME'); szVal := compNode.text; compNode := node.selectSingleNode('LNAME'); szVal := szVal + ' ' + compNode.Text; cdsCustomer.FieldByName('Contact').AsString := szVal; compNode := node.selectSingleNode('STATE'); szVal := compNode.Text; cdsCustomer.FieldByName('State').AsString := szVal; compNode := node.selectSingleNode('COUNTRY'); szVal := compNode.Text; cdsCustomer.FieldByName('Country').AsString := szVal; cdsCustomer.Post; end; Document._Release;