HMG is a great harbour distribution that runs out of the box without fuzz. It enables you to write windows applications in an easy to learn language.

Harbour is based on xBase language and will compile your old clipper-code into windows exe-files.

This howto expects you to already know about harbour, hmg. You need to have hmg installed an running, to follow this howto.

If you don't know harbour and hmg, yet, download the latest version from http://www.hmgforum.com/site/ and start playing with it. It includes good documentation and the friendly people at hmgforum.com will help you if you ask nicely. :-)

PostgreSQL is a free relational Database Management System (RDBMS). It is similar to Oracle and MySQL. A lot cheaper than Oracle and more free (as in freedom) than MySQL.

You don't need to know much about SQL to start with this howto.

I will show you how to get your own postgresql-server running on a regular windows pc, create some tables and build a small hmg-program

to manipulate data.

This is not a documentation for hmg nor postgresql. So please understand that it is very short.

Once you got started, you might want to take a look at the postgresql-documentation.

Many harbour applications store their data in dbf-files and keep index-files. This works well, is fast, well understood, simple and very stable.

hmg-Applications don't need much installation. They run almost all windows-systems as long as they have access to their database-files.

Why would you want to make this more complicated by adding a gigantic sql-server to this formular?

1. Speed: you can have the postgresql-server running on a big and fast server. The Client does not need much power.

2. Safety: Die RDBMS Server protects your data from damages. Since your client does not write to the database-files itself, but merely asks the server to change, delete or insert data, less bad things can happen.

3. Refertial Integrity:If you tell postgresql about the relations of tables, by declaring foreign keys, it will make sure, that your database makes sense. Let's say, you have one table with books and another one with authors. A book has an author. Postgresql will make sure, that the authorId in books will always point to a valid author.

4. Multi-User: If you have a regular xBase application for many users, you have to think about shared access to the dbf- and ntx-files. An SQL-Server takes care of that by itself.

Don't get me wrong: Of course, all this is doable in hmg, but needs more thinking-about.

Enough praise for postgresql and hmg. Let's get started.

Download postgresql from http://www.enterprisedb.com/products/pgdownload.do#windows. Simply klick here to get the latest version: http://www.enterprisedb.com/getfile.jsp?fileid=810

The Install-Wizard will walk you through all the settings step by step. But you don't need to configure anything. You only need to setup a password.

So this is all a piece of cake. To show you, I took screenshots:

The wizard will ask you for the directory of the program and your data. I simply leave it to the default.

The Installer will create a user named postgres, that runs the service. All you need to do ist think of a password.

The postgresql-server will listen on port 5432. This is default for postgresql. If you don't have a good reason, don't change it.

You can choose a locale for a different country. But don't worry. Just leave it to the default locale.

This will take a few minutes.

Your installation is finished now. You do not need to start the Stack Builder. It could install an ODBC-Driver, PHP-frontend, etc. But it is not necessary for now. You can launch it later, if you want to. So uncheck the box.

You now have a new startmenu item. It is pretty straightforward. "pgAdmin III" is a graphical client. Launch it, to see, if you can connect to your first database.

Double-click on PostgreSQL 8.4 (localhost:5432) to connect to your new server.

Do you remember the password you entered during installation? You need it now.

Postgresql has many features. They are arranged in a tree. A newbee sometimes does not see the forest of all the trees. ;-)

Open databases. You have one database names "postgres". Open schemas. You have one schema named "public". Doubleclick on Tables. You have no tables, yet.

To create one, simply right-click on Tables and choose "New Table...".

A wizard opens and you can enter the name of the first table. In my example, I use "books".

Fields are called Columns. So click on the "Columns" tab and Add one.

The first column ist an id. The Data type serial is basically an integer field that counts. So your first record will have the bookid 1 and so on. By the way: Records are called rows or tuples.

The next column is the name of the book. You can choose data type text, if you want not limitation of the length. That would be like a memo-field. If you choose "character varying" you can define a maximum length. The third way is to choose "character". Then the string will be padded with spaces. This is exactly like a dbf-field.

I personally like character varying. If you want to know more about the data-types, read here.

Be careful not to choose "character varying[]". The brackets mean, that it is an array!

You can also set the column to be not null. Trying to add a row without a name will then result in an error and the record will not be saved. Please note, that null is something different than a string of spaces or even a string of length zero.

The price of the book is a numeric field. Notice, that the lenght does not include the decimal point itself. So in this example you have 8 figures left of the decimal point and 2 after the point.

This is a little different from dbf-format.

You can set rules for the SQL-Server, so the data gets meaning.

First, we have to tell it that the bookId is the primary key. That means, it identifies a record (or tuple or row or whatever you want to call it). Without it, it would be hard to change existing rows, because you don't have RecNos.

Postgresql will automatically build an index on that column.

You can give a name to the contraint. I choose "pk_books" ("pk" standing for "primary key")

After that, click on Colums to choose the columns of the primary key.

Choose bookid from the combobox and click Add.

Then click OK and you have built your primary key.

You might have guessed, that all those gui-dialogs are just an interface. When you click on the last tab "SQL", you can see the code that will be executed to create your table.

