H2 database and Excel VBA

[Paluee]

Hi there,

I know this is a Java based database.
But is there a way using Excel VBA using ADO to create and
use H2 database.

I would like to use H2 database as a backend for Excel app, because
Excel is a good data entry GUI for the work that I do.

Paluee

[Kerry Sainsbury]

You could start H2 in server mode and connect to it via the ODBC driver: http://www.h2database.com/html/advanced.html#odbc_driver

Cheers
Kerry

--
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.

[Paluee]

Hi,
So I guess there is no other way than ODBC way.
I am trying to avoid that. I wish to just write code and not have to
do any kind of configuring, like setting up a datasource in the
windows controlPanel/Administrative Tools.
I was hoping for a zero administration way of connecting excel to H2.
I have eve seen the ODBC-JDBC bridge, but I don't like that solution
either.
Anybody no of any other way to get Excel-H2db to work together. Is
there
any commercial third party component that can possibly help.

OR
Is there an easy way to use OpenOffice Calc and connect to H2. Because
OpenOffice as of version 3.0 supports VBA.

Paluee

On Mar 22, 3:48 pm, Kerry Sainsbury <ke...@fidelma.com> wrote:
> You could start H2 in server mode and connect to it via the ODBC driver:http://www.h2database.com/html/advanced.html#odbc_driver
>
> Cheers
> Kerry
>

> On Tue, Mar 23, 2010 at 7:25 AM, Paluee <palme...@gmail.com> wrote:
> > Hi there,
>
> > I know this is a Java based database.
> > But is there a way using Excel VBA using ADO to create and
> > use H2 database.
>
> > I would like to use H2 database as a backend for Excel app, because
> > Excel is a good data entry GUI for the work that I do.
>
> > Paluee
>
> > --
> > 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<h2-database%2Bunsu...@googlegroups.com>

[Wildam Martin]

On Tue, Mar 23, 2010 at 01:02, Paluee <palm...@gmail.com> wrote:
> So I guess there is no other way than ODBC way.
> I am trying to avoid that. I wish to just write code and not have to
> do any kind of configuring, like setting up a datasource in the
> windows controlPanel/Administrative Tools.

If this is the only reason why you want to avoid ODBC, you can be
helped. An ODBC connection string can be defined also DSN-less. For H2
in particular you might need to test a little. As I know from other
databases - at least for DSN less connections it is important to
specify the parameters also in the correct order as the database
driver wishes.

You need to specify all needed parameters in the connection string and
you must have a "DSN=;..." at the beginning (DSN being empty).

Here is an example for MS SQL Server without the need of configuring a DSN:
ODBC;DSN=;SERVER=localhost;DATABASE=TEST;UID=sa;PWD=nothing;DRIVER={SQL Server}

For H2 it is maybe "ODBC;Data Source=;..." according to
http://www.h2database.com/html/advanced.html#odbc_driver - I did not
try this, so I can't tell you the exact valid string.

--
Martin Wildam

[Paluee]

Yes, I almost forgot about that.
DSN-less. Actually it is due to the fact that I never fully understood
the differences between ODBC (always thought that it had to have
datasource),
and confused DSN, DSN-less as though they were separate constructs,
and mixed up
OLEDB along with the mix.

Now that you have cleared up the confusion, I now see that DSN and DSN-
less
are different modes of ODBC.
Funny, how I learned COM based ideas from a Java group, instead of
from
VB6 world, from where I originally came from.

Thanks again. I can now research this out.

Paluee

On Mar 22, 8:15 pm, Wildam Martin <mwil...@gmail.com> wrote:

> On Tue, Mar 23, 2010 at 01:02, Paluee <palme...@gmail.com> wrote:
> > So I guess there is no other way than ODBC way.
> > I am trying to avoid that. I wish to just write code and not have to
> > do any kind of configuring, like setting up a datasource in the
> > windows controlPanel/Administrative Tools.
>
> If this is the only reason why you want to avoid ODBC, you can be
> helped. An ODBC connection string can be defined also DSN-less. For H2
> in particular you might need to test a little. As I know from other
> databases - at least for DSN less connections it is important to
> specify the parameters also in the correct order as the database
> driver wishes.
>
> You need to specify all needed parameters in the connection string and
> you must have a "DSN=;..." at the beginning (DSN being empty).
>
> Here is an example for MS SQL Server without the need of configuring a DSN:
> ODBC;DSN=;SERVER=localhost;DATABASE=TEST;UID=sa;PWD=nothing;DRIVER={SQL Server}
>

> For H2 it is maybe "ODBC;Data Source=;..." according tohttp://www.h2database.com/html/advanced.html#odbc_driver- I did not

[Wildam Martin]

On Tue, Mar 23, 2010 at 17:25, Paluee <palm...@gmail.com> wrote:
> Yes, I almost forgot about that.
> DSN-less. Actually it is due to the fact that I never fully understood
> the differences between ODBC (always thought that it had to have
> datasource),
> and confused DSN, DSN-less as though they were separate constructs,
> and mixed up
> OLEDB along with the mix.

