Hello group. I’m continuing to try to convert my old database access to MySql. I converted my database with Bullizip, to a dump.sql file. Now how do I connect to this file?
dim db as new MySQLCommunityServer
db.Host=“”
db.UserName=“Admin”
db.Password=“myPassword”
db.DatabaseName=“C:\Users\Federico\Desktop\Database\dump.sql”
//Type of connection UTF8
if db.Connect then
messagebox “CONNECTED”
db.SQLExecute “SET NAMES ‘utf8’”
else
Messagebox “NOT CONNECTED”
end if
I set the various fields…the field. What does HOST mean? Should I install something on my PC? From what I’ve read
MySQL is a client/server based database. You run a server, which houses the database, then applications connect to that server to read and write to the database.
If you plan on distributing your application, your users will also need to have access to a MySQL server instance.
It sounds like you’re used to accessing a file-based database. If you don’t need the benefits/complexities of MySQL you may want to look into SQLite instead.
Yes, SQlite might also work, both SQlite and MySQL are fine for Linux. The problem is that I am having trouble converting the microsoft access database.
The dump file contains the SQL queries for the database. Any database app should have a command “load dump”. The dumps of different types of databases may not be compatible.
I would recommend that you install a LAMP (for Linux) or a WAMP (for Windows)
This will allow you set up a PHP driven web interface which you access via a web browser.
You can then create your database by importing your SQL file using the PHPAdmin web interface.
That will allow you to see and check your data, and setup the required user and password that you need to access the data from your xojo application.
It’s also possible to just install MySQL (or MariaDB) along with a user front end, but it’s inevitable that you will eventually need to get familiar with a server-based web interface, and PHPAdmin is as good a place to start as any.
LAMP servers are commonly used for MySQL/MariaDB and Microsoft IIS is more often used for MSSQL. Neither are exclusive though.
It does depend on what your requirements are. If there’s only 1 database and all users will access the same one, then installing MySql on a server accessed by everyone would be the way to go. You can test locally by installing XAMP or MAMP to check it works, then migrate the database to a server when ready.
If each user has their own database on their machine, then SQLite would be better so you would need to find a way to convert the existing database to SQLite - maybe export/import CSV for example.
I’d need a bit more info on what the end goal is before I can suggest how to do it.
At the moment the database is accessible from only one PC and is accessible on Windows. However, I would like my application to also be distributed for Linux and the database to work on a network of PCs (some with Windows, others with Linux), a small company network of 2 to 5 PCs.
Can you remind us what the problem with SQLite was? I think it would be far easier to continue using SQLite. And that doesn’t even touch the licensing for MySQL.
Valentina Server can also be used as server for SQLite and it not that expensive.
For SQLite, I would use the sqlite3 CLI program and tel it to import your dump and to write that out to a new database. This assumes that the dump is a proper .csv file.
Alternatively, you can write a program that reads the rows from the tables in your msaccess file and writes them to a new SQLite or MySQL database, your choice.
In your target setup, (small number of PCs), what is the expected rate at which people will be accessing your database? If this is say few times/day each, then you can risk having the SQLite database on a network host, mounted on each client PC. If it is hundreds of read/write per hour from each machine, then MySQL would be wiser.
This requirement means you’re going to want one of the database server engines. That is, if you want all of the users accessing the same data. PostgreSQL tends to be the business friendly one because of the aforementioned MySQL licensing that may or may not apply consult your lawyer, I am not one.
If, however, you need the database to act more like a “save file”, then SQLite is the direction to go. Each user will have their own copy of the database and will work with their own local data.
Does that help you determine which engine you need? Once you can determine whether your needs are for a local database or a server, recommending a specific engine becomes easier.
I would suggest getting a helper program to connect to the database. It can help see that the connection information is correct and give a nice view of the tables. Take a look at https://tableplus.com/ get your data to look the way you want in table plus then connect from your xojo client. There are a lot of parts to getting a working client/server system going. Having an independent test/view is helpful.
