H2 rev engineer

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

H2 rev engineer

Tony Giaccone
I created an H2 database, using our standard SQL. Now I'd like to
rev-engineer that db with cayenne, but the schema doesn't show up in the
modeler.

To reproduce this problem, I follow these steps:

$ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers

this creates a H2 database.

I connect with Squirrel SQL Client
driver: org.h2.Driver
URL: jdbc:h2:mem:test;MVCC=TRUE

This connects to the H2 Db, where I execute

CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;

This creates the new schema which, when I refresh the tree in squirrel, is
displayed.

I then try to rev-engineer with the cayenne modeler 4.1.M1

Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA and
PUBLIC the newly created  schema  CORE is not in the list.


Any Suggestions?


Tony Giaccone
Reply | Threaded
Open this post in threaded view
|

Re: H2 rev engineer

Mike Kienenberger
I see a couple of possible reasons:

First I would guess that "jdbc:h2:mem" is creating a separate
in-memory database rather than using your tcp-served db.

Second, you might need to specify your non-standard schemas like as follows.

Below is a url I use modified for what values you provided:

jdbc:h2:tcp://localhost/<path-to-test>;AUTO_SERVER=TRUE;MVCC=TRUE;SCHEMA_SEARCH_PATH=CORE_OPERATION

On Fri, Oct 20, 2017 at 11:55 AM, Tony Giaccone <[hidden email]> wrote:

> I created an H2 database, using our standard SQL. Now I'd like to
> rev-engineer that db with cayenne, but the schema doesn't show up in the
> modeler.
>
> To reproduce this problem, I follow these steps:
>
> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>
> this creates a H2 database.
>
> I connect with Squirrel SQL Client
> driver: org.h2.Driver
> URL: jdbc:h2:mem:test;MVCC=TRUE
>
> This connects to the H2 Db, where I execute
>
> CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;
>
> This creates the new schema which, when I refresh the tree in squirrel, is
> displayed.
>
> I then try to rev-engineer with the cayenne modeler 4.1.M1
>
> Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA and
> PUBLIC the newly created  schema  CORE is not in the list.
>
>
> Any Suggestions?
>
>
> Tony Giaccone
Reply | Threaded
Open this post in threaded view
|

Re: H2 rev engineer

Andrus Adamchik
In reply to this post by Tony Giaccone
> jdbc:h2:mem:test;MVCC=TRUE

This is an in-memory DB URL. So it exists in Squirrel SQL Client process and not on disk. Try an experiment - close and reopen Squirrel SQL Client and see if the DB is still there.

You will need to use a URL that points to a file. E.g. jdbc:h2:/data/sample

FWIW I prefer Derby for my embedded DB. Feels more reliable than H2 or HSQL. Also has an in-memory and on-disk modes.

Andrus


> On Oct 20, 2017, at 6:55 PM, Tony Giaccone <[hidden email]> wrote:
>
> I created an H2 database, using our standard SQL. Now I'd like to
> rev-engineer that db with cayenne, but the schema doesn't show up in the
> modeler.
>
> To reproduce this problem, I follow these steps:
>
> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>
> this creates a H2 database.
>
> I connect with Squirrel SQL Client
> driver: org.h2.Driver
> URL: jdbc:h2:mem:test;MVCC=TRUE
>
> This connects to the H2 Db, where I execute
>
> CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;
>
> This creates the new schema which, when I refresh the tree in squirrel, is
> displayed.
>
> I then try to rev-engineer with the cayenne modeler 4.1.M1
>
> Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA and
> PUBLIC the newly created  schema  CORE is not in the list.
>
>
> Any Suggestions?
>
>
> Tony Giaccone

Reply | Threaded
Open this post in threaded view
|

Re: H2 rev engineer

Tony Giaccone
In reply to this post by Mike Kienenberger
Mike & Andrus,

Thanks for your help you got me a bit closer.

So one step forward.. but not quite there.

So, here's the path now...

In the first shell:

$ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers

In a second shell

java -cp h2*.jar org.h2.tools.RunScript -url
"jdbc:h2:tcp://localhost/mem:test" -user sa -script ../script.sql


This works. The SQL in the script.sql gets executed and I can connect with
Squirrel nwith the following URL:

jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE

