Thread: odbc 8.01.02 8190 bytes limitation
Hi all !
I use PostgreSQL database via ado (win32).
I succeeded in storing/retrieving binary format (bytea) to/from database (I use it for images).
But I am limited to 8190 bytes. Is it a way to disable this limitation ?
I saw that the odbc have a MaxLongVarChar option set to 8190. May be my problem come from this, but how can I update this option using ado ?
I saw many posts about this subject but I did not find the solution - may be I am too blind today ;)
thanks
Mike
> I use PostgreSQL database via ado (win32). > I succeeded in storing/retrieving binary format (bytea) to/from > database (I use it for images). > > But I am limited to 8190 bytes. Is it a way to disable this limitation ? Could you try 07.03.026X version? It's enhanced experimental branch which is now CVS tip and it'll be released as new stable release in relatively short time. IMHO it's more stable version. > I saw that the odbc have a MaxLongVarChar option set to 8190. May be > my problem come from this, but how can I update this option using ado ? There is connection string too in ADO. So specify it in it. > I saw many posts about this subject but I did not find the > solution - may be I am too blind today ;) I don't want waste my time in developing dead branch (which 08.01 is). Regards, Luf
Hi Luf !
thanks for your answer.
I will give you some precisions : I work with ado and odbc driver to
connect. For insertion in database I use variant types. For array my variant
is defined as this : VT_ARRAY|VT_UI1
I tried the ODBC driver 7.03.02.60, I got the following errors :
- connection with default settings (maxLongVarChar = 8190) :
. less than 8k insertion : ERROR during field affectation :
"Multiple-step operation generated errors. Check each status value"
. less than 16k insertion : idem
... idem
- connection with maxLongVarChar = 16382 :
. less than 8k insertion : idem as above
... idem
With the ODBC driver 8.01.02.00, I got the following errors :
- conenction with default settings : maxLongVarChar = 8190
. less than 8k insertion : OK !
. less than 16k insertion : ERROR during RecordSet update : Microsoft
cursor engine : data provider or other service returned an E_FAIL status.
... idem as 16k insertion
- connection with maxLongVarChar = 16382 :
. less than 8k : OK !
. less than 16k : OK !
. more than 16k : ERROR during RecordSet update : Microsoft cursor
engine : data provider or other service returned an E_FAIL status.
- connection with maxLongVarChar = 32768 :
. less than 8k : ERROR during RecordSet update : type "lo" does not
exist
... always the same error
After to have inserted data of 8k with ODBC 8.01.02, if I tried to connect
with ODBC 7.03.02.60 and retrieve the data, I have no errors.
But if I try to insert new data, I have always the error : type "lo" does
not exist !
I am a bit lost with these errors. I am not a database specialist and may be
I do not use the good way to insert binary data. I also will try to insert
binary data using the ado streams ....
thanks for your help
Mike
----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "Michael GUIARD" <m.guiard@nestor-tech.com>
Cc: <pgsql-odbc@postgresql.org>
Sent: Tuesday, April 25, 2006 12:37 PM
Subject: Re: [ODBC] odbc 8.01.02 8190 bytes limitation
>> I use PostgreSQL database via ado (win32).
>> I succeeded in storing/retrieving binary format (bytea) to/from
>> database (I use it for images).
>>
>> But I am limited to 8190 bytes. Is it a way to disable this limitation ?
>
> Could you try 07.03.026X version? It's enhanced experimental branch which
> is now CVS tip and it'll be released as new stable release in relatively
> short time. IMHO it's more stable version.
>
>> I saw that the odbc have a MaxLongVarChar option set to 8190. May be
>> my problem come from this, but how can I update this option using ado ?
>
> There is connection string too in ADO. So specify it in it.
>
>> I saw many posts about this subject but I did not find the
>> solution - may be I am too blind today ;)
>
> I don't want waste my time in developing dead branch (which 08.01 is).
>
> Regards,
>
> Luf
> ---------------------------------------------------------------------------------------
> Wanadoo vous informe que cet e-mail a ete controle par l'anti-virus mail.
> Aucun virus connu a ce jour par nos services n'a ete detecte.
>
>
>
Michael GUIARD wrote: > Hi Luf ! > thanks for your answer. > > I will give you some precisions : I work with ado and odbc driver to > connect. For insertion in database I use variant types. For array my > variant is defined as this : VT_ARRAY|VT_UI1 > > I tried the ODBC driver 7.03.02.60, I got the following errors : > - connection with default settings (maxLongVarChar = 8190) : > . less than 8k insertion : ERROR during field affectation : > "Multiple-step operation generated errors. Check each status value" > . less than 16k insertion : idem > ... idem > - connection with maxLongVarChar = 16382 : > . less than 8k insertion : idem as above > ... idem > > With the ODBC driver 8.01.02.00, I got the following errors : > - conenction with default settings : maxLongVarChar = 8190 > . less than 8k insertion : OK ! > . less than 16k insertion : ERROR during RecordSet update : > Microsoft cursor engine : data provider or other service returned an > E_FAIL status. > ... idem as 16k insertion > - connection with maxLongVarChar = 16382 : > . less than 8k : OK ! > . less than 16k : OK ! > . more than 16k : ERROR during RecordSet update : Microsoft cursor > engine : data provider or other service returned an E_FAIL status. > - connection with maxLongVarChar = 32768 : > . less than 8k : ERROR during RecordSet update : type "lo" does not > exist > ... always the same error > > After to have inserted data of 8k with ODBC 8.01.02, if I tried to > connect with ODBC 7.03.02.60 and retrieve the data, I have no errors. > But if I try to insert new data, I have always the error : type "lo" > does not exist ! Please turn on the ByteaAsLongVarchar option. regards, Hiroshi Inoue
Hi Hiroshi, I did not find the ByteaAsLongVarchar option in the odbc driver. But because of your request I searched a bit more and I saw the "byte as lo" option. I tried it and now it works !! GOOOOD :) So I configure the maxLongVarChar as -4 and check the "byte as lo" option, and now I can store/retreive image of more than 16KB ! Thanks a lot for your help !!! :) I tried with the 7.03.02.60 and 8.01.02 drivers. With the 7.03.02xx version the recordset update takes very long time. With the 8.01.02 I have no problems. All is good for me except one thing : how can I set the "byte as lo" option in the odbc connection string ? Mike ----- Original Message ----- From: "Hiroshi Inoue" <inoue@tpf.co.jp> To: "Michael GUIARD" <m.guiard@nestor-tech.com> Cc: "Ludek Finstrle" <luf@pzkagis.cz>; <pgsql-odbc@postgresql.org> Sent: Wednesday, April 26, 2006 2:58 PM Subject: Re: [ODBC] odbc 8.01.02 8190 bytes limitation > Michael GUIARD wrote: > >> Hi Luf ! >> thanks for your answer. >> >> I will give you some precisions : I work with ado and odbc driver to >> connect. For insertion in database I use variant types. For array my >> variant is defined as this : VT_ARRAY|VT_UI1 >> >> I tried the ODBC driver 7.03.02.60, I got the following errors : >> - connection with default settings (maxLongVarChar = 8190) : >> . less than 8k insertion : ERROR during field affectation : >> "Multiple-step operation generated errors. Check each status value" >> . less than 16k insertion : idem >> ... idem >> - connection with maxLongVarChar = 16382 : >> . less than 8k insertion : idem as above >> ... idem >> >> With the ODBC driver 8.01.02.00, I got the following errors : >> - conenction with default settings : maxLongVarChar = 8190 >> . less than 8k insertion : OK ! >> . less than 16k insertion : ERROR during RecordSet update : Microsoft >> cursor engine : data provider or other service returned an E_FAIL status. >> ... idem as 16k insertion >> - connection with maxLongVarChar = 16382 : >> . less than 8k : OK ! >> . less than 16k : OK ! >> . more than 16k : ERROR during RecordSet update : Microsoft cursor >> engine : data provider or other service returned an E_FAIL status. >> - connection with maxLongVarChar = 32768 : >> . less than 8k : ERROR during RecordSet update : type "lo" does not >> exist >> ... always the same error >> >> After to have inserted data of 8k with ODBC 8.01.02, if I tried to >> connect with ODBC 7.03.02.60 and retrieve the data, I have no errors. >> But if I try to insert new data, I have always the error : type "lo" does >> not exist ! > > > Please turn on the ByteaAsLongVarchar option. > > regards, > Hiroshi Inoue > --------------------------------------------------------------------------------------- > Wanadoo vous informe que cet e-mail a ete controle par l'anti-virus mail. > Aucun virus connu a ce jour par nos services n'a ete detecte. > > >
> All is good for me except one thing : how can I set the "byte as lo" option > in the odbc connection string ? Try enable mylog output for Datasource and you can see the connection string in the log output. I don't remember it and this solution is more complex ;-) Regards, Luf
ok the option is ByteaAsLongVarBinary=1 thanks a lot :) Mike ----- Original Message ----- From: "Ludek Finstrle" <luf@pzkagis.cz> To: "Michael GUIARD" <michael@nestor-tech.com> Cc: <pgsql-odbc@postgresql.org> Sent: Wednesday, April 26, 2006 4:03 PM Subject: *** SPAM *** Re: [ODBC] odbc 8.01.02 8190 bytes limitation >> All is good for me except one thing : how can I set the "byte as lo" >> option >> in the odbc connection string ? > > Try enable mylog output for Datasource and you can see the connection > string in the log output. I don't remember it and this solution > is more complex ;-) > > Regards, > > Luf > --------------------------------------------------------------------------------------- > Wanadoo vous informe que cet e-mail a ete controle par l'anti-virus mail. > Aucun virus connu a ce jour par nos services n'a ete detecte. > > >