MySQL Database Connectivity / Methods

I am trying to solve this since a couple of days now, and if I can’t get help here, I am probably going to deinstall XoJo again. There is just no description of how to what I want.

I am developing a tool that will connect to a MySQL DB containing multiple tables (nothing weird so far). I have been looking at the Eddies’ Electronic example, where an SQLite DB is used. I want to move (or call from) all database methods (functions?) from within a folder called Database (see EE). I was able to open the database and connect to it. But when I want to - for example - call a method that would load a recordset from a specific table (lets call it GetVendors) I get an error while compiling (in my try version of XoJo). Code lines are as follows:

WindowVendor.VendorsListbox.DeleteAllRows

// Define SQL Statement
Dim sql As String
sql = “SELECT * FROM Vendors”

Dim data As RecordSet

data = DB_Gab.SQLSelect(sql) --> brings "This item does not exist: data = DB_Gab.SQLSelect(sql) with SQLSelect(sql) higlighted

DB_Gab is defined as MySQLCommunityServer also in the folder Database (under DB_Gab I have the methods Open Database and GetVendors)

I have not found ANYTHING that would help me in solving this problem. Can anyone of you professionals help me, please?

Thanks in advance.

Have you actually created an instance of the database class somewhere?

You might also want to look at the MySQL example (Examples/Database/MySQL/MySQLExample) to get a better understanding of how to connect, create a table and retrieve data.

You can try doing something like this:

Dim DB_Gab As New MySQLCommunityServer
DB_Gab.Host = “…”
DB_Gab.Port = 3306
DB_Gab.DatabaseName = “…”
DB_Gab.UserName = “…”
DB_Gab.Password = “…”
If DB_Gab.Connect Then
Dim data As RecordSet = DB_Gab.SQLSelect(“SELECT * FROM Vendors”)
While Not data.EOF
// Add rows to the listbox
data.MoveNext
Wend
Else
MsgBox(DB_Gab.ErrorMessage)
End If?

This should work well.

Later you can declare DB_Gab as a class property. Instead of the following instruction:

Dim DB_Gab As New MySQLCommunityServer

you can write:

DB_Gab = New MySQLCommunityServer

while DB_Gab is declared as a property.

I assume that in your case, DB_Gab is a property, yet it is perhaps set to private or protected, which can eventually be the reason for the error message. Verify if DB_Gab is highlighted with a red or yellow color.

Thanks!!

@Paul: I had a look at the example before. And that is the easy thing. Connecting, retrieving like that works fine, if you have only one window to work with. But I want the retrieving, connecting, storing, xyz-Procedures be available globally, so they can be called from any window I am having visible. That’s why I created a folder “Database”, inserted a Super “MySQLCommunityServer” called DB_Gab, and underneath that (lie in the Eddie’s example) Methods and Shared Methods… But I am not able to call these methods without producing errors.

@Payam: Just like to Paul, that I had implemented before, but I would then need to code it multiple times, depending on what window I have open (which also means different tables from the database).

@Greg: Hmm, I think so… New to Object Oriented Programming, thought I understood it. Man, coding programs was so easy when I used punch cards or Turbo Pascal :slight_smile:

Add a module to your application and create the global instance of the database there as a public property. You can instantiate it in the app.open event (DB_Gab = New MySQL…) and it will be available throughout your project.

Peter, thanks, that worked. However now in my LoadVendors Method (as shown above in the original post) I am getting a NilObjectException …

Dim data As RecordSet

data = DB_Gab.SQLSelect(sql) <-- right here !!

… and I don’t understand why? Seems I have to check for a nil return code, however, I don’t know how to do that and the online documentation does not help me… How would I check after that statement (or at the same time) whether the return value is valid?

Dim data As RecordSet data = DB_Gab.SQLSelect(sql) <-- right here !!
A NilObjectException on that line means that DB_Gab is Nil. You need to create an instance of MySQLCommunityServer As Payam has explained above.

… which I have in a method called “OpenDatabase” in the App (Tab). Code is herE:

Dim DB_Gab As MySQLCommunityServer
DB_Gab = New MySQLCommunityServer

Dim errormessage As String

DB_Gab.Host = “127.0.0.1”
DB_Gab.Port = 3306
DB_Gab.UserName = “xxx”
DB_Gab.Password = “xxx”
DB_Gab.DatabaseName = “xxxxxx”

If DB_Gab.Connect Then
mIsConnected = True
Else
mIsConnected = False
errormessage = "Unable to connect to Database: "
errormessage = errormessage + DB_Gab.ErrorMessage
MsgBox(errormessage)
End If

[quote=127652:@Frank Mühlenbrock]… which I have in a method called “OpenDatabase” in the App (Tab). Code is herE:

Dim DB_Gab As MySQLCommunityServer
[/quote]

db_gab is a LOCAL variable and it goes out of scope once this method ends
see user guides - fundamentals section 3.2

As Norman explained above, DB_Gab is local (only) to your method. This means that once the instructions in your OpenDatabase method have been read, DB_Gab “dies out” and exists no more. The reason for the Nil message which you get is because DB_Gab has become just an empty, meaningless word, which holds no more place in your computer’s memory, because it has once been deallocated at the end of your OpenDatabase method. I therefore advice you to proceed as follows:

Right-click the App item from the list in your Xojo Navigator. From the menu which pops up, select the first item, which says: Add to “App”. A submenu appears, and you must then select “Property”. Create a property called DB_Gab and set it to Public (not Private and not Protected).

Now in your OpenDatabase method, delete only the following line:

Dim DB_Gab As MySQLCommunityServer

And as regards the instruction:

data = DB_Gab.SQLSelect(sql)

you must change it to this:

data = App.DB_Gab.SQLSelect(sql)

because DB_Gab is a property in App.

This will somehow, if not completely, solve your problems.

To All: THANK YOU !!! It worked, just so easy :slight_smile: