Memo feilds, MS Access, ODBC and PostgreSQL - Mailing list pgsql-interfaces

I cannot get MS Access 97 to recognize any memo field in a linked table
of type text with more than 255 characters.  MS access thinks the fields
are memo, and I have tried many different combinations. All attempts to
read the fields fail with ODBC -- call failed.  Family Data is a table
in the database, and notes it the text (memo) field with more than 255
characters (inc new lines) in it.

This database (on PostgreSQL 6.5.3 running under a modified Mandrake 6.0
and kernel 2.2.14pre12) was imported with a utility I have written to
transfer MS access databases into interfaces to the postgreSQL back end
(using connections and passing SQL for execution).  I will publish this
if anybody is interested.  

Andrew Bartlett
abartlet@pcug.org.au

Below: psqlodbc log file

conn=50727036, SQLDriverConnect(

in)='DRIVER={PostgreSQL};UID=Admin;PWD=;DATABASE=dataoptsql;SERVER=database;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=0
Global Options: Version='06.40.0006', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=1               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0               extra_systable_prefixes='dd_;', conn_settings=''
conn=50727036, query=' '
ERROR from backend during send_query: 'FATAL 1:  SetUserId: user 'Admin'
is not in 'pg_shadow''
CONN ERROR: func=SQLDriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'           ------------------------------------------------------------
henv=51773572,conn=50727036, status=0, num_stmts=16           sock=51773588, stmts=51773628, lobj_type=-999
----------------Socket Info -------------------------------           socket=256, reverse=0, errornumber=0,
errormsg='(null)'          buffer_in=50733364, buffer_out=50737464           buffer_filled_in=58, buffer_filled_out=0,
buffer_read_in=58
conn=50727036, SQLDriverConnect(

in)='DRIVER={PostgreSQL};DATABASE=dataoptsql;SERVER=database;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=3
Global Options: Version='06.40.0006', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=1               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0               extra_systable_prefixes='dd_;', conn_settings=''
conn=50727036, query=' '
conn=50727036, query='set DateStyle to 'ISO''
conn=50727036, query='set geqo to 'OFF''
conn=50727036, query='set ksqo to 'ON''
conn=50727036, query='BEGIN'
conn=50727036, query='declare SQL_CUR0306413C cursor for select oid from
pg_type where typname='lo''
conn=50727036, query='fetch 100 in SQL_CUR0306413C'   [ fetched 1 rows ]   [ Large Object oid = 480267 ]
conn=50727036, query='close SQL_CUR0306413C'
conn=50727036, query='END'
conn=50727036,

SQLDriverConnect(out)='DRIVER={PostgreSQL};DATABASE=dataoptsql;SERVER=database;PORT=5432;UID=abartlet;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=50727036, query='BEGIN'
conn=50727036, query='declare SQL_CUR0306413C cursor for SELECT Config,
nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR:  msysconf: Table does not
exist.'
conn=50727036, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'               
------------------------------------------------------------                hdbc=50727036, stmt=50741564, result=0
         manual_result=0, prepare=0, internal=0                bindings=0, bindings_allocated=0
parameters=0,parameters_allocated=0                statement_type=0, statement='SELECT Config, nValue FROM
 
MSysConf'                stmt_with_params='declare SQL_CUR0306413C cursor for
SELECT Config, nValue FROM MSysConf'                data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1,current_col=-1, lobj_fd=-1                maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
 
scroll_concurrency=1                cursor_name='SQL_CUR0306413C'                ----------------QResult Info
-------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: 
msysconf: Table does not exist.'           ------------------------------------------------------------
henv=51773588,conn=50727036, status=1, num_stmts=16           sock=51773604, stmts=51773780, lobj_type=480267
----------------Socket Info -------------------------------           socket=256, reverse=0, errornumber=0,
errormsg='(null)'          buffer_in=50733364, buffer_out=50737464           buffer_filled_in=3, buffer_filled_out=0,
buffer_read_in=2
conn=50727036, query='BEGIN'
conn=50727036, query='declare SQL_CUR0306413C cursor for SELECT "Family
Data"."Family ID" FROM "Family Data" '
conn=50727036, query='fetch 100 in SQL_CUR0306413C'   [ fetched 100 rows ]
conn=50727036, query='fetch 100 in SQL_CUR0306413C'   [ fetched 100 rows ]
conn=50727036, query='fetch 100 in SQL_CUR0306413C'   [ fetched 34 rows ]
conn=50810544, SQLDriverConnect(

in)='DRIVER={PostgreSQL};UID=abartlet;PWD=;DATABASE=dataoptsql;SERVER=database;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=0
Global Options: Version='06.40.0006', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=1               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0               extra_systable_prefixes='dd_;', conn_settings=''
conn=50810544, query=' '
conn=50810544, query='set DateStyle to 'ISO''
conn=50810544, query='set geqo to 'OFF''
conn=50810544, query='set ksqo to 'ON''
conn=50810544, query='BEGIN'
conn=50810544, query='declare SQL_CUR030787DC cursor for select oid from
pg_type where typname='lo''
conn=50810544, query='fetch 100 in SQL_CUR030787DC'   [ fetched 1 rows ]   [ Large Object oid = 480267 ]
conn=50810544, query='close SQL_CUR030787DC'
conn=50810544, query='END'
conn=50810544,

