[fpc-devel] Questions regarding PostgreSQL support in SqlDB

[Desmond Coertzen]

Hi Graeme,

The libpq_api.pas that ships with FPC implements all the functions from the
version 3 native api on windows and linux. This .so/.dll is backwards
compatible even with the latest versions of postgresql server.

I tried TPQConnection in a production environment on a server-side deamon,
and bytea was the first missing data type that haunted me. The "encode"
postgres function will save you here: Encode your bytea field to hex, then
run a hextobin afterwards. (Sorry if you use dbAware components in this
case). Some other field types were missing as well, I did not try further
with this component and I'm hoping for more developments in the future as
well.

I also tried Zeos db components. They work with FPC very well, and the bytea
type encodes all characters correct when using TField.AsString, but there
was this massive memory leak somewhere when opening a dataset every time.
The leak was so bad it made the daemon unfit for production running. The
Zeos forum at that time laid the blame with FPC's TDataSet class, but I
could not bother to investigate if this was so or not. I was pressed for
time to release the software.

Because the version 3 API is so simple, it was too easy to use the
libpq_api.pas wrapper that comes with FPC, write a TDBConnection, TQuery and
TField (with the .As* functions for data types I needed) that compiled
nicely up to the TDataSet code compatibility. Of course, the code is not
usable to dbAware components because it is not a descendant of TDataSet,
but, it worked for everything I needed in those daemons, it is lightweight
and stable. !!*No support for parameters and prepared statements*!!

Judging from my experience, I think the FPC developers, given enough time,
can do some good work with their own TPQConnection, seeing that I'm still a
novice pascal programmer. If you are interested in my code, I can publish
somewhere and send you a url.

Desmond

On Sun, Aug 16, 2009 at 10:11 PM, Graeme Geldenhuys <graemeg.lists at gmail.com
> wrote:

> 2009/8/16 Joost van der Sluis <joost at cnoc.nl>:
> > But it's this way because nobody looked at it before, and all those
> > things like column_datatype are effectively used by no-body, afaik. But
> > patches are welcome. (The only reason this function is implemented at
> > all is that it is used by Connection.getTableNames and .GetFieldNames.)
>
> So is SqlDB+PostgreSQL not tested or Alpha / Beta quality? I was
> hoping to use it in a production environment. PostgreSQL seems to be a
> lot more powerful than other open source database + the PostgreSQL
> tools like pgAdmin III are brilliant.
>
>
> > This probably didn't exist when this code was written (postgres 5) and
> > the information_schema is probably just a view which references to
> > pg_attribute...
>
> Yes, any implementation of "information_schema" is normally a view.
> The nice thing is that even if the underlying system tables change,
> the information_schema views will not. Hence the reason it is
> preferable to use the information_schema's if they exist, instead of
> querying the system tables directly.
>
> Is it still worth supporting anything before PostgreSQL 7.2 - as far
> as I can see many major changes (for the better) occurred after 7.2
> and 8.0. Hey, the database server is free, so there shouldn't be any
> reason not to upgrade. :-)
>
>
> > Could be. In fact, Bytea is not a blob field. So to support blob-fields,
>
> All the documentation I read suggests that Bytea is used for BLOB
> types. And Firebird's "blob subtype 1" is equal to "Text" in
> PostgreSQL.
>
>
> > the databases you are used to use. (For blob-fields Postgresql ask you
> > to use a plain number-field in which you store the blob-id, and then use
> > seperate functions to retrieve the blob-data.
>
> As far as I understood the documentation, that is all handled
> internally by PostgreSQL. Users do not need to worry about something
> like that, you use Bytea just like any other field type. The server
> stores BLOB (bytea) data in a separate location to overcome the table
> row size limit. Internally a reference is used in the users table, but
> the end user never sees that.
>
>
> > But please create bug-reports, preferrably with actual user-case
> > problems, not only based on the code, but also show the results. That
> > way we can find some way to be as compatible possible with other
> > databases like Firebird.
>
> Will do - expect many patches. I'm determined to get Free Pascal to
> pass the extensive test suite of tiOPF.  The database test suite gives
> the DB components a very good workout. SqlDB+Firebird is doing pretty
> well, but SqlDB+PostgreSQL still fails a lot of tests. Both are not
> 100% pass rate, but hopefully when I am done, they will be.
>
>
>
> Regards,
>  - Graeme -
>
>
> _______________________________________________
> fpGUI - a cross-platform Free Pascal GUI toolkit
> http://opensoft.homeip.net/fpgui/
> _______________________________________________
> fpc-devel maillist  -  fpc-devel at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20090817/aea81e93/attachment.html>