Thread: Re: TEXT datatype and VB6...
Hello, all - this is my first posting to this list... I have a question, which I don't know if it is a bug, or if it is something I am doing wrong. I have an application which accesses a table inside a PostgreSQL DB. This table was created by a custom conversion process (written in VB6) to convert Access 97 tables to PostgreSQL tables. In my conversion engine, I convert Access 97 Memo type fields to PostgreSQL TEXT type fields. In my application, if I perform an AddNew to add a new record to this table, it is able to update the fields OK on the table prior to the Update, but if I perform another update to the field which is a TEXT type field prior to the Update, it dies with the error: Run-time error 3032: Can't perform this operation. --- Here is a little VB-ish pseudocode to illustrate what I am trying to do, and what is happenning: With Table .AddNew ![Field] = "Foo" ![Field] = "Bar" ' Error happens here .Update End With This is only happenning with TEXT type fields - if I change my conversion process to convert Memo fields to say VARCHAR(90000), the above code framework processes correctly without the error, just like it works with an Access 97 database. Can someone help me out here - what I may or may not be doing right or wrong? If you need more detail, email me and I will try to provide what I can... Thank you, Andrew L. Ayers
I am posting this to clarify my last email about it:
First off, this is only happenning when I perform the following sequence
in my VB code:
Open PGSQL Database (via DSN-less ODBC connection, using psqlODBC driver)
Select Recordset
With Recordset
.AddNew
![Field] = "blah" ' Field is a TEXT-type field on the pgsql table
![Field] = "blah" ' Error occurs here
.Update
End With
Close Recordset
Close Database
---
If instead of the .AddNew, I do a .Edit (on a pre-existing record I have
selected), it works OK. It also works OK if the field is of a type other
than TEXT - ie, VARCHAR().
If I do a single "update" of the field with the .AddNew:
With Recordset
.AddNew
![Field] = "blah"
.Update
End With
...the record is added fine.
Does anyone here suspect a problem with the ODBC driver?
I also cannot entertain suggestions of recoding the app to use ADO, RDO,
or SQL INSERT statements, etc - mainly because it is a large legacy
application heavily invested in DAO (the reason for moving from Access
97 to PostgreSQL is so that a rewrite of the application, in something
other than VB, can be accomplished, while still allowing the current
application to function and update the new DB).
Thank you,
Andrew
microsoft code has a habit of having little nagging problems like this, like, "Oh yeah, you need to make sure to add a space before a command,it's the microsoft way" Not meaning to bash, but I HAD to do some VB programming to access some oracle stuff and do a web page in a class, and the logic and reasoning behind little, extra characters one had to add in queries just galled me, WHY? <grrrr> Not much help to you, I know. I do believe that you're going to find that VB has trouble with that text field, and my (just now) intuition is because it can't handle a variable that has a variable length,without some special effects. Andrew Ayers wrote: > I am posting this to clarify my last email about it: > > First off, this is only happenning when I perform the following sequence > in my VB code: > > Open PGSQL Database (via DSN-less ODBC connection, using psqlODBC driver) > Select Recordset > > With Recordset > .AddNew > ![Field] = "blah" ' Field is a TEXT-type field on the pgsql table > ![Field] = "blah" ' Error occurs here > .Update > End With > > Close Recordset > Close Database > > --- > > If instead of the .AddNew, I do a .Edit (on a pre-existing record I have > selected), it works OK. It also works OK if the field is of a type other > than TEXT - ie, VARCHAR(). > > If I do a single "update" of the field with the .AddNew: > > With Recordset > .AddNew > ![Field] = "blah" > .Update > End With > > ...the record is added fine. > > Does anyone here suspect a problem with the ODBC driver? > > I also cannot entertain suggestions of recoding the app to use ADO, RDO, > or SQL INSERT statements, etc - mainly because it is a large legacy > application heavily invested in DAO (the reason for moving from Access > 97 to PostgreSQL is so that a rewrite of the application, in something > other than VB, can be accomplished, while still allowing the current > application to function and update the new DB). > > Thank you, > > Andrew > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
To add to this, is it possible to set it to varchar(yourlimithere) for what you need? My guess is if the limit is very high (over 8192 or so) then the DAO methods may break in strange ways. On Tue, 25 Mar 2003, Dennis Gearon wrote: > microsoft code has a habit of having little nagging problems like this, like, > "Oh yeah, you need to make sure to add a space before a command,it's the > microsoft way" > > Not meaning to bash, but I HAD to do some VB programming to access some oracle > stuff and do a web page in a class, and the logic and reasoning behind little, > extra characters one had to add in queries just galled me, WHY? <grrrr> > > Not much help to you, I know. > > I do believe that you're going to find that VB has trouble with that text field, > and my (just now) intuition is because it can't handle a variable that has a > variable length,without some special effects. > > Andrew Ayers wrote: > > I am posting this to clarify my last email about it: > > > > First off, this is only happenning when I perform the following sequence > > in my VB code: > > > > Open PGSQL Database (via DSN-less ODBC connection, using psqlODBC driver) > > Select Recordset > > > > With Recordset > > .AddNew > > ![Field] = "blah" ' Field is a TEXT-type field on the pgsql table > > ![Field] = "blah" ' Error occurs here > > .Update > > End With > > > > Close Recordset > > Close Database
scott.marlowe wrote: > To add to this, is it possible to set it to varchar(yourlimithere) for > what you need? My guess is if the limit is very high (over 8192 or so) > then the DAO methods may break in strange ways. Well, it is looking like I am going to have to do that, and just see what happens. Looking at the data on my Access DB, and looking at the max size for my Memo-type fields, the largest is at about 87K (that is, on one particular record, it has 87K worth of data in the field). It hasn't choked on this size with the Access DB and DAO - not sure what it will do with ODBC, PostgreSQL, DAO, etc - it will just have to be a "try-and-see" type thing. Since this is the route I am taking (that is, using a large VARCHAR rather than TEXT for these fields), I did some experimenting: I created a table in PostgreSQL with two fields, one a sequential number field, the other a VARCHAR with various sizing. In the documentation I have seen for PostgreSQL and VARCHAR, the max-size is said to be 10,485,760 bytes - and PostgreSQL seems to allow you to make a VARCHAR field on a table this size. However, when trying to access it in VB, VB would get an application error upon trying to select the field, and die. I found that if I dropped the size down to 1 meg (1048576 - ie 1024^2), it stopped doing that, and updated the field properly. This size of 1 meg is still approximately 12x larger than my largest field above, so this limit seems reasonable. I did try larger limits, to see where and how things acted - somewhere around 1.4 to 1.5 meg, the error would still occur. Below 1.4 meg, the client machine (my desktop machine is a PII400 with 384 meg) would churn and churn (what I mean by this is that it seemed to be hitting some kind of caching or virtual memory limit or something), and eventually the connection would time-out (I suppose that is what happened, anyhow). Dropping it further to 1.3 meg, a small amount of churning would occur, but the update would go through. Below that amount, very little observable (that is, to the user) churning would happen. So, I think I will stick to the 1 meg (1048576 bytes) limit for now. I still find it disconcerting that the TEXT field type won't update the same way - I have a feeling it is the ODBC driver, and not anything else. Unfortunately I have no way to test and verify this, and I don't have an alternate driver to try... Andrew
There was a quote about a rep of mysql saying postgres has incomplete solutions. If so, it's NOTHING compared to usoft 'solutions'. scott.marlowe wrote: > To add to this, is it possible to set it to varchar(yourlimithere) for > what you need? My guess is if the limit is very high (over 8192 or so) > then the DAO methods may break in strange ways. > > On Tue, 25 Mar 2003, Dennis Gearon wrote: > > >>microsoft code has a habit of having little nagging problems like this, like, >>"Oh yeah, you need to make sure to add a space before a command,it's the >>microsoft way" >> >>Not meaning to bash, but I HAD to do some VB programming to access some oracle >>stuff and do a web page in a class, and the logic and reasoning behind little, >>extra characters one had to add in queries just galled me, WHY? <grrrr> >> >>Not much help to you, I know. >> >>I do believe that you're going to find that VB has trouble with that text field, >>and my (just now) intuition is because it can't handle a variable that has a >>variable length,without some special effects. >> >>Andrew Ayers wrote: >> >>>I am posting this to clarify my last email about it: >>> >>>First off, this is only happenning when I perform the following sequence >>>in my VB code: >>> >>>Open PGSQL Database (via DSN-less ODBC connection, using psqlODBC driver) >>>Select Recordset >>> >>>With Recordset >>> .AddNew >>> ![Field] = "blah" ' Field is a TEXT-type field on the pgsql table >>> ![Field] = "blah" ' Error occurs here >>> .Update >>>End With >>> >>>Close Recordset >>>Close Database > > >