Errors when trying to use PostgreSQL ODBC driver version 9.02

[GoogleCodeExporter]

Environment:
OS: Windows8
H2: 1.3.172
PostgreSQL ODBC driver: 9.02.0100
wrapper.conf:
# Application parameters.  Add parameters as needed starting from 1
wrapper.app.parameter.1=org.h2.tools.Server
wrapper.app.parameter.2=-tcp  
wrapper.app.parameter.3=-web
wrapper.app.parameter.4=-pg

Problem:
Cannot use H2 Database with ODBC. The connection seems to be ok, but when 
trying to execute the first Select SQL statement, the following error message 
from Lazarus/ODBC appears:
Could not rettrieve index metadata for table namepv using SQLStatistics. ODBC 
error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; 
NativeError: 7; Message: Error while executing the query.

In the .trace.db file of the datase this can be seen:
06-04 10:46:19 jdbc[5]: exception
org.h2.jdbc.JdbcSQLException: Funktion "PG_GET_EXPR" nicht gefunden
Function "PG_GET_EXPR" not found; SQL statement:
select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, 
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, 
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype 
else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join 
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = E'namepv' 
and n.nspname = E'PUBLIC') inner join pg_catalog.pg_attribute a on (not 
a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join 
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on 
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by 
n.nspname, c.relname, attnum [90022-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
....

The tests have been performed using a Lazarus/FreePascal application and 
LibreOffice. The error messaage shown to the user differs a little, but the 
content of the .trace.db file is the same. When using LibreOffice, the tables 
can be seen, but trying to se the contents leads to the error. A new table may 
be created, but trying to add a row runs into the same error.

Regards

Original issue reported on code.google.com by hl...@gmx.at on 4 Jun 2013 at 9:11

[GoogleCodeExporter]

Helmut, I have just submitted a patch as part of issue #472 which I suspect 
will fix this issue too.

Original comment by arbfrank...@gmail.com on 8 Jun 2013 at 1:58

[GoogleCodeExporter]

Hi,

is the patch included in the h2-latest,jar ? It is because I tried with 
h2-latest and the problem is still present.

Original comment by hl...@gmx.at on 10 Jun 2013 at 4:54

[GoogleCodeExporter]

No, the patch isn't in trunk as of yet; awaiting feedback on the relevant 
tickets. If you don't mind compiling it yourself, you can try my branch at 
http://github.com/arbfranklin/h2database.

Original comment by arbfrank...@gmail.com on 12 Jun 2013 at 12:40

[GoogleCodeExporter]

Problem still present after compilation from your branch. (Function PG_GET_EXPR 
not found).

Original comment by hl...@gmx.at on 12 Jun 2013 at 2:02

[GoogleCodeExporter]

Are you sure it's the exact same exception? If I take your exact query above 
and run it on my branch, I see:

H2DB=> select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, 
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, 
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype 
else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join 
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = E'namepv' 
and n.nspname = E'PUBLIC') inner join pg_catalog.pg_attribute a on (not 
a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join 
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on 
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by 
n.nspname, c.relname, attnum;
 nspname | relname | attname | atttypid | typname | attnum | attlen | atttypmod | attnotnull | relhasrules | relkind | oid | "pg_catalog"."pg_get_expr"(d."adbin", d."adrelid") | casewhen((t."typtype" = 'd'), t."typbasetype", 0) | typtypmod | relhasoids 
---------+---------+---------+----------+---------+--------+--------+-----------
+------------+-------------+---------+-----+------------------------------------
----------------+---------------------------------------------------+-----------
+------------
(0 rows)

Btw, if you want to be able to run the regular H2 1.3.172 that should work fine 
but you'll need an older ODBC driver; I think the 8.0 series driver is meant to 
work ok? My change in issue #472 is about trying to make the more recent 
drivers work.

Original comment by arbfrank...@gmail.com on 12 Jun 2013 at 7:53

[GoogleCodeExporter]

From trace.db file:
06-12 15:53:13 jdbc[2]: exception
org.h2.jdbc.JdbcSQLException: Funktion "PG_GET_EXPR" nicht gefunden
Function "PG_GET_EXPR" not found; SQL statement:
select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, 
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, 
pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype 
else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join 
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = E'namepv' 
and n.nspname = E'PUBLIC') inner join pg_catalog.pg_attribute a on (not 
a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join 
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on 
a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by 
n.nspname, c.relname, attnum [90022-172]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.command.Parser.readJavaFunction(Parser.java:2138)
    at org.h2.command.Parser.readFunction(Parser.java:2190)
    at org.h2.command.Parser.readTerm(Parser.java:2500)
    at org.h2.command.Parser.readFactor(Parser.java:2062)
    at org.h2.command.Parser.readSum(Parser.java:2049)
    at org.h2.command.Parser.readConcat(Parser.java:2022)
    at org.h2.command.Parser.readCondition(Parser.java:1887)
    at org.h2.command.Parser.readAnd(Parser.java:1868)
    at org.h2.command.Parser.readExpression(Parser.java:1860)
    at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1773)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1805)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1699)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1542)
    at org.h2.command.Parser.parseSelect(Parser.java:1530)
    at org.h2.command.Parser.parsePrepared(Parser.java:405)
    at org.h2.command.Parser.parse(Parser.java:279)
    at org.h2.command.Parser.parse(Parser.java:251)
    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:425)
    at org.h2.engine.Session.prepareCommand(Session.java:374)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156)
    at org.h2.server.pg.PgServerThread.process(PgServerThread.java:388)
    at org.h2.server.pg.PgServerThread.run(PgServerThread.java:90)
    at java.lang.Thread.run(Unknown Source)

