Retrieving a Name From MySQL Based on ID

Not sure you need the semi-colon in the select statement.

Otherwise, I checked a couple of possibilities in the doc and it should work. One other question - is AUTO_INCREMENT right? In SQLite it’s AUTOINCREMENT.

TimStreater, I’ve tried it both with and without the semicolon. It doesn’t seem to make a difference as far as I can tell. Also, according to MySQL Docs, AUTO_INCREMENT is correct. Thanks again!!

Then I’m out of ideas. Best hope for an actual mysql user to check over what you have now. By the way in post 3 you said:

You should check whether the underscore (_) used for continuation implies whitespace as well. If not then you should add a space in front of the underscore. That would at least account for the exception since your sql would then have a syntax error.

Edit: actually I don’t know how that managed to compile since the string is broken in the middle.

Can you share a sample project? You can use dropbox, onedrive, google drive share links.

It may be easier for someone to take that sample, connect to their MySQL database and find what is wrong with your code.

That means that there was no error in the sql per se, but it did not return anything. At least you’re not getting a Nil back. So there is something that is not quite right about the sql. Copy that string into Workbench and see if you get any results. I have done a ton of database work with Xojo and MySql, and I haven’t seen this kind of behavior.

We need a self contained sample test instead of exchanging fragments. From a complete sample we can diagnose the problem, line by line.

Try to create new sample app, and run this:

Var db As New MySQLCommunityServer

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

db.Connect

Var Rows As Rowset = db.SelectSQL("SELECT * FROM Members LIMIT 1;")

If Rows.AfterLastRow Then
  MessageBox "The DB is empty"
  Quit
End

Var memberId As Integer = 2

Rows = db.SelectSQL("SELECT FirstName FROM Members WHERE ID = ?;", memberId)

If Rows.AfterLastRow Then
  MessageBox "There's no ID = 2"
  Quit
End

MessageBox "Name: " + Rows.Column("FirstName").StringValue

Quit

I have seen no records returned, but several (probably at least 5) years ago. It only happened with MySQL running on a Windows server, with MySQL on Linux it was OK. I had almost the exact same problem with my app, a simple SELECT to find the user’s record returned an empty recordset. And confirmed with Workbench that Xojo got it wrong. And the problem was intermittent - some queries worked just fine. Another thing that sometimes helped (and yes this looks crazy) is add another field to the WHERE clause.

SELECT * FROM users WHERE ID=2 AND FirstName<>'no_name';

I have since moved to Linux database servers though I did try it about 2 years ago with a Windows server and the problem seems to have been fixed. Not sure if the actual issue was with MySQL on Windows, or the Xojo database connectors, or possibly both.

If older versions of MySQL or Xojo are involved, I suggest trying with current versions.

Don’t. If you need this kind of hack, something is so fundamentally broken that you should stop and pursue a real solution.

This is a good advice + using a self contained sample

If you need this kind of hack, something is so fundamentally broken that you should stop and pursue a real solution.

I agree … my solution at the time was to move to a Linux server. But switching servers isn’t always an easy option. And some kind of workaround was needed, waiting for bugs to be fixed would leave the application dead.

Just passing on knowledge I gained that could help with troubleshooting and/or identifying the actual issue.

The problem with that is that you found something unstable, and it could be doing more messes, silently, more than that you did notice. Like you working around to make a report to be printed while some bank balance account field could be silently getting damaged by this same kind of bug in some unrecoverable way.

Great info, but trying to workaround instead of detecting the problem and fixing it can lead to undesirable side effects. So I should advise those that potentially could find the same environment you found in the past about it.

1 Like

TimStreater, you went above and beyond as did everyone. I appreciate all your input. As for the underscore, I just did that when I posted the code because it looked like it was cutting off. My code doesn’t actually have that.

1 Like

After an incalculable number of fruitless attempts, this was my solution:

  1. Connect to the Database and run a "SELECT * FROM Members;" query.