SQLDriverConnect(out)='DRIVER={PostgreSQL};DATABASE=dataoptsql;SERVER=database;PORT=5432;UID=abartlet;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=50810544, query='BEGIN'
conn=50810544, query='declare SQL_CUR030787DC cursor for SELECT "Family
ID","Home Postal Address 1","Home Postal Address 2","Home Postal Suburb
ID","Home Postal Suburb","Home Postal State",'#S_C_H#' Postal Post
Code","Home Postal Country","Home Address 1","Home Address 2","Home
Suburb ID","Home Suburb","Home State","Home Post Code","Home
Country","Home Similar","Medicare No","Home Phone Area Code","Home Phone
No","Home Phone Note","Notes","Exists","Created By","Created Time"  FROM
"Family Data"  WHERE "Family ID" = 2 OR "Family ID" = 3 OR "Family ID" =
5 OR "Family ID" = 6 OR "Family ID" = 7 OR "Family ID" = 8 OR "Family
ID" = 9 OR "Family ID" = 10 OR "Family ID" = 11 OR "Family ID" = 12'
ERROR from backend during send_query: 'ERROR:  parser: parse error at or
near "postal"'
conn=50810544, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'               
------------------------------------------------------------                hdbc=50810544, stmt=50825180, result=0
         manual_result=0, prepare=1, internal=0                bindings=0, bindings_allocated=0
parameters=50893028,parameters_allocated=10                statement_type=0, statement='SELECT "Family ID","Home 
Postal Address 1","Home Postal Address 2","Home Postal Suburb ID","Home
Postal Suburb","Home Postal State",'#S_C_H#' Postal Post Code","Home
Postal Country","Home Address 1","Home Address 2","Home Suburb ID","Home
Suburb","Home State","Home Post Code","Home Country","Home
Similar","Medicare No","Home Phone Area Code","Home Phone No","Home
Phone Note","Notes","Exists","Created By","Created Time"  FROM "Family
Data"  WHERE "Family ID" = ? OR "Family ID" = ? OR "Family ID" = ? OR
"Family ID" = ? OR "Family ID" = ? OR "Family ID" = ? OR "Family ID" = ?
OR "Family ID" = ? OR "Family ID" = ? OR "Family ID" = ?'                stmt_with_params='declare SQL_CUR030787DC
cursorfor
 
SELECT "Family ID","Home Postal Address 1","Home Postal Address 2","Home
Postal Suburb ID","Home Postal Suburb","Home Postal State",'#S_C_H#'
Postal Post Code","Home Postal Country","Home Address 1","Home Address
2","Home Suburb ID","Home Suburb","Home State","Home Post Code","Home
Country","Home Similar","Medicare No","Home Phone Area Code","Home Phone
No","Home Phone Note","Notes","Exists","Created By","Created Time"  FROM
"Family Data"  WHERE "Family ID" = 2 OR "Family ID" = 3 OR "Family ID" =
5 OR "Family ID" = 6 OR "Family ID" = 7 OR "Family ID" = 8 OR "Family
ID" = 9 OR "Family ID" = 10 OR "Family ID" = 11 OR "Family ID" = 12'                data_at_exec=-1,
current_exec_param=-1,put_data=0                currTuple=-1, current_col=-1, lobj_fd=-1                maxRows=0,
rowset_size=1,keyset_size=0, cursor_type=0,
 
scroll_concurrency=1                cursor_name='SQL_CUR030787DC'                ----------------QResult Info
-------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: 
parser: parse error at or near "postal"'           ------------------------------------------------------------
 henv=51773588, conn=50810544, status=1, num_stmts=16           sock=50816872, stmts=50825112, lobj_type=480267
 ---------------- Socket Info -------------------------------           socket=260, reverse=0, errornumber=0,
errormsg='(null)'          buffer_in=50816912, buffer_out=50821012           buffer_filled_in=3, buffer_filled_out=0,
buffer_read_in=2
conn=50727036, query='close SQL_CUR0306413C'
conn=50727036, query='END'
conn=50810544, SQLDisconnect
conn=50727036, SQLDisconnect