Connection to MySQL

Databases
1

I am kind of new to XOJO. I was wondering what is the best design to get a connection to MySQL server. My app is going to have multiple windows and containerControl. I don’t want to place the following code in every windows or containers. Where can I define once and just reuse in all the windows or containerControls?

db = New MySQLCommunityServer
db.Host = “127.0.0.1”
db.Port = 3306
db.DatabaseName = “directory”
db.UserName = “root”
db.Password = “1234567”

I means I will check is I have a db connection then execute the sql code if a valid connection is present.

if db.Connect then
– code
else
– code
end if

2

one connection per user is enough.
So each has it’s own transaction.

3

Yes, that’s what I would like to happen too. But how? Where do I place it in app?
Where can I define globally so the connection can be reuse?
i.e. In web application I establish a db connection, after that just pass the connection object around.

4

In a web application, you should have one connection per session. Do not use one and then “pass it around”. Put the db object in the session and connect on session open.

Add a property to your Session: db as MySQLCommunityServer
In session.open:

db = new MySQLCommunityServer
db.Host = "127.0.0.1"
...
if not db.Connect then
   // throw an error and quit the session
   ShowURL "myStaticErrorPage.html"
end

If Connect succeeds, you can use the global db variable in the rest of your code as Session.db.SQLSelect(…)

5

Ok, thanks, but I am creating a desktop application not web application.

6

In a desktop app because there is one user PER launch of your app you can put it on the app class & pass it around

7

This comment made me think you were writing a web app. In a Desktop app, you can put the property in App or in a Module. If you put it in app, you need to access it as app.db. If you put it in a module, then you can make it Global and access it as db or you can make it Protected and access it with the module name, module1.db.

8

Thanks Tim and Norman comments.

Tim, Do you have a short snippet code on put it in as module and access it globally? Thanks a lot.

9

simply declare a property “db” as “MySQLCommunityDatabase” in a module as a global item.
Than use db everywhere in code to refer to it.

10

Here is what I did.

  1. created a module - Module1
  2. Inside Module1 created a dbg as MySQLCommunityServer
  3. Inside Module1 created a method getDB, ( but I don’t know what I need to put in parameters and Return Type?) and place the following code inside
    dbg = New MySQLCommunityServer
    dbg.Host = “127.0.0.1”
    dbg.Port = 3306
    dbg.DatabaseName = “diretcory”
    dbg.UserName = “root”
    dbg.Password = “1234567”

But when I try to access from a ContainerControl either the two code produced NilObjectException in Container Open event handlers. What did I do wrong?

if Module1.dbg.Connect then
if dbg.Connect then
11

Did you execute getDB somewhere? For a desktop project I’d recommend running it in the App.Open event.

12

hmmm, probably not. I am still try to wrap my head around app, module, classes. I was just wondering where I can declare it, authenticate the db object and reuse it everywhere.

So you are saying I should go with declare the db as MySQLCommunityServer property in app
and place the snippet code in app.Open?

db = New MySQLCommunityServer
db.Host = “127.0.0.1”
db.Port = 3306
db.DatabaseName = “diretcory”
db.UserName = “root”
db.Password = “1234567”

13

Declare it in a module. Instatiate it in app.open. Connect in app.open. Then you can use it anywhere.