[WM24] Is it possible to convert HFSQL to Mysql?

Hello everyone,

I am wondering if it is possible to convert HFSQL to Mysql and what are the methods I can take, and is there any ramifications for using Mysql for android/ios apps outside of the need to create REST to connect the app to the DB?

yes, it's possible -IF- you are not using anything that is HF specific. How do you know? You try and check what's broken with a HUGE round of comprehensive testing

How? Declare two connections, one to HF, the other to mysql, read on one side, write on the other...

Ramifications :
- You will not be able to use the automatic DB schema modification anymore (only for HF) which means each time you change your DB schema, you will have to create alter table queries and run them against any existing DB (for me that's enough to say no)

On the questions you didn't ask:
- NO, you shouldn't work like that for a mobile application. You will run into trouble each time the connection drops. and mysql does not have the automatic reconnection system offered by HFSQL
- and no, you shouldn't work with ANY distant DB, instead, use a local HF DB and a replication system to have a robust application. I'm using Fabrice's open source WXReplication for my apps, and if you really want to use mysql on your servers, you can do it as WXReplication is DB neutral.

Hi Robert,

in addition to Argus:
I switched an WD application to MYSql, and can say that there is a noticable performance improvement.
Also I am the opion that MySQL is a grown database system, compared to MySQL HFCS is a toy.
(Replication System, management tools, performance).
This application is very database orientated, but I had a very few changes in code only.

Regarding the automatic DB schema modification:
I made my own update procedure, so I store the requered database version in the exe, the current version in a table called dbVersion, and a textfile containing SQL Alter Table statements which is delivered with the exe.
Everytime the exe starts it checks wheather the current DB version is older than the version needed by the exe I start the Update procedure.
This procedure reads the needed SQL statements and executes them. This works for many years now, and I spent about half a day to program it.

Regarding connecting the mobile app to directly to the server: in addition to Argus this is a critical security issue.
Opening HF for direct connections might be tolerable, as HF is a very unknown database system, so there is a good chance that this is not recognized by the "bad guys" scanning your server, but if you open a well known db system like mysql a compromised system is not a question of whether or not, but of when and how.

Have a look at SOAP Services, this is a very easy and fast solution to consume a database on a distant server in a fast and secure way.

Regards

Stefan.



Edited 1 time(s). Last edit at 07/01/2020 06:08PM by stefan.kern.

Stefan and Argus,

I thank the both of you for your input in this regard, and truth be told, I am at a loss on how to deal with the database, because at the moment we are using a direct connection to a HFSQL-centered cloud, which in retrospect, may be fine for an alpha build of my application, but moving forward I am considering on which option I can choose, because I recall that one of the biggest downsides of HFSQL is that it can only retain 10 thousand records, whilst the app I am making will be catered to at least 10% of the population of my country, so from both a technical and software stand point I don't see it feasible to keep the current configuration, especially from a security stand point .

Hence why I need advice on where to go forward from here

Hi Robert,

as Argus said:
- use a local database on the device, so the user works on a local database with a good performance, independant of the internet connection quality. You can manage everything locally.
- Then use a replication system (I personally had a look at Fabrice Solution, and as far as I know this solution can only handle H-commands, but no Inserrt or Update SQL commands.) So I am using SOAP Services, which are very well implemented in WX. Have a look at them, they are pretty simple and powerfull, The only disadvantage: They are hard to debug (or I didn't find the right solution until now).

Using SOAP Services you can crypt your payload, implement your own acess system (to forbid acess to he service for non authorized users) and so on.

Regards

Stefan.

Stefan,

wouldn't using a local database bloat the apk size? Or is my understanding of local DB wrong?

Robert,

I agree with the others. Use a local database on the device and some kind of middleware to sync/replicate with the backoffice for the reasons mentioned. And will add another one.

When you use direct access to a backoffice database the only changes allowed in the database structure are new fields. Whenever you change a existing table or field ALL devices using your app will run into an error. And that's quite an issue while you are not into control of those users and devices. They are forced to install an update first before to move on. Let's say they entered a series of data and are then unable to update the ont the server. With the amount of users you have in mind, that will lead to more support for sure.

With soap or rest services you can create different versions (endpoints) for each outstanding version. Thus supporting different versions of your app (although I would encourage users to install the latest at some point in time)

Arie

RobertLeo Wrote:
-------------------------------------------------------
> Stefan,
>
> wouldn't using a local database bloat the apk
> size? Or is my understanding of local DB wrong?


Hi Robert,

I would not deliver the tables within the APK.
Just do Hcreation or Hcreationifnotfound()

If you use HF on Android you have to deliver the database framework, which is quite huge, but you can also use sqlite on Android, which is found on all devices.



Regards

Stefan.

ArieM and Stefan,

Thank you for the insight, but for the sake of dispelling my confusion, when the both of you say local database, do you mean include the database within the app/phone or do you mean something else?

Hi Robert,

I have 2 apps with a different approach.

First app creates, on the device, a new empty database. Then contacts the webservice to get it's data, which then is stored locally.

Second app starts the same, but is only getting some base data like projects and so on. User then picks a project and then calls the webservice for data (adresses and workorders). Due to the requirements this can be a lot of data. In the early days I added all this data locally record-by-record, but that was acting too slow.Now I let the webservice generate a sqllite database and do a download of this file.

Both times SQLite by the way, mainly because HFSQL wasn't an option few years ago. But sqlite does a fine job, it's also the standard on Android.

So you can include a database in the APK if it holds some inital settings whcih are always the same and required for your app to start. Otherwise just create an empty database on the device

Arie

ArieM,

Thank you for the information, in your experience, does including the database in the app itself cause the apk to increase in size? And do I need windev and webdev to be able to do this?

Yes,

the APK will incrase. An APK is just a ZIP of a bunch of files. If you rename one you can easily view it with zip-viewer.
To give you an idea I have a sqlite file with about 1000 record (mainly orderinfo and some small other tables) which is 2MB. But it depends of course on the number of tables, field and if it holds large pieces of text or just numbers.

You don't need WB to develope webservices, can be done with WD (not WM!) if you already have that.
You do need however the webdev engine on the back office server. This engine is "running" your code, on top of IIS or Apache.
There is a free version which allow you to support 10 simultaneous users. If that's not enough you need to but the unlimited version 299 euros.

Arie

ArieM,

if it is possible can I have an example of your app? And I am honestly unsure on how it will work out on my app, because there will be hundred thousands of records with pictures included within.

Ah, that may be a problem, because I have webdev 24, but no Windev. And can you elaborate on what do you mean by 10 simultaneous users? Is it for the engine? And do you mean 10 users using webdev concurrently or the app I am developing?

Quote
Is it for the engine? And do you mean 10 users using webdev concurrently or the app I am developing?

Yes, 10 concurrent users connecting to your webservice (the engine)

It's not an option to download that amount of images on a mobile device.(either by download of indluded in the APK). Far too much data. You need some mechanism to download the required ones on-the-fly.
Maybe the "WD Webservice Server" example is a good starting point. It does basicly that. Has a server-side webservice and a WD client to call that webservice. I assume the code can be ported to WM quite easy.

I can't give you a copy of my app I'm afraid. It's not really my app, but just one I'm working on.

There's also a nice example in LST113 called "WM Messenger". It's a fully functioning messaging app like whatsapp Inluding a webservice and the option to download photos. Normally you have to buy an LST but maybe PCSoft is willing to send you this particular example. Code is in French btw, but all functioncalls can be translated into english with WD. For all comments (as in in-code-documentation) you need Google translate if you don't understand French :-)

Arie

you said : ...of the biggest downsides of HFSQL is that it can only retain 10 thousand records....

That is COMPLETELY NOT TRUE.... I have MILLIONS OF RECORDS in my HFSQL DBs and you can have way more than that...

as I said before, I'm using Fabrice Harari's wxReplication open source system, and that works on ANY DB... so you can choose (I'm using HF most of the time, but that is entirely up to you)...

The principle of the thing:
Your mobile app is working against a local DB... NO DB is included with the APK and it is created empty. At first start, the app connect to the server and starts a first replication cycle that will populate the local DB (it's called enrollment)...

If you fill unsure about how to setup all that, I suggest that you buy a few hours of consulting with Fabrice to help you with that, as I did a few years back. He has a LOT of experience in that area so he will be able to first analyse your specific problem, then give you sound advise on the best way to go forward, as this is NOT a one size fits all experience.

ArieM,

Thanks again, I will check it out, but on the webservice can I run/make it with using webdev instead of windev?


Argus,

That is odd, because that is what I was told by the retailer I got WM and WB from

RobertLeo,

Yes, you can develop webservice with WB as well. No need for WD.

And Argus is right about de number of record. It's practically unlimited, only by the hardware (disksize) you use. Check this page for more info [doc.windev.com]

Depends on the size of a record (for a 20-byte record: 329 millions of billions of records, for a 4,096-byte record: 2 millions of billions of records)

Arie

ArieM,

Thank you for the reply, I guess that means the retailer had essentially lied to me, kind of explains why he was pushing for the MySQL connector now that I think about it

Hi Robert,

there is no limit of HF/CS but my experience is, that you have a better performance if you use MySQL, compared to HF/CS.
There are also some other posts here in the forum discussing the performance of several database systems.
My opinion: HF/CS is a good and stable database system, but others like MySQL are better suitet for a large project.

So if I where you, I would tend to use MySQL instead of HF/CS.


Regards

Stefan.

Stefan,

thank you for the information, I will have to do some checking on the scope of the project to see which DB I would end up using for it.

To everyone else,

In the case of a large project that requires data stored within the local db to only be the user's and nobody else (to adhere to HIPAA), as well as synchronize the data in the cloud db without harming/modifying data(s) belonging to other users, as well as possibly moving some of the app-side queries to webservice, would it be better if I were to use SOAP or REST API? Or should I use GraphQL instead of REST?

Hi Robert,

I can say nothing about GraphQL, but if you ask wheather to use soap or rest:

I think it is not relevant regarding HIPAA or other security regulations.
Both are stable and secure, if they are properly implemented.
It is up to you, to ensure that only the "allowed" data are transmitted, that there is a proper access management to avoid that unauthorized users connect to the service, to crypt the data and to use SSL/TLS.

I prefere SOAP, as it is more standardized then REST, and (thats only my opinion) better implemented in WX.

Just to be curious: What is the purpose of you app?

Regards
Stefan.

Stefan,

Alright I need to read up more on how to implement either SOAP then, thanks

as for my app, the purpose is to give doctors and patients an app that'd allow them to have similar functions they'd have in a clinical management system, but on the go (as in no need to walk-in to make an appointment), that's about all I can say about it

if you opt for wxreplication, it's open source, includes a REST webservice communication system already built in, and works against ANY DB that has direct access (hxxx functions available), so you may save a lot of time just watching the how to videos for it.

Argus,

Thanks for the information, I will study up on how to use wxreplication, coincidentally do you happen to have the instruction manual for it? I tried to check it in Fabrice's website but I can't seem to find that document