I've was having problems creating a remote view in Foxpro 3.0.
When I attempted to create a new remote view I entered the information
needed to make the connection such as database, server, port, user name and
password. The view designer then opens and I'm presented with an open
dialogue displaying the sole table in the database I indicated.
Everything's fine up to this point. When I selected the table I get the
message:
Connectivity error: Error while executing the query. ERROR: parser: parse
error at or near "."
And then I'm returned to an empty view designer.
After a little more playing around I discovered that selecting the Include
System Tables check box in the open dialogue allows the selection of the
desired table without the error and I'm able to complete the creation of the
remote view.
Reviewing the odbc driver CommLog created during the error condition
indicates that the offending query is:
conn=95289464, query='declare SQL_CUR95303956 cursor for SELECT * FROM
mds.medical_bills WHERE 1=0'
ERROR from backend during send_query: 'ERROR: parser: parse error at or
near "."'
mds is the username for the connection and medical_bills is the selected
table. The SELECT statement also produces the error in psql.
The question is where and why is the user name "mds" being added as a prefix
to the table name "medical_bills" -- is this something related to the odbc
driver or do I need to look elsewhere?
Thanks in advance!
The more specific information follows:
Server:
i486
Linux 2.0.0
PostgreSQL 6.3.2
Client:
Pentium
Win95
psqlodbc 6.30.0249
Visual FoxPro 3.0
CommLog creating the error:
CONN ERROR: func=SQLSetConnectOption, desc='fOption=112, vParam=4096',
errnum=5, errmsg='Driver does not support setting this connect option'
------------------------------------------------------------
henv=96341980, conn=95289464, status=0, num_stmts=16
sock=96338052, stmts=96338092, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=95295756, buffer_out=95299856
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=95289464, SQLDriverConnect( in)='DSN=PostgreSQL ODBCtest;UID=;PWD=;',
fDriverCompletion=1
DSN info: DSN='PostgreSQL
ODBCtest',server='alpha',port='5432',dbase='odbctest',user='',passwd=''
readonly='0',protocol='6.2',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
conn=95289464, SQLDriverConnect(out)='DSN=PostgreSQL
ODBCtest;DATABASE=odbctest;SERVER=alpha;PORT=5432;UID=mds;PWD=*deleted*;READ
ONLY=0;PROTOCOL=6.2;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTE
MTABLES=0;CONNSETTINGS='
Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=254, max_longvarchar_size=4094
disable_optimizer=0, unique_index=0, use_declarefetch=1
text_as_longvarchar=0, unknowns_as_longvarchar=0,
bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn=95289464, query=' '
conn=95289464, query='set DateStyle to 'ISO''
conn=95289464, query='BEGIN'
conn=95289464, query='declare SQL_CUR95303956 cursor for select oid from
pg_type where typname='lo''
conn=95289464, query='fetch 100 in SQL_CUR95303956'
[ fetched 0 rows ]
conn=95289464, query='close SQL_CUR95303956'
conn=95289464, query='END'
STATEMENT ERROR: func=SQLSetStmtOption, desc='fOption=4, vParam=0',
errnum=10, errmsg='Driver does not support setting this statement option'
-----------------------------------------------------------
-
hdbc=95289464, stmt=95303956, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=-2, statement='(null)'
stmt_with_params=''
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=''
----------------QResult
Info -------------------------------
CONN ERROR: func=SQLSetStmtOption, desc='fOption=4, vParam=0', errnum=0,
errmsg=''
------------------------------------------------------------
henv=96341980, conn=95289464, status=1, num_stmts=16
sock=96338052, stmts=96338092, lobj_type=-999
---------------- Socket Info -------------------------------
socket=37, reverse=1, errornumber=0, errormsg='(null)'
buffer_in=95295756, buffer_out=95299856
buffer_filled_in=2, buffer_filled_out=0, buffer_read_in=2
conn=95289464, query='BEGIN'
conn=95289464, query='declare SQL_CUR95371004 cursor for select relname,
usename, relhasrules from pg_class, pg_user where relkind = 'r' and usename
like 'mds' and relname !~ '^pg_|^dd_' and relname !~ '^xinv[0-9]+' and
int4out(usesysid) = int4out(relowner) order by relname'
conn=95289464, query='fetch 100 in SQL_CUR95371004'
[ fetched 1 rows ]
conn=95289464, query='close SQL_CUR95371004'
conn=95289464, query='END'
conn=95289464, query='BEGIN'
conn=95289464, query='declare SQL_CUR95303956 cursor for SELECT * FROM
mds.medical_bills WHERE 1=0'
ERROR from backend during send_query: 'ERROR: parser: parse error at or
near "."'
conn=95289464, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'
-----------------------------------------------------------
-
hdbc=95289464, stmt=95303956, result=0
manual_result=0, prepare=0, internal=0
bindings=95370520, bindings_allocated=5
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT * FROM
mds.medical_bills WHERE 1=0'
stmt_with_params='declare SQL_CUR95303956 cursor for SELECT
* FROM mds.medical_bills WHERE 1=0'
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_CUR95303956'
----------------QResult
Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=10, errmsg='ERROR: parser:
parse error at or near "."'
------------------------------------------------------------
henv=96341980, conn=95289464, status=1, num_stmts=16
sock=96338052, stmts=96338092, lobj_type=-999
---------------- Socket Info -------------------------------
socket=37, reverse=1, errornumber=0, errormsg='(null)'
buffer_in=95295756, buffer_out=95299856
buffer_filled_in=2, buffer_filled_out=0, buffer_read_in=2
conn=95289464, SQLDisconnect
CommLog not creating an error:
CONN ERROR: func=SQLSetConnectOption, desc='fOption=112, vParam=4096',
errnum=5, errmsg='Driver does not support setting this connect option'
------------------------------------------------------------
henv=96341980, conn=95289464, status=0, num_stmts=16
sock=96338052, stmts=96338092, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=95295756, buffer_out=95299856
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=95289464, SQLDriverConnect( in)='DSN=PostgreSQL ODBCtest;UID=;PWD=;',
fDriverCompletion=1
DSN info: DSN='PostgreSQL
ODBCtest',server='alpha',port='5432',dbase='odbctest',user='',passwd=''
readonly='0',protocol='6.2',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
conn=95289464, SQLDriverConnect(out)='DSN=PostgreSQL
ODBCtest;DATABASE=odbctest;SERVER=alpha;PORT=5432;UID=mds;PWD=*deleted*;READ
ONLY=0;PROTOCOL=6.2;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTE
MTABLES=0;CONNSETTINGS='
Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=254, max_longvarchar_size=4094
disable_optimizer=0, unique_index=0, use_declarefetch=1
text_as_longvarchar=0, unknowns_as_longvarchar=0,
bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn=95289464, query=' '
conn=95289464, query='set DateStyle to 'ISO''
conn=95289464, query='BEGIN'
conn=95289464, query='declare SQL_CUR95303956 cursor for select oid from
pg_type where typname='lo''
conn=95289464, query='fetch 100 in SQL_CUR95303956'
[ fetched 0 rows ]
conn=95289464, query='close SQL_CUR95303956'
conn=95289464, query='END'
STATEMENT ERROR: func=SQLSetStmtOption, desc='fOption=4, vParam=0',
errnum=10, errmsg='Driver does not support setting this statement option'
-----------------------------------------------------------
-
hdbc=95289464, stmt=95303956, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=-2, statement='(null)'
stmt_with_params=''
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=''
----------------QResult
Info -------------------------------
CONN ERROR: func=SQLSetStmtOption, desc='fOption=4, vParam=0', errnum=0,
errmsg=''
------------------------------------------------------------
henv=96341980, conn=95289464, status=1, num_stmts=16
sock=96338052, stmts=96338092, lobj_type=-999
---------------- Socket Info -------------------------------
socket=35, reverse=1, errornumber=0, errormsg='(null)'
buffer_in=95295756, buffer_out=95299856
buffer_filled_in=2, buffer_filled_out=0, buffer_read_in=2
conn=95289464, query='BEGIN'
conn=95289464, query='declare SQL_CUR95303956 cursor for SELECT * FROM
medical_bills Medical_bills'
conn=95289464, query='fetch 100 in SQL_CUR95303956'
[ fetched 12 rows ]
conn=95289464, query='close SQL_CUR95303956'
conn=95289464, query='END'
conn=95289464, SQLDisconnect
