Problems with questionnaires when conecting OOBase to H2

[Riccard Montén]

Hi,

I suspect I am in the wrong Forum but since I haven`t found any better

I send my question, but please send me to the right one if there is a

more adequate one!

 

When connecting OpenOffice Base to H2 there, is one problem that nobody seems

to understand. In foras of OOB many people are trying to find ways to get around it

but sofar no solution and I have followed this subject for a long time.

Today I tested with the latest version of H2 (159) but no difference.

I use connection jdbc: h2:~/test;MODE=HSQLDB and driver org.h2.Driver

 

Problem: When using questionnaires and sub questionnaires built with OOB/H2 only the

main questionnaire works - but never the subquestionnaires. They get empty.

What in the connection between H2 and OOB stops this? Is there a way to get around 

this problem? The many tests made can be followed in the OOB fora at http://oooforum.org/forum/viewtopic.phtml?t=99146

I have thought of the possibillity that the questionnaires are built in the textsoftware (.odt)

but why do the main querstionnaire work?

Do someone on the H2 side understand this problem? Obviously noone on the OOB-side

can tell the reason.

I am a user with almost no teoretical background when it comes to how connections work

but it seems that better studied people have the same problem.

Longing for a way to come forward . or knowing it ios not possible.

 

Petit Po

[Thomas Mueller]

Hi,

I'm sorry, but I can't help here. I don't understand the problem, for
example what are subquestionnaires?

The thread you linked to is very old (Apr 13, 2010).

Could you describe what you do step by step, including the database
schema you use and the data, so I can understand the problem? I would
also need to know the exact version of OpenOffice you use, and all
config parameters and such.

Regards,
Thomas

[Riccard Montén]

Hi Thomas,

See my answers below.
Riccard
--------------------------------------------------
From: "Thomas Mueller" <thomas.to...@gmail.com>
Sent: Wednesday, August 24, 2011 7:10 PM
To: "h2-database" <h2-da...@googlegroups.com>
Subject: Re: Problems with questionnaires when conecting OOBase to H2

> Hi,
>
> I'm sorry, but I can't help here. I don't understand the problem, for
> example what are subquestionnaires?

I`m sorry for my Swenglish - I mean subforms.

> The thread you linked to is very old (Apr 13, 2010).

I know, I was surprised too! I hadn`t checked this for some time and
realized that
changes been made but the problem seem to be the same.

> Could you describe what you do step by step, including the database
> schema you use and the data, so I can understand the problem? I would
> also need to know the exact version of OpenOffice you use, and all
> config parameters and such.

Sure:

A database originally set up in OOBase 3.1 I wanted to connect to H2.
I followed "drcm:s" instructions for this from the thread I linked to you.
I created an OOB database that was connected to H2 using the JDBC-driver
org.h2.Driver
and the URL jdbc: H2:~test;MODE=HSQLDB which worked well.
The H2 1.2.140 version was used first time and I have now also tested OOBase
3.2 and H2 1.3.157 -
with the same results.

All the tables from the original OOBase version were pasted to the H2:OOB.
During this process
the schema was changed to PUBLIC everywhere and the format of all foreign
keys were changed to BIGINT.
Data consists of 15 tables describing educational courses, the organisation
and teachers delivering theese, students,
booking and so on - nothing complicated and no big number of records (2000
students, 60 courses held
at 3 times a year in average).

All queries were pasted to the H2-connected Base and the column names in the
SQL-commands
were changed to the PUBLIC SCHEMA to have the queries to work.
They all worked and there are no problems to create new queries.

The problems starts when trying to create forms with a main form with
subforms.
In OOBase you can create forms using tables and queries easily with no
SQL-writing - just clicking.
You add/use the information which are linked with primary/foreign keys in
theese forms
meaning that information in the main form can be complemented with
information in the subform that
are placed in the linked table.
When connected to H2 information from the main form is presented correctlyt
and new info can be added,
BUT - the subforms never shows any information. They never seems to link to
the main form as they should.

The connection between main form and subform is created by linking the
columns that connect
the tables/qeuries that lie behind the main- and subforms.

Creating theese forms takes place in the Writer software (.odt) in
OpenOffice and works
with no problems in OOBase using the embedded HSQLDB database - but this
shift to Writer may be the
cause of the problem. But I think that since all info in the main form is
fetched from H2_db also the
info for the subforms could be!?

If this is possible to solve I would be most greatful.

Thanks for your efforts.

Riccard

In OOBase
> Regards,
> Thomas
>
> --
e> 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,

I'm sorry for the delay. I will not have the time to install OOBase myself and test it, so my answer might not be helpful...

and the URL jdbc: H2:~test;MODE=HSQLDB which worked well.

To ensure this is not the problem, I suggest to use an absolute path, for example jdbc:h2:/data/test;MODE=HSQLDB - but I know the chance this will solve the problem is very small.

I guess to analyze the problem one would have to verify the SQL statements generated by OOBase. To do that, you could use

jdbc:h2:/data/test;MODE=HSQLDB;TRACE_LEVEL_FILE=2

or

jdbc:h2:/data/test;MODE=HSQLDB;TRACE_LEVEL_FILE=3

and analyze the file /data/test.trace.db, which then contains all SQL statements generated by OOBase.

Regards,

Thomas

[RICCARD]

Hi,

 

Thanks a lot for your suggestions which helped come forward - but there is still a bit left.

 

The change in URL didn`t create any difference as you guessed.

