Postgresql Text field / Visual FoxPro Memo and ODBC

[M***@chromalloy.com]

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Scott Marlowe]

Post by M***@chromalloy.com
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[M***@chromalloy.com]

Post by Scott Marlowe

Post by M***@chromalloy.com
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


---------------------------(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

[Scott Marlowe]

Post by M***@chromalloy.com

Post by Scott Marlowe

Post by M***@chromalloy.com
I am using ODBC to move Visual FoxPro7 tables (windows 2000 client)

into

Post by Scott Marlowe

Post by M***@chromalloy.com
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

Post by Scott Marlowe

Post by M***@chromalloy.com
table and get a cursor, update the cursor with the data, and send it

back.

Post by Scott Marlowe

Post by M***@chromalloy.com
The contents of the memo field never make it back to the Postgresql

text

Post by Scott Marlowe

Post by M***@chromalloy.com
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

Post by Scott Marlowe

Post by M***@chromalloy.com
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[M***@chromalloy.com]

Post by Scott Marlowe

Post by M***@chromalloy.com

Post by Scott Marlowe

Post by M***@chromalloy.com
I am using ODBC to move Visual FoxPro7 tables (windows 2000 client)

into

Post by Scott Marlowe

Post by M***@chromalloy.com
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

Post by Scott Marlowe

Post by M***@chromalloy.com
table and get a cursor, update the cursor with the data, and send it

back.

Post by Scott Marlowe

Post by M***@chromalloy.com
The contents of the memo field never make it back to the Postgresql

text

Post by Scott Marlowe

Post by M***@chromalloy.com
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

Post by Scott Marlowe

Post by M***@chromalloy.com
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Paul Tillotson]

Post by M***@chromalloy.com

Post by Scott Marlowe
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Josué Maldonado]

Hello,

Post by M***@chromalloy.com
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?

Post by M***@chromalloy.com
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.

---------------------------(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

[M***@chromalloy.com]

Post by Josué Maldonado
Hello,

Post by M***@chromalloy.com
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?

Post by M***@chromalloy.com
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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Josué Maldonado]

Post by M***@chromalloy.com
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.

Post by M***@chromalloy.com
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[M***@chromalloy.com]

Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

Post by Josué Maldonado

Post by M***@chromalloy.com
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.

Post by M***@chromalloy.com
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Josué Maldonado]

Post by M***@chromalloy.com
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[M***@chromalloy.com]

Post by Josué Maldonado

Post by M***@chromalloy.com
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

[Josué Maldonado]

Margaret,

Post by M***@chromalloy.com
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Wilson, David]

The date on the ODBC driver I have is 10/8/2004. Running on WinXP Pro.

-----Original Message-----
From: ***@chromalloy.com
[mailto:***@chromalloy.com]
Sent: Wednesday, January 12, 2005 5:23 PM
To: Josué Maldonado
Cc: pgsql-***@postgresql.org
Subject: Re: [GENERAL] Postgresql Text field / Visual FoxPro Memo and
ODBC

Post by Josué Maldonado

Post by M***@chromalloy.com
I just downloaded and installed the lastest PGADMIN III, but I didnt'

get a

Post by Josué Maldonado

Post by M***@chromalloy.com
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

Post by Josué Maldonado

Post by M***@chromalloy.com
newest driver I see listed on the Postgresql site. Where can I get the

8

Post by Josué Maldonado

Post by M***@chromalloy.com
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html