# MySQL Database Connectivity / Methods

**URL:** <https://forum.xojo.com/t/mysql-database-connectivity-methods/19120>\
**Category:** Databases\
**Created:** [September 6, 2014, 7:00pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120 "2014-09-06T19:00:55Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Frank\_Mühlenbrock](https://forum.xojo.com/letter_avatar_proxy/v4/letter/f/4da419/32.png) [@Frank\_Mühlenbrock](https://forum.xojo.com/u/Frank_M%C3%BChlenbrock)\
**Post date:** [September 6, 2014, 7:00pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/1 "2014-09-06T19:00:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Greg\_O\_Lone](https://forum.xojo.com/user_avatar/forum.xojo.com/greg_o_lone/32/49_2.png) [@Greg\_O\_Lone](https://forum.xojo.com/u/Greg_O_Lone)\
**Post date:** [September 6, 2014, 7:06pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/2 "2014-09-06T19:06:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\
**Post date:** [September 6, 2014, 7:46pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/3 "2014-09-06T19:46:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Payam\_Arzani](https://forum.xojo.com/user_avatar/forum.xojo.com/payam_arzani/32/3515_2.png) [@Payam\_Arzani](https://forum.xojo.com/u/Payam_Arzani)\
**Post date:** [September 6, 2014, 8:56pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/4 "2014-09-06T20:56:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Frank\_Mühlenbrock](https://forum.xojo.com/letter_avatar_proxy/v4/letter/f/4da419/32.png) [@Frank\_Mühlenbrock](https://forum.xojo.com/u/Frank_M%C3%BChlenbrock)\
**Post date:** [September 7, 2014, 9:19am UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/5 "2014-09-07T09:19:58Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![Peter\_Fargo](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_fargo/32/977_2.png) [@Peter\_Fargo](https://forum.xojo.com/u/Peter_Fargo)\
**Post date:** [September 7, 2014, 10:11am UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/6 "2014-09-07T10:11:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Frank\_Mühlenbrock](https://forum.xojo.com/letter_avatar_proxy/v4/letter/f/4da419/32.png) [@Frank\_Mühlenbrock](https://forum.xojo.com/u/Frank_M%C3%BChlenbrock)\
**Post date:** [September 7, 2014, 5:45pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/7 "2014-09-07T17:45:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Eli\_Ott](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/5f8ce5/32.png) [@Eli\_Ott](https://forum.xojo.com/u/Eli_Ott)\
**Post date:** [September 7, 2014, 5:56pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/8 "2014-09-07T17:56:30Z")

</div>

`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.

---

<div class="post-metadata">

**Author:** ![Frank\_Mühlenbrock](https://forum.xojo.com/letter_avatar_proxy/v4/letter/f/4da419/32.png) [@Frank\_Mühlenbrock](https://forum.xojo.com/u/Frank_M%C3%BChlenbrock)\
**Post date:** [September 7, 2014, 6:16pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/9 "2014-09-07T18:16:52Z")

</div>

… 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

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [September 7, 2014, 7:05pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/10 "2014-09-07T19:05:17Z")

</div>

[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

---

<div class="post-metadata">

**Author:** ![Payam\_Arzani](https://forum.xojo.com/user_avatar/forum.xojo.com/payam_arzani/32/3515_2.png) [@Payam\_Arzani](https://forum.xojo.com/u/Payam_Arzani)\
**Post date:** [September 7, 2014, 10:03pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/11 "2014-09-07T22:03:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Frank\_Mühlenbrock](https://forum.xojo.com/letter_avatar_proxy/v4/letter/f/4da419/32.png) [@Frank\_Mühlenbrock](https://forum.xojo.com/u/Frank_M%C3%BChlenbrock)\
**Post date:** [September 8, 2014, 5:41am UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/12 "2014-09-08T05:41:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:24pm UTC](https://forum.xojo.com/t/mysql-database-connectivity-methods/19120/13 "2020-10-29T17:24:28Z")

</div>