Which Database Solution For This?

Databases
1

Hello

I want to make a Windows desktop app that is used by about 50-100 different people around the world at the same time. The Windows app fetches data from a remote central database and also writes data back to it.

Thanks

2

mySQL might be a good solution… I used it for a Special Interest Group website a few years back (with PHP not Xojo), there were 6000 members and usually about 100-150 connections at any one time … and it was used by people in about 27 countries

3

Any “real” RDBMS will do. Your likely candidates are

  • mySQL/MariaDB (Read the licence carefully if you are in a commercial context)
  • MS SQL Server express (free. Windows (native), Linux (native) or MacOS in a docker container)
  • PostgreSQL (always free, can be installed on virtually any OS)
  • Any other RDBMS that can be accessed through ODBC: IBM DB2 (community edition used to be free), Oracle Express edition, Firebird and many others.

My (new) personal favorite is PostgreSQL.

4

For a multi-user environment I would first look to see if it can be made into a Web application, thus eliminating the need for an OS client. In some cases that is not possible, so an OS client is required. For the DB in a multiuser environment I would use PostgreSql as there are no licensing issues to mess with so it can be used in many situations.

5

+1 for PostgreSql and their really free licence.

6

I like the PostgreSQL license, but it should be noted that there are some plugin level bugs that cause issues.

Dropped columns appear in the schema:
<https://xojo.com/issue/15632> (7 years)

Capital letters screw everything up:
<https://xojo.com/issue/33055> (4 years)
<https://xojo.com/issue/29982> (5 years)

You can work around these with raw SQL, but the workarounds start cutting into development time.

7

For administration of the db, I use PGAdmin 4 or Valentina Studio pro. That works around the issue with 15632.

The issue with capital letters in table names and field names is to be fair, not strictly a Xojo issue. Even using PostgreSQL’s own tools, if you create a field or a table using capital letters, then you must then access them by quoting the name, or else you will not see anything. For example: table Customers must be used as follows: Select * from "Customers" . In Xojo, that would be even worse: Select * from ""Customers"" - The double quote sends a single quote through the plugin. You can test it in PGAdmin directly. I experienced the issue first hand when I converted a MS SQL Server database full of capital letters and accented characters (a bad idea to begin with!) to PostgreSQL. (with tools unrelated to Xojo). Once the behavior is understood, it all becomes much easier.

8

No it doesn’t.

The second issue affects you if you use the built in RecordSet functions, especially with updating.

As I mentioned originally, they can be worked around, but these issues are of significant importance. One should be aware of the problems as well as the benefits before making a decision.

9

Then I don’t understand what is in that FC. Back to reading it. :wink:

[quote]The second issue affects you if you use the built in RecordSet functions, especially with updating.[/quote]You are correct, I do not use recordsets for updating. I almost exclusively use prepared statements. Which also has its own set of issues that we can work around.

10

don’t use fieldschema.

dim ch as string
  dim rs as RecordSet
  ch = "SELECT table_name,column_name,ordinal_position,data_type,is_nullable,character_maximum_length,udt_name,column_default FROM information_schema.columns WHERE table_schema = '"
  ch = ch + schemaName +"' ORDER BY table_name,ordinal_position ASC"
  rs = SQLSelect( ch)
  if rs<>nil then
...
   end if

where schemaName is the name of the schema of your database.

you get more informations than fieldschema with this.

11

postgresql is case-sensitive, any database developer must be aware of that.

12

[h]As I mentioned originally,they can be worked around, but these issues are of significant importance. One should be aware of the problems as well as the benefits before making a decision.[/h]

Edit: I don’t know how much bigger I can make this.

13

Well <https://xojo.com/issue/15632> is now marked as Fixed.

14

That’s great news! Thanks, Xojo people!

15

[quote=404887:@Richard Ramos]Hello

I want to make a Windows desktop app that is used by about 50-100 different people around the world at the same time. The Windows app fetches data from a remote central database and also writes data back to it.

Thanks[/quote]
Why not do this as a Web app?

16

Tim Parnell wrote about PostgreSQL: [quote]Capital letters screw everything up:[/quote]

This is not a bug in PostgreSQL or Xojo but a maybe slightly surprising feature PGAdmin which takes the capitalization of the table and field names given by the user more seriously than the he/she might have intended (by quoting the names behind the users back). It has been discussed many times in this forum.

I’d also recommend Postgres, if your users are from all over the planet you may want to compare the timezone handling of the different databases, I know that PG’s is great.

17

In three of my desktop projects I use MySQL plugin and it works surprisingly well. If you have access to MySQL/MariaDB server, then you can work with it wherever you are.

Yes – it is also case sensitive, but I don’t see any problem from that. Much bigger problem is, that in never Xojos MySQL plugin did not work as it should. One of my program, that is in use in different places and uses different physical MySQL servers, works if connected to native MySQL server, but gives SSL unknown error when connects to MariaDB server. So last plugin that works, is from Xojo 2015r4.1…

And for database management I suggest HeidiSQL. It is free and quite powerful.

18

thanks for all the replies!