PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 22 → Question on DB to use
Question on DB to use
Débuté par Allard, 06 mai 2018 13:37 - 16 réponses
Posté le 06 mai 2018 - 13:37
Hi,

I have always used HyperfileSQL and have had no problems. I like features it has for updating tables etc from the analysis etc .

My Question:
When should one use microsoft sql server ? Is there a specific reason why companies choose to use sql server.

Regards
Allard
Posté le 06 mai 2018 - 15:58
Hi Allard,

I had a problem with HFCS using queries over several tables with subselects.
I tested the same query with MySQL, and the Performance was a lot better.

So my (personal) findings:
HFCS can handle huge tables (more then 1 Million records) with no Problems if you do not use complex queries like subselects and so on.

Best Regards


Stefan.
Posté le 06 mai 2018 - 17:40
Hi Allard,

I am using MSSQL. The main reason is, that it is well known at the customers world.
As we have often to interchange data with other Systems or the customer needs some data from our database, it is very convenient that they may have direct Access to my tables or views. For example some customers are using Excel or Access and can get data from our tables directly (very simple and easy).
Furthermore I like the Management Studio to make tests or to solve Special Problems or run Special queries from windev, which could not be created with Windev Query Editor.
Creating complex queries, even if possible with WD Query Editor, is much more faster to do and to test.
Only drawback so far: there is no automatic database modification (at least I do not know), so with each Change of a table I have to create an update script and handle that seperately.

g

Erik
Posté le 06 mai 2018 - 20:55
Quote
Erik Schwarz

Hi Allard,



I am using MSSQL. The main reason is, that it is well known at the customers world.

As we have often to interchange data with other Systems or the customer needs some data from our database, it is very convenient that they may have direct Access to my tables or views. For example some customers are using Excel or Access and can get data from our tables directly (very simple and easy).

Furthermore I like the Management Studio to make tests or to solve Special Problems or run Special queries from windev, which could not be created with Windev Query Editor.

Creating complex queries, even if possible with WD Query Editor, is much more faster to do and to test.

Only drawback so far: there is no automatic database modification (at least I do not know), so with each Change of a table I have to create an update script and handle that seperately.



g



Erik

Hi Erik,

We use MS SQL too for many of same reasons. For auto-generating scripts and managing the DB's we use a tool called Navicat. We keep a baseline version of our DB in navicat and while still need to manually update the database schema there, we can then use that to do a diff against production DB's and auto-generate and run scripts within the tool. I wouldn't know what do do with out it.

Steve
Posté le 07 mai 2018 - 02:31
Hi,

Thanks for the info.

Allard
Posté le 07 mai 2018 - 09:40
Hi Steve,

thanks a lot for that hint. Seems to be a pretty nice tool to save a lot of work.
Will load the test Version as soon as possible. :)

g

Erik
Posté le 07 mai 2018 - 17:50
In our case we work with MSSQL and ORACLE and the main reason is when you try to install your app in midle /large size companies they already have the licences for the db engine (the cost in this cases is 0), the dba guys, the backup/restore and replication setup and they don't want to use anoher db engine only for our apps.

Regarding the struture changes we have invested some time develpoing it but nowdays the only thing we have tol do for each app is to include the classes that mange this king of stuff.

It's no so dificult to do but it takes a bit of time to develop.
The main steps are list the analisys definition, query the db for the definition compare the two and generare some DDL commands to create the new tables, change the exsiting ones, create/drop indexes and so on.
Posté le 07 mai 2018 - 22:22
...and if there's a tight budget and you go down the mysql route, there's always sqlyog for the management side of things; premium or enterprise ($199 or $299 respectively)
Posté le 08 mai 2018 - 14:23
My recommendation .

1. if you have existing program that using hfsql classic and you want to move to multi user without doing big change then hfsql client/server is best option.

2. if you creating new program for multi user then I recommend MS-sql (if you have budget) or mysql/mariadb/postgresql (free)

3. Do not use "hfsql client/server" . even if you really have no choice also don't . even MS-SQL 2000 is alot better than the latest version of "hfsql client/server"

4. for single user application, use sqlite , if you want to use hfsql classic , do not use transaction . it will failed to rollback , your program will stuck.

5. if you are ok with missing some function , I recommend to try MS-SQL (linux version) , the speed is out performance windows version. since released, I been using in development environment , I love it . the bad part is no SSRS :angry:


even though PCS make WD with alot fancy stuff, but in term reliable , clarion is still one step above .

clarion file , is one of the best flat file db. if long ago someone opensource it, i believe it will become one best flatfile db.
Posté le 10 mai 2018 - 09:36
Hi Steve,

So you recomment using navcat when using mssql? I took a look at the tool and it seems to have lots of features.

regards
Allard
Posté le 10 mai 2018 - 14:13
Quote
Allard

Hi Steve,



So you recomment using navcat when using mssql? I took a look at the tool and it seems to have lots of features.



regards

Allard

Also worth taking a look at HeidiSQL
Lot of functionality, open source and its free
Posté le 10 mai 2018 - 18:57
Hi CCC2

mssql has an express Version for free (restrictions: 1 CPU max. and 10GB database size)
For my application and customer with several hundreds of employees this will take 10's of years to fill up.
If a Company Needs more, in most times they will have a Standard mssql anymore.


g

Erik
Posté le 10 mai 2018 - 22:24
Quote
Allard

Hi Steve,



So you recomment using navcat when using mssql? I took a look at the tool and it seems to have lots of features.



regards

Allard

Yes I do. It is expensive but it's a great tool with a ton of features. The enterprise version ca connect to multiple types of DB from one interface which is super useful
Posté le 11 mai 2018 - 00:36
We always use MySql as primary multi-user DB.
Transactions, triggers, and so on... atfer 3 years of work and study we managed to make the whole thing run as we want.

We use HF (no client-server) as secondo choice when the machine needs to run the software disconnected by the MySQL server, when no connection is available.
We developed an engine that download local HF (for stand-alone temporary working) taken from official MySql data and upload local data on MySql when the connection returns.
All as an automation, no need to do it by hand.

The only pain left on MySql is having no control on timout when connection goes down.
The programs are stuck for 20-30 seconds before throwing an exception we can manage.
No way to tell the WD data layer (on WD20) to try a query for 5-10 seconds max.
Except closing each query into a threaded "try-catch" that drop the thread after our own timeout. But it's foolish.
Posté le 11 mai 2018 - 10:03
Hi stefano,

I don't have experience with mysql but with other db engines the WD Command Timeout
works, did you test it?
https://doc.windev.com/en-US/…
Posté le 11 mai 2018 - 10:44
Hi!

I am close to deploying my multiuser application. I am using Windev 22 and HFSQL C/S at the moment and having no mjor issues.

A hypothetic question: How much work would you say it is to convert to start using MSSQL Express? Would be to also get a few examples of what kind of modifications that has to be made.

Best regards,
Carl
Posté le 11 mai 2018 - 14:22
Yes. I has no effect on MySql Native Access (WD20).

Told directly to Jerome Aerts in a meeting. Answer: oh.. O_O