Thread: varchar() access
No one has any comment at all? Not even 'your a dick-head'! Oh well, I guess I'll have to re-write my code to cope with this problem that I am having. > I'm accessing a postgres 6.3 server on Linux 2.0.35 using the 6.40.0004 ODBC driver on NT 4S. > > I am having a problem with varchar columns being truncated to 4K bytes when coming from the backend, although I can store> 4K into the backend. > > I have set max LongVarChar to 7000 and -4 (as suggested in the FAQ) > I have tried checking and not checking the 'text as LongVarChar' box. > > I have seen the problem in Access 97 and ColdFusion 4 on 2 separate servers. > > I'm I doing something wrong, have I got the wrong end of a stick, or do I have to use large objects... > > Thanks, in advance, for any help. -- Mark Himsley. Technical Director, Clockwork Web.
Mark Himsley wrote: > No one has any comment at all? > Not even 'your a dick-head'! > Oops, since I didn't see ODBC in the subject, I missed it. But hard to miss that line! > Oh well, I guess I'll have to re-write my code to cope with this problem > that I am having. > > > I'm accessing a postgres 6.3 server on Linux 2.0.35 using the 6.40.0004 ODBC driver on NT 4S. > > > > I am having a problem with varchar columns being truncated to 4K bytes when coming from the backend, although I can store> 4K into the backend. > > > > I have set max LongVarChar to 7000 and -4 (as suggested in the FAQ) > > I have tried checking and not checking the 'text as LongVarChar' box. > Yes indeed, the driver has an internal limit of 4k on text fields *plus* Postgres has a limit of 4k too. The ability toset the number was more intended for applications that needed it to be lower. Like Access will allow you to index on a text field if you set thisnumber to 254. We have run into this problem before of the 4k limit. You could try using large objects, but I'm not too sure how they willwork with character data, as they were more intended for handling LongVarBinary data. If you are going to create a column in the table yourself tohold this long char data, you should probably check the faq which describes how to setup a "lo" data type. That is the only way the driver will recognizeit as a large object. Currently, the driver can only "retrieve" the character data from a large object (if this whole thing works), such as ina BindCol or GetData call.. There is no way it would be able to send LongVarChar data to a large object, such as in a BindParameter or data-at-executionparameter. It is currently only capable of doing this for LongVarBinary data type. So if you have control over what kind of field you are binding, then you *should* be able to use longvarbinary, and it possiblywill work. The real solution is to allow an option in the driver of mapping a large object to a LongVarChar data type, instead of atext field. Let me know what happens. Maybe if you hound me on this issue, I may add it. Anybody else have any feelings on the LongVarChar-->Large Object issue? Byron
Byron Nikolaidis <byronn@insightdist.com> wrote: > Mark Himsley wrote: > > > No one has any comment at all? > > Not even 'your a dick-head'! > > Oops, since I didn't see ODBC in the subject, I missed it. But hard to miss that line! Sorry, I'll put ODBC in the subject next time. I feel suitably chastised <grin> > > > I'm accessing a postgres 6.3 server on Linux 2.0.35 using the 6.40.0004 ODBC driver on NT 4S. > > > > > > I am having a problem with varchar columns being truncated to 4K bytes when coming from the backend, although I canstore > 4K into the backend. > > > > > > I have set max LongVarChar to 7000 and -4 (as suggested in the FAQ) > > > I have tried checking and not checking the 'text as LongVarChar' box. > > > > Yes indeed, the driver has an internal limit of 4k on text fields *plus* Postgres has a limit of 4k too. The ability toset the number was more > intended for applications that needed it to be lower. Like Access will allow you to index on a text field if you set thisnumber to 254. I thought Postgres had a limit of 8K? That's what it says in the docs... Anyway, as I said I can put more that 4K worth of data into a text field via ODBC, and I car get all that data out using a SELECT in the psql command line interface. It's just a bit frustrating that I can't get this data out when accessing the same database via ODBC. > We have run into this problem before of the 4k limit. You could try using large objects, but I'm not too sure how theywill work with character data, > as they were more intended for handling LongVarBinary data. If you are going to create a column in the table yourselfto hold this long char data, > you should probably check the faq which describes how to setup a "lo" data type. That is the only way the driver willrecognize it as a large object. Thanks. I think I'll actually try a different approach, being that I'll split the data into 4K chunks and store it into separate sequenced records. This will only work with a program writing the data rather than appending the data with Access. This is something I can do quickly, to get the web site I promised would be live last Wednesday. -- Mark Himsley. Technical Director, Clockwork Web.
Mark Himsley wrote: > I thought Postgres had a limit of 8K? That's what it says in the docs... > Maybe, but the entire statement length also has a limit of 8K! That's where I think it gets a little ridiculous to tryand use in-line sql to send/retrieve data of these sizes. The large object interface is perfect for this. I am going to try to add LongVarChar/Large Object support to the driversoon. We will need two different data types now -- one for binary (already have "lo") and one for char (maybe "lo_char"). Byron
On Fri, 29 Jan 1999 09:58:30 -0500, you wrote: >Mark Himsley wrote: > >> I thought Postgres had a limit of 8K? That's what it says in the docs... > >Maybe, but the entire statement length also has a limit of 8K! Yes, I see the problem. I have written some code to split my text data into 4K blocks and write them to separate rows for now, but I am very interested in using the large object support when it's there <grin>. Thanks for your time. -- Mark Himsley. Technical Director Clockwork Web. +44 171 471 0770 http://www.clockworkweb.com