I am trying to get started with a local MySQL database being accessed with a web app and am not sure how to enter the credentials to access the database.
I created a property in the Session object called mydbase and set the type to MySQLCommunityServer.
When the session is opened, mydbase is nil, and I am not exactly sure I understand why. Wouldn’t the database object be created in a new session?
So when I try to enter something like:
mydbase.Username = “username”
I get a Nil object exception.
How is the initial connection to the database initialized?
You’re right about creating a property in Session but I do think you have to start by reading the Language reference about MySQL.
There are great examples there that will help you 
http://documentation.xojo.com/index.php/MySQLCommunityServer
In other words, you need to initialize the object and tell it to connect to the database.
So even though the database mydbase is a property in the session, I need to:
Dim db As New MySQLCommunityServer
I was thinking, as for example when I have a property in a the app (such as rs as RecordSet), I do not have to initialize that property.
Yes. Having a property on the Session is just the intent to have a database connection. You still need to initialize it, and set things like where it is, what credentials to use and then connect to it when you’re ready. Don’t forget to write some code to deal with the Connect method failing and returning false. This can still happen locally if you’re out of memory, have too many users, etc…
OK, thanks. I was thinking I could just do something like:
session.mydbase.username = “username”
but I kept getting a Nil object.
Do you ever reference the session object?
You’re getting a nilobjectexceprion because the property hasn’t been initialized.
Not sure what you’re asking about Sessiin.
myDatabase = New MySQLCommunityServer
You already have the property but it is basically empty. So you have to “fill” it with a new database.
Ok. I was able to assign properties in the session db.
I am able to see the properties when using the following:
#if DebugBuild then
Dim CurrentSession As Session = Session()
#Endif
So I have a method in the WebPage shown event as follows:
[code] Dim sql as string
Dim rs as RecordSet
If session.Available then
#if DebugBuild then
Dim CurrentSession As Session = Session()
#Endif
//do a query
sql = "SELECT * FROM Patient_Info WHERE PtNumber < 50 ORDER BY Last_Name,First_Name"
rs=Session.db.SQLSelect(sql)
If session.db.Error then
MsgBox Session.db.ErrorMessage
End if
[/code]
I check the database credentials using SequelPro and the very same credentials are present in the session db - so it should be connected.
I then take the SQL query and paste it into SequelPro to see if I get any errors. The query executes fine.
I then run the code above and when I get to the line:
rs=Session.db.SQLSelect(sql)
the recordset rs remains nil.
There is no error generated in session.db.Error.
All the credentials in session.db (when viewed in CurrentSession) look correct:
Session.db.host="127.0.0.1"
Session.db.port=3306
Session.db.databaseName="TEST"
Session.db.userName=session.Cookies.Value("username")
Session.db.Password=session.Cookies.Value("password")
It looks like I am so close.
And you’ve called Session.db.Connect?
Of course not. Let me try that.
There is no option:
session.db.connect
Could it be called something slightly different?
Found it.
It was:
If session.db.connect then
//do database stuff
End if
Thanks. All is working now.
Sometimes the smallest things are the most frustrating.
I appreciate all the help.