ODBC login = cannot see tables?

hibikidan333

unread,

Feb 1, 2008, 5:47:39 AM2/1/08

to H2 Database

Hi all,

I seem to be having some problems with the ODBC (postgresql) drivers.

When I log in via the web console, I have my tables there.

However, when I import the h2 database into MS Access thru ODBC, it
treats it as if I am starting from scratch.

Here are the settings for ODBC:

Data Source: chinese
Database: chinese
Server: localhost
Port: 5435
Username: sa
Password: sa
SSL Mode: disable

Here are my settings for the web console:

Driver Class: org.h2.Driver
JDBC URL: jdbc:h2:~/chinese
User Name: sa
Password: <blank>

Any ideas?

Hibiki Joji

unread,

Feb 1, 2008, 6:05:39 AM2/1/08

to dbsu...@h2database.com, h2-da...@googlegroups.com

Hi,

I did a "SELECT * FROM INFORMATION_SCHEMA.TABLES" in the web console and it gives me this:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	STORAGE_TYPE	SQL	REMARKS	ID
CHINESE	PUBLIC	DEFINITION	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.DEFINITION(		37
					DEFINITION_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_A698C788_CDCD_4E15_84DA_A7F574975BC8) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_A698C788_CDCD_4E15_84DA_A7F574975BC8,
					DEFINITION_LITERAL VARCHAR(3000),
					LOCALITY_ID BIGINT NOT NULL
					)
CHINESE	PUBLIC	PHRASE_PHONETIC	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.PHRASE_PHONETIC(		63
					PHRASE_ID BIGINT NOT NULL,
					PHONETIC_ID BIGINT NOT NULL
					)
CHINESE	PUBLIC	PHONETIC	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.PHONETIC(		49
					PHONETIC_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_9F99BB3B_37D4_4274_9931_6286A72FD67D) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_9F99BB3B_37D4_4274_9931_6286A72FD67D,
					PHONETIC_LITERAL VARCHAR(255),
					LOCALITY_ID BIGINT NOT NULL
					)
CHINESE	PUBLIC	CHARACTER_PHONETIC	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.CHARACTER_PHONETIC(		33
					CHARACTER_PHONETIC_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_816571CF_05BB_42E4_984B_CF63A2D16C5E) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_816571CF_05BB_42E4_984B_CF63A2D16C5E,
					CHARACTER_ID BIGINT,
					PHONETIC_ID BIGINT,
					CHARACTER_DEFINITION_RELEVANCE INTEGER
					)
CHINESE	PUBLIC	CHARACTER_DEFINITION	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.CHARACTER_DEFINITION(		29
					CHARACTER_DEFINITION_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_4686A625_0E8D_4839_AD14_827D47455489) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_4686A625_0E8D_4839_AD14_827D47455489,
					CHARACTER_ID BIGINT,
					DEFINITION_ID BIGINT,
					CHARACTER_DEFINITION_RELEVANCE INTEGER
					)
CHINESE	PUBLIC	CHARACTER	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.CHARACTER(		21
					CHARACTER_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_2B611985_02D3_49CA_8B68_E1122468644B) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_2B611985_02D3_49CA_8B68_E1122468644B,
					CHARACTER_LITERAL VARCHAR(255),
					LOCALITY_ID BIGINT NOT NULL
					)
CHINESE	PUBLIC	PHRASE	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.PHRASE(		57
					PHRASE_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_72A1B1E3_5FBE_4B8D_965A_5464E66B9E9E) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_72A1B1E3_5FBE_4B8D_965A_5464E66B9E9E,
					PHRASE_LITERAL VARCHAR(255),
					LOCALITY_ID BIGINT NOT NULL
					)
CHINESE	PUBLIC	LOCALITY	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.LOCALITY(		45
					LOCALITY_ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_81B101ED_A895_4978_8974_D73C05554096) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_81B101ED_A895_4978_8974_D73C05554096,
					LOCALITY_LOCALE VARCHAR(255) NOT NULL,
					LOCALITY_DESCRIPTION VARCHAR(255)
					)
CHINESE	PUBLIC	PHRASE_TRANSLATION	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.PHRASE_TRANSLATION(		65
					PHRASE_ID BIGINT NOT NULL,
					PHRASE_TRANSLATION_ID BIGINT NOT NULL
					)
