Yes, and what did you get?
Yes, the document is about SQLite, but you may learn things (the mechanics):
Also, in the Examples, you may check:
TimStreater, it builds but it displays the message, “No records returned.”
And is there a row in the database which has id = 2 ??
Yes, sir. 2 and 3
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.
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.
After an incalculable number of fruitless attempts, this was my solution:
- Connect to the Database and run a
"SELECT * FROM Members;"query.
Var sql As String sql = "SELECT * FROM Members;"
- 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
- 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
- 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.
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?