Re: XPA Databases


 


On 2-5-2021 13:42, JK Heydt wrote:
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,

Join main@magicu-l.groups.io to automatically receive all group messages.