Xojo will not read PostgreSQL in secondary schema

Databases
1

I have a postgre database with 2 schemas. The first schema is ‘public’ and the second is ‘user’. The tables in the public schema were created by OpenERP. The table in the user schema were created buy me.

If I use the gui to connect to the database I can only read tables with no capitals in the field or table names.

If I connect through code it only reads tables on the first schema, even though I have specified schema in recordset.

Has anyone got a solution for this?

2

[quote=82039:@Trevor Campbell]I have a postgre database with 2 schemas. The first schema is ‘public’ and the second is ‘user’. The tables in the public schema were created by OpenERP. The table in the user schema were created buy me.
If I use the gui to connect to the database I can only read tables with no capitals in the field or table names.
[/quote]
PostgreSQL prefers tables etc with strictly lower case names
You can change that in its config

What permissions & roles have you got assigned ?
I’d hazard a guess that has bearing on this - especially if they are created by 2 different owners

3

I have checked the permissions. They have the same owner. I thought that would be the problem too.

4

well I just created this on my local postgres with the same owner and it worked just fine

is there an error in the DB object when you try to do this ?

5

I get no error it just doesn’t fill my listbox. here is the code I am using.

Dim rs As RecordSet
rs = db.SQLSelect("SELECT * FROM user.TblTrans")
If rs <> Nil Then
  While Not rs.EOF
    
    ListBox1.AddRow(rs.Field("TransID").StringValue, rs.Field("TransName").StringValue, rs.Field("Email").StringValue)
    rs.MoveNext
  Wend
  msgbox("ok")
  rs.Close
Else
  // Check if there was an error
  If db.Error Then
    MsgBox(db.ErrorMessage)
  End If
End If

Else
// DB Connection error
MsgBox(db.ErrorMessage)
End If

6

You are currently running Postgres 8.4.12

7

9.2

8

and user.TblTrans definitely has data in it ?

9

Yes, it has data in it. I just created a new database and created the 2 schemas and a table in each schema with 2 records in it. The public schema reads, but I can’t get the user schema to read. So the problem seems to be -
1 I can only read the defaul schema.
2 It will not work with capital letters. (So you think this can be changed in the config of Postgre?)

10

The only way I’m able to get this to NOT work is to have the two schemas with different owners and the owner of one is not permitted to access the other

But then in the db object I get an error like “ERROR: permission denied for relation ….”

11

Could it be the version of Postgre that we are using (8.4.12)? Also, I am using a trial version of Xojo 2014 r1.

I really want this to work so I can buy it.

12

I doubt the version of postgres is the issue
8.4 supports cross schema queries fine
And the version of Xojo is fine as it lets you try everything out before you buy :stuck_out_tongue:
Obviously you have the Postgresql plugin installed or things would not run at all

What tools have you got that can dump out the sql that was used to create the new schema & tables within them

13

This solved it. Both schema and the capital Letter issue.

rs = db.SQLSelect("SELECT * FROM " + chr(34) + "user" + chr(34) + "." + chr(34) + "TblTrans" + chr(34))

It seems that the capital letters and schemas have to be in quotes.

14

You can write the quote right inline by doubling up the quotes

rs = db.SQLSelect(“SELECT * FROM ““user””.”“TblTrans”")

15

Thanks. That makes it easier.

16

I realize this is an older thread, but you can also change the search_path in postgresql for your session.

pg’s search_path is similar to windows path environment variable; it is used to tell postgresql which schema (or schemas) to search, (and in which order to search) for the database objects in your queries. It can be set for all connections or by user in your database configuration, or for each session by issuing a SET SQL statement after connecting to the database.

For the latter, immediately after you connect to the pg database, issue the following SQL statement:

SET search_path TO my_schema, public;

This causes postgreSQL to first search for the database objects in my_schema, and if not found, in public.

You can also include the specific schema name explicitly as a qualifier in your SQL statement; i.e.

Select foo, bar from my_schema.my_table

Since schemas in postgresql are equivalent to namespaces in various programming languages, I’m actually very surprised that Xojo did not include a .schema property in their PostgreSQLDatabase object, especially as implementing it would be as simple as fetching the current search path, inserting the named schema string property at the beginning of the search path string, then issuing the SET statement, per above, once each time a connection is established.