CHINESE	INFORMATION_SCHEMA	HELP	SYSTEM TABLE	CACHED	null		-8
CHINESE	INFORMATION_SCHEMA	VIEWS	SYSTEM TABLE	CACHED	null		-18
CHINESE	INFORMATION_SCHEMA	CONSTRAINTS	SYSTEM TABLE	CACHED	null		-21
CHINESE	INFORMATION_SCHEMA	RIGHTS	SYSTEM TABLE	CACHED	null		-12
CHINESE	INFORMATION_SCHEMA	FUNCTION_COLUMNS	SYSTEM TABLE	CACHED	null		-22
CHINESE	INFORMATION_SCHEMA	SETTINGS	SYSTEM TABLE	CACHED	null		-7
CHINESE	INFORMATION_SCHEMA	TABLE_TYPES	SYSTEM TABLE	CACHED	null		-4
CHINESE	INFORMATION_SCHEMA	SCHEMATA	SYSTEM TABLE	CACHED	null		-14
CHINESE	INFORMATION_SCHEMA	INDEXES	SYSTEM TABLE	CACHED	null		-3
CHINESE	INFORMATION_SCHEMA	IN_DOUBT	SYSTEM TABLE	CACHED	null		-19
CHINESE	INFORMATION_SCHEMA	CROSS_REFERENCES	SYSTEM TABLE	CACHED	null		-20
CHINESE	INFORMATION_SCHEMA	USERS	SYSTEM TABLE	CACHED	null		-10
CHINESE	INFORMATION_SCHEMA	SESSIONS	SYSTEM TABLE	CACHED	null		-26
CHINESE	INFORMATION_SCHEMA	TABLE_PRIVILEGES	SYSTEM TABLE	CACHED	null		-15
CHINESE	INFORMATION_SCHEMA	CONSTANTS	SYSTEM TABLE	CACHED	null		-23
CHINESE	INFORMATION_SCHEMA	DOMAINS	SYSTEM TABLE	CACHED	null		-24
CHINESE	INFORMATION_SCHEMA	TABLES	SYSTEM TABLE	CACHED	null		-1
CHINESE	INFORMATION_SCHEMA	COLUMNS	SYSTEM TABLE	CACHED	null		-2
CHINESE	INFORMATION_SCHEMA	COLLATIONS	SYSTEM TABLE	CACHED	null		-17
CHINESE	INFORMATION_SCHEMA	ROLES	SYSTEM TABLE	CACHED	null		-11
CHINESE	INFORMATION_SCHEMA	SEQUENCES	SYSTEM TABLE	CACHED	null		-9
CHINESE	INFORMATION_SCHEMA	FUNCTION_ALIASES	SYSTEM TABLE	CACHED	null		-13
CHINESE	INFORMATION_SCHEMA	TYPE_INFO	SYSTEM TABLE	CACHED	null		-5
CHINESE	INFORMATION_SCHEMA	TRIGGERS	SYSTEM TABLE	CACHED	null		-25
CHINESE	INFORMATION_SCHEMA	LOCKS	SYSTEM TABLE	CACHED	null		-27
CHINESE	INFORMATION_SCHEMA	COLUMN_PRIVILEGES	SYSTEM TABLE	CACHED	null		-16
CHINESE	INFORMATION_SCHEMA	CATALOGS	SYSTEM TABLE	CACHED	null		-6

This, of course, is correct.

However, when I do the same thru ODBC, I get this


TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	STORAGE_TYPE	SQL	ID

