Retrieving a Name From MySQL Based on ID

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!

Var Rows As Rowset = db.SelectSQL("SELECT FirstName FROM Members WHERE id = ?;", member_id)

NameField.Text = Rows.Column("FirstName").StringValue

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")

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:

  1. In the first line, “nameVar” should perhaps be a string rather than a RowSet.

  2. In the second line, I should have used “nameVar” rather than “@Name” which I should have realized.

  3. 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!

You don’t need to SET a MySQL variable here.

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?

1 Like

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.

1 Like

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.

Silly question, but is db connected? And as a followup, is it defined in more than one place?

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.

Tim, not as silly as jumping through all those hoops but yes, db connects immediately before running the query.

Ok. Are you setting db.DatabaseName when you connect?


db = New MySQLCommunityServer

db.Host = ""
db.Port = 3306
db.UserName = "root"
db.Password = "@Sheba773"
db.DatabaseName = "seniorsdb2"


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.

Knowing the table definition would help.

Wayne, that’s what you would think, right? But here’s the table:

Create Table Members 
FirstName VarChar(25),
MiddleName VarChar(25),
LastName VarChar(25),
NickName VarChar(25),
Last_4_of_Social VarChar(25),
Date_of_Birth VarChar(25),
EstimatedIncome VarChar(25),
Veteran_Status VarChar(25));

Have you looked at the exception to see what the message is?

Does this code work?

Var Rows As Rowset = db.SelectSQL("SELECT `FirstName` FROM `Members` WHERE `ID` = ?;", member_id)

NameField.Text = Rows.Column("FirstName").StringValue

Wayne, still no go.

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
  1. Do you get a non-nil rs?
  2. Do you get the MessageBox
  3. Do you get an fname value?

This could help separate the differences between SelectSQL and SQLSelect.

Sorry, Tim. That won’t build.


rs = db.SQLSelect ( ...

Yep, tried that…