Thread: Postgresql Text field / Visual FoxPro Memo and ODBC
I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into Postgresql 7.x on a Redhat Linux server. All is working well except for Memo fields, which are Text fields in PostgreSql. I query the Postgresql table and get a cursor, update the cursor with the data, and send it back. The contents of the memo field never make it back to the Postgresql text field. Other changes to the record's fields are saved with no problem. At this time all my UI's have to be written in VFP. Is there a work around for this or am I going to have to eliminate Text/Memo fields from my tables? Thanks. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
On Wed, 2005-01-12 at 14:59, MargaretGillon@chromalloy.com wrote: > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into > Postgresql 7.x on a Redhat Linux server. All is working well except for > Memo fields, which are Text fields in PostgreSql. I query the Postgresql > table and get a cursor, update the cursor with the data, and send it back. > The contents of the memo field never make it back to the Postgresql text > field. Other changes to the record's fields are saved with no problem. > > At this time all my UI's have to be written in VFP. Is there a work around > for this or am I going to have to eliminate Text/Memo fields from my > tables? Postgresql doesn't support updatable cursors.
Hello, El 12/01/2005 2:59 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into > Postgresql 7.x on a Redhat Linux server. All is working well except for > Memo fields, which are Text fields in PostgreSql. I query the Postgresql > table and get a cursor, update the cursor with the data, and send it back. > The contents of the memo field never make it back to the Postgresql text > field. Other changes to the record's fields are saved with no problem. Are you using SPT or remote views? > At this time all my UI's have to be written in VFP. Is there a work around > for this or am I going to have to eliminate Text/Memo fields from my > tables? I use VFP8 against Postgresql with ODBC without any issue with the memo fields. -- Sinceramente, Josué Maldonado. "La TV es muy educativa. Cuando está encendida, me voy a otra habitación y me pongo a leer un libro." --Groucho Marx.
Scott Marlowe <smarlowe@g2switchworks.com> wrote on 01/12/2005 01:06:30 PM: > On Wed, 2005-01-12 at 14:59, MargaretGillon@chromalloy.com wrote: > > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into > > Postgresql 7.x on a Redhat Linux server. All is working well except for > > Memo fields, which are Text fields in PostgreSql. I query the Postgresql > > table and get a cursor, update the cursor with the data, and send it back. > > The contents of the memo field never make it back to the Postgresql text > > field. Other changes to the record's fields are saved with no problem. > > > > At this time all my UI's have to be written in VFP. Is there a work around > > for this or am I going to have to eliminate Text/Memo fields from my > > tables? > > Postgresql doesn't support updatable cursors. The updateable cursors are inside the FoxPro software. By using them I always get the table's current structure to build a UI on. Foxpro has an UpdateTable command that is used with the cursor. The command sends the data back with updates and inserts via ODBC. I have been doing this for a month now, and it's going fine, but today is the first time I tried to include data in the TEXT field. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
Josué Maldonado <josue@lamundial.hn> wrote on 01/12/2005 01:14:39 PM: > Hello, > > El 12/01/2005 2:59 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) into > > Postgresql 7.x on a Redhat Linux server. All is working well except for > > Memo fields, which are Text fields in PostgreSql. I query the Postgresql > > table and get a cursor, update the cursor with the data, and send it back. > > The contents of the memo field never make it back to the Postgresql text > > field. Other changes to the record's fields are saved with no problem. > > Are you using SPT or remote views? > > > At this time all my UI's have to be written in VFP. Is there a work around > > for this or am I going to have to eliminate Text/Memo fields from my > > tables? > > I use VFP8 against Postgresql with ODBC without any issue with the memo > fields. > > > -- > Sinceramente, > Josué Maldonado. > > "La TV es muy educativa. Cuando está encendida, me voy a otra habitación > y me pongo a leer un libro." --Groucho Marx. Hi Josué , I am using updatable cursors. I set up the cursor and set the cursor properties to updateable and set the key field property so it knows the key on the Postgresql table. The cursors are managed by a VFP class written by a programmer named Andy Kramek. He uses this class to manage updateable cursors in UI's for Oracle databases, I think on Unix servers. I have also used the class to talk to an SQLServer on Windows2000. The advantage with cursors is that I can pull down a set of 100-200 records, edit them, and send them back to the server and only one round trip has occured. Saves a lot of time when loading new tables, which is what I'm doing currently. I looked in the archives and there are messages about this problem but the fix for it was in the PGAdmin software. I didn't see anything that talked about the same problem / fix for ODBC. Regards, Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
On Wed, 2005-01-12 at 15:28, MargaretGillon@chromalloy.com wrote: > Scott Marlowe <smarlowe@g2switchworks.com> wrote on 01/12/2005 01:06:30 PM: > > > On Wed, 2005-01-12 at 14:59, MargaretGillon@chromalloy.com wrote: > > > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) > into > > > Postgresql 7.x on a Redhat Linux server. All is working well except for > > > Memo fields, which are Text fields in PostgreSql. I query the > Postgresql > > > table and get a cursor, update the cursor with the data, and send it > back. > > > The contents of the memo field never make it back to the Postgresql > text > > > field. Other changes to the record's fields are saved with no problem. > > > > > > At this time all my UI's have to be written in VFP. Is there a work > around > > > for this or am I going to have to eliminate Text/Memo fields from my > > > tables? > > > > Postgresql doesn't support updatable cursors. > > The updateable cursors are inside the FoxPro software. By using them I > always get the table's current structure to build a UI on. Foxpro has an > UpdateTable command that is used with the cursor. The command sends the > data back with updates and inserts via ODBC. I have been doing this for a > month now, and it's going fine, but today is the first time I tried to > include data in the TEXT field. Ahh, ok. Misunderstood what your method was. Is there a setting in your ODBC driver for memo as text or something like that? It's been a while since I played with pgsql from a windows / odbc box, so I am a bit rusty here. It's just a wild guess.
Scott Marlowe <smarlowe@g2switchworks.com> wrote on 01/12/2005 01:36:34 PM: > On Wed, 2005-01-12 at 15:28, MargaretGillon@chromalloy.com wrote: > > Scott Marlowe <smarlowe@g2switchworks.com> wrote on 01/12/2005 01:06:30 PM: > > > > > On Wed, 2005-01-12 at 14:59, MargaretGillon@chromalloy.com wrote: > > > > I am using ODBC to move Visual FoxPro7 tables (windows 2000 client) > > into > > > > Postgresql 7.x on a Redhat Linux server. All is working well except for > > > > Memo fields, which are Text fields in PostgreSql. I query the > > Postgresql > > > > table and get a cursor, update the cursor with the data, and send it > > back. > > > > The contents of the memo field never make it back to the Postgresql > > text > > > > field. Other changes to the record's fields are saved with no problem. > > > > > > > > At this time all my UI's have to be written in VFP. Is there a work > > around > > > > for this or am I going to have to eliminate Text/Memo fields from my > > > > tables? > > > > > > Postgresql doesn't support updatable cursors. > > > > The updateable cursors are inside the FoxPro software. By using them I > > always get the table's current structure to build a UI on. Foxpro has an > > UpdateTable command that is used with the cursor. The command sends the > > data back with updates and inserts via ODBC. I have been doing this for a > > month now, and it's going fine, but today is the first time I tried to > > include data in the TEXT field. > > > Ahh, ok. Misunderstood what your method was. > > Is there a setting in your ODBC driver for memo as text or something > like that? It's been a while since I played with pgsql from a windows / > odbc box, so I am a bit rusty here. It's just a wild guess. On my Windows box the Postgresql ODBC driver is set with TEXT AS LongVarChar, with a maximum length of 8190. FoxPro then turns the LongVarChar into a Memo. Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
El 12/01/2005 3:38 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > I am using updatable cursors. I set up the cursor and set the cursor > properties to updateable and set the key field property so it knows the key > on the Postgresql table. The cursors are managed by a VFP class written by > a programmer named Andy Kramek. He uses this class to manage updateable > cursors in UI's for Oracle databases, I think on Unix servers. I have also > used the class to talk to an SQLServer on Windows2000. > > The advantage with cursors is that I can pull down a set of 100-200 > records, edit them, and send them back to the server and only one round > trip has occured. Saves a lot of time when loading new tables, which is > what I'm doing currently. True, I also use SPT cursors in VFP and haven't had any issues with text /memo columns so far. > I looked in the archives and there are messages about this problem but the > fix for it was in the PGAdmin software. I didn't see anything that talked > about the same problem / fix for ODBC. How about ODBC version conflicts, I have Postgresql ODBC driver version 8, I got it downloading latest pgadmin 3 version. -- Sinceramente, Josué Maldonado. "El aspecto más triste de la vida actual es que la ciencia gana en conocimiento más rápidamente que la sociedad en sabiduría." -- Isaac Asimov
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 Josué Maldonado <josue@lamundial.hn> wrote on 01/12/2005 01:50:07 PM: > El 12/01/2005 3:38 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > > > I am using updatable cursors. I set up the cursor and set the cursor > > properties to updateable and set the key field property so it knows the key > > on the Postgresql table. The cursors are managed by a VFP class written by > > a programmer named Andy Kramek. He uses this class to manage updateable > > cursors in UI's for Oracle databases, I think on Unix servers. I have also > > used the class to talk to an SQLServer on Windows2000. > > > > The advantage with cursors is that I can pull down a set of 100-200 > > records, edit them, and send them back to the server and only one round > > trip has occured. Saves a lot of time when loading new tables, which is > > what I'm doing currently. > > True, I also use SPT cursors in VFP and haven't had any issues with text > /memo columns so far. > > > I looked in the archives and there are messages about this problem but the > > fix for it was in the PGAdmin software. I didn't see anything that talked > > about the same problem / fix for ODBC. > > How about ODBC version conflicts, I have Postgresql ODBC driver version > 8, I got it downloading latest pgadmin 3 version. > > > -- > Sinceramente, > Josué Maldonado. > > "El aspecto más triste de la vida actual es que la ciencia gana en > conocimiento más rápidamente que la sociedad en sabiduría." -- Isaac Asimov I just downloaded and installed the lastest PGADMIN III, but I didnt' get a new ODBC driver. The only file in the download was pgadmin3.msi. The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the newest driver I see listed on the Postgresql site. Where can I get the 8 version? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
El 12/01/2005 4:40 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > I just downloaded and installed the lastest PGADMIN III, but I didnt' get a > new ODBC driver. The only file in the download was pgadmin3.msi. > > The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the > newest driver I see listed on the Postgresql site. Where can I get the 8 > version? I believe odbc 8 is win32 server install http://pgfoundry.org/projects/pginstaller/ -- Sinceramente, Josué Maldonado. "Toda ciencia viene del dolor. El dolor busca siempre la causa de las cosas, mientras que el bienestar se inclina a estar quieto y a no volver la mirada atrás." Stefan Zweig. Escritor austríaco.
> El 12/01/2005 4:40 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > > I just downloaded and installed the lastest PGADMIN III, but I didnt' get a > > new ODBC driver. The only file in the download was pgadmin3.msi. > > > > The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the > > newest driver I see listed on the Postgresql site. Where can I get the 8 > > version? > > I believe odbc 8 is win32 server install > http://pgfoundry.org/projects/pginstaller/ > > > -- > Sinceramente, > Josué Maldonado. > > "Toda ciencia viene del dolor. El dolor busca siempre la causa de las > cosas, mientras que el bienestar se inclina a estar quieto y a no volver > la mirada atrás." Stefan Zweig. Escritor austríaco. Josue, Are you using Postgresql on a Windows Server or on a Linux Server? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
The date on the ODBC driver I have is 10/8/2004. Running on WinXP Pro. -----Original Message----- From: MargaretGillon@chromalloy.com [mailto:MargaretGillon@chromalloy.com] Sent: Wednesday, January 12, 2005 5:23 PM To: Josué Maldonado Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and ODBC > El 12/01/2005 4:40 PM, MargaretGillon@chromalloy.com en su mensaje escribio: > > I just downloaded and installed the lastest PGADMIN III, but I didnt' get a > > new ODBC driver. The only file in the download was pgadmin3.msi. > > > > The driver I have is ODBC version 7.03.02.00 dated 10-22-2003. That is the > > newest driver I see listed on the Postgresql site. Where can I get the 8 > > version? > > I believe odbc 8 is win32 server install > http://pgfoundry.org/projects/pginstaller/ > > > -- > Sinceramente, > Josué Maldonado. > > "Toda ciencia viene del dolor. El dolor busca siempre la causa de las > cosas, mientras que el bienestar se inclina a estar quieto y a no volver > la mirada atrás." Stefan Zweig. Escritor austríaco. Josue, Are you using Postgresql on a Windows Server or on a Linux Server? Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Margaret, > Josue, > Are you using Postgresql on a Windows Server or on a Linux Server? > Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 Both platforms, I'm developing with VFP for Postgresql 7.4.3 running on RH Linux 8 and also work in another app for Postgresql 8.0 RC4 win32. I guess your problem has something to do with ODBC settings or something could be wrong on the VFP side, but I'm just guessing. -- Sinceramente, Josué Maldonado. "Toda la felicidad que la humanidad puede alcanzar, está, no en el placer, sino en el descanso del dolor." John Dryden. Poeta, dramaturgo y critico inglés.
>>Ahh, ok. Misunderstood what your method was.
>>
>>Is there a setting in your ODBC driver for memo as text or something
>>like that? It's been a while since I played with pgsql from a windows /
>>odbc box, so I am a bit rusty here. It's just a wild guess.
>>
>>
>
>On my Windows box the Postgresql ODBC driver is set with TEXT AS
>LongVarChar, with a maximum length of 8190. FoxPro then turns the
>LongVarChar into a Memo.
>
>Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
>
>
>
Margaret,
I haven't tried using text, but I came across a very similar problem
when using VARCHAR(n) columns with foxpro:
Since foxpro supports only CHAR (not VARCHAR), then if you have a table
like this:
create table foo (t varchar(6));
insert into foo (t) values ('aaa');
And, in foxpro, you do this to an updateable view of the table:
replace t with 'bbbbbbb'
Then the SQL that foxpro actually generates looks like this:
UPDATE foo SET t = 'bbbbbbb' WHERE t = 'aaa ';
Notice the WHERE clause says t = 'aaa<space><space><space>'; Trailing
whitespace is significant for varchar columns, and so the update hits no
rows.
Now, if foxpro truly thinks that your text columns are of type MEMO then
I believe it would generate the correct SQL; however, if you have your
ODBC driver set to turn text into VARCHAR(n) columns, then I believe
you're being bit by the same bug that I was: foxpro is probably padding
your value with 8000 or so blanks because it thinks your text column is
varchar(8192).
How do you figure this out? Use ethereal.
http://www.ethereal.com/
Paul Tillotson