POSTGRE - table->Open() Topic is solved

[rosswg]

There is an existing POSTGRE data base.
I can connect.
I can access all the table names and list them.
I can access all the column names and list them with their details.
I now want to open a table.
Using one of the exising table names it has 58 columns.
I used setcoldefs for two of the column names. ( primary key).
I then tried to open the table and got a false return.
'Table/View does not exist in the data base ',error.
I then put in a (db) TableExists("tablename","schema")
-- and this is where the problem seems to start.
a TableExists ("tablename") gives a false return.
a TableExists ("tablename","schema") gives a true return.
I then put in a TablePrivileges(dbo.tablename,"SELECT") - this gave a false return;
but a TablePrivileges,"SELECT","user_id") gave a true return;
and a TablePrivileges,"SELECT","schema") gave a true return;
and a tTablePrivileges,"SELECT","user_id","schema") gave a true return;
but a TablePrivileges,"SELECT","schema","user_id") gave a false return;(documentation says user_id should be first);

however the table->Open() always gives a false return except as follows table->(false,false) where a true return is given.

Unless I can get the table->Open() to work, I cannot use the table, but it would seem as if a "schema" name is required.

The underlying Postgre data base is set up as follows
Database db1
schema s1
table t1
table t1 ...
schema s2
table x1 ..
table x2 ..
---------
The documentation says that one does not have to setcoldefs for all the columns, only the ones that are going to be used.
---
Can anyone tell me where my error could possible be.

[tbreina]

Well, this forum isn't correct for your question, but I happen to have a lot of experience with PostgreSQL dbs.

You should be looking at the postgresql.log file. It will most likely give you more detailed explanations of why your queries are failing. The log file is really the best way to diagnose the problem.

-Tony

[rosswg]

Interesting thought. Never considered that approach.
It was my thought that it is a wx problem.
Postgres as I understand it
has a dns
one or more databases ( with user-id and password)
each data base having one or more schemas
and each schema having one or more tables etc...

wx - does not provide for schema name when defining a table.
wx does provide for schema however when checking to see if the
table exists.
so TableExists(tablename,schemaname) finds the table but
table->Open() - responds with 'table does not exist'
I know the table exists, because I can access it with PGADMIN III.
I know I have opened the connection - because I can find the table using TableExists.
On connecting - wx will only show you those tables you have access rights to.

[rosswg]

Thank you -
I used the error log information available in Postgre.
It told me that
1. One of the 'sql -exec' was incomplete and
2. That my program was actively not accepting the connection.
----------------
So I started again starting the connection from the first line of code, then checking the error-log.
I found the following error.
That I was not closing the connection - and deleting the
'new' db. connection and also any 'new' tables.
Once I had done this I was able to access the tables as I wanted to.
----------------
Thankyou for helping me to find a place where to look.
It is really appreciated.

[tbreina]

Thank you -
I used the error log information available in Postgre.
----------------
Thankyou for helping me to find a place where to look.
It is really appreciated.

I'm glad you found the problem. Thanks for posting the solution too. It will probably help someone else down the road. The PostgreSQL log file has always steered me in the right direction.

-Tony

[rosswg]

Finally found the root cause of the problem.

UNDER POSTGRES YOU MUST USE THE EXECSQL COMMAND TO SET THE DEFAULT SCHEMA. IF YOU DO NOT DO THIS. YOU WILL NOT BE ABLE TO OPEN A TABLE.

It had to be something simple and easy.

[danielch]

If you don't find a schema in your query...
remember...
If you are logged as user studentA you see object from your schema (studentA), and next from public

Look at this in Postgresql config file:

#search_path = '$user,public' # schema names

File:postgresql.conf
Regards,
Daniel

[rosswg]

Thank you for the thought. I will still get back to you on it.
It may be possible that if I get the catalog with the options on the schema increased to include more than one schema then I will be able to find more tables.

HOWEVER, I cannot see how to open a table from one schema and then another from another schema, because the open command/function in wx, does not provide for a schema name only a table name.

The command/function to see if the table exists under wx does provide for a SCHEMA name. It would seem as if this provision for if table exists should also be in table open!