PostgreSQL and FieldSchema

  1. ‹ Older
  2. 3 years ago

    Bob K

    22 Aug 2016 Pre-Release Testers, Xojo Pro, Third Party Store 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

  3. Jean-Yves P

    22 Aug 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 years ago

    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
  4. 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'

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

  6. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    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?

  7. Norman P

    23 Aug 2016 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

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

  8. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    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.

  9. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro, Third Party Store 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.

  10. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro, Third Party Store 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.

  11. Bob K

    23 Aug 2016 Pre-Release Testers, Xojo Pro, Third Party Store 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?

  12. Norman P

    23 Aug 2016 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...
    Edited 3 years ago

    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

  13. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    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.

  14. Norman P

    23 Aug 2016 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...
    Edited 3 years ago

    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

  15. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    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
  16. Norman P

    23 Aug 2016 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

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

  17. Kem T

    23 Aug 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    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.

  18. Norman P

    23 Aug 2016 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

    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 !

  19. 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 :)

  20. Maximilian T

    23 Aug 2016 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    Peter, you don't seem to be the 1st person going through this. http://johnatten.com/2015/04/05/a-more-useful-port-of-the-chinook-database-to-postgresql/

  21. Bob K

    24 Aug 2016 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    Thanks for the links. I used one of the 'fixed' Chinook scripts and everything is working as expected. What a pain.

or Sign Up to reply!