Hello

I'm curious about what kind of DB's (Gateways) are used out there with Magic XPA 4.x
Do some of you want to share their experience and maybe
point out issues or severe reasons "to not use" a specific gateway? It would be used for bigger
Applications in a Multiuser-Environment (Windows Server-Client, LAN)

What about Postgre?
Or MS SQL?
Actian Zen?
and others...

I'am tempted to go for Postgre despite not having experience in combination with XPA...

Thanks
 

David:

I use MySQL coming from TSQL because MySQL is supported by the more affordable express licence..  Couple of lessons I had to learn when coming from TSQL:

• MySQL doesn't support active directory authentication. I invoke 'cmd /c whoami > %WorkingDir%User.txt' i the main program and read the results from the file. Then crete users each time using UserDel, User Add, and RightAdd based on matching table values. It's a pain but it works great once you set it up.
• MySQL doesn't support cross apply in SQL. You have to join to expressions or CTEs. It does the same thing just not as elegantly. 
• You can't rename databases. You have to copy data to a new database and drop the old one.
• You can't delete records from a table using a subquery using the same table in the where or from claus (for example when trying to remove duplicate records based on top record subqueries)  It's a pain, and I'm still struggling with the best way to do this. 
• You can't start a use Begin Tran, Commit, or Rollback on a single transaction. You have to use the workbench interface and it effects the entire database until the transaction is completed.
• There is no Top X clause (select top 10  from table).. You have to use the workbench and the effect is also global

Other than that most of the syntax between MySQL and TSQL is the same,

David:

I use MySQL coming from TSQL because MySQL is supported by the more affordable express licence..  Couple of lessons I had to learn when coming from TSQL:

• MySQL doesn't support active directory authentication. I invoke 'cmd /c whoami > %WorkingDir%User.txt' i the main program and read the results from the file. Then crete users each time using UserDel, User Add, and RightAdd based on matching table values. It's a pain but it works great once you set it up.

"MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. MySQL uses LDAP to fetch user, credential, and group information.", see: https://dev.mysql.com/doc/refman/8.0/en/ldap-pluggable-authentication.html

• MySQL doesn't support cross apply in SQL. You have to join to expressions or CTEs. It does the same thing just not as elegantly.
  

Possible since MySQL8.0.14, see: join - CROSS/OUTER APPLY in MySQL - Stack Overflow

• You can't rename databases. You have to copy data to a new database and drop the old one.

Why would anyone need to rename a database in an application?

• You can't delete records from a table using a subquery using the same table in the where or from claus (for example when trying to remove duplicate records based on top record subqueries)  It's a pain, and I'm still struggling with the best way to do this. 
• You can't start a use Begin Tran, Commit, or Rollback on a single transaction. You have to use the workbench interface and it effects the entire database until the transaction is completed.
• There is no Top X clause (select top 10  from table).. You have to use the workbench and the effect is also global

In MySQL you have to use LIMIT and/of OFFSET, see: MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement

Other than that most of the syntax between MySQL and TSQL is the same,

Thanks for the added comments Luux!

• I use the community version but good to know AD is supported in Enterprise. I would definitely prefer this but more $$.
• I only need to rename databases on the back end when set up new instances of the database for a new client. No more RENAME DATABASE!
• Thanks for the CROSS APPLY link. This will make my life easier.
• Sharing is caring!