Which DB system to choose?

Databases
41

The ODBC plugin works fine with FileMaker once you get used to the oddities. The problem is FM has crippled SQL access in order to discourage using their server without their clients. It is glacially slow compared to anything else I have ever connected to. I connect Xojo apps to FM everyday due to limitations imposed on me at work. The only thing worse than FM’s SQL access is how easy they have made it for people to create labyrinths of illogical database structure. Couple that with a dialog driven interface and you have a recipe for hair loss and acid stomach (OPC Cocktail) Nothing like a table with 20 or so core fields and 100+ one-off fields added at some point because (name the employee) had to have it one thursday in april.

+1 for CubeSQL (ease)
+1 for PostgreSQL (power/price)

42

Really?

Sure, I can ask $300 for my app. I just won’t sell any.

One of my apps can sell at a maximum of $20 for single user (student) to $39 (scientist, though $39 is pushing it), and $99 for small groups.

Asking them to buy a separate $150 licence or add the cost is simply not on.

43

[quote=246896:@Emile Schwarz]Beatrix, Jean-Yves, about FileMaker:
I used it (20 years ago), but I do not recall bad things about it.
The only explanation I got is that… other DB have evolved to the point FileMaker gaves this bad feeling ?[/quote]
the real bad thing about filemaker is their script system : soooo unusable
it has not evolved at all
I had (was forced) to work some on a recent filemaker server (v15) and the script system is still unusable
I choosed 4th dimension 20 years ago mainly for that reason and I don’t regret it
filemaker was really better for the design of windows that is really nice (far better than 4th dimension at the time)
but the design of the tables and relations and script is not usable for someone who knows what programming is.

44

[quote=246905:@Markus Winter]Really?

Sure, I can ask $300 for my app. I just won’t sell any.

One of my apps can sell at a maximum of $20 for single user (student) to $39 (scientist, though $39 is pushing it), and $99 for small groups.

Asking them to buy a separate $150 licence or add the cost is simply not on.[/quote]

Fine, but you are clearly far away from the typical c/s or networked application vendor that we have in mind.

45

Yes, that is pretty obvious.

But so seems to be the original poster (I get the feeling some posters haven’t even read Raphael’s contributions), and nobody so far seems to have bothered to consider if Raphael’s requirements fit what you have in mind. From what he said I don’t see that - he is not experienced with databases (so Norman’s insistence on PostGres is puzzling) and he is concerned about costs (so CubeSQL might not fit either). I actually start to suspect that he is one of those Filemaker developers who is badly affected by the recent prize rises for Filemaker and is now looking for an alternative (possibly got to Xojo via Hal Gumberts site).

So I simply added another point of view, the one from someone who can’t simply charge $75 an hour or sell his apps for $500. The reaction to that is puzzling …

46

Thank you all for all your replies.

I started to think that CubeSQL was the solution but then I looked at the licensing and it is just not an option. The software that I am developing is for a NGO working all around the world. It will be deployed in various places/countries where they are operating so I would need multiple licenses of CubeSQL for that purpose, which is a cost that I am not ready to pay on my own and that I don’t want to charge them neither.

So in a way I could get around with using postgresql but the idea is for the software to be easily deployed even in remote location for a small team. I’m starting to think if I should do a single user version using SQLite, and a multi-user version using PostGreSQL… not sure if I want to go that way though.

Though right now I’m leaning to do some test using only SQLite and see if I can manage to make it work even in a multi-user situation, mainly see if it is not too slow when several people needs to write in the same table.

Anyway, thanks again for all your inputs!

To know more about me: I have some experience way back developing softwares and have developed as well a bunch of database solutions using Microsoft Access in the good old time and Filemaker more recently. I have some experience in working with database through the building of websites also.

47

The decision on what to go for has to take several things into account:

App requirements: what does the app have to do?

Budget: free or commercial ok?

Support: can users provide support or would that fall to the developer?

Speed: time critical or ok to take a few seconds?

Usage: are we talking just a few users accessing the database each hour or thousands of users?

Connectivity: how good is access? Slow network? Intermittent?

Skill level of developer: write your own server?

In Xojo > Example projects > Communication you find some simple examples to get you started on how computers talk to each other via the network.

But one alternative that was mentioned and that might be worth exploring is a web app accessing the database on the webserver. Pretty much every computer has a web server build in (well, at least Unix and Mac, but I believe Windows too), and most NGOs that I know of do have internal local websites (for example for a meeting calendar, or room booking, or a wiki). So going this route is equivalent to writing your own server, except that the server part has already been written and all you have to do is figure out how to qccess it.

48

[quote=246950:@Raphael Vaney]I have some experience in working with database through the building of websites also.[/quote]It’s entirely possible to share a sqlite database by using a web server + php. Your php + sqlite back end can communicate with your front end (be that in Xojo, HTML5 or whatever…) using JSON. Read this wiki for a little more detail on JSON-RPC servers.

In the wiki they give examples using the form:
{“method”: “echo”, “params”: [“Hello JSON-RPC”], “id”: 1}

but I also like to add a “service” element to the json object, i.e.
{“service”,“whatever”, “method”: “echo”, “params”: [“Hello JSON-RPC”], “id”: 1}

Collecting related functions together under the umbrella of a ‘service’ and making that part of the routing just makes more sense to me.

