Обсуждение: BLOB issue(s) & questions
one last attempt: I am working with a window-manufacturing firm (the real thing!). One of the reasons we are about to choose one of the vertical-market applications is that they claim ODBC compliance, and "Using other ODBC compliant database engines should not present a problem but may require some additional testing..." This is in stark contrast to all the other vendors who require MSSQL on the back end (or an AS400), and the usual windoziness about why you'd want to do anything else. When I asked about using PostgreSQL this is the reply I received: > I discussed PostureSQL with Paul and his technical director sent me > the following comment: > > PostgreSQL is open source and so far they have not agreed on a blob > field properly we use blob fields for the item bitmap, old > conservatory data and meta files for graphics. An ODBC driver is > available and describes how to fudge a blob field but it says that it > does not clean them up properly when updating. I suggest moving to > MySQL which is also open source ?? > > They estimate about one week additional time to make necessary changes > and to test for MySQL. Let me know what you think. > These folks develop using MSAccess and MSSQL. Can anyone shed any light on how serious this problem is, what the problem actually might be, and whether it is ever likely to be resolved so that I could use PostgreSQL? One suggestion from the general list was as follows > I wonder if they've got confused about the 2 ways in PostgreSQL can > store blobs. There is the older Large Object method and there is the > newer bytea data type. Each has its advantages and disadvantages. > http://www.varlena.com/varlena/GeneralBits/44.php could help them > understand which to use. Or they could ask on this list. > > Perhaps you should also ask them them to comment on > http://sql-info.de/mysql. TIA! PS - the vertical market software is Caliburn v8 at http://www.caliburn-software.com/ -- Derek Shaw BIS Business Information Systems Inc. Victoria, BC. voice: 250-885-2021 fax: 250-386-4060 PGP Public Key ID: 0xD3783198
> -----Original Message----- > From: Derek Shaw [mailto:derek@bisi.ca] > Sent: 09 February 2004 20:30 > To: pgsql-odbc > Subject: [ODBC] BLOB issue(s) & questions > > Can anyone shed any light on how serious this problem is, > what the problem actually might be, and whether it is ever > likely to be resolved so that I could use PostgreSQL? Hi Derek, There are three options: 1) Use a bytea column. You might create a domain as an 'lo' alias to bytea if required. 2) Use the /contrib/lo code. This custom datatype allows you to create a cleanup trigger for each lo column. 3) Use the 'simple' ODBC lo option documented at: http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52 - this does not cleanup after itself, however in /contrib/vacuumlo is a small utility that will remove orphaned large objects - just run it prior to a normal vacuum, or at whatever frequency suits if you use the autovacuum daemon. Please note that I haven't tried using blobs in years... Regards, Dave.
On Monday 09 February 2004 20:30, Derek Shaw wrote: > one last attempt: > > I am working with a window-manufacturing firm (the real thing!). One of > the reasons we are about to choose one of the vertical-market > applications is that they claim ODBC compliance, and "Using other ODBC > compliant database engines should not present a problem but may require > some additional testing..." This is in stark contrast to all the other > vendors who require MSSQL on the back end (or an AS400), and the usual > windoziness about why you'd want to do anything else. > > When I asked about using PostgreSQL this is the reply I received: > > I discussed PostureSQL with Paul and his technical director sent me > > the following comment: > > > > PostgreSQL is open source and so far they have not agreed on a blob > > field properly we use blob fields for the item bitmap, old > > conservatory data and meta files for graphics. An ODBC driver is > > available and describes how to fudge a blob field but it says that it > > does not clean them up properly when updating. I suggest moving to > > MySQL which is also open source ?? Without knowing more about their real issues, the only thing I can suggest is to look at the mini-howto on the pgsqlodbc site: http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-index There's a large-object item in the list. There are also bitstring and bytearray types that might serve for their purposes, but I'm afraid I can't comment on their use via ODBC since I've never needed them. I don't know of the issue regarding blobs not updating properly. I haven't heard of any such problem. It's difficult to say more without knowing what their issues are. -- Richard Huxton Archonet Ltd
> There are three options: > > 1) Use a bytea column. You might create a domain as an 'lo' alias to > bytea if required. This works. And very easy. But for files upt to around 2 MB. Beware. > 2) Use the /contrib/lo code. This custom datatype allows you to create a > cleanup trigger for each lo column. This works. but not easy with odbc and other apps... > > 3) Use the 'simple' ODBC lo option documented at: > http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52 - > Please note that I haven't tried using blobs in years... > > Regards, Dave. Solution 3 does not work for Postgresql since 7.x, as I can remember. For 7.3.x is sure (I tried). The innards of Pg changed a lot and it does not allow the tricks cited. Use the solution 2, instead, for very large objects over 2MB. good luck. Andre Felipe http://www.andrefelipemachado.hpg.ig.com.br/linux/index.html