I can view the changes. New Schema's are present, new tables, selects
against the tables get the correct number of rows.  All done using Squirrel.

I then go to Cayenne Modeller and try to connect for rev engineering.

When I look at the log file, it's show's that it's connecting to the DB
succesfully...

Oct 20, 2017 12:45:58 PM INFO: Connecting to
'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
Oct 20, 2017 12:45:58 PM INFO: Connecting to
'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
Oct 20, 2017 12:45:58 PM INFO: Connecting to
'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.

But the modeller puts up a dialog:

Connection is broken: "unexpected status 16777216 [90067-192]

And that's it, nothing more happens.


Now I should also point out that Squirrel also puts up a dialog box on
connection:

Error occurred during task execution:
for input string: "127.0.0.1:9092"

But when I dismiss that error and examine the log file, it shows:

Caused by: java.lang.NumberFormatException: For input string: "
127.0.0.1:9092"
    at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Integer.parseInt(Integer.java:580)
    at java.lang.Integer.valueOf(Integer.java:740)
    at java.lang.Integer.decode(Integer.java:1197)
    at org.h2.tools.SimpleResultSet.getInt(SimpleResultSet.java:709)


But Squirrel continues on and manages to read the object tree and execute
SQL against the db.


Tony



On Fri, Oct 20, 2017 at 12:04 PM, Mike Kienenberger <[hidden email]>
wrote:

> I see a couple of possible reasons:
>
> First I would guess that "jdbc:h2:mem" is creating a separate
> in-memory database rather than using your tcp-served db.
>
> Second, you might need to specify your non-standard schemas like as
> follows.
>
> Below is a url I use modified for what values you provided:
>
> jdbc:h2:tcp://localhost/<path-to-test>;AUTO_SERVER=TRUE;MVCC
> =TRUE;SCHEMA_SEARCH_PATH=CORE_OPERATION
>
> On Fri, Oct 20, 2017 at 11:55 AM, Tony Giaccone <[hidden email]>
> wrote:
> > I created an H2 database, using our standard SQL. Now I'd like to
> > rev-engineer that db with cayenne, but the schema doesn't show up in the
> > modeler.
> >
> > To reproduce this problem, I follow these steps:
> >
> > $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
> >
> > this creates a H2 database.
> >
> > I connect with Squirrel SQL Client
> > driver: org.h2.Driver
> > URL: jdbc:h2:mem:test;MVCC=TRUE
> >
> > This connects to the H2 Db, where I execute
> >
> > CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;
> >
> > This creates the new schema which, when I refresh the tree in squirrel,
> is
> > displayed.
> >
> > I then try to rev-engineer with the cayenne modeler 4.1.M1
> >
> > Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA
> and
> > PUBLIC the newly created  schema  CORE is not in the list.
> >
> >
> > Any Suggestions?
> >
> >
> > Tony Giaccone
>
Reply | Threaded
Open this post in threaded view
|

Re: H2 rev engineer

Andrus Adamchik
This unfortunately goes way beyond my H2 knowledge. H2 is good for small tests in embedded mode, but I never tried running it as a server.

Since you are no longer embedding it, is there a reason to use H2 to begin with? You can run MySQL or PostgreSQL on Docker or something.

Andrus