INFORMATION_SCHEMA_TABLES2
CHINESE	INFORMATION_SCHEMA	HELP	SYSTEM TABLE	CACHED		-8
CHINESE	INFORMATION_SCHEMA	VIEWS	SYSTEM TABLE	CACHED		-18
CHINESE	INFORMATION_SCHEMA	CONSTRAINTS	SYSTEM TABLE	CACHED		-21
CHINESE	INFORMATION_SCHEMA	RIGHTS	SYSTEM TABLE	CACHED		-12
CHINESE	INFORMATION_SCHEMA	FUNCTION_COLUMNS	SYSTEM TABLE	CACHED		-22
CHINESE	INFORMATION_SCHEMA	SETTINGS	SYSTEM TABLE	CACHED		-7
CHINESE	INFORMATION_SCHEMA	TABLE_TYPES	SYSTEM TABLE	CACHED		-4
CHINESE	INFORMATION_SCHEMA	SCHEMATA	SYSTEM TABLE	CACHED		-14
CHINESE	INFORMATION_SCHEMA	INDEXES	SYSTEM TABLE	CACHED		-3
CHINESE	INFORMATION_SCHEMA	IN_DOUBT	SYSTEM TABLE	CACHED		-19
CHINESE	INFORMATION_SCHEMA	CROSS_REFERENCES	SYSTEM TABLE	CACHED		-20
CHINESE	INFORMATION_SCHEMA	USERS	SYSTEM TABLE	CACHED		-10
CHINESE	INFORMATION_SCHEMA	SESSIONS	SYSTEM TABLE	CACHED		-26
CHINESE	INFORMATION_SCHEMA	TABLE_PRIVILEGES	SYSTEM TABLE	CACHED		-15
CHINESE	INFORMATION_SCHEMA	CONSTANTS	SYSTEM TABLE	CACHED		-23
CHINESE	INFORMATION_SCHEMA	DOMAINS	SYSTEM TABLE	CACHED		-24
CHINESE	INFORMATION_SCHEMA	TABLES	SYSTEM TABLE	CACHED		-1
CHINESE	INFORMATION_SCHEMA	COLUMNS	SYSTEM TABLE	CACHED		-2
CHINESE	INFORMATION_SCHEMA	COLLATIONS	SYSTEM TABLE	CACHED		-17
CHINESE	INFORMATION_SCHEMA	ROLES	SYSTEM TABLE	CACHED		-11
CHINESE	INFORMATION_SCHEMA	SEQUENCES	SYSTEM TABLE	CACHED		-9
CHINESE	INFORMATION_SCHEMA	FUNCTION_ALIASES	SYSTEM TABLE	CACHED		-13
CHINESE	INFORMATION_SCHEMA	TYPE_INFO	SYSTEM TABLE	CACHED		-5
CHINESE	INFORMATION_SCHEMA	TRIGGERS	SYSTEM TABLE	CACHED		-25
CHINESE	INFORMATION_SCHEMA	LOCKS	SYSTEM TABLE	CACHED		-27
CHINESE	INFORMATION_SCHEMA	COLUMN_PRIVILEGES	SYSTEM TABLE	CACHED		-16
CHINESE	INFORMATION_SCHEMA	CATALOGS	SYSTEM TABLE	CACHED		-6
CHINESE	PG_CATALOG	PG_VERSION	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_VERSION( VERSION INTEGER )	21
CHINESE	PG_CATALOG	PG_TYPE	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_TYPE( OID INT NOT NULL, TYPNAME VARCHAR_IGNORECASE, TYPNAMESPACE INT, TYPLEN INT, TYPBASETYPE INT )	27
CHINESE	PG_CATALOG	PG_CLASS	VIEW	MEMORY	0 AS RELTUPLES, 0 AS RELPAGES, FALSE AS RELHASRULES, FALSE AS RELHASOIDS FROM INFORMATION_SCHEMA.INDEXES /* INFORMATION_SCHEMA."" */)	29
CHINESE	PG_CATALOG	PG_ROLES	VIEW	MEMORY	A.USERS /* INFORMATION_SCHEMA."" */	23
CHINESE	PG_CATALOG	PG_ATTRIBUTE	VIEW	MEMORY	BLE_NAME = C.TABLE_NAME) AND ((T.TABLE_NAME = I.TABLE_NAME) AND (T.TABLE_SCHEMA = I.TABLE_SCHEMA))))	37
CHINESE	PG_CATALOG	PG_USER	VIEW	MEMORY	CREATE FORCE VIEW PG_CATALOG.PG_USER(OID, USENAME, USECREATEDB, USESUPER) AS SELECT ID AS OID, CAST(NAME AS VARCHAR_IGNORECASE) AS USENAME, TRUE AS USECREATEDB, TRUE AS USESUPER FROM INFORMATION_SCHEMA.USERS /* INFORMATION_SCHEMA."" */	65
CHINESE	PG_CATALOG	PG_DESCRIPTION	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_DESCRIPTION( OBJOID INTEGER, OBJSUBID INTEGER, CLASSOID INTEGER, DESCRIPTION VARCHAR_IGNORECASE )	71
CHINESE	PG_CATALOG	PG_AM	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_AM( OID INT, AMNAME VARCHAR_IGNORECASE )	69
CHINESE	PG_CATALOG	PG_PROC	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_PROC( OID INT, PRONAME VARCHAR_IGNORECASE, PRORETTYPE INT, PRONAMESPACE INT )	31
CHINESE	PG_CATALOG	PG_TRIGGER	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_TRIGGER( OID INT, TGCONSTRRELID INT, TGFOID INT, TGARGS INT, TGNARGS INT, TGDEFERRABLE BOOLEAN, TGINITDEFERRED BOOLEAN, TGCONSTRNAME VARCHAR_IGNORECASE, TGRELID INT )	33
CHINESE	PG_CATALOG	PG_ATTRDEF	VIEW	MEMORY	CREATE FORCE VIEW PG_CATALOG.PG_ATTRDEF(OID, ADSRC, ADRELID, ADNUM) AS SELECT ID AS OID, 0 AS ADSRC, 0 AS ADRELID, 0 AS ADNUM FROM INFORMATION_SCHEMA.TABLES /* INFORMATION_SCHEMA."": FALSE */ WHERE FALSE	35
CHINESE	PG_CATALOG	PG_TABLESPACE	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_TABLESPACE( OID INT, SPCNAME VARCHAR_IGNORECASE, SPCLOCATION VARCHAR_IGNORECASE, SPCOWNER INT, SPCACL ARRAY )	61
CHINESE	PG_CATALOG	PG_NAMESPACE	VIEW	MEMORY	CREATE FORCE VIEW PG_CATALOG.PG_NAMESPACE(OID, NSPNAME) AS SELECT ID AS OID, CAST(SCHEMA_NAME AS VARCHAR_IGNORECASE) AS NSPNAME FROM INFORMATION_SCHEMA.SCHEMATA /* INFORMATION_SCHEMA."" */	25
CHINESE	PG_CATALOG	PG_AUTHID	TABLE	CACHED	NLIMIT BOOLEAN, ROLPASSWORD BOOLEAN, ROLVALIDUNTIL TIMESTAMP, ROLCONFIG ARRAY )	67
CHINESE	PG_CATALOG	PG_INDEX	VIEW	MEMORY	E = I.TABLE_NAME */ WHERE (I.ORDINAL_POSITION = 1) AND ((I.TABLE_SCHEMA = T.TABLE_SCHEMA) AND (I.TABLE_NAME = T.TABLE_NAME))	39
CHINESE	PG_CATALOG	PG_SETTINGS	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_SETTINGS( OID INT, NAME VARCHAR_IGNORECASE, SETTING VARCHAR_IGNORECASE )	63
CHINESE	PG_CATALOG	PG_DATABASE	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_DATABASE( OID INT, DATNAME VARCHAR_IGNORECASE, ENCODING INT, DATLASTSYSOID INT, DATALLOWCONN BOOLEAN, DATCONFIG ARRAY, DATACL ARRAY, DATDBA INT, DATTABLESPACE INT )	59
CHINESE	PG_CATALOG	PG_GROUP	TABLE	CACHED	CREATE CACHED TABLE PG_CATALOG.PG_GROUP( OID INTEGER, GRONAME VARCHAR_IGNORECASE(0) )	73

