Postgresql as a backend for HMG

About HMG

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. :-)

About PostgreSQL

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.

Why this combination?

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.

Download Postgresql

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

 

 

Start the Installation

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:

PostgreSQL Setup-Wizard
PostgreSQL Setup-Wizard

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

Data-Directory
Data-Directory
Password for the user postgres
Password for the user postgres

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

Port-Number for postgresql
Port-Number for postgresql

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.

Locale
Locale

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

Installer runs
Installer runs

This will take a few minutes.

Setup is finished
Setup is finished

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.

Launch PgAdmin III

Start-Menu entries for postgresql
Start-Menu entries for postgresql

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.

PgAdmin III Enter password
PgAdmin III Enter password

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.

Most important: Tables
Most important: Tables

Creating your first table

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...".

New Table...
New Table...
First table: books
First table: books

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.

Third column: price
Third column: price

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.

Constraints

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.

New Primary Key
New Primary Key

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.

Colmun of the primary key
Colmun of the primary key

Choose bookid from the combobox and click Add.

 

Then click OK and you have built your primary key.

CREATE TABLE code

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.

Table authors

SQL-Button
SQL-Button

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
)
;

Run SQL-Statements
Run SQL-Statements

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.

Refresh the Tables-list

Refresh the Tables-List in PgAdmin
Refresh the Tables-List in PgAdmin

Foreign Key: authorid in books

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 to a table
Add a Column to a table

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;

Add a foreign key to a table
Add a foreign key to a table

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"

Action for foreign Key
Action for foreign Key

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.

PostgreSQL Setup complete

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.

HMG Access

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.

Conclusion

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?

Uninstall / deactivate

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.

Feedback

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!

Write a comment

Comments: 6
  • #1

    S. Rathinagiri (Monday, 22 February 2010 19:05)

    Compact and very much useful article for beginners.

    Thanks Raumi.

  • #2

    Sudip Bhattacharyya (Tuesday, 23 February 2010 06:28)

    Raumi,

    Thanks a lot. It's one of the best tutorials I have seen to teach how to work with one of the toughest RDBMSs - to the Dummies :)

  • #3

    Nelson Stuardo (Wednesday, 24 February 2010 02:33)

    Excellent ,great tutorial!!!

  • #4

    bartsoft (bartsoft@interia.pl) (Thursday, 08 December 2011 12:06)

    Hello
    I try to use postgres sql via pgrdd or DBPGCONNECTION but I always get errors while compile. How version of hmg You use ?
    I read your's posts on hmgfourm and have same problems with hmg version newer than 3.0.35 (3.0.36 test). Please help me ?

  • #5

    José Eduardo (Wednesday, 15 August 2012 02:24)

    Hi, good night.

    Greetings from Brazil, I am beginning my project with HMG and Postgres.

    I saw your message on Raumi's Tutorial and I am having compilation errors too.

    Did you solve it? What version of the HMG to use?

    Thanks

  • #6

    raumi75 (Thursday, 16 August 2012 09:40)

    I am using Postgresql 9.1. The upgrade went well. I use hmg 3.0.35 with no problems. Newer versions cause compile errors. I hope they will be fixed in the future. I was not able to debug it. In the meantime, use 3.0.35