PostgreSQL and FieldSchema

Has anyone else had issues with using FieldSchema with PostgreSQL databases? I’m getting a valid list of table names but then when I call FieldSchema I get a valid recordset back with zero fields.

I don’t see anything in Feedback, but sometimes it’s hard to tell.

Is it always reporting NIL in the debugger ?

  dim pg as new PostgreSQLDatabase
  
  pg.Host = "localhost"
  pg.Port = 5432
  pg.DatabaseName = ... insert your db name here ...
  pg.UserName = ... insert your db username here ...
  pg.Password = ... insert your db password here ...
  
  if pg.Connect then
    
    dim rs as recordset = pg.TableSchema()
    
    if rs = nil then 
      break
      return
    end if
    
    while rs.EOF <>  true
      
      dim flds as recordset = pg.FieldSchema( rs.Field("TableName").StringValue )
      
      if flds = nil then
        break
        return
      end if
      
      while flds.eof <> true
        
        dim fldname as string = flds.Field("ColumnName").StringValue
        
        system.debuglog fldName // <<<<<<< put a break here & look at flds and fldname
        
        flds.MoveNext
      wend
      
      rs.MoveNext
      
    wend
    
  end if

Just did this with 2016r2.1 using latest plugin & pointed at a postgresql 9.3 db

It’s not reporting nil for me, the recordset just has no records in it. Given your example above, flds is NOT nil but EOF and BOF are true so nothing is processed.

I’ve created a simple feedback report at feedback://showreport?report_id=45023

I remember having some issues like this, and the need to get more informations than what Fieldschema was returning.
so I asked postgres directly about his schema like this, at the beginning of the app to store it localy in app properties.
this method is in a database subclass. “Alerte” method is like msgbox.

[code]Private Sub postgresLoadFieldSchema(forceReload as Boolean = False, schemaName as String=“public”)
’ --------------------------------------------------------------------------------
’ lire toutes les fields d’une base PostgreSQL en direct
’ --------------------------------------------------------------------------------

dim ch as String
dim rs2 as RecordSet

if UBound(postgresField_table_name)<0 or forceReload then
’ liste toutes les fields de la base et les stocke localement une seule fois
’ table_name,column_name,ordinal_position,data_type,is_nullable,character_maximum_length
ch = “SELECT table_name,column_name,ordinal_position,data_type,is_nullable,character_maximum_length,udt_name FROM information_schema.columns WHERE table_schema = '”
ch = ch + schemaName +"’ ORDER BY table_name,ordinal_position ASC"
rs2 = SQLSelect( ch)
if rs2<>nil then
do
postgresField_table_name.Append rs2.IdxField(1).StringValue
postgresField_column_name.Append rs2.IdxField(2).StringValue
postgresField_ordinal_position.Append rs2.IdxField(3).StringValue
postgresField_data_type.Append rs2.IdxField(7).StringValue // udt_name est plus explicite
postgresField_is_nullable.Append rs2.IdxField(5).StringValue
postgresField_character_maximum_length.Append rs2.IdxField(6).StringValue
rs2.MoveNext
loop until rs2.EOF
else
Alerte “postgresLoadFieldSchema: rs2 is nil !”
end if
end if

End Sub
[/code]

#45023 - serious issue , but I think you can work around:

SELECT attrelid::regclass, attnum, attname FROM pg_attribute WHERE attrelid = 'public.tbl_rel'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum;

schema = ‘public’ table = ‘tbl_rel’

I still have a copy of an old blog post I wrote for REALbasic years ago that show how to get this information. A few things may have changed but it should help. I can email a copy if you wish.

Bob, I can reproduce your issue if there are caps in the table name, e.g., “MyTable” instead of “mytable” or “my_table”. Could that be it?

BINGO !
recreated chinook using the script as all lower case & away we go

I can’t find a way to force it when there are caps in the table name either. I’ve tried double-quotes and square brackets (not even sure if that would work normally) with no effect.

Yeah, all of the table names in Chinook are camel case.

I’ll see if I can play around with it a bit more today. Very frustrating.

[quote=283371:@Joost Rongen]#45023 - serious issue , but I think you can work around:

SELECT attrelid::regclass, attnum, attname FROM pg_attribute WHERE attrelid = 'public.tbl_rel'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum;