Notice that the 'public' schema and the associated tables are gone, and that the PG tables are seen in the ODBC and not in the web console.

Any ideas on what the problem is and how to resolve it?

Thomas Mueller

unread,

Feb 1, 2008, 12:36:42 PM2/1/08

to h2-da...@googlegroups.com

Hi,

> as if I am starting from scratch

Probably you are right: you are using two databases: one in the user
home directory (~/..) and the other in the directory where the
PgServer is started. How did you start the PgServer? Did you have a
look at the docs:

http://h2database.com/html/advanced.html#odbc_driver

"By default, databases are stored in the current working directory
where the server is started."
"Use -baseDir to save databases in another directory, for example the
user home directory"

Please tell me if you need more information or if the documentation is
wrong or unclear.

Regards,
Thomas

hibikidan333

unread,

Feb 2, 2008, 12:56:00 AM2/2/08

to H2 Database

I started as the guide says:

D:\Programs\H2\bin>java -cp h2.jar org.h2.tools.Server
TCP server running on tcp://localhost:9092 (only local connections)
PG server running on pg://localhost:5435 (only local connections)
Web server running on http://localhost:8082 (only local connections)

with these ODBC settings:

Data Source: chinese
Database: chinese
Server: localhost
Port: 5435
Username: sa
Password: sa
SSL Mode: disable