You can see the ODBC with DSN like having a database available as a
predefined reference in your application server. If you want to
directly access the database you need the whole connection string
rather than just the reference. An ODBC DSN is not more than a
database registered to the system so that different application could
share it (apart from user name and password for login that has to be
specified separately in the connection string anyway).

The only thing - that I also never understood - is: Why the hack is it
so important to specify all the option in the right order as the
driver wants it. (Well this is maybe to compare with a commandline
application where you might also need to have specific option in a
specific order.)

> Now that you have cleared up the confusion, I now see that DSN and DSN-
> less are different modes of ODBC.
> Funny, how I learned COM based ideas from a Java group, instead of
> from VB6 world, from where I originally came from.

:-) Maybe it is because I am also coming from that field. I did about
15 years of Windows development (and before under MS DOS :-))) ). But
enough is enough - and I am so happy in the Linux world. :-)
BTW: Similar happens in Linux forums: They sometimes help with serious
Windows problems also.
I think that a lot of "old Windows experts" are found nowadays in the
Linux world.

> Thanks again. I can now research this out.

Let us know the final exact ODBC connection string needed.
Unfortunately it is not listed at http://www.connectionstrings.com/
nor on
http://www.carlprothman.net/Default.aspx?tabid=81

Should be added there if found out.

Live long and prosper,

Martin.

[Brian]

I believe H2 can essentially speak the same wire protocol - I haven't
tried this myself but potentially the oledb/ADO.net providers for
PgSql could work to connect to H2.

[Thomas Mueller]

Hi,

>> I would like to use H2 database as a backend for Excel app, because
>> Excel is a good data entry GUI for the work that I do.

OpenOffice supports JDBC. I never used it to edit data with OpenOffice
Calc so far, but you could the OpenOffice Database tool.

ODBC should work as well of course, but it is not the primary API.

Regards,
Thomas

[Ryan How]

Have you got the pgserver running in H2 ? on Port 5432?

From memory the default Postgres port & default H2 pgserver port might be different...

On 3/06/2013 10:41 AM, Balaji M wrote:

Hi,
 When i try to connect through DSN less  connection  i am getting error pls help

Could not Connect to the Server;
No connection could be made because the target machinne actively  refused it .
[127.0.0.1:5432]

Connection string
ODBC;DSN=;SERVER=localhost;DATABASE=GE2;UID=admin;PWD=Testing;DRIVER={PostgreSQL Unicode}

Thanks  - Balaji

--

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-US.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ryan How]

When you start the PGServer you can also set the port it is listening on.

[-pgPort <port>]    The port (default: 5435)



On 3/06/2013 3:55 PM, Balaji M wrote:

Hi ,
I am new to the H2 ...

For ODBC DSN connection by default the port no is 5435 ..it is working fine ....
For ODBC DSN less Connection by default the port no is 5432..

How to check server is running in specific port ?

Thanks & Regards
Balaji

[Balaji M]

Hi,
       Thanks, I have change the port it was working..... Thx .

Thanks & Regards
Balaji M

[Kovács András]

Hi all, im new to this topic and h2, do you know a good way to connect c# code to h2 database?

I would like to use it together with an entity data model. My connection string looks like this:

protected const string ConnectionFormatString =

                "metadata=res://{0}/{1}LocalDatabase.csdl|" +

                "res://{0}/{1}LocalDatabase.ssdl|" +

                "res://{0}/{1}LocalDatabase.msl; " +

                "provider=System.Data.SqlClient;" +

                "provider connection string=\"Data Source=Localdatabase.h2.db;" +

                "User ID=admin;Password=1234;"+

                "Persist Security Info=false\";";

I get the error: provider: Named Pipes Provider, error: 40

Do you have any idea perhaps? Should i use it throught tcp, or just the local file? I tried many things, and none of them works yet.

thx in advance!

[Thomas Mueller]

Hi,

Are you using the PostgreSQL ODBC driver?

> Should i use it throught tcp

Yes, it would need to be TCP/IP.

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 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.

[Kovács András]

Nope. I dont use any driver. I thought c# can handle it, because i also tried now with tcp connection. I set up a local database as server, and want to conect to it local as client. It should go... but it doesnt work. Do i need a driver for it? i cant avoid that?

my connection string:

                "metadata=res://{0}/{1}LocalDatabase.csdl|" +

                "res://{0}/{1}LocalDatabase.ssdl|" +

                "res://{0}/{1}LocalDatabase.msl; " +

                "provider=System.Data.SqlClient;" +

                "provider connection string=\"Data Source=tcp:localhost;Initial Catalog=Localdatabase;" +

                "User ID=admin;Password=1234;"+

                "Persist Security Info=false\";";

[Thomas Mueller]

Hi,

If you want to connect to a H2 server over TCP/IP (assuming this is what you want to do), then you do need a driver.

Regards,
Thomas