How to show SQL STATUS message in odt Form?

[balamut]

Hello, I have a strange problem.

I have created some database in HSQL3.2.2, data integrity checked using constraints. When I created frontend forms in Ooo Base environment, database messages (like"integrity constraint violation...") were shown correctly as standard message box.

When I saved the same form as standalone odt file, these messages are not displayed at all. Invalid record is not possible to save (its correct), but user doesnt see the reason, which is described in database exception message.

Being more database than macro programmer, i have the whole application written using sql instrumensts, now i dont see the way, how to simply communicate with frontend user. Im looking for help - solution, or workaround, of this problem.

[Sliderule]

Welcome to the OpenOffice / LibreOffice Base forum.

Please note, I suspect in your original post, you are really using HSQL Version 2.3.2 ( not 3.2.2 ) since, as of the date I am writing this, 2.3.4 is the latest. I only mention this for others reading the post.

Since you are NOT using the Embedded database ( congratulations ) HSQL 1.8.0.10 . . . you have many additional database capabilities. Including the use of TRIGGER . This will allow your database back-end to perform an action before an INSERT or UPDATE or DELETE . . . including writing validations AND user defined / friendly error messages. Please note, that, another real advantage for using a TRIGGER with the database back-end is, anytime you make a change to database values, it is checked in ONE PLACE. That means, you do not have to re-create multiple 'checks', for example, with SQLTOOL, vs, OpenOffice Base, vs OpenOffice Base Form, vs SQLWorkbench/J etc.

Please do a search in this forum, using the keywords: +CREATE +TRIGGER +SQLSTATE .

For example:

1. viewtopic.php?f=61&t=68434&p=333097&hil ... te#p333097
2. viewtopic.php?f=61&t=78134&p=357705&hil ... te#p357705

Furthermore, I would suggest you read the HSQL documentation, as HTML in link below, or, from the HSQL PDF file. Chapter 9. Triggers

http://www.hsqldb.org/doc/2.0/guide/triggers-chapt.html

If you create a TRIGGER , and, want to discuss it ( have specific questions ), or, show others what you wrote, please add the code here, and, either myself, or, other forum members might help.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[balamut]

Thank you for your reply. Yes, the engine is ceirtamly 2.3.2

Maybe I had not clearly explain the essence of problem, which is NOt in database engine but probably in Open Office Writer forms. Probably it was my mistake, to post this request in Tables&Queries thread, but i had not found better one.

So Ill try once more:

- I have created edit Form in Ooo Base
- Saved it in Ooo Base
- Worked fine, that means when HSQL reported error, upon the form popped up status window with SQLSTATE Message (something like "integrity constraint violation..." )
- Needed this Form Read-Only i saved it as ODT file.
- when the database error occurs in that standalone form, no message appears - wrong values ar not saved (db engine works ok), but user doesnt see any response and doesnt of course know, whats happened

Im lookig for the way, how to catch or (better) directly display database response in standalone odt Form.

*** UPDATE ***
when I tried to signal error from form Events using simple macro as described in other thread viewtopic.php?f=61&t=68434&p=306909&hil ... ed#p331145 it worked (Form knows, that some error occured)

when data in standalone Form were changed (incorrectly) and unsaved, I tried to close the Form document.
At this moment displayed popup with question like "Do you want to save changed data?" (Not exactly - I am translating from Czech)
When I clicked Yes, Writer displayed correct message (that "integrity constraint violation..." )
This occurs also when switching form to design mode, but only sometimes

It seems, that database message is generated, but Writer displays it only in some situation

[Sliderule]

Just so you know / understand. When using, NOT a macro, but, having the data validation and any associated error message generated by the database ( that is using a TRIGGER ), in a form, it will be a pop-up window.

I would recommend, you try it, perhaps with a new dummy ( fake ) table, perhaps one, for example, if value, to be inserted, of an integer is greater than 300, your TRIGGER generates the error message. Just try it. This way, the error message, is exactly what you want, with your words. Put another way, the error message pop-up is 'user-friendly', but, not computereze.

Just a thought.

Sliderule

[balamut]

Just so you know / understand. When using, NOT a macro, but, having the data validation and any associated error message generated by the database ( that is using a TRIGGER ), in a form, it will be a pop-up window.

i understand, assumed it - but that pop-up simply DOES NOT APPEAR when using form - its strange as i described in update of my second post...

Triggers and other exceptions work ok, but nor SQLSTATE nor MESSAGE_TEXT are visible in form. When I am modyfying the same database directly, using sql commanda, all works fine...

...in referenced thread had someone similar problem

[Sliderule]

Thank you for explaining further. Therefore, I have no further suggestion.

Perhaps, another forum reader might be able to make a suggestion. Sorry I could not help further.

Sliderule

[balamut]

Anyway, thank you very much