ODBC-connection for SQL-query to external databases assigned to a field-definition using in topic-text

[Rosenberg]

Hi Developer-Team of Help and Manual,

your product is a very useful sysytem to support help-effort to end-users!
Thanks a lot for that good job!

To improve the functionalitay of your product I have a suggestion about SQL-connectivity.
To prevent to store same information several times at different sources, it would be very helpful, if there is an ODBC-interface to connect to other souce-databases via SQL-statements to information for documentation.

1) SQL-Connection
By default SQL-connection should be available by a standard-connection-system like ODBC in windows or LDBC for java.
This way SQL - queries could be send to the source-DB via a standard-interface.
Lots of other applications like word, excel and other office-applications support this functionality.

1.1) SQL-Assignment
Every field should can have a separate ODBC-definition or use the same one as other fields.
A SQL-Connection should by assigned to a field.
1.2) Field-definition
- All definitions as they currently are for a field-varable.
- An extended register containing:
+ SQL-connection: defined valid ODBC-definition
+ SQL-query: SQL-query-text-field to the connected DB and tabels
+ Output - position: should be the current fieldposition in the topic-text
+ Format:
# Standard-Text: Formated as unicode like shown in XML-view of a topic
# HTML5-markup-text (optional in future-version)
Meaning: All HTML-code is converted to the XML-code of HelpAndManual to insert it into topic
1.3) Refresh
SQL-queries should be executed on every refresh of a certain view.
Like:
- Manual by pressing for example the key F5
- Topic-editor
- Preview
- Printing

2) Formatting
2.1) Standard
The result-text of an executed SQL-query is unicode-formated text and is inserted at the field-position in the topic-text. Field and field-position is not replaced, it is kept to keep insert-position for a refresh of its contence. Word-wrap works like the standard.

2.2) HTML
The result-text of an executed SQL-query is HTML-markup-text.
All HTML-code is converted to the XML-code of HelpAndManual to insert it into topic like normal unicode-text

3) Topic-Editing
While editing a topic-text it should be possible to refresh the field-output, to see the current view of contence. Manual refresh should work by context-menu-item of a topic-text-editor or by pressing the key F5.

4) Output
All outputs initiate an automatic refresh of all Fields containing SQL-connections before output is done. Outputs are available for:
- Preview
- Printing
- PDF-Export
- Output-desiner for PDF, Word, CHM, etc. should support SQL-field-output

5) Link to existing similar posts
There is also an issue done posted in 2018 in a german forum:
- https://ecsw-forum.helpdesign.eu/viewto ... =sql#p6747

Well this should be a first base for discussion this functionality.
This way to support ODBC-connection, should be easy to implement - I suppose.
In hope of soon implementation I spend my suggestion of extended functionality for development-team of HelpAndManual.

Kind regaards
Klemens Rosenberg (QMB)
RVC Medical IT GmbH

[Alexander Halser]

We talked in private about this for a while and my suggestion was to post such a request in the user forum to see if it stirs up interest from other users. However, I would like to clarify what is possible/thinkable and what is not.

There's no way that we implement a full-fledged database connection in Help+Manual directly. MySQL, SQL, ODBC, whatever, no chance. It must work with a REST api and responsible for the REST service is: you. It would be your job to set up the required database connections and deliver requests to this database connection via a (local) REST service.

Let me give you an example.

We could implement a "REST Object" in Help+Manual. Such an object is very much like a snippet. It could be inline, multi-line, plain text, formatted text, a picture. The data type is pretty much flexible.

How does the REST object retrieve its data? By performing a request to a local or public REST service. Imagine you have set up a REST service that runs on http://127.0.0.1/mydatabaseapi/. Now you place a REST object in a topic, like you would insert a linked snippet. When a snippet defines the file name of the snippet file, the REST object specifies an api call. Something like this:

Code: Select all

http://127.0.0.1/mydatabaseapi/?q=greetme

The REST service would respond with something like this:

Code: Select all

HTTP/1.1 200 OK
# ...
Content-Type: text/html; charset=UTF-8
Hi Klemens, glad to me you.

Or this:

Code: Select all

HTTP/1.1 200 OK
# ...
Content-Type: application/json
{"name":"Klemens","message":"Nice to mee you!"}

Or this:

Code: Select all

HTTP/1.1 200 OK
# ...
Content-Type: application/xml
<para styleclass="Normal">Hi Klemens,</para>
<para styleclass="Normal">nice to meet you! This is an <text style="font-style:italic;">XML response</text> from the REST api.</para>

The REST api must respond with either plain text, a JSON object or XML code that adheres to the Help+Manual XML schema. It is your job as a programmer to make sure that your REST service contacts the requested database in the background, retrieves the content and delivers the content in one of the formats accepted by Help+Manual.

In other words: the standardized REST api is the middle layer between Help+Manual and your database. Programming such an api is not trivial, but we can't help it - only you know which information you need to pull into a help topic, and where the information is stored.

[Rosenberg]

Hello Alexander,
thanks a lot for this information!

Yes this suggested solution seems to be a very good way to get external data from external datasources.
The mentioned layer as a REST-API-call to external data-sources is perfect to support information as needed.
ODBC was only to suggest a connection-type which is independently from type of database-connectivity.
But your'e right REST-API-calls is more independently and it's also available for other OS.
REST-API-calls are more flexible to access to external data.
Yes the programmer of the REST-API is responseble to support requested information the way HaM needs.
This point can be seen as a limitation but also as a chance to get extarnel information as needed.
Handling REST-API-Results as snippet is by sure a very good solution, because of free formatting the result of a REST-API-call to an external data-source by using a REST-API-server-interface.

Well the only thing I can see as a limitation is that a call to an external REST-server has to be done carefully, because of possible hacking the interface-call if it is not a "https"-call. While do it within a local area it should be not critical.

If I can support you with further information don't hesitate to contact me or post a reply.
Also testing effort we are pleased to support you with!
Well I'm looking forward to the new functionality of an REST-API-call with hope of a soon availability.

Kind regards
Klemens Rosenberg

[Gerold Krommer]

Hi,

I absolutely 100% sencond that request.

My use case are several complex configations that need to be documented. The config is probably not THAT dynamic and changed very often, but with a high degree of probability one either forgets or frowns the effort...

I have written several programs that read configs (mainy from databases and ini files) and produce H%M XML, which I then have to manually put into the right places... and I also tend to forget (other call it ignore...).

Doing it at build time woul dbe perfect...

regards,

/Gerold

[Ted Carlson]

I also second (third?) this. Having a capability like this would open up some interesting possibilities for my project, particularly in GIS and collaboration.