> On Oct 20, 2017, at 7:54 PM, Tony Giaccone <[hidden email]> wrote:
>
> Mike & Andrus,
>
> Thanks for your help you got me a bit closer.
>
> So one step forward.. but not quite there.
>
> So, here's the path now...
>
> In the first shell:
>
> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>
> In a second shell
>
> java -cp h2*.jar org.h2.tools.RunScript -url
> "jdbc:h2:tcp://localhost/mem:test" -user sa -script ../script.sql
>
>
> This works. The SQL in the script.sql gets executed and I can connect with
> Squirrel nwith the following URL:
>
> jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE
>
> I can view the changes. New Schema's are present, new tables, selects
> against the tables get the correct number of rows.  All done using Squirrel.
>
> I then go to Cayenne Modeller and try to connect for rev engineering.
>
> When I look at the log file, it's show's that it's connecting to the DB
> succesfully...
>
> Oct 20, 2017 12:45:58 PM INFO: Connecting to
> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
> Oct 20, 2017 12:45:58 PM INFO: Connecting to
> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
> Oct 20, 2017 12:45:58 PM INFO: Connecting to
> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
>
> But the modeller puts up a dialog:
>
> Connection is broken: "unexpected status 16777216 [90067-192]
>
> And that's it, nothing more happens.
>
>
> Now I should also point out that Squirrel also puts up a dialog box on
> connection:
>
> Error occurred during task execution:
> for input string: "127.0.0.1:9092"
>
> But when I dismiss that error and examine the log file, it shows:
>
> Caused by: java.lang.NumberFormatException: For input string: "
> 127.0.0.1:9092"
>    at
> java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
>    at java.lang.Integer.parseInt(Integer.java:580)
>    at java.lang.Integer.valueOf(Integer.java:740)
>    at java.lang.Integer.decode(Integer.java:1197)
>    at org.h2.tools.SimpleResultSet.getInt(SimpleResultSet.java:709)
>
>
> But Squirrel continues on and manages to read the object tree and execute
> SQL against the db.
>
>
> Tony
>
>
>
> On Fri, Oct 20, 2017 at 12:04 PM, Mike Kienenberger <[hidden email]>
> wrote:
>
>> I see a couple of possible reasons:
>>
>> First I would guess that "jdbc:h2:mem" is creating a separate
>> in-memory database rather than using your tcp-served db.
>>
>> Second, you might need to specify your non-standard schemas like as
>> follows.
>>
>> Below is a url I use modified for what values you provided:
>>
>> jdbc:h2:tcp://localhost/<path-to-test>;AUTO_SERVER=TRUE;MVCC
>> =TRUE;SCHEMA_SEARCH_PATH=CORE_OPERATION
>>
>> On Fri, Oct 20, 2017 at 11:55 AM, Tony Giaccone <[hidden email]>
>> wrote:
>>> I created an H2 database, using our standard SQL. Now I'd like to
>>> rev-engineer that db with cayenne, but the schema doesn't show up in the
>>> modeler.
>>>
>>> To reproduce this problem, I follow these steps:
>>>
>>> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>>>
>>> this creates a H2 database.
>>>
>>> I connect with Squirrel SQL Client
>>> driver: org.h2.Driver
>>> URL: jdbc:h2:mem:test;MVCC=TRUE
>>>
>>> This connects to the H2 Db, where I execute
>>>
>>> CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;
>>>
>>> This creates the new schema which, when I refresh the tree in squirrel,
>> is
>>> displayed.
>>>
>>> I then try to rev-engineer with the cayenne modeler 4.1.M1
>>>
>>> Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA
>> and
>>> PUBLIC the newly created  schema  CORE is not in the list.
>>>
>>>
>>> Any Suggestions?
>>>
>>>
>>> Tony Giaccone
>>

Reply | Threaded
Open this post in threaded view
|

Re: H2 rev engineer

Mike Kienenberger
I use H2 as my test environment (alternative to the Oracle instances
used for production) as it is almost drop-in compatible with Oracle.
I almost always use it in file mode, though.   I have used it in
tcp/ip mode a few times without issue.

I never have used the run script classes.

I recommend you try using the url format I sent you before that runs
it in file mode.  That will help you determine if the problem is
entirely with your tcp server setup (if it works in file mode but not
tcp/ip mode) or if there's some more general problem.

Also, I'm using H2 with JPA not Cayenne.   For my legacy Cayenne
project, I use HSQLDB for testing and a development oracle
environment.   So if there's some H2/Cayenne interaction that's
causing your problem, I can't help with that.   The H2 developer has
always been good responding to questions posted on the support
channels, though, especially if you can reduce the problem to
something specific to H2.





On Tue, Oct 24, 2017 at 8:30 AM, Andrus Adamchik <[hidden email]> wrote:

