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.
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 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
#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;
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.
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.
[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
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
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.