DB used with Xojo the most?

For an app that needs a single file portable database definitely use SQLite.
For multi-user apps Postgres is my preference for the following reasons.

  1. Completely free with no strings attached.
  2. Notifications eliminate the need for polling. (How did I ever do without notifications!)
  3. Extensive Json support.
  4. High performance and fast.
  5. Very easy to setup with windows installer.
  6. Supports upserts using on conflict do update.

One con is the that compared to MS SQL there isn’t a very good way to use variables in a query.

Cubesql is based on SQLite and allows multiple users.
It’s a great tool also

[quote=470198:@James Nicholson-Plank]Cubesql is based on SQLite and allows multiple users.
It’s a great tool also[/quote]

i have been using CubeSQL for the last 15 years (RealSQLServer for a while). The reason i am still using this is because my application can support both single and multiple users with the same application.

With just an extra file, i can turn the single user to multiple users.

I’m loving Xojo for it’s multi-platform possibilities. for the same reasons, when it comes to databases, I wanted something multi databases aware. so I made a database class, that handles all the calls to the databases plugins. and I only call methods from that database class from my apps.
So I can use sqlite, postgres, and mysql independantly, change the database type with one pref property. and I’m open to other databases as soon as there is a plugin available. It only takes 1-2 hours to add a new database type.
of course this is a lot more work to do, but it’s very interesting for any future development.

I use MySQL Community via Amazon RDS. It works like a charm, and the options from AWS to replicate, make more instances is awesome for scalability.

[quote=470202:@Jean-Yves Pochez]I’m loving Xojo for it’s multi-platform possibilities. for the same reasons, when it comes to databases, I wanted something multi databases aware. so I made a database class, that handles all the calls to the databases plugins. and I only call methods from that database class from my apps.
So I can use sqlite, postgres, and mysql independantly, change the database type with one pref property. and I’m open to other databases as soon as there is a plugin available. It only takes 1-2 hours to add a new database type.
of course this is a lot more work to do, but it’s very interesting for any future development.[/quote]

how do you handle the different SQL from all the database type.
i assume you use prepared statement

[quote=470262:@Richard Duke]how do you handle the different SQL from all the database type.
i assume you use prepared statement[/quote]
This really isn’t as big a deal as you think it is. You can make everything generic enough that it works pretty much with any database.

[quote=470262:@Richard Duke]how do you handle the different SQL from all the database type.
i assume you use prepared statement[/quote]
you’re right : I don’t handle the prepared statements in my modules. I do them specifically in the each app.
so is for any sqlselect, they are specific to the database, and the app.
I have a sqlselect method for my database class, that tramsmits the sql query to the base database
and also do all the “dirty” job like counting the found records, or storing the last row id, that is different in each database.
the main benefit is like your’s with cubesql : I can switch from local to remote database very easily
(except from the specific sql queries, but all in all they are only a few calls in an app)

There are definite differences between the SQL brands, but SQLite, MySQL and PostgreSQL are very similar.

Where there are differences, I have methods to:

  1. getAllTableColumns lists the column names
  2. getAutoIncrementResetWAD resets the starting auto increment to one more than the MAX(id), since PostgreSQL can go crazy and overwrite imported records
  3. getBetweenDatesWAD since MySQL is different
  4. getBracketsWAD for field names
  5. getConcatWAD since SQLite uses different delimiters
  6. getDefaultPortNumberWAD since they all connect to different ports
  7. getDuplicateRecordWAD also returns the new record’s ID
  8. getFindLimitWAD since MSSQL uses TOP instead
  9. getFormatWAD since they all use different escape characters for strings which differ again for LIKE commands
  10. getGreatestWAD since SQLite uses MAX() while others use Greatest()
  11. getLastInsertedID to get the id of the last inserted row
  12. getSQLiteForceSequenceWAD to force an SQLite table to auto increment at a particular number
  13. getTablesWAD lists the tables within a database
  14. isCopyRecordIntoRecordWAD copies the data from every field of one record (except primary) into another record
  15. isEncryptSQLiteDatabaseWAD to encrypt or decrypt an existing SQLite database file
  16. isSwapRecordSetsWAD to swap the data from two records (except Primary)

I use SqlServer and SAP MAXDB (the free Community Edition) which are both suited well for medium to big sizes of data / users.

SqlServer is free only in the limited express variant while MAXDB/Community Edition is free as long as you do not run SAP on it or sell it as part of your Software (in house developement is okay though)

MaxDb has some really nice features like its “no reorganization” approach (no index rebuilds etc.) or automatic space administration. You never have to worry about exploding logfiles that are unwilling to shrink again, it can be accessed in native or “Oracle Mode”, it has all the functions, sequences and whatever else you expect from a modern Dbms.

I am looking forward to the SAP HANA community edition (just kidding).

What tools do you use to manage your MAXDB instances? Also, what drivers do you use with Xojo to connect? Last question: what OS do you host MAXDB on?

Have you all forgotten consultant’s standard operation procedures for answering such questions? :wink:

“it depends”

Sometime it’s more effective to counter a question with a question:

“What’s your usecase?”

If everything is fucked up and you really do not know, then just say anything with a strict tone and strong commitment, so nobody doubts your answer.

:wink:

For normal managing I use Database Studio (free, also from SAP) which is based on JDBC.

Mostly I use ODBC drivers, some programs go via OleDb/Active X using OleDb-Odbc provider.

In earlier times we used linux servers to host the instances but switched to Windows a couple of years ago.

the funny site of life: exactly the same with my customers but vice verca: we all switched from windows to linux (mariadb, postgresql)

@Tomas Jakobs
Well, in our case license costs for some windows servers more or less were not a factor so we just tried to minimize diversity

@Louis Desjardins
there is a SAP Hana Express Version (up to 32 GB)

Indeed. After reading original your post, I went to the community download page and noticed SAP HANA Express. I was quite surprised. It is “a bit of an overkill” for my applications, for sure!

I gave a quick glance to mAXDB a few years ago, but at the time, I went with MS SQL Server Express instead (which I abandoned for recent projects, to PostgreSQL and/or SQLite depending on the use case). I am very curious to take a new look at MAXDB. Thank you for the information!

As many have mentioned here all ready, SQLite for single user applications, and PostgreSQL for web applications. Both are free, easy to use and extremely stable.

+1 for Postgres.

As an additional note if you’re deploying to a Mac check out https://postgresapp.com/ it’s a free 1 click installer and makes maintaining the db really simple.

We’ve used this package a lot in the past. Although we are transitioning to MongoDB for some things.