I also tried by running the H2 console (not both at the same time
though)

I still get the same problem.

On Feb 2, 4:36 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > as if I am starting from scratch
>
> Probably you are right: you are using two databases: one in the user
> home directory (~/..) and the other in the directory where the
> PgServer is started. How did you start the PgServer? Did you have a
> look at the docs:
>
> http://h2database.com/html/advanced.html#odbc_driver
>
> "By default, databases are stored in the current working directory
> where the server is started."
> "Use -baseDir to save databases in another directory, for example the
> user home directory"
>
> Please tell me if you need more information or if the documentation is
> wrong or unclear.
>
> Regards,
> Thomas
>

> ...
>
> read more »

Thomas Mueller

unread,

Feb 2, 2008, 1:15:24 AM2/2/08

to h2-da...@googlegroups.com

Hi,

I looks like at one place, you connect to a database in the user home directory, but the ODBC driver uses a database in the current working directory.

> I started as the guide says:

Which guide do you refer to? Because you are using the ODBC driver, I suggest you read the documentation under 'Advanced Topics' / 'ODBC Driver' at http://www.h2database.com/html/advanced.html#odbc_driver

Here a copy of the docs:

ODBC Driver

...

Starting the Server

After installing the ODBC driver, start the H2 Server using the command line:

java -cp h2.jar org.h2.tools.Server

The PG Server (PG for PostgreSQL protocol) is started as well. By default, databases are stored in the current working directory where the server is started. Use -baseDir to save databases in another directory, for example the user home directory:

java -cp h2.jar org.h2.tools.Server -baseDir ~

The PG server can be started and stopped from within a Java application as follows:

Server server = Server.createPgServer(new String[]{"-baseDir", "~"});
server.start();
...
server.stop();

By default, only connections from localhost are allowed. To allow remote connections, use -pgAllowOthers true when starting the server.

Do you need more information?

Regards,
Thomas

hibikidan333

unread,

Feb 2, 2008, 2:00:53 AM2/2/08

to H2 Database

I did exactly that.

like you said, i think ODBC and the web console reads in two different
places.

I'll step you through what I did:

D:\Programs\H2\bin>java -cp h2.jar org.h2.tools.Server -baseDir c:
\docume~1\owner

TCP server running on tcp://localhost:9092 (only local connections)
PG server running on pg://localhost:5435 (only local connections)
Web server running on http://localhost:8082 (only local connections)

Notice the basedir switch. That's all fine and dandy. I log on with
the web console and I see my database...thats fine.

Now, when I try to make a connection via the ODBC driver....

org.h2.jdbc.JdbcSQLException: IO Exception: java.io.IOException: The
filename, d
irectory name, or volume label syntax is incorrect; c:\docume~1\owner/
\c:\docume
~1\owner/chinese.data.db [90031-65]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.convertIOException(Message.java:181)
at
org.h2.store.fs.FileSystemDisk.normalize(FileSystemDisk.java:208)
at org.h2.util.FileUtils.normalize(FileUtils.java:107)
at org.h2.engine.ConnectionInfo.getName(ConnectionInfo.java:
242)
at org.h2.engine.Engine.openSession(Engine.java:39)
at org.h2.engine.Engine.getSession(Engine.java:100)
at org.h2.engine.Session.createSession(Session.java:166)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:917)
at org.h2.Driver.connect(Driver.java:52)
at org.h2.server.pg.PgServerThread.process(PgServerThread.java:
192)
at org.h2.server.pg.PgServerThread.run(PgServerThread.java:75)
at java.lang.Thread.run(Unknown Source)
Caused by: java.io.IOException: The filename, directory name, or
volume label sy
ntax is incorrect
at java.io.WinNTFileSystem.canonicalize0(Native Method)
at java.io.Win32FileSystem.canonicalize(Unknown Source)
at java.io.File.getCanonicalPath(Unknown Source)
at
org.h2.store.fs.FileSystemDisk.normalize(FileSystemDisk.java:206)
... 10 more

Notice this: c:\docume~1\owner/\c:\docume~1\owner/chinese.data.db

So it looks like basedir associated with the web console is an
absolute path, while ODBC uses a relative path... hmm :\

