ODBC

[Paul]

Hi,

I'm trying to connect H2 from Ms. Access using ODBC.
H2 database I use: H2-1.2.135.jar
Postgre ODBC: psqlODBC version 8.03.0400


Problems I got:

- If the DB is encrypted, can't connect at all from odbc, if I
decrypted the db I can connect to it, is there documentation how to
connect to encrypted H2 db using ODBC?

- Unable to link to a table that has a field with primary key or
unique key, for example: id_employee INT AUTO_INCREMENT PRIMARY KEY
The table is shown, but when I select to link it, it says error.
If I make the field INT AUTO_INCREMENT, there is no problem linking to
it.

- And the big problem is, when a table has _ (underscore) in the name,
for example: table name employee_jobs, when trying to link to that
table, ms access says the table is not exists, very strange why it
said there is no such table, because I pick the table from the list of
the tables that shown by ms access.


I followed the ODBC documentation in here:
http://www.h2database.com/html/advanced.html?highlight=odbc&search=odb#odbc_driver

Please help.

Thanks...

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

> - If the DB is encrypted, can't connect at all from odbc, if I
> decrypted the db I can connect to it, is there documentation how to
> connect to encrypted H2 db using ODBC?

Most likely the problem is that only simple names are supported in the
"database" property of the connection property. Therefore you can't
append ";CIPHER=..." there. Is that the problem? It looks like there
needs to be some escape mechanism for that. Otherwise there is nothing
special about using ODBC, it should work exactly the same as when
using JDBC. That is, the database password is the combination of the
file password, a space, and the user password.

> - Unable to link to a table that has a field with primary key or
> unique key, for example: id_employee INT AUTO_INCREMENT PRIMARY KEY
> The table is shown, but when I select to link it, it says error.
> If I make the field INT AUTO_INCREMENT, there is no problem linking to
> it.
>
> - And the big problem is, when a table has _ (underscore) in the name,
> for example: table name employee_jobs, when trying to link to that
> table, ms access says the table is not exists, very strange why it
> said there is no such table, because I pick the table from the list of
> the tables that shown by ms access.

I'm not sure what the problem is. Unfortunately I currently don't have
Microsoft Access installed...

Could you start the PG Server with the -trace option, and then post or
send me the output when you retry those operations? Probably it's
easier if you redirect the output to a file.

Another option (probably not required, let's see) would be to set the
trace level to 3 so that each JDBC API call is written to the
.trace.db file, and then post or send me this file. You will need to
do that from a different connection (a JDBC connection), and append
;TRACE_LEVEL_FILE=3 to the database URL.

Regards,
Thomas

[Paul]

Hi,

> Most likely the problem is that only simple names are supported in the
> "database" property of the connection property. Therefore you can't
> append ";CIPHER=..." there. Is that the problem? It looks like there
> needs to be some escape mechanism for that. Otherwise there is nothing
> special about using ODBC, it should work exactly the same as when
> using JDBC. That is, the database password is the combination of the
> file password, a space, and the user password.

Yes, if I put DBNAME;CIPHER=AES then the database will be created as
DBNAME;CIPHER=AES.h2.db
If I remove CIPHER=AES, then I can't connect to the db
The ODBC said "Communications closed during the authentication; Socket
has been closed"
Looking at the console H2 it said:
"org.h2.jdbc.JdbcSQLException: Wrong user name or password [8004-135]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.message.DbException.get(DbException.java:133)
at org.h2.engine.Engine.validateUserAndPassword(Engine.java:
275)
at org.h2.engine.Engine.getSession(Engine.java:133)
at org.h2.engine.Session.createSession(Session.java:119)
at
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:
239)
at
org.h2.engine.SessionRemote.createSession(SessionRemote.java:217)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:111)
at org.h2.server.pg.PgServerThread.process(PgServerThread.java:
188)
at org.h2.server.pg.PgServerThread.run(PgServerThread.java:76)
at java.lang.Thread.run(Unknown Source)"

And this is the trace:
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME;CIPHER=AES
param user=Db-u5312
param DateStyle=ISO
param extra_float_digits=2
param geqo=on
PasswordMessage
Query
select oid, typbasetype from pg_type where typname = 'lo';
Terminate
Close
Disconnect
Close
Connect
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME
param user=Db-u5312
param DateStyle=ISO
param extra_float_digits=2
param geqo=on
PasswordMessage
Disconnect
Close



