PostgreSQL and FieldSchema

  1. 7 months ago

    Bob K

    22 Aug 2016 Pre-Release Testers, Xojo Pro Kansas City

    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.

  2. Edited 7 months ago by Norman P

    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

  3. Bob K

    22 Aug 2016 Pre-Release Testers, Xojo Pro Kansas City

    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 Case #45023

  4. Jean-Yves P

    22 Aug 2016 Pre-Release Testers, Xojo Pro Europe (France, Besancon)
    Edited 7 months ago by Jean-Yves P

    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.

    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
  5. Joost R

    22 Aug 2016 Pre-Release Testers, Xojo Pro The Netherlands

    #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'

  6. 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.

  7. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro New York

    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?

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

  9. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro New York

    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.

  10. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro Kansas City

    @Kem T 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?

    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.

  11. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro Kansas City

    @Joost R #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'

    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.

  12. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro Kansas City

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

    @Norman P, do I need to submit a Feedback report for this?

  13. Edited 7 months ago by Norman P

    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

  14. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro New York

    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.

  15. Edited 7 months ago by Norman P

    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 :P

  16. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro New York

    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\n]*|\p{Lu}[^\p{Lu}\d\r\n]+"
      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
  17. how does this help ?
    postgresql still wants lower case - in fact it basically forces that

  18. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro New York

    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.

  19. 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 !

  20. Peter F

    23 Aug 2016 Pre-Release Testers, Xojo Pro New England

    I thought this sounded familiar so I spotlighted "Chinook Postgres" and found...

    "Chinook_PostgreSql_lowercaseNoQuotes" - a BBEdit file I converted in the past :)

  21. Newer ›

or Sign Up to reply!