Thread: Large Objects...ODBC/VB6/ADO...& PostgreSQL
I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6 Application accessing the DB through postgrSQL ODBC driver v. 6.50. I am porting an application that used a lot of memo fields in the jet database engine, well over 8k limit of PG. I now need to store these using the large object data type of PG. Examples given in the large objects chapter of PG Programmers manual depicts an example in C and all I know about C is that it comes after B and before D ;-)). Can somebody please give me a pure SQL based implementation of how to use the lo datatype, assuming I've got a text string of 12 kb named A$ and I want to create a table of lo fields named info and store the string in it and then readit ;-)) Another query is that since lo operations need to be in transactions....does this have to be a seperate transaction dealing with the lo operation or can the lo operation be executed in a transaction that is updating other tables as well. Another problem I am facing is that opening a recordset using Microsoft data shape OLE DB with following and using the open method leads to error (-2147418113(8000ffff) Catastrophic Failure): CursorType=adoOpenStatic LockType=adoLockOptimistic Can somebody please tell me what it is? How do I workaround this? Do I need to abandon data shape or the ADO itself? Is there any FAQ on using postgreSQL with VB? or ADO etc.? Any pointers to any resources thatcan enable me to marry VB & postgreSQL successfully? My company is yelling blue murder on my pushing postgreSQL in place of a commercial product like Oracle etc. Pleeeeaseee HEEELLLLPPPP. Sanjay.
Sanjay Arora <sk@pobox.com> writes:
> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 6.50.
> I am porting an application that used a lot of memo fields in the jet
> database engine, well over 8k limit of PG. I now need to store these using
> the large object data type of PG.
AFAIK it's pretty painful to use large objects through ODBC (but I don't
know much about ODBC, maybe there is a way?).
The record length limit will be effectively gone in 7.1, so it's a shame
to see you going to great lengths to work around it now. How far away
are you from being able to fit --- ie, what's the most data you need to
put in a row/field at the moment? Two stopgap measures you could take
are (a) rebuild Postgres with BLCKSZ 32K instead of 8K, and (b) declare
your large fields as lztext, not text or varchar, to get compression
applied to their contents. Depending on what your data looks like,
lztext might be good for a 2X or so savings on average, which would get
you up to ~64K per row. Perhaps that will be good enough to hold the
fort until 7.1 is out.
BTW, if you are going to go to the trouble of rebuilding Postgres from
source, I'd definitely recommend fetching the 7.0.3 release first.
There are some small but critical bug fixes over 7.0.2...
> Another query is that since lo operations need to be in
> transactions....does this have to be a seperate transaction dealing with
> the lo operation or can the lo operation be executed in a transaction that
> is updating other tables as well.
It just has to be a transaction; you can do whatever you want therein.
One thing to be wary of is that lo_unlink is not rollback-able in 7.0.*;
if you do an unlink, better be sure your transaction commits, rather
than failing later :-(
> Another problem I am facing is that opening a recordset using Microsoft
> data shape OLE DB with following and using the open method leads to error
> (-2147418113(8000ffff) Catastrophic Failure):
Sorry, don't know a thing about OLE or ADO. Anyone?
regards, tom lane
What version of ADO are you using? Adam Lang Systems Engineer Rutgers Casualty Insurance Company > > > Another problem I am facing is that opening a recordset using Microsoft > > data shape OLE DB with following and using the open method leads to error > > (-2147418113(8000ffff) Catastrophic Failure): > > Sorry, don't know a thing about OLE or ADO. Anyone? > > regards, tom lane
Also, if you could post some of the code that you are having a problem with. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Sanjay Arora" <sk@pobox.com> > > > Another problem I am facing is that opening a recordset using Microsoft > > data shape OLE DB with following and using the open method leads to error > > (-2147418113(8000ffff) Catastrophic Failure): > > Sorry, don't know a thing about OLE or ADO. Anyone? > > regards, tom lane
First, try upgrading to ADO 2.5 There was an error like that for MS SQL
that was fixed in the 2.5 version.
Also, why are you using JET? You should use the OLE DB provider for ODBC.
By using JET, you are adding another level of abstraction that you don't
need.
As for using Shpe, I never have, so I can't help much there, but what I
would recommend then is trying your code without shape and see if you still
get connection problems. That way you can see if it is shape or not that is
causing it.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Sanjay Arora" <sk@pobox.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Sent: Thursday, November 23, 2000 6:24 PM
Subject: Re: [INTERFACES] Large Objects...ODBC/VB6/ADO...& PostgreSQL
At 07:05 PM 11/21/00 , you wrote:
>What version of ADO are you using?
>
>Adam Lang
>Systems Engineer
>Rutgers Casualty Insurance Company
ADO Ver 2.1, postgreSQL 7.0.3 on RH Linux 6.2, ODBC 6.50, Visual Basic 6,
Win NT SP3 client
Seems I can't reproduce that catastrophic failure error...came quite a few
times.....am working on it....took the update out of transactions and the
catastrophic failure stopped but by then other changes were also there in
the project....can I take a raincheck ? ;-((
Another prob, if you can help me...
' Shape Query Creating Trouble getting error "parser cannot understand near or after shape"......is this
due to MS extensions in the shape data control & should I chuck it out or is
there a byepass?
strShape = "SHAPE ( SHAPE {SELECT Act_Id FROM `TbAccountMaster` Where Act_Id
IN (" & mCodes & ") And Act_Specification <> 'CASH'} AS Command2 APPEND
({SELECT Transaction_Date AS VOUCHER_DATE,iif(Transaction_Table =
4,iif(Entry_Id <> '" & GetOpeningId & "','JOURNAL ENTRY','YEAR
OPENINGS'),'BY CASH A/C') AS PARTICULARS,iif(DRCR =
'DR',Transaction_Amount,' ')AS DEBIT,iif(DRCR = 'CR',Transaction_Amount,' ')
AS CREDIT,DRCR,Account_Code AS BALANCES ,Entry_Id FROM `TbAccountTransact`
ORDER BY Transaction_Date,Entry_Id,DRCR} AS Command1 RELATE 'Act_Id' TO
'BALANCES') AS Command1) COMPUTE Command2 BY Act_Id"
If conTmp.State = adStateClosed Then With conTmp .Provider = "MSDataShape.1" Select Case Command()
Case "access" .Open "Data Source=" & sDbPath & ";Data Provider=Microsoft.Jet.OLEDB.4.0"
Case "postgres" .Open conAct.ConnectionString End Select End With
End If
If rsTmp.State = adStateOpen Then rsTmp.Requery Else With rsTmp Set .ActiveConnection = conTmp
.CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOptimistic
.Source = strShape
----> .Open (Error happening here)
End With End If