> This unfortunately goes way beyond my H2 knowledge. H2 is good for small tests in embedded mode, but I never tried running it as a server.
>
> Since you are no longer embedding it, is there a reason to use H2 to begin with? You can run MySQL or PostgreSQL on Docker or something.
>
> Andrus
>
>> On Oct 20, 2017, at 7:54 PM, Tony Giaccone <[hidden email]> wrote:
>>
>> Mike & Andrus,
>>
>> Thanks for your help you got me a bit closer.
>>
>> So one step forward.. but not quite there.
>>
>> So, here's the path now...
>>
>> In the first shell:
>>
>> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>>
>> In a second shell
>>
>> java -cp h2*.jar org.h2.tools.RunScript -url
>> "jdbc:h2:tcp://localhost/mem:test" -user sa -script ../script.sql
>>
>>
>> This works. The SQL in the script.sql gets executed and I can connect with
>> Squirrel nwith the following URL:
>>
>> jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE
>>
>> I can view the changes. New Schema's are present, new tables, selects
>> against the tables get the correct number of rows.  All done using Squirrel.
>>
>> I then go to Cayenne Modeller and try to connect for rev engineering.
>>
>> When I look at the log file, it's show's that it's connecting to the DB
>> succesfully...
>>
>> Oct 20, 2017 12:45:58 PM INFO: Connecting to
>> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
>> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
>> Oct 20, 2017 12:45:58 PM INFO: Connecting to
>> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
>> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
>> Oct 20, 2017 12:45:58 PM INFO: Connecting to
>> 'jdbc:h2:tcp://localhost/mem:test;MVCC=TRUE;' as 'sa'
>> Oct 20, 2017 12:45:58 PM INFO: +++ Connecting: SUCCESS.
>>
>> But the modeller puts up a dialog:
>>
>> Connection is broken: "unexpected status 16777216 [90067-192]
>>
>> And that's it, nothing more happens.
>>
>>
>> Now I should also point out that Squirrel also puts up a dialog box on
>> connection:
>>
>> Error occurred during task execution:
>> for input string: "127.0.0.1:9092"
>>
>> But when I dismiss that error and examine the log file, it shows:
>>
>> Caused by: java.lang.NumberFormatException: For input string: "
>> 127.0.0.1:9092"
>>    at
>> java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
>>    at java.lang.Integer.parseInt(Integer.java:580)
>>    at java.lang.Integer.valueOf(Integer.java:740)
>>    at java.lang.Integer.decode(Integer.java:1197)
>>    at org.h2.tools.SimpleResultSet.getInt(SimpleResultSet.java:709)
>>
>>
>> But Squirrel continues on and manages to read the object tree and execute
>> SQL against the db.
>>
>>
>> Tony
>>
>>
>>
>> On Fri, Oct 20, 2017 at 12:04 PM, Mike Kienenberger <[hidden email]>
>> wrote:
>>
>>> I see a couple of possible reasons:
>>>
>>> First I would guess that "jdbc:h2:mem" is creating a separate
>>> in-memory database rather than using your tcp-served db.
>>>
>>> Second, you might need to specify your non-standard schemas like as
>>> follows.
>>>
>>> Below is a url I use modified for what values you provided:
>>>
>>> jdbc:h2:tcp://localhost/<path-to-test>;AUTO_SERVER=TRUE;MVCC
>>> =TRUE;SCHEMA_SEARCH_PATH=CORE_OPERATION
>>>
>>> On Fri, Oct 20, 2017 at 11:55 AM, Tony Giaccone <[hidden email]>
>>> wrote:
>>>> I created an H2 database, using our standard SQL. Now I'd like to
>>>> rev-engineer that db with cayenne, but the schema doesn't show up in the
>>>> modeler.
>>>>
>>>> To reproduce this problem, I follow these steps:
>>>>
>>>> $ java -cp bin/h2*.jar org.h2.tools.Server -tcp -tcpAllowOthers
>>>>
>>>> this creates a H2 database.
>>>>
>>>> I connect with Squirrel SQL Client
>>>> driver: org.h2.Driver
>>>> URL: jdbc:h2:mem:test;MVCC=TRUE
>>>>
>>>> This connects to the H2 Db, where I execute
>>>>
>>>> CREATE SCHEMA IF NOT EXISTS CORE AUTHORIZATION SA;
>>>>
>>>> This creates the new schema which, when I refresh the tree in squirrel,
>>> is
>>>> displayed.
>>>>
>>>> I then try to rev-engineer with the cayenne modeler 4.1.M1
>>>>
>>>> Only two schema's show up in the modeler drop down, INFORMATION_SCHEMA
>>> and
>>>> PUBLIC the newly created  schema  CORE is not in the list.
>>>>
>>>>
>>>> Any Suggestions?
>>>>
>>>>
>>>> Tony Giaccone
>>>
>