On Feb 2, 5:15 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I looks like at one place, you connect to a database in the user home
> directory, but the ODBC driver uses a database in the current working
> directory.
>
> > I started as the guide says:
>
> Which guide do you refer to? Because you are using the ODBC driver, I
> suggest you read the documentation under 'Advanced Topics' / 'ODBC Driver'

> athttp://www.h2database.com/html/advanced.html#odbc_driver

>
> Here a copy of the docs:
> ODBC Driver...
>

> *Starting the Server*

>
> After installing the ODBC driver, start the H2 Server using the command
> line:
>
> java -cp h2.jar org.h2.tools.Server
>

> The PG Server (PG for PostgreSQL protocol) is started as well. *By default,

> databases are stored in the current working directory where the server is
> started. Use -baseDir to save databases in another directory, for example

> the user home directory:*
>
> *java -cp h2.jar org.h2.tools.Server -baseDir ~*

hibikidan333

unread,

Feb 2, 2008, 2:06:03 AM2/2/08

to H2 Database

Solved!

I executed the database like this:

C:\DOCUME~1\Owner>java -cp D:\Programs\H2\bin\h2.jar
org.h2.tools.Server

Where "C:\DOCUME~1\Owner" is my basedir.

Also, ODBC must include a password; I cannot use the default 'sa' with
no password for it.

This feels like a bug. Any comments?

Tony

On Feb 2, 6:00 pm, hibikidan333 <hibikij...@gmail.com> wrote:
> I did exactly that.
>
> like you said, i think ODBC and the web console reads in two different
> places.
>
> I'll step you through what I did:
>
> D:\Programs\H2\bin>java -cp h2.jar org.h2.tools.Server -baseDir c:
> \docume~1\owner
> TCP server running on tcp://localhost:9092 (only local connections)
> PG server running on pg://localhost:5435 (only local connections)

> Web server running onhttp://localhost:8082(only local connections)

Thomas Mueller

unread,

Feb 2, 2008, 2:10:35 AM2/2/08

to h2-da...@googlegroups.com

Hi,

You are right, this is a bug in H2! It is already fixed in the trunk, see the change log at http://groups.google.com/group/h2-database/web/change-log

"The acting as PostgreSQL server, when a base directory was set, and the H2 Console was started as well, the base directory was applied twice."

The only workaround is to not start the H2 Console:

java -cp h2.jar org.h2.tools.Server -pg -baseDir c:\docume~1\owner

> ODBC must include a password; I cannot use the default 'sa' with no password for it.

I didn't know that... I will have a look.

Yes,

Regards,
Thomas

hibikidan333

unread,

Feb 2, 2008, 2:30:55 AM2/2/08

to H2 Database

Thats Ok Thomas. I love your program, keep up the good work. Its
*much* faster than PostgreSQL and easier to package with a program I
am developing.

On Feb 2, 6:10 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>

> You are right, this is a bug in H2! It is already fixed in the trunk, see

> the change log athttp://groups.google.com/group/h2-database/web/change-log

>
> "The acting as PostgreSQL server, when a base directory was set, and the H2
> Console was started as well, the base directory was applied twice."
>
> The only workaround is to not start the H2 Console:
>
> java -cp h2.jar org.h2.tools.Server -pg -baseDir c:\docume~1\owner
>
> > ODBC must include a password; I cannot use the default 'sa' with no
>
> password for it.
>
> I didn't know that... I will have a look.
>
> Yes,
>
> Regards,
> Thomas
>

> On Feb 2, 2008 8:00 AM, hibikidan333 <hibikij...@gmail.com> wrote:
>
>
>
> > I did exactly that.
>
> > like you said, i think ODBC and the web console reads in two different
> > places.
>
> > I'll step you through what I did:
>
> > D:\Programs\H2\bin>java -cp h2.jar org.h2.tools.Server -baseDir c:
> > \docume~1\owner
> > TCP server running on tcp://localhost:9092 (only local connections)
> > PG server running on pg://localhost:5435 (only local connections)

> > Web server running onhttp://localhost:8082(only local connections)

Thomas Mueller

unread,

Feb 2, 2008, 6:48:32 AM2/2/08

to h2-da...@googlegroups.com

Hi,

Also, ODBC must include a password; I cannot use the default 'sa' with
no password for it.
This feels like a bug. Any comments?

I just tried this, and you are right. A password must be set in the ODBC client configuration. I will add the following to the documentation:

