Re: [fpc-devel] Questions regarding PostgreSQL support in SqlDB
Jong, I did not make much changes to the fpc files itself. The only change I made to the api wrapper is the location of the libpq.so / dll file. It use to load from current working dir, or going on a rampant search according to the LD_LIBRARY_PATH environment variable on linux. I made a mod where the location of the lib file may be set. You might not be able to use this code at all due to the TDataSet ancestry being non existent.. BUT, if you can find any use at all for this code, check it out at svn://196.36.108.47/fpc_pgsqlclientlib The classes are in libpq_components.pas. References to other units to compile is in an svn external under lib directory. The code is extremely ugly. Good luck. On Mon, Aug 17, 2009 at 1:30 PM, Graeme Geldenhuys < grae...@opensoft.homeip.net> wrote: > Desmond Coertzen wrote: > >> 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 >> > > I can't remember, but I think bytea (I call it blob) work fine for me, but > I'll have to double check to be 100% sure. > > > 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 >> > > I do not use DB-aware components in our applications. I use "mediator > views" also know as Model-GUI-Mediator. The the following URL for an article > on it. > > http://opensoft.homeip.net/articles/ > > > of TDataSet, but, it worked for everything I needed in those daemons, it >> is lightweight and stable. !!/No support for parameters and prepared >> statements/!! >> > > I have had some "prepare statement" issues with SqlDB+PostgreSQL as well. I > still need to investigate this further. In the mean time, I disable all > "prepare" calls in my application for now. > > > I'm still a novice pascal programmer. If you are interested in my code, I >> can publish somewhere and send you a url. >> > > Are we allowed to merge some of your changes back into the SqlDB code? If > so, yes please do post a url. > > > Regards, > - Graeme - > > -- > fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal > > http://opensoft.homeip.net/fpgui/ > > ___ > fpc-devel maillist - fpc-devel@lists.freepascal.org > http://lists.freepascal.org/mailman/listinfo/fpc-devel > ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Questions regarding PostgreSQL support in SqlDB
Desmond Coertzen wrote: 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 I can't remember, but I think bytea (I call it blob) work fine for me, but I'll have to double check to be 100% sure. 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 I do not use DB-aware components in our applications. I use "mediator views" also know as Model-GUI-Mediator. The the following URL for an article on it. http://opensoft.homeip.net/articles/ of TDataSet, but, it worked for everything I needed in those daemons, it is lightweight and stable. !!/No support for parameters and prepared statements/!! I have had some "prepare statement" issues with SqlDB+PostgreSQL as well. I still need to investigate this further. In the mean time, I disable all "prepare" calls in my application for now. I'm still a novice pascal programmer. If you are interested in my code, I can publish somewhere and send you a url. Are we allowed to merge some of your changes back into the SqlDB code? If so, yes please do post a url. Regards, - Graeme - -- fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/ ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Questions regarding PostgreSQL support in SqlDB
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 wrote: > 2009/8/16 Joost van der Sluis : > > 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@lists.freepascal.org > http://lists.freepa
Re: [fpc-devel] Questions regarding PostgreSQL support in SqlDB
2009/8/16 Joost van der Sluis : > 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@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] Questions regarding PostgreSQL support in SqlDB
Op zaterdag 08-08-2009 om 19:03 uur [tijdzone +0200], schreef Graeme Geldenhuys: > 1..) > In the function TPQConnection.GetSchemaInfoSQL(..) there is the following SQL > statements. > * First off, this statement doesn't return a single row when I run it > directly in psql or pgAdmin III. > * Why are so many fields got the value 0. For example the system table > "pg_attribute" contains most of the information for the fields that return 0. > Fields like type, datatype, scale, length, etc... The output of the query has to the same as for the other databases. So that's why several 'empty/0' fields are there. And for type/datatype the value in pg_attribute could have to be translated to the right value. 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.) > > stColumns: s := 'select '+ > 'a.attnum as recno, '+ > ' as catalog_name, '+ > ' as schema_name, '+ > 'c.relnameas table_name, '+ > 'a.attnameas column_name, '+ > '0as column_position, '+ > '0as column_type, '+ > '0as column_datatype, '+ > ' as column_typename, '+ > '0as column_subtype, '+ > '0as column_precision, '+ > '0as column_scale, '+ > 'a.atttypmod as column_length, '+ > 'not a.attnotnull as column_nullable '+ > 'from '+ > ' pg_class c, pg_attribute a '+ > 'WHERE '+ > // This can lead to problems when case-sensitive > tablenames are used. > '(c.oid=a.attrelid) and (a.attnum>0) and (not > a.attisdropped) and (upper(c.relname)=''' + Uppercase(SchemaObjectName) + > ''') ' + > 'order by a.attname'; > > > > Instead of the above query, why not use the Information Schema views to pull > that information out in a much more friendly manner. Here is a quick > example... > > > SELECT ordinal_position, >column_name, >data_type, >column_default, >is_nullable, >character_maximum_length, >numeric_precision > FROM information_schema.columns >WHERE table_name = 'test_table' > ORDER BY ordinal_position > 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... > 2..) > Then in function TPQConnection.TranslateFldType(..) we have the following > lines... > > Oid_text : Result := ftBlob; > Oid_Bytea : Result := ftBlob; > > Shouldn't Oid_text return ftMemo instead of ftBlob? Could be. In fact, Bytea is not a blob field. So to support blob-fields, Text was abused. Which is also wrong. But Text isn't memo either. Actually it's a varchar Base problem is that Postgres has a different idea of field-types then 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. Problem is that sqldb can never map correctly to those blob-fields, because you can never know if a number-field actually contains a reference to a blob-field) But before my vacation i wrote something about making this mapping adjustable. That's still one my investigation list. > 3..) > In procedure TPQConnection.PrepareStatement(..) there is a const TypeStrings > being setup. Many of those entries show "unknown" when in fact they could > probably have PostgreSQL types associated instead. The 16th and 18th item > (counting starts at 1) could most probably be "bytea" instead of "unknown". > There are a few others as well. Probably they are not used at all, so why bother. ;) 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 li
[fpc-devel] Questions regarding PostgreSQL support in SqlDB
Hi, I have recently been playing around with PostgreSQL and SqlDB. I seem to have quite a few problems. tiOPF's test suite normally gives database components a good workout and currently SqlDB + PostgreSQL has about 40 tests failing out of 160 tests. SqlDB + Firebird does much better, but still not a 100% success rate. I hope to resolve all of these. Here are a few questions regarding the PostgreSQL support in SqlDB: 1..) In the function TPQConnection.GetSchemaInfoSQL(..) there is the following SQL statements. * First off, this statement doesn't return a single row when I run it directly in psql or pgAdmin III. * Why are so many fields got the value 0. For example the system table "pg_attribute" contains most of the information for the fields that return 0. Fields like type, datatype, scale, length, etc... stColumns: s := 'select '+ 'a.attnum as recno, '+ ' as catalog_name, '+ ' as schema_name, '+ 'c.relnameas table_name, '+ 'a.attnameas column_name, '+ '0as column_position, '+ '0as column_type, '+ '0as column_datatype, '+ ' as column_typename, '+ '0as column_subtype, '+ '0as column_precision, '+ '0as column_scale, '+ 'a.atttypmod as column_length, '+ 'not a.attnotnull as column_nullable '+ 'from '+ ' pg_class c, pg_attribute a '+ 'WHERE '+ // This can lead to problems when case-sensitive tablenames are used. '(c.oid=a.attrelid) and (a.attnum>0) and (not a.attisdropped) and (upper(c.relname)=''' + Uppercase(SchemaObjectName) + ''') ' + 'order by a.attname'; Instead of the above query, why not use the Information Schema views to pull that information out in a much more friendly manner. Here is a quick example... SELECT ordinal_position, column_name, data_type, column_default, is_nullable, character_maximum_length, numeric_precision FROM information_schema.columns WHERE table_name = 'test_table' ORDER BY ordinal_position 2..) Then in function TPQConnection.TranslateFldType(..) we have the following lines... Oid_text : Result := ftBlob; Oid_Bytea : Result := ftBlob; Shouldn't Oid_text return ftMemo instead of ftBlob? 3..) In procedure TPQConnection.PrepareStatement(..) there is a const TypeStrings being setup. Many of those entries show "unknown" when in fact they could probably have PostgreSQL types associated instead. The 16th and 18th item (counting starts at 1) could most probably be "bytea" instead of "unknown". There are a few others as well. As I work through the rest of the code, I'll make notes and report more findings here. Regards, - Graeme - fpGUI - a cross-platform Free Pascal GUI toolkit http://opensoft.homeip.net/fpgui/ ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
