postgres

From Lazarus wiki
Revision as of 08:46, 14 September 2012 by BigChimp (talk | contribs) (Added example; still needs editing)
Jump to navigationJump to search

Overview

You can use Free Pascal/Lazarus to access a PostgreSQL database server. If you are looking for information on the mysql package in FPC, please see postgres#PostgreSQL_package:_the_low_level_units below.

Advantages of PostgreSQL:

  • It is very widely used and available
  • Very stable and has a complete feature set

Disadvantage of PostgreSQL:

  • Some hosters may not offer PostgreSQL)
  • No embedded version

SQLDB

Lazarus SQLDB has a PostgreSQL connection component; FPC has a corresponding connection class.

Example

BigChimp: todo: this was taken from the DB FAQ; it needs to be checked and modified. In general, we'd better move the entire section to a PostgreSQL specific page as it is way to big the component doesn't support the following connection options:

  • Port
  • ClientEncoding

Maybe there are other unsupported connection parameters.

The TPQConnection component does not directly support a Port property, but one can pass the port into the component via the Params parameter:

PQConnection.Params.Add('port=' + VariableContainingPort);

Also other connection parameters can be specified using Params property:

PQConnection.Params.Add('application_name=''yourappname''')

BigChimp: end extract from DB FAQ

This is a very short tutorial to get Lazarus 0.9.12 or later to connect to a PostgreSQL database, local or remote, using TPQConnection. This component is added to Lazarus when the package lazarus/components/sqldb/sqldblaz.lpk is installed.

After correct install, follow these steps:

  • Place a PQConnection from the SQLdb tab
  • Place a SQLQuery from the SQLdb tab
  • Place a SQLTransaction from the SQLdb tab
  • Place a DataSource from the DataAccess tab
  • Place a DBGrid from the DataControls tab
  • In the PQConnection fill in:
    • transaction property with the respective SQLTransaction object
    • Database name
    • HostName
    • UserName + password
  • Check that the SQLTransaction was automatically changed to point to the PQConnection
  • In the SQLQuery fill in:
    • transaction property with the respective object
    • database property with respective object
    • SQL (something like 'select * from anytable')
  • In the DataSource object fill in the DataSet property with the SQLQuery object
  • In the DBGrid fill in the datasource as the DataSource Object

Turn everything to connected and active and the DBGrid should be filled in design time. TDBText and TDBEdit seem to work but (for me) they only _show_ _data_.

To change contents in the database, I called the DB Engine direct with the following code:

try
  sql:= 'UPDATE table SET setting=1';
  PQDataBase.Connected:=True;
  PQDataBase.ExecuteDirect('Begin Work;');
  PQDataBase.ExecuteDirect(sql);
  PQDataBase.ExecuteDirect('Commit Work;');
  PQDataBase.Connected:=False;
except
  on E : EDatabaseError do
    MemoLog.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
  on E : Exception do
    MemoLog.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
end;
  • Notes:
    • Tested on windows, Lazarus 0.9.12 + PgSQL 8.3.1
    • Some tests in linux, Lazarus 0.9.12 and PgSQL 8.0.x


  • Instalation and errors:
    • In the tested version of Lazarus .12, fields of type "text" and "numeric" have bugs
    • I used with no problems char fixed size, int and float8
    • Sometimes restarting Lazarus solves stupid errors...
    • After some errors, the transactions remain active and should be deactivated mannually
    • Changes made in Lazarus are of course not visible until transaction commited
    • The integrated debugger seems buggy (at least in windows) - sometimes running outside of the IDE may help to find errors
    • In linux certain error messages are printed in the console -- run your program in the command line, sometimes there is some extra useful debugging info
    • Error: "Can not load Postgresql client. Is it installed (libpq.so) ?"
      • Add the path to seach libpq* from the PostgreSQL installation.
      • In linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
      • It may be necessary to create a symboloic link ln -s /usr/lib/pqsql.so.5 /usr/lib/pqsql.so
      • In windows, add these libs anywhere in the Path environment variable or project dir
      • I windows, I copied all the DLLs in my C:\Program Files\PostgreSQL\8.1\bin dir to another dir in the PATH
      • Or add this postgres\bin dir to the path

A good example about connecting Lazarus with PostgreSQL under Windows is easyDB.


PostgreSQL package: the low level units

As with all databases, the SQLDB code depends on a lower level PostgreSQL specific unit that wraps around the PostgreSQL driver library (.so/.dll). Normally, you would use the higher-level SQLDB code as it allows you to code more quickly, easily switch databases etc.

Using this is very easy, all you need to do is compile some units, and use these units in your program. You need to specify the place of the PostgreSQL client Library (libpq) when compiling, and that is it.

The main unit is called postgres, normally this is the only unit you must include in your uses clause.

Requirements

You need at least version 0.99.5 of Free Pascal (basically any version of FPC except extremely old ones). The headers are translated from PostGreSQL version 6.3.1.

Installation

The prostgres unit comes with the Free Pascal packages, and is distributed together with the compiler. This contains a directory postgres with the units, a test program and a makefile. cd to the directory and edit the Makefile to set the variables for your system. You must provide only 1 thing:

  1. The directory where the libpq library resides, usually /usr/local/pgsql/lib

Typing

make

Should compile the units and the program. If compilation was succesfull, you can install with

make install

(Remember to set the directory where the units should be installed.)

You can then test the program by running

make test

This will:

  • Run the test program testpg. It is a straightforward pascal translation of the example program in the PostGreSQL programmers' guide.
  • Run a script to create a table in a database, and fill it with some data. (the psql program should be in your PATH for this) . By default, the used database is testdb.
  • Run the testprogram testemail
  • Run a shell script again to remove the created table.

You will see a lot of messages on your screen, giving you feedback and results. If something went wrong, make will inform you of this.

Go back to Packages List

See also