Problem with MySQL connection

Briefly, in MS Access ODBC connection to .mdb file I declared DB as type ODBCDatabase
a FileDiText variable of type FolderItem where, reading the complete path from a text, I associate the database name
and then db.DataSource = “Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=” + PATHFILEMDB+ “;Uid=;Pwd=;”
If db.Connect Then

I followed the video instructions.

for MySQL I started the MySQL Workbench (I opened the program). In Xojo project I replaced DB=ODBCDatabase into DB=MySQLCommunityServer
and tried to connect to database with
db.Host = “localhost”
db.Port = 3306
db.DatabaseName = ReadLineFileOfText(1)
db.UserName = “root”
db.Password = “test”

** ReadLineFileOfText(1) contains the complete path where the database resides + the name of the sql database.

I can’t connect.
Does the database fit in any directory or do I have to move it somewhere in particular? And then, MySQL server just open the application and load the database ? I get the message: Local Instance MySQL80 and below the window that contains my database.

The error is nilobject

db is probably nil at this point.
first thing to try is probably to change to DB= New MySQLCommunityServer

Perhaps the connect code has not been run before this line is reached, or the connect failed.

Go back to db.connect and check whether db is nil there, and whether it shows as connected.

Hi Jeff, in fact declaring DB as NEW goes on … now it stops me at the connection, because the path is too long it says, but I also tried to put only the database name and it doesn’t work. Where am I wrong? Can the database be found wherever I want or should I place it in a particular path?

Actually… it says that the identifier is too long, not the path. Are you passing the whole path into the DatabaseName field?

Whoa wait a second… you’re not trying to open a MySQL database at a particular path are you? MySQL does not work that way.

1 Like

I managed to connect it, at least that’s how it seems to me … because I got the “Connected” message as per the listing.
I just removed .sql from the filename and set the filename only without Path and extension.
Now I have other problems related to the formatting of the query (I will have to investigate) certainly different between Access and MySQl.

As I understand it, you configure the server to know about a database with a name or alias.
The server knows where the file is.
Your app does not.

If the server has a database which it refers to as ‘banana’, you connect to the server and ask for database banana.
The underlying file could be anywhere as far as your app is concerned.
(Why? If you are on a network, YOUR C: drive is not the same place as someone else’s C: drive)

The Xojo examples give this:

Var db As New MySQLDatabase
db.Host = ""
db.Port = 5432
db.DatabaseName = "BaseballLeague"     <-   not a path, but a name
db.UserName = "broberts"
db.Password = "streborb"

Yes, perfect, I got there after hitting my head :slight_smile: Now I understand how the Server works :slight_smile:

1 Like