It should look like this:

CREATE TABLE books
(
  bookid serial NOT NULL,
  bookname character varying(50) NOT NULL,
  price numeric(10,2),
  CONSTRAINT pk_books PRIMARY KEY (bookid)
)
WITH (
  OIDS=FALSE
);

Click OK, to create the table.

Let's create another table. This time, we just run the code, to create it.

Click on the SQL-Button and insert the following code:

CREATE TABLE authors
(
   authorid serial NOT NULL PRIMARY KEY,
   firstname text,
   middlename text,
   lastname text
)
WITH (
  OIDS = FALSE
)
;

To execute your query, press F5. You will get a notice that the table was created and that an index for the foreign key authorid was created.

You can then close the window without saving.

We can now add a field that references the authors to its books. That is called a foreign key. Postgresql will watch over that relation.

Add a Column named authorid of type integer. You know the dialogs from earlier. The SQL-Code is:

ALTER TABLE books ADD COLUMN authorid integer;
ALTER TABLE books ALTER COLUMN authorid SET STORAGE PLAIN;
ALTER TABLE books ALTER COLUMN authorid SET NOT NULL;

Postgresql does not yet know, that this is a relation to the table authors.

We need to add a "New Foreign Key". I name it fk_authorid

In the Columns-Tab, chose the local column authorid and the foreign column authorid.

Don't forget to click "Add"

As I said earlier, postgresql will watch over the integrity of your data. By default, it will not delete an authors if there are books by him. In SQL this means "ON DELETE NO ACTION".

If you want postgresql to also delete all books, of the author, you can set it to "ON DELETE CASCADE".

Be careful. This is very powerful! ;-)

If you did everything like I did, your SQL-Code should look like this:

ALTER TABLE books ADD CONSTRAINT fk_authorid FOREIGN KEY (authorid) REFERENCES authors (authorid)
   ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE INDEX fki_authorid ON books(authorid);

Klick OK to execute it.

Congratulations! You have now successfully set up a postgreql-server on your machine, created two tables with relations.

Now Lets start building our hmg-application to access the database.

As I expect you to know hmg at least as well as I do, I will just give you a brief example how to read and write data.

It is not an application. Use your imagination about the possibilities!

#include <hmg.ch>
#include "postgres.ch"

#command USE <(db)> [VIA <rdd>] [ALIAS <a>] [<nw: NEW>] ;
            [<ex: EXCLUSIVE>] [<sh: SHARED>] [<ro: READONLY>] ;
            [CODEPAGE <cp>] [CONNECTION <nConn>] [INDEX <(index1)> [, <(indexN)>]] => ;
         dbUseArea( <.nw.>, <rdd>, <(db)>, <(a)>, ;
                    if(<.sh.> .or. <.ex.>, !<.ex.>, NIL), <.ro.>,  [<cp>], [<nConn>] ) ;
         [; dbSetIndex( <(index1)> )] ;
         [; dbSetIndex( <(indexN)> )]

Function Main
    local pg_host     := "localhost"
    local pg_database := "postgres"
    local pg_user     := "postgres"
    local pg_password := "postgres"
    local nConn

    local PQconn
    local cSQL

    nConn := dbpgconnection( pg_host+";"+pg_database+";"+pg_user+";"+pg_password)

    use "select * from authors order by authorid" alias authors via "pgrdd" connection nConn

          dbappend()
    replace authors->firstname with 'Michael'
    replace authors->lastname  with 'Crichton'
    dbcommit()

    msgbox ("Last inserted author: "+authors->firstname+" "+authors->lastname+" with authorid :" + str(authors->authorid) )

    * of course, you can also send SQL-STATEMENTs directly by using the pglib
    PQconn := PQconnect(pg_database, pg_host, pg_user, pg_password, 5432)
    cSQL := 'INSERT INTO books (bookname, price, authorid) '
   cSQL += " VALUES ('Gold', 12.99, "+ str(authors->authorid) +");"
    res := PQexec(PQconn, cSQL)
    * check for error:
       if (PQresultStatus(res) == PGRES_COMMAND_OK .or. PQresultStatus(res) == PGRES_TUPLES_OK)
        * no error
    else
        msgbox("Error: "+str(PQresultStatus(res)) + PQerrorMessage(PQconn) )
    endif

    use

Return

Make sure, your password is set correctly.

If you followed the instructions, you now have a running PostgreSQL-Server, that runs as a windows-service and listens on port 5432.

You can now explore a lot of possibilities and hopefully be able to get the best of both worlds. HMG and SQL is a great team!

To keep you thinking: Do you know how to tell postgresql to make sure, that no two authors should have the same first-, middel- and last-name?

Of course I hope that you caught the fever and keep using your new installation.

But if you don't want postgresql to start every time you start your computer, you can take a look at your Windows-services. There will be an entry named "postgresql-8.4", which is set to automatically. Simply switch it to manually.

Wow! you read all the way to here! Great! Was it boring? I hope not.

I am surprised, that this little howto turned out to be so big. I really hope, it was understandable and useful.

If you had trouble following my instructions, please let me know in hmgforum.com

If you have any questions I will be more than happy to help!