Hi
I pick up the schema of a remote database
I get a valid recordset from tableschema
but then fieldschema returns a valid recordset with no record, althrought there are fields in the tables.
it’s working ok with an sqlite database, but not with a postgres database
can it be a connexion role from the postgres server that prevents me to do that ?
thanks.
Xojo2015R31 - mac os x 10.9.5
Postgresql is not the same enginr as sqlite
I’m only using the xojo build in tableschema and fieldschema methods
they should return the same thing from a sqlite or postgres database
and tableschema works on both databases, only fieldschema returns an empty recordset
seems to me that SOME Database engines are CASE SENSTIVE when it come to Table/Field names
so it Postgres is, you might want to check that
fieldSchema works fine for me with Postgres. It is probably something simple you are missing.
it used to work for me, so I asked if some role parameters in postgres can have such results on fieldschema ?
You mean if you need to be a postgres superuser to read the fieldschema? Well, if this was the case than you’d get a proper errormessage. I assume you did check mydbconnection.error, right?
Actually I don’t think so, because psql allows reading the schema for non-superusers (just checked). I have no idea how the PG-plugin obtains the fieldschema though. You might want to roll your own fieldschema-command: select * from information_schema.columns where
table_name=‘myTableName’;
[quote=241632:@Dave S]seems to me that SOME Database engines are CASE SENSTIVE when it come to Table/Field names
so it Postgres is, you might want to check that[/quote]
thanks Dave, this seems to be the key point !
I can access the fields of tables that are lowercase, but not the others
then I can access the fields that are lowercase but not the others, even if I write correctly their names !
this is quite a weird behaviour, is there something to change in the postgres configuration to have a different (normal?) behaviour ?
If you plan to have the same application working with different kind of databases, I think it is good practice to avoid using uppercase at all.
It sounds like your instance is set to case insensitive already. If I remember correctly, that means that you have to make all of your table names and field names lowercase for it to work correctly.
Ah, well with PostgreSQL it works like this; if you named your tables in title case you need to quote them.
So this might work:
dim rs as recordset
rs=myDBConnection.fieldschema(chr(34)+“MyTablename”+chr(34))
finally I made a method to lowercase all my tables and fields in postgresql
it works like that
I still have to do the same method for sqlite because my original database in sqlite still has mixedcase tables and fields names.
if I transfert it again it will still not work.