Original comment by hl...@gmx.at on 12 Jun 2013 at 8:39

[GoogleCodeExporter]

Can you confirm that you're starting with a new database (or a mem database)? 
The catalog migration only gets done the first time, if you were running a DB 
with an attempted init on 1.3.172 then that could be the cause.

Original comment by arbfrank...@gmail.com on 12 Jun 2013 at 8:47

[GoogleCodeExporter]

I tested now with a new database, the problem with PG_GET_EXPRESSION not found 
has disappeared.

But I cannot see the data values. There are exceptions:
06-12 23:10:07 jdbc[3]: exception
org.h2.jdbc.JdbcSQLException: Datenumwandlungsfehler beim Umwandeln von "(0,0)"
Data conversion error converting "(0,0)"; SQL statement:
EXECUTE 
"_KEYSET_0685D0C0"('(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0
)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0
)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0)','(0,0
)','(0,0)','(0,0)','(0,0)','(0,0)') [22018-172]

Original comment by hl...@gmx.at on 12 Jun 2013 at 9:17

[GoogleCodeExporter]

Additional info:
the exception mentionned before need not occur, but I cannot access the data. I 
need to do more tests to find the reason.

I often see
06-12 23:22:28 jdbc[3]: exception
org.h2.jdbc.JdbcSQLException: Schema 
"C:/mnt/e/Ahnen/Tests/TMGConvert\Leininger" nicht gefunden
Schema "C:/mnt/e/Ahnen/Tests/TMGConvert\Leininger" not found; SQL statement:
SELECT "MSTB_FLAGLABEL", "MSTB_FLAGVALUE", "MSTB_DESCRIPT" FROM 
"C:/mnt/e/Ahnen/Tests/TMGConvert\Leininger"."PUBLIC"."CUSTFLAGS" AS "CUSTFLAGS" 
WHERE ...

What is mentionned here as Schema is the database, not the table. I don't know 
yet where it comes from.

Original comment by hl...@gmx.at on 12 Jun 2013 at 9:30

[GoogleCodeExporter]

The problem you're seeing with the schema is related to the problem I reported 
in issue #473. I also have a somewhat working solution in my branch. 

You'll want to provide something like "-key LENIGER TMGConvert\Leininger" on 
the command-line, and then you connect with a DB name of "LENIGER". It's worth 
adding "-trace" to the command-line too so you can see what's being sent on the 
wire.

Original comment by arbfrank...@gmail.com on 12 Jun 2013 at 9:43

[GoogleCodeExporter]

improve the bug title

Original comment by noelgrandin on 18 Jun 2013 at 7:12

• Changed title: Errors when trying to use PostgreSQL ODBC driver version 9.02

[GoogleCodeExporter]

I wonder: is this issue now fixed?

Original comment by thomas.t...@gmail.com on 29 Jul 2013 at 7:29

[GoogleCodeExporter]

The original cited problem was fixed in #472. The followup issue in the comment 
on June 12th is a problem I have observed in some applications when using ODBC. 
In some cases the database name is prefixed to the schema name; eg. instead of 
"select * from public.table" you end up with "select * from 
dbname.public.table". You can somewhat work around the remaining issue via the 
"-key" alias argument (#473) or writing custom queries. An example application 
that exhibits this behaviour is Excel. I'm unsure what Helmut was using?

While I don't have the code in front of me, I recall even with the "-key" 
method that the alias name needed to match the internal short DB name. I'd 
suggest you leave this bug open for a bit and we look at whether there may be a 
more robust solution to handling this dbname prefix; it may involve reworking 
#473.

Original comment by arbfrank...@gmail.com on 29 Jul 2013 at 10:32

[GoogleCodeExporter]

Now I am ableto access the data (h2-1.3.173, PostGRE SQL 9.01.02).

Original comment by hl...@gmx.at on 30 Jul 2013 at 5:55

[GoogleCodeExporter]

Marking this as fixed for now. Please re-open if the problem recurs.

Original comment by noelgrandin on 9 Oct 2013 at 7:55

• Changed state: Fixed