Hi, I’m trying to retrieve a user name from a record in MySQL based on the row ID. Here’s what I have:
Var nameVar As RowSet = db.SelectSQL("SET @Name:= NULL")
Var name As RowSet = db.SelectSQL("SELECT FirstName INTO @Name_
FROM Members WHERE ID = member")
Var theName As RowSet = db.SelectSQL("SELECT @Name")
NameField.Text = str(theName)
Running the query in MySQL Workbench retrieves the data perfectly but I seem to be using the wrong Xojo syntax. It doesn’t throw an error but the TextField comes up empty. Does anyone know how to execute this in Xojo correctly? Of course, if there’s a better way to accomplish this altogether, I’m completely open. Thanks so much!
Wayne, thanks for the reply. Unfortunately, it’s still no go. I got the error “Rows.Column does not exist.” However, you did reveal some things I was doing wrong. I made those corrections but alas, the value of FirstName still comes up empty. This was my last attempt:
Var nameVar As String = ("SET @Name:= NULL")
db.ExecuteSQL(nameVar)
Var Rows As Rowset = db.SelectSQL("SELECT FirstName into nameVar FROM_
Members WHERE id = ?;", member_id)
Var userName As RowSet = db.SelectSQL("SELECT Rows")
It occurred to me that:
In the first line, “nameVar” should perhaps be a string rather than a RowSet.
In the second line, I should have used “nameVar” rather than “@Name” which I should have realized.
In the third line, since “Rows” is being assigned the value of the user name, I used “SELECT Rows.”
Anyway, no need to rack your brain. I appreciate your help!
nameVar is a Xojo variable, so “Select FirstName into nameVar” will not work as MySQL will not assign a MySQL result to a Xojo variable directly (at least I have never seen this before).
Have you reviewed the examples that comes with Xojo?
Wayne’s code looks correct to me. I don’t understand why you’re jumping so many hoops in the database for what should be a simple query. Care to share how you tried to implement Wayne’s suggestion? I should have just worked.
Thanks, Tim. I misunderstood Wayne to be suggesting that I replace what I had in line 2 with his code. After I read your comment, I realized that he wanted me to just replace everything with his code. I did that but I still get a NilObjectException. The debugger says that Rows is Nil. The thing is, you’re right, it’s a simple query and it works perfectly in Workbench.
It’s clear that the variable Rows is Nil. Put a breakpoint on the “NameField.Text = Rows.Column” etc. and take a look at the variable db: you will surely see an error message that causes the Rows variable to be Nil.
This implies that the database does not have a table named Members or that the table Members doesn’t have a column named Firstname or ID - basically the SQL Command is wrong.
Var Rows As Rowset = db.SelectSQL("SELECT `FirstName` FROM `Members` WHERE `ID` = ?;", member_id)
NameField.Text = Rows.Column("FirstName").StringValue
TimStreater, the message and the reason are both blank.
I continue to try things to no avail. I’m at a loss. You guys have been more than helpful and I don’t want to take up anymore of your time. I appreciate everyone’s efforts!!!
Let’s back it up a little, just to get a fence around the issue, what happens if you instead try (this is the old style)
dim rs as RecordSet
dim fname as String
rs = SQLSelect("select FirstName from members where ID = 2")
if rs = nil then MessageBox("There was a database error: " + db.ErrorMessage)
if rs.EOF then MessageBox("No records returned")
fname = rs.Field("FirstName").StringValue
Do you get a non-nil rs?
Do you get the MessageBox
Do you get an fname value?
This could help separate the differences between SelectSQL and SQLSelect.
