ODBC Error

[fd]

When i connect a table from Access i see this error in the trace log
of H2,
and the operation fails

Error: Table "PG_ATTRDEF" not found; SQL statement:

I'm using

> h2-1.2.129.jar
> psqlodbc_08_04_0200.zip
>Access 2007

thanks
F.D.

Trace details *******************************************

Connect
Init
StartupMessage
version 196608 (3.0)
param database=mimweb
param user=MIMWEB
param DateStyle=ISO
param extra_float_digits=2
param geqo=on
param client_encoding=UTF8
PasswordMessage
Query
select oid, typbasetype from pg_type where typname = 'lo';
Query
SELECT Config, nValue FROM MSysConf;
org.h2.jdbc.JdbcSQLException: Table "MSYSCONF" not found; SQL
statement:
SELECT Config, nValue FROM MSysConf [42102-129]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
317)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.get(DbException.java:145)
at org.h2.command.Parser.readTableOrView(Parser.java:4334)
at org.h2.command.Parser.readTableFilter(Parser.java:1002)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1530)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1627)
at org.h2.command.Parser.parseSelectSub(Parser.java:1524)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1369)
at org.h2.command.Parser.parseSelect(Parser.java:1357)
at org.h2.command.Parser.parsePrepared(Parser.java:396)
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:223)
at org.h2.engine.Session.prepareLocal(Session.java:422)
at org.h2.engine.Session.prepareCommand(Session.java:383)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1071)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:163)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.pg.PgServerThread.process(PgServerThread.java:340)
at org.h2.server.pg.PgServerThread.run(PgServerThread.java:76)
at java.lang.Thread.run(Thread.java:619)
Query
select relname, nspname, relkind from pg_catalog.pg_class c,
pg_catalog.pg_namespace n where relkind in ('r', 'v') and n.oid =
relnamespace order by nspname, relname;
Query
select c.relhasrules, c.relkind, c.relhasoids from
pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid =
c.relnamespace and c.relname = E'TMIMAMB' and u.nspname = E'MIMWEB';
Query
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, d.adsrc, case t.typtype when 'd' then t.typbasetype
else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'TMIMAMB' and n.nspname like E'MIMWEB') 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;
org.h2.jdbc.JdbcSQLException: Column "T.TYPTYPMOD" 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, d.adsrc, case t.typtype when 'd' then t.typbasetype
else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'TMIMAMB' and n.nspname like E'MIMWEB') 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 [42122-129]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
317)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.get(DbException.java:145)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:
126)
at org.h2.command.dml.Select.prepare(Select.java:714)
at org.h2.command.Parser.prepareCommand(Parser.java:224)
at org.h2.engine.Session.prepareLocal(Session.java:422)
at org.h2.engine.Session.prepareCommand(Session.java:383)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1071)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:163)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.pg.PgServerThread.process(PgServerThread.java:340)
at org.h2.server.pg.PgServerThread.run(PgServerThread.java:76)
at java.lang.Thread.run(Thread.java:619)
^CException in thread "Thread-16" java.lang.AbstractMethodError:
it.ubi.mimweb.h2.AutoInc.close()V
at org.h2.schema.TriggerObject.close(TriggerObject.java:373)
at org.h2.engine.Database.close(Database.java:1031)
at org.h2.engine.DatabaseCloser.run(DatabaseCloser.java:80)

[Ryan How]

I've never been able to get it to work. I always have access say unknown error -4744 or something like that, do you get that also?

[fd]

After selecting the table from the list
Access resp is:

ODBC: Chiamata non riuscita ( Call failed )
Error while executing the query ( #7 )

[Sergi Vladykin]

Hi,
You should try ANSI driver but not Unicode.

[fd]

Same error

On 22 Feb, 18:02, Sergi Vladykin <sergi.vlady...@googlemail.com>
wrote:

[Thomas Mueller]

Hi,

You have used version psqlodbc_08_04_0200 but the last version I tried
was psqlodbc-08_02* - that's probably the reason.

I guess the main problem is that the column T.TYPTYPMOD is missing.
This is a bug in H2, in the file pg_catalog.sql. This will be fixed in
the next release. If you want to test it now you would have to replace
the file pg_catalog.sql from here:
http://h2database.googlecode.com/svn/trunk/h2/src/main/org/h2/server/pg/pg_catalog.sql
- and build H2 yourself. In any case you will also have to drop the
pg_catalog first using "drop schema pg_catalog" and then re-connect.
I'm not sure if this will completely solve the problem however - there
might be more columns missing. Please send a mail if so.

Another problem might be that trigger class it.ubi.mimweb.h2.AutoInc
doesn't implement close(). This is due to a change in the trigger
interface in H2 in version 1.2.129. As far as I see this is not a big
problem, but you should probably fix that in the near future.

By the way, the message about "MSYSCONF" is not really a problem, you
can ignore that.

Regards,
Thomas

[fd]

Perfect.

I build the version get from the svn trunk:
"svn checkout http://h2database.googlecode.com/svn/trunk/ h2database-
re"

all the problems seems resolved.

many thanks
regards

Francesco.

On 23 Feb, 13:02, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> You have used version psqlodbc_08_04_0200 but the last version I tried
> was psqlodbc-08_02* - that's probably the reason.
>
> I guess the main problem is that the column T.TYPTYPMOD is missing.
> This is a bug in H2, in the file pg_catalog.sql. This will be fixed in
> the next release. If you want to test it now you would have to replace

> the file pg_catalog.sql from here:http://h2database.googlecode.com/svn/trunk/h2/src/main/org/h2/server/...

[Thomas Mueller]

Hi,

> all the problems seems resolved.

Thanks a lot for your help!

Regards,
Thomas

[Ryan How]

I always used to have problems when using Access with H2 via the postgres driver. At the time I tried just opening the simplest of databases first, and then working up to a more complex database. It ended up the problem was when opening a table that had a certain type of key defined. I can't really remember, it was a while ago. Maybe support for access has been broken again in a more recent H2 build.

Is the database accessible from something like MS Excel instead? At least it would narrow down the issue...



On 17/02/2013 11:31 AM, Drew wrote:

I'm not sure I understood the solution...

I think I set up the PostgreSQL ODBC driver correctly in the System DSN (at least hittling [ Test ] returns "Connection Successful".)

In Access, when I go to Link tables, select ODBC, Machine Data Source: PostgreSQL (from ODBC Data Sources), it brings up the tables (all listed as PUBLIC):



...but selecting one and clicking [ OK ] returns the following error:



This is what I see after trying to link tables to the H2 database from Access. Not sure how to run trace and what to post.

My setup:

H2: h2-1.3.170
psqlodbc: psqlodbc_09_01_0200-1
Access 2003: 11.0.8321

Any help would be greatly appreciated.

Thanks,
Drew

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Drew]

OK, that was fun. I haven't used the data import function in Excel before -- I'm use to saving out the XLS files and importing them manually in Access. Here's the result of this attempt (and no data in the table).

Any thoughts?

Thanks.
Drew

[Ryan How]

That looks a bit messed up!

I'm not sure sorry, apart from trying different versions of drivers, etc. and setting H2 to trace level logging.

Does it work if you try it with postgres server?

It feels like it might be a bit of work sorting this out, and a bit of trial and error.

I know it used to work for me, so I know in the past it has worked. But I haven't got any of it set up at the moment to verify it still works...

[Andrew Ruggles]

Actually, my buddy just reviewed my log files and suggested exactly that -- set it up as a PostgreSQL server. I'll post details if it gets anywhere... (shouldn't be long)

[Drew]

OK, so now I'm a little (more) confused, because doesn't the PG Server start automatically? At least it's showing up as running in Terminal...

Web Console server running at http://192.168.23.17:8082 (others can connect)
TCP server running at tcp://192.168.23.17:9092 (others can connect)
PG server running at pg://192.168.23.17:5435 (others can connect)