When from Ms Access connecting to ODBC to retrieve the tables, this is
the trace:
Connect
Disconnect
Close
Connect
PG server running on pg://192.168.1.111:5435 (only local connections)
Disconnect
Close
Connect
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME
param user=Db-u5312
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;

> - Unable to link to a table that has a field with primary key or
> unique key, for example: id_employee INT AUTO_INCREMENT PRIMARY KEY
> The table is shown, but when I select to link it, it says error.
> If I make the field INT AUTO_INCREMENT, there is no problem linking to it.

When linking to a table with PRIMARY KEY field:
The trace:
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'ADJUSTSTYLES' 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, d.adsrc from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'ADJUSTSTYLES' 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;
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 from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname =
E'ADJUSTSTYLES' 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;
Query
select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
c.relhasrules, n.nspname, c.oid from pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespace n where d.relname = E'ADJUSTSTYLES' and
n.nspname = E'PUBLIC' and n.oid = d.relnamespace and d.oid =
i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by
i.indisprimary desc, i.indisunique, n.nspname, c.relname;
Query
select pg_get_indexdef(20, 1, true);
Query
select pg_get_indexdef(20, 4, true);
Query
select pg_get_indexdef(20, 5, true);
Query
select pg_get_indexdef(20, 7, true);
Query
select pg_get_indexdef(20, 29, true);

The access form:
"Reserved error (-7747); there is no message for this error."


Trying to link again, the trace is getting long:
Connect
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME
param user=Db-u5312
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 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'ADJUSTSTYLES' 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, d.adsrc from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'ADJUSTSTYLES' 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;
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 from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname =
E'ADJUSTSTYLES' 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;
Query
select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
c.relhasrules, n.nspname, c.oid from pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespace n where d.relname = E'ADJUSTSTYLES' and
n.nspname = E'PUBLIC' and n.oid = d.relnamespace and d.oid =
i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by
i.indisprimary desc, i.indisunique, n.nspname, c.relname;
Query
select pg_get_indexdef(20, 1, true);
Query
select pg_get_indexdef(20, 2, true);
Query
select pg_get_indexdef(20, 3, true);
Query
select pg_get_indexdef(20, 4, true);
Query
select pg_get_indexdef(20, 5, true);
Query
select pg_get_indexdef(20, 6, true);
Query
select pg_get_indexdef(20, 7, true);
Query
select pg_get_indexdef(20, 8, true);
Query
select pg_get_indexdef(20, 9, true);
Query
select pg_get_indexdef(20, 10, true);
Query
select pg_get_indexdef(20, 11, true);
Query
select pg_get_indexdef(20, 12, true);
Query
select pg_get_indexdef(20, 13, true);
Query
select pg_get_indexdef(20, 14, true);
Query
select pg_get_indexdef(20, 15, true);
Query
select pg_get_indexdef(20, 16, true);
Query
select pg_get_indexdef(20, 17, true);
Query
select pg_get_indexdef(20, 18, true);
Query
select pg_get_indexdef(20, 19, true);
Query
select pg_get_indexdef(20, 20, true);
Query
select pg_get_indexdef(20, 21, true);
Query
select pg_get_indexdef(20, 22, true);
Query
select pg_get_indexdef(20, 23, true);
Query
select pg_get_indexdef(20, 24, true);
Query
select pg_get_indexdef(20, 25, true);
Query
select pg_get_indexdef(20, 26, true);
Query
select pg_get_indexdef(20, 27, true);
Query
select pg_get_indexdef(20, 28, true);
Query
select pg_get_indexdef(20, 29, true);
Disconnect
Close

>
> > - And the big problem is, when a table has _ (underscore) in the name,
> > for example: table name employee_jobs, when trying to link to that
> > table, ms access says the table is not exists, very strange why it
> > said there is no such table, because I pick the table from the list of
> > the tables that shown by ms access.
>

When linking to a table with the name has _ (underscore):
The trace:
Connect
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME
param user=Db-u5312
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 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'PROJECT_DATA' 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, d.adsrc from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'PROJECT\\_DATA' 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;

The access form said:
"The Microsoft Jet database engine could not find the object
'PUBLIC.PROJECT_DATA'. Make sure the object exists and that you spell
its name and the path name correctly."

Perhaps because of this: c.relname like E'PROJECT\\_DATA' and
n.nspname like E'PUBLIC'
Why it search for PROJECT\\_DATA not PROJECT_DATA, the previous query
it search for c.relname = E'PROJECT_DATA' and u.nspname = E'PUBLIC'


