Postgresql Text field / Visual FoxPro Memo and ODBC

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

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.

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

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

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

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