I stepped back to 1.3.169 (last stable) version of H2 to see if that would help -- it didn't.

Here is my process. DB I'm trying to connect with is regapp.h2.db in the ~/RegApp/dbhome directory:

I'm using the h2.sh shell script to start the H2 Server.

$ cat h2.sh
#!/bin/sh
dir=$(dirname "$0")
java -cp "$dir/h2-1.3.169.jar:$H2DRIVERS:$CLASSPATH" org.h2.tools.Console "$@"

$ ./h2.sh -pgAllowOthers -tcpAllowOthers -webAllowOthers -baseDir ~/RegApp/dbhome -trace >RegApp.log

The PostgreSQL ODBC is configured on the Windows machine, and [ Test ] returns a "Connection Successful" message. Start Access, open local mdb, then go to Insert > Tables > Link... Select ODBC as the Datasource, then point to the PostgreSQL System DSN. A list of tables will come up (this looks correct to me). Select one, then it returns an ODBC connection failed due to a "Error while executing the query (#7)" Here is the last bit of the log. If it helps to include more, please let me know.

Connect
Init
StartupMessage
 version 196608 (3.0)

 param database=~/RegApp/dbhome/regapp
 param user=sa

 param DateStyle=ISO
 param extra_float_digits=2
 param geqo=on
 param client_encoding=UTF8
PasswordMessage
Query
select oid, typbasetype from pg_type where typname = 'lo';
Query
SELECT Config, nValue FROM MSysConf;

Query
select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in ('r', 'v') and n.oid = relnamespace order by nspname, relname;
Query

select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = E'Event' and u.nspname = E'PUBLIC';
Query
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 like E'Event' and n.nspname like 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;
Terminate
Close
Disconnect
Close

When I put any of these queries in the Console, it returns an error:

select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = E'Event' and u.nspname = E'PUBLIC';
Schema "PG_CATALOG" not found; SQL statement:
select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = E'Event' and u.nspname = E'PUBLIC' [90079-169] 90079/90079 (Help)

and apparently, the error message above links to this:

    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.getSchema(Parser.java:613)
    at org.h2.command.Parser.getSchema(Parser.java:620)
    at org.h2.command.Parser.readTableFilter(Parser.java:1059)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1690)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1797)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1684)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1527)
    at org.h2.command.Parser.parseSelect(Parser.java:1515)
    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:217)
    at org.h2.engine.Session.prepareLocal(Session.java:415)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:253)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:149)
    at java.lang.Thread.run(Thread.java:680)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:567)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:67)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:46)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:439)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1114)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:164)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1311)
    at org.h2.server.web.WebApp.query(WebApp.java:1001)
    at org.h2.server.web.WebApp$1.next(WebApp.java:964)
    at org.h2.server.web.WebApp$1.next(WebApp.java:953)
    at org.h2.server.web.WebThread.process(WebThread.java:166)
    at org.h2.server.web.WebThread.run(WebThread.java:93)
    at java.lang.Thread.run(Thread.java:680)

Any thoughts?
Thanks. Drew

[Thomas Mueller]

Hi,

The PG_CATALOG in H2 is only available when using the PostgreSQL client. You could use the PostgreSQL JDBC client, then the query should work.

Regards,
Thomas

[Sylvain Nault]

I can see the Support for the PG_CATALOG is part of the version 1.4.x roadmap. Will it work with the MS Office applications like Access or Excel? Any time frame for this to be available? I am trying to access than 2 database via ODBC using the PostgreSQL driver and am trying to link tables and keep getting errors. Will the support for the PG_CATALOG wix those issues?

[Ryan How]

Hi,

I'd try using the latest 1.3 version. I've had it working from Access and Excel for quite some time, but I don't use it regularly.

Ryan

--

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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Sylvain Nault]

I am using version 1.3.169 and have the issue with Excel and Access. When trying to connect to the ODBC data souce, I can see the list of tables. However, if I try to Iink or Import from a able I select, I get "Error while executing the query (#7)". The issue seems to be tied to the PG_CATALOG.