"PostgreSQL ODBC Driver Setup requires a database password, that means it
is not possible to connect to H2 databases without password. This is a limitation
of the ODBC driver."

I don't currently see a way to solve this problem in H2. Is it a big problem for you?

Regards,
Thomas

hibikidan333

unread,

Feb 2, 2008, 5:45:19 PM2/2/08

to H2 Database

That should be fine as long as its documented.

On Feb 2, 10:48 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

hibikidan333

unread,

Feb 2, 2008, 7:33:25 PM2/2/08

to H2 Database

I installed the latest version (1.0.66) and followed these steps:

1. Deleted all database files in C:\Docume~1\Owner (that's my home)
and D:\Programs\H2\bin
2. executed Start>Programs>H2> H2 Console
3. logged into web console with these settings :

Driver Class: org.h2.Driver
JDBC URL: jdbc:h2:~/chinese
User Name: sa

Password: sa

This created a chinese.db in C:\Docume~1\Owner

4. logged in via ODBC with these settings

Data Source: chinese
Database: chinese
Server: localhost
Port: 5435
Username: sa
Password: sa
SSL Mode: disable

This created chinese.db in D:\Programs\H2\bin

So, the problem seems to be that the basedir for the web server is set
to C:\Docume~1\Owner, while the PG server is set to D:\Programs
\H2\bin.

Of course, I can forcefully set the web console to read from C:
\Docume~1\Owner, but ideally I would like both to have the same
basedir by default.

Is this a bug? please comment.

hibikidan333

unread,

Feb 2, 2008, 7:33:49 PM2/2/08

to H2 Database

I installed the latest version (1.0.66) and followed these steps:

1. Deleted all database files in C:\Docume~1\Owner (that's my home)
and D:\Programs\H2\bin
2. executed Start>Programs>H2> H2 Console
3. logged into web console with these settings :

Driver Class: org.h2.Driver
JDBC URL: jdbc:h2:~/chinese
User Name: sa

Password: sa

This created a chinese.db in C:\Docume~1\Owner

4. logged in via ODBC with these settings

Data Source: chinese
Database: chinese
Server: localhost
Port: 5435
Username: sa
Password: sa
SSL Mode: disable

This created chinese.db in D:\Programs\H2\bin

So, the problem seems to be that the basedir for the web server is set
to C:\Docume~1\Owner, while the PG server is set to D:\Programs
\H2\bin.

Of course, I can forcefully set the web console to read from C:
\Docume~1\Owner, but ideally I would like both to have the same
basedir by default.

Is this a bug? please comment.

On Feb 3, 9:45 am, hibikidan333 <hibikij...@gmail.com> wrote:

hibikidan333

unread,

Feb 2, 2008, 7:41:47 PM2/2/08

to H2 Database

Here's another issue:

When I do this:
D:\>java -cp D:\Programs\H2\bin\h2.jar org.h2.tools.Server -baseDir "C:
\DOCUME~1\Owner"

and log onto the web console, it creates a "~" folder in C:
\DOCUME~1\Owner (C:\DOCUME~1\Owner\~) and dumps the database files in
there

my JDBC URL is jdbc:h2:~/chinese

The workaround is using
jdbc:h2:chinese

Is this a bug?

On Feb 3, 9:45 am, hibikidan333 <hibikij...@gmail.com> wrote:

Thomas Mueller

unread,

Feb 4, 2008, 2:23:54 PM2/4/08

to h2-da...@googlegroups.com

Hi,

> So, the problem seems to be that the basedir for the web server is set
> to C:\Docume~1\Owner, while the PG server is set to D:\Programs
> \H2\bin.

No. You didn't set the base dir, and you have used different database URLs:
H2 Console: jdbc:h2:~/chinese
PgServer: chinese

I suggest to use

java -cp D:\Programs\H2\bin\h2.jar org.h2.tools.Server -baseDir "C:\DOCUME~1\Owner"

H2 Console: jdbc:h2:chinese
PgServer: chinese

> java org.h2.tools.Server -baseDir "C:\DOCUME~1\Owner"
> jdbc:h2:~/chinese
> C:\DOCUME~1\Owner\~

This is strange behavior. I think I will change it in the next release so that the database is stored in the user home directory in this case.

Thanks for your help!
Thomas

Reply all

Reply to author

Forward