schema = ‘public’ table = ‘tbl_rel’[/quote]

It’s kind of funny, but this where I started on all of this. I had this query and it was giving me errors with a ‘missing relationship’ when clearly the tables are there. Starting to think that maybe having the mixed case table names is the root cause of a lot of issues.

[quote=283436:@Norman Palardy]BINGO !
recreated chinook using the script as all lower case & away we go[/quote]
@Norman Palardy, do I need to submit a Feedback report for this?

This is normal for postgres
This db is set up “odd” for postgresql

in fact running sql against that schema the way it is set up results in errors

select * from Artist

ERROR:  relation "artist" does not exist
LINE 1: select * from Artist
                      ^

********** Error **********

ERROR: relation "artist" does not exist
SQL state: 42P01
Character: 15

You actually have to query this way

select * from "Artist"

I’d actually alter the DB set up script and remove all the double quotes around table names & column names and things will just work

EDIT : In fact if you remove every double quote (takes about 5 seconds in bbedit) then things just work

And I would send a bug report - to the folks who publish the DB as it is a really screwy set up for postgresql which uses lower case for table & column names. That script forces them to be upper case which is just odd

The problem is, postgres itself will tend to lowercase table and column name identifiers. For example, if you have a table “my_table”, you can issue SQL like this and it will work:

SELECT * FROM My_Table

On the other hand, if the table name is actually “My_Table”, it won’t because PSQL lowercases the identifier in the SQL. If you quote “My_Table” in the SQL, it will work in the latter case but not in the former.

Technically, the Xojo calls should have a way to use mixed case identifiers, but I can’t think of a clean way to do that without breaking other code. Based on my research, Norman’s recommendation is not only the way around the problem, but seems to be “best practice” when it comes to Postgres.

Oh you CAN use quoted table & column names - its just not normal for PostgreSQL

And fieldschema doesn’t work even if you explicitly quote the table name - that may be a bug and I added that to the notes on that case

But for right now the Chinook set up script IS the root cause - its not really set up “well” for Postgressql and the quoted table & column names are the issue
Get rid of those - the db works Xojo works and writing plain old sql just works without having to quote everything - and I do mean “everything”

And I did mention that you had to use quotes previously :stuck_out_tongue:

For your convenience, here is code to convert camel-case to snake_case:

Public Function CamelToSnake(Extends camel As String) as String
  if camel.Trim = "" then
    return ""
  end if
  
  dim hexedCamel as string = EncodeHex(camel)
  Static camelCache As New Dictionary
  dim r as string = camelCache.Lookup(hexedCamel, "")
  if r <> "" then
    return r
  end if
  
  static rx as RegEx
  if rx is nil then
    rx = new RegEx
    rx.SearchPattern = "(?mi-Us)\\p{Lu}+(?=\\p{Lu}\\P{Lu}|\\d|$)|\\d+|^.[^\\p{Lu}\\d\\r\
]*|\\p{Lu}[^\\p{Lu}\\d\\r\
]+"
  end if
  
  Dim result() As String
  dim match as RegExMatch = rx.Search(camel)
  while match isa RegExMatch
    result.Append match.SubExpressionString(0).Lowercase
    match = rx.Search
  wend
  
  r = Join(result, "_")
  dim squeezed as string = r
  do
    r = squeezed
    squeezed = squeezed.ReplaceAll("__", "_")
  loop until (r = squeezed)
  
  camelCache.Value(hexedCamel) = r
  
  Return r
End Function

how does this help ?
postgresql still wants lower case - in fact it basically forces that

We use it to match property names to table fields, so we can have in the database “table.some_field” and map it to the property “SomeField” in class “Table” pretty easily.

But here I was thinking as part of the initial “cleanup” to convert the camel-case names to snake-case without having to work through each one by hand.

just remove every " in the original script that creates the DB and things work as expected

its literally the set up of the db that is the root cause - its not right for postgresql and how you normally expect to use it

otherwise you have to write queries where you quote every table name AND column name EVERYWHERE

blech !

I thought this sounded familiar so I spotlighted “Chinook Postgres” and found…

“Chinook_PostgreSql_lowercaseNoQuotes” - a BBEdit file I converted in the past :slight_smile: