Reading from PostgreSQL via ODBC... 1

[thatguy]

Hello--

I wrote an app in VFP8 that reads data from another app's database (Advantage). Recently, tho, that app has changed its backend to PostgreSQL, so I need to update my VFP app to read from PostgreSQL.

I've installed the psqlODBC (8.2.4) driver and can connect and read from the database. The trouble I'm having is that every text field is coming across as a memo, which is screwing up my ORDER BYs, etc..

I'm wondering if anyone else around here has dealt with PostgreSQL and had/fixed this behavior.

Thanks.

 

[Mike Lewis]

You don't say how you are accessing the database. Are you using remote views, or SQL pass-through, or cursor adapters, or ADO, or what?

Also, when you say that "every text field is coming across as a memo", are you referring to fields in the database whose data type is Text, or are you simply using the word "text" as a synonym for character data?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndyKr]

>>The trouble I'm having is that every text field is coming across as a memo, which is screwing up my ORDER BYs, etc..

Generally speaking, VFP will create Memo fields for character strings when their defined size exceeds 254 characters. You can set the limit lower, but there is no way to increase it because that is the maximum size for character data columns.

You can use SQLTABLES() with the "NATIVE" format to see how these columns are actually defined but if they are set up as something larger than 254 characters (like VARCHAR(2000) for example) you are stuck I am afraid. The only solution I know of is to post-process the result set and test the actual length of the data.

----
Andy Kramek
Visual FoxPro MVP

 

[thatguy]

Thanks AndyKR. SQLColumns() showed what you described.. The fields were defined as simply "text" with no precision/length specified, but they come across to VFP as being 8190 chars wide.

So you're saying that there's no way to limit the chars that come thru? My only option is to 1) read the data thru SQLEXEC(); 2) create my own cursor with the same field names "AS space(254)" or "AS 0" or "AS date()"; then 3) read the values from the SQL table into the new cursor?

If that's the only option, then fine, but if there's another way.......

Thanks.

 

[thatguy]

Well that wasn't so tough..

SQLEXEC(cxnhandle,sql,tempcursor)
CREATE CURSOR realcursor (;
   field1 b,;
   field2 c(254),;
   field3 c(254)...)
INSERT INTO realcursor ;
   SELECT field1,allt(field2),allt(field3)... ;
   FROM tempcursor

This is my solution. Thanks again AndyKR.

 

[AndyKr]

Glad I could help. I think the TEXT column in Postgres SQL is actually a "Memo" field anyway (it certainly is in SQL Server) so probably the VFP mapping is correct in this case.

----
Andy Kramek
Visual FoxPro MVP