49

Btw I’ve even used this app to run a JSON-RPC server on my Android phone.

50

Well ValentinaDB could help you also.

The VDB-ADK and VDN is not that Expensive if you only need the small Server Lic (Say max. 5 Con. for VDB and Report or max. 10 Con. for SQLite.)

Another way would be to use FirebirdSQL which also can be embedded ito you Application Setup. (for Windows for Example.) Using Firebird with Xojo.
Other Platforms should also be possible (never tried it.) I don’t know if the embedded Server would work so easy like this.

Best Regards,

R. Landscheidt

51

[quote=246963:@RenLandscheidt]Another way would be to use FirebirdSQL which also can be embedded ito you Application Setup. (for Windows for Example.) Using Firebird with Xojo.
Other Platforms should also be possible (never tried it.) I don’t know if the embedded Server would work so easy like this.[/quote]Firebird is an excellent choice, it’s as close to a zero admin database server as you’re ever likely to find.

Those instructions could do with tweaking though, when you create a connection to a Firebird 2.x server you should state the encoding of the strings you’re going to be sending, the server will then transliterate between that and the encoding of the database you’re connecting to. If your application is the only thing writing to/reading from the database you may not even realise there’s an issue but if other tools read from the database (e.g. a report engine) and you’ve been connecting with character set NONE you may not get the output you expect.

52

And now very expensive too

53

And sharing a single sqlite file across a network CAN be a recipe for poor performance or worse - corruption
It’s not recommended

54

Firebird:

[quote]“Although Xojo does not have a native plugin for connecting to Firebird, you can use the ODBC plugin in conjunction with the Firebird ODBC drivers to use Firebird with Xojo.
[…]
Note, there are only ODBC drivers available for Windows and Linux.”[/quote]

So much for cross-platform.

55

Here’s my .02 cents: Everyone has their own favorite DB. I have used most in one form or another from MySQL, MongoDB, PostgreSQL, Filemaker, SQLite, to Oracle a few times. Spent too much one one, couldn’t quite get the over the learning curve of another, etc… They all worked with Xojo and did a good job.

Out of curiosity I tried Valentina and have been hooked ever since. <-- truly.

It’s unbelievably fast and what I like best is the API access to the DB versus the usual SQL approach. And they have their own DB editor called Valentina Studio which can work with numerous other DBs, including most mentioned in this thread. That’s worth a look all on its own.

You can still use a basic SQL approach with Valentina (and I recommend it with a very slow network connection using their server product), but once you get the hang of it - it’s very easy. Multiple cursors, record locking, notification channels, multiuser, local DBs as well as Server DBs in the same deployment, and they have a standalone non-royalty embedded server that’s pretty cheap considering other RDBMS offerings. This keeps Valentina IMHO in line with the big boys in the DB realm.

It’s a robust RDBMS with a lot to offer. It’s cross platform and supported on my programming IDEs.

What hooked me what their Reports ADK integration. A great deal of our clients need built in reporting and although Crystal Reports is a good product: It’s not cross platform and it’s insanely expensive. ( I haven’t played with Bob K’s reporting tool yet, but have heard it’s a damn good )

Granted, the documentation is what gets most Valentina Developers to bang their heads against their keyboards, but as I said before - the developers are pretty responsive and it really isn’t that hard once you figure it out.

Off the shelf it’s pricey, but I took advantage of their OmegaBundle offer (as Paul mentioned above) for $399 a few years back and I never regretted it. You can use all their products for free in a “trial” type mode if you prefer to play with it prior to plunking down the $.

Here comes the disclaimer: I don’t work for Paradigma nor do I get anything for pitching it.

I just really like it.

56

To save Raphael the work:

Valentina Embedded royalty free server:

5 concurrent connections: $600
10 concurrent connections: $3000

57

Well for Firebird you also could use Christians SQL Plugin,

wich would work on all 3 Platform’s just looked it up - it’s supported (don’t checked how it works, have to test it later - do most my DB work with ValentinaDB or PostgreSQL now).

and yes. the VDN 5 Connection License Costs about $600 - you Get 5 ValentinaBD / RestAPI / Report Connections, you also get 10 SQLite Connections - So if you have SQLite Single-Place Software you could switch to mult user easyly.

Here comes the disclaimer: I don’t work for Paradigma nor do I get anything for pitching it.

I just really like it.

And no don’t work for MBS either :wink:

58

Or you could get the Omegabundle like I did for $399 and you get the unlimited connection server (both Valentina and SQLite), Valentina Studio, the Reports ADK, the DB ADK for all three platforms (Mac, Linux, Windows) plus a bunch of other very useful tools that include MBS plugins.

I just really like them too.

And no, I don’t work for MBS either…

59

I have the Omegabundle 2014 Version - and yes, i plan to get the 2016 Version also.

It is not the Redistributable Server Version. So you cloud use it inhouse but not distribute it to your clients.

there are some interesting things in there. Like Valentina Studio Universal, or HTML Edit or Answers Pro, and yes bought an ADK update just end last year, just that a second license is never a bad thing.

60

To me, this really screams Xojo Web Standalone with SQLite database. No need to configure a web server. Can run on a LAN or on a web server for WAN access. Can distribute a pre-built database as a single file. Easy installation. Low to no maintenance.