Sqlite to Xojo Cloud MySQL

Hi,

I’am going to port over a SQLite database to the MySQL server on the Xojo Cloud.

The plan is to use Navicat Premium to copy/port the SQLite db file onto the MySQL server.

My question is; are there any Gotcha’s that I should be aware of, either in execution of this or any code changes that maybe required in the app (with the obvious exceptions of setting up the code for connecting to the server as opposed the sqlite db.file)

Many thanks in advance.

Colin

Make sure you are making your connection to your db in each new session as a user connects and not just in app.

Hi Steve,

Thanks for that nugget.

Also for any other members reading this in the future, other problems encountered on the way to get it working were.

I used Navicat Premium to port across/copy from the SQlite file to MySQL Xojo Cloud Server database.

It didn’t not copy across the Primary Key Auto Increment Values (maybe there was a hidden checkbox option for this, but I haven’t read their manual, my bad.) I had to set these manually for each table using Navicat.

If your using “COLLATE NO CASE” in any of your SelectSQL fields in Sqlite are not valid in MySQL.

Getting data from MySQL (strings/text) into the app if solely developed with a SQLite db file now require encoding.

For example: Var EmailFooter as String=rs.Column(“TextBody”).StringValue.DefineEncoding(encodings.UTF8)

Also sent to the server on each new session connection - db.SQLExecute(“SET NAMES ‘utf-8’”)

Also helpful reading below https://sqlite.org/quirks.html

Anyway I’ve done it now and it all works - hope this helps someone in the future.