And this is when connecting to a table without underscore and without
primary key.
Connect successfully:
Init
StartupMessage
version 196608 (3.0)
param database=DBNAME
param user=Db-u5312
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 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'ADJUSTSTYLES' 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, d.adsrc from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like
E'ADJUSTSTYLES' 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;
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 from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname =
E'ADJUSTSTYLES' 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;
Query
select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
c.relhasrules, n.nspname, c.oid from pg_catalog.pg_index i,
pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespace n where d.relname = E'ADJUSTSTYLES' and
n.nspname = E'PUBLIC' and n.oid = d.relnamespace and d.oid =
i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by
i.indisprimary desc, i.indisunique, n.nspname, c.relname;
Disconnect
Close


Thanks for the help

[Thomas Mueller]

Hi,

I have fixed some problems now (in the trunk).

> DBNAME;CIPHER=AES

That should work now.

>> - Unable to link to a table that has a field with primary key or
>> unique key

I'm not sure about that. Is the problem just with auto-increment
primary key, or is it with all kinds of primary keys? Meaning, does
this work: create table(id int primary key, name varchar(255))? I
think it's not yet fixed (unfortunately I don't have MS Access
currently so I can't test it).

> when a table has _ (underscore) in the name

That should be fixed now.

Regards,
Thomas

[Paupau]

Thanks, when this version is available for download so I can try it
out.

>> I'm not sure about that. Is the problem just with auto-increment
>> primary key, or is it with all kinds of primary keys? Meaning, does
>> this work: create table(id int primary key, name varchar(255))? I
>> think it's not yet fixed (unfortunately I don't have MS Access
>> currently so I can't test it).

The problem is with primary key and unique key, the auto-increment is
not a problem.

>>> If I make the field INT AUTO_INCREMENT, there is no problem linking to it.

Thanks


On Jul 4, 9:42 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

I think I know where the problem is, but it's relatively complicated
to fix. As a workaround, I have now disabled the index meta data. MS
Access will then ask you for the unique keys, but I guess that's
acceptable. For existing databases, run this statement: delete from
pg_catalog.pg_index; For new databases, you would need to change
pg_catalog.sql (add "and 1=0" to the select statement where
pg_catalog.pg_index is filled) and then re-compile H2. However, I
found out there is still a bug that prevents editing data in MS Access
(UnsupportedOperationException). I will fix that in the next release.
If you want to try, I suggest to use the current trunk.

Regards,
Thomas

[Paupau]

Hi,

I'll wait for the next release.
But I'm not sure what you mean by run that statement. My H2 database
is already populated (many tables & data) from previous version of H2
and I want to connect it from ODBC, so to migrate to new version of H2
of course I'll need to backup with runscript from the previous version
and then execute script to create the database against the new
version, is it considered new database or old?
So after I have my H2 database in new version, I need to execute this
SQL query to my h2 database?
SQL: "delete from pg_catalog.pg_index"

Thanks,
Paul


On Jul 6, 11:31 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Balaji M]

[Balaji M]

Hi ,

I have the  same issue  when connect to encrypted data base  iam getting  "Communication closed during authentication;Socket has been closed " error Please help
Connection string iam using "ODBC;DSN=;SERVER=localhost;DATABASE=GE04;CIPHER=AES;PORT=5435;UID=admin;PWD=abc123 abc123;DRIVER={PostgreSQL Unicode}"

Thanks and Regards
Balaji Manickam

[Thomas Mueller]

Hi,

Sorry, you can not use "CIPHER=AES" in the ODBC connection string. This is only allowed in the JDBC database URL.

You would need to set the database name to "GE04;CIPHER=AES", but I'm not sure if this is possible or how to do it.

Or, as a workaround, you could start the PgServer with an undocumented feature "key" that allows mapping of one database URL to another:

    java org.h2.tools.Server -pg -key GE04 "~/temp/ge04;cipher=aes"

I will need to document this as follows:

To map an ODBC database name to a different JDBC database name, use the option "-key" when starting the server. Please note only one mapping is allowed. The following will map the ODBC database named "TEST" to the database URL "jdbc:h2:~/data/test;cipher=aes":

    java org.h2.tools.Server -pg -key TEST "~/data/test;cipher=aes"

Regards,
Thomas

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/groups/opt_out.