Learning about the trace.db-file gave me errormessages showing what was missing in the

links between the main form and subforms. But I haven`t succeded in getting it to work fully.

In OOBase you enter only the name of the field that should connect between tables.

Now I entered the full field-name TEST.PUBLIC.TABLE.FIELD which resulted in that the subforms

were no longer empty - but I could never have the subforms to connect the correct information

between the forms. I have tried to enter full SQL-sentences but with no better result.

 

Then i tested to create another database in Embedded mode - and now the forms WORK!!!

 

In the server mode i can only find SQLs like this: 

/*SQL l:119 #:1*/SELECT * FROM \"OVDB1\".\"PUBLIC\".\"Utbildningar\" WHERE \"OVDB1\".\"PUBLIC\".\"Utbildningar\".\"UtbShort\" = ?

 

... but in the embedded mode there are also the prep statements below but I don`t understand there exact meaning.

I have tried to find a SQL telling something like in a query combining the tables - which also are used in the forms: 

SELECT "Utbildningar".*, "Utbildningstillfällen".* FROM "OVDB1"."PUBLIC"."Utbildningstillfällen", "OVDB1"."PUBLIC"."Utbildningar" 

WHERE "Utbildningstillfällen"."UtbShort" = "Utbildningar"."UtbShort"  - but didn`t succeed.

/*SQL l:98 #:1*/SELECT * FROM \"OVDB1\".\"PUBLIC\".\"Utbildningar\" WHERE \"OVDB1\".\"PUBLIC\".\"Utbildningar\".\"UtbShort\" = ?

/**/PreparedStatement prep246 = conn2.prepareStatement("SELECT * FROM \"OVDB1\".\"PUBLIC\".\"Utbildningstillf\u00e4llen\" WHERE ( 0 = 1 ) AND ( ( \"UtbShort\" = ? ) )", 1003, 1007); 

/**/PreparedStatement prep273 = conn2.prepareStatement("SELECT * FROM \"OVDB1\".\"PUBLIC\".\"Utbildningstillf\u00e4llen\" WHERE ( \"UtbShort\" = ? )", 1005, 1008);

("SELECT * FROM \"OVDB1\".\"PUBLIC\".\"Utbildningstillf\u00e4llen\" WHERE ( 0 = 1 ) AND ( ( \"UtbShort\" = ? ) )", 1003, 1007);

I have used h2 1.3.159 and OO 3.2, 3.3 and 3 OOo-dev.

My question is whether you have any idea how to make the forms work in client/server mode. Mixed mode I never tested but that is also possible
if it mean any difference. 

Another question: as you see I use Swedish letters like ÅÄÖ which causes no problems except when using the command-field in h2.
When clicking a table in the left column I get SELECT * FROM "Utbildningstillfällen" and when i run this I get an errormessage
" Table "Utbildningstillfällen" " not found. So I have to change "ä" to "ä" and then it works.
This problem can be new because I haven`t discovered it before.

Thank you Thomas for your immense work and that you could use some time to my problem.

Best regards

Riccard 

 

From: Thomas Mueller

Sent: Tuesday, October 11, 2011 7:03 PM

To: h2-da...@googlegroups.com

Subject: Re: Problems with questionnaires when conecting OOBase to H2

--