I have looked at the code and found the issue. I am using the PG Server for connectivity and it has the PostgreSQL mode hard coded which overrode the MODE I set when starting H2. I am logging into the PG Server with an admin user to generate the pg_catalog after start up.
The reason for the PG server is to access H2 from .NET and also via ODBC. I don't think MODE should be hard coded so you can use the PG interface for access but still use other modes such as SQL Server. I have also found some other issues in with the pg server: - If DATABASE_TO_UPPER=false the pg_catalog is created on each connection as conn.getMetaData().getTables(null, "PG_CATALOG", "PG_VERSION", null); is case sensitive. - The pg_catalog script should drop the regproc domain if it exists. - The row descriptions are in lowercase StringUtils.toLowerEnglish(names[i]), not sure if that is a postgres thing but it should be actual case or have an option to be the correct case. If have also added a few things to make it improve the compatibility with SQL Server: DATEPART & FORMAT functions plus constants so the functions can be used interchangeably, i.e. yy, qq, mm, etc. Have to submit a patch/issue or more info if you are interested. On Wednesday, January 6, 2016 at 3:26:57 PM UTC+11, Tim wrote: > > Great database, the easiest to use open source DB there is. > > Wondering whether the non-admin part was ever fixed? I can't connect with > a non admin and set the MODE via the console or using the Postgresql driver > (it is working if I don't add MODE). > > > <https://lh3.googleusercontent.com/-73BBvcS1bfM/VoxfmilA35I/AAAAAAAAAFQ/w1iriM8If6A/s1600/H2%2BMode.png> > > I am also setting the MODE both in the url and in code when I startup the > database in server mode but this isn't persisted to other connections. > > > jdbc:h2:db/docs;DATABASE_TO_UPPER=false;IGNORECASE=TRUE;MODE=MSSQLServer; > > I would like to all connections to use MSSQLSERVER mode as that is what > the users are familiar with. I am using version: 1.3.174 > > Help appreciated. > > On Sunday, June 19, 2011 at 10:16:15 PM UTC+10, Thomas Mueller wrote: >> >> Hi, >> >> Actually, it's a bit different: >> >> So far, non-admin users can open a database using a mode (MODE=xxx), >> but only if the database is not open already (even if the mode matched >> the current mode). This is a bug and will be fixed. There was another >> bug: non-admin users could not open a database over the PgServer, >> because they didn't have enough access rights to read the pg_catalog >> tables. This will be fixed as well. >> >> Anyway, the first connection that opens a database using the >> PostgreSQL server needs to be an administrator user (so the system >> tables can be created). Subsequent connections don't need to be opened >> by an administrator. The reason is that >> >> Regards, >> Thomas >> >> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