Var sql As String
sql = "SELECT * FROM Members;"
  1. Populate the ListBox with the table data.
If data <> Nil Then
  For Each row As DatabaseRow In data
    DataList2.AddRow(row.ColumnAt(0).StringValue, row.ColumnAt(1).StringValue, _
    row.ColumnAt(2).StringValue, row.ColumnAt(3).StringValue, row.ColumnAt(4).StringValue, _
    row.ColumnAt(5).StringValue, row.ColumnAt(6).StringValue, row.ColumnAt(7).StringValue, row.ColumnAt(8).StringValue)
  Next
  data.Close
End If
  1. Retrieve everything in the ID and FirstName Columns, and add the values to two arrays respectively:
For i as integer = 0 to rowCount - 1
  idArray.Add(DataList2.CellTextAt(i, 0))
Next

For j as integer = 0 to rowCount - 1
  nameArray.Add(DataList2.CellTextAt(j, 4))
Next
  1. Loop through each array. When the value of the “idArray” index matches the ID entered into the Login Field, the corresponding item in the nameArray is the name I’m after and it’s assigned to “userName.”
For k As Integer = 0 to rowCount - 1
  if idArray(k) = logInField.Text Then
    userName = nameArray(k)
  End If
Next

Once again, the adage proves true; “Where there’s a will there’s a way.”

A huge thanks to everyone for their kind assistance!!! I’ve always been of the opinion that the forum alone is worth the price of a Xojo license.

Blessings!

1 Like

Well, this solution is creative but not exactly efficient. I’d try Tim Hare’s code again, but replace recordset with rowset. It should just work.

Do you need the arrays for something else?
Do you need to fill a Listbox or this is a hidden listbox just to find the userName from logInField.Text?

I thought I’d post this for anyone who might find it interesting if not totally bizarre. I gave Maximilian Tyrtania’s suggestion a try and ran Tim Hare’s code again replacing recordset with RowSet. The first time I ran it, I got the same NilObjectException but after fiddling with it a little, wouldn’t you know, it actually worked! Now it’s true that the execution of the query was written a little different but in terms of the logic, it seemed to me that it was essentially what I ran the first time. So I scrolled up to see what I had originally posted and rewrote the query practically verbatim. And it worked!!! But WHY??? As you can see from these pics, the code is for all practical purposes, identical:

Original Code:

New Code:

Now I did modify the table so that “Members” is now all lower case, and I’m querying “Nic” rather than “FirstName” but other than that, the code is virtually the same. And yes, in the original table, the M in members was upper case, and there was in fact, a column called FirstName as can be seen from my conversation with Wayne Golding:

Although I’m thrilled that it works now, it’s frustrating at the same time.

Yes, that’s exactly what I was doing with the ListBox. Having no expertise in SQL, it was a quick alternative since the SELECT WHERE statement wasn’t working. Now that it’s working, I can just get rid of both. Thanks!

1 Like

But Members doesn’t have a column called Nic, so how is it you are able to query it? Your original query should have worked, and your new one should be giving the Nil exception.

Anyone know whether MySQL is case-sensitive for table names etc in queries?

Correct, the table I was using several weeks ago (Members) did not have a column called Nic. But the modified version of it I’m now using does. :slight_smile:

Be careful using common words for the rows/fields, see that ‘First’ and ‘Last’ are not black.

See: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

In MySQL you can force symbols being treated as identifiers using `backticks`

SELECT `TABLE` FROM TABLE;

The ANSI standard are double quotes. SELECT "TABLE" FROM TABLE;

That differs from strings, that are set using single quotes.

Double quotes must be avoided on MySQL, it defaults to strings, but MySQL has an option that if set it start to interpret as identifiers to keep compatibility with other servers. So in one server it may work, but in another one it causes headaches. So just avoid it on MySQL.

Prefer single quotes for strings as SELECT 'TABLE';

image

Wrong:
image

Acceptable if you have a table with a field TABLE on it:
image