Database row has less columns than RowSet

Database: MySQL

The following code should fill a ListBox with data from a MySQL Database. It is working despite the fact that one database field is missing in the result. When I set a breakpoint at the end of the code where the fields of the listbox are filled I can see that the resulting databaseRow contains 9 values. The RowSet includes 10 values. If I execute the sql SELECT directly on the DB-Server (in phpMyAdmin) it works like expected and returns 10 values. Is this a Bug or I’m doing something wrong? Thank you for your help.

CODE-Sniped:

sql = “SELECT items.barcode, kinds.name, items.type, producers.name, items.serialnumber, items.puchase_date, items.invoicenumber, items.warranty_time, items.change_date, users.surname FROM items LEFT JOIN producers ON items.producer_id = producers.ID LEFT JOIN suppliers ON items.supplier_id = suppliers.ID LEFT JOIN users ON items.user_id = users.ID LEFT JOIN kinds ON items.kind_id = kinds.ID ORDER BY change_date;”
Var data As RowSet

Try
data = App.mDB.SelectSQL(sql)
Catch e As DatabaseException
MessageBox("DB Error: " + e.Message)
Return
End Try

If data <> Nil Then
For Each row As DatabaseRow In data
EditListBox.AddRow(row.ColumnAt(0).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(1).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(2).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(3).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(4).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(5).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(6).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(7).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(8).StringValue.DefineEncoding(Encodings.UTF8))
Next

data.Close
End If

If you guarantee this, maybe it is a 0 based x 1 based bug related to API1->API2

I may be missing something but it seems as if you are only pulling 9 values (0-8). Wouldn’t you need another line as

row.ColumnAt(9).StringValue.DefineEncoding(Encodings.UTF8))

to get the 10th data item?

1 Like

yes, here are the outcome from the debugger.
DatabaseRow:
DataBaseRow
RowSet:
RowSet1

When I try to insert 10 values I would get an “Database out of bounce” Error. Because of the one missing value. So with …ColumnAt(9)… the code would crash.

You appear to have 2 columns with the same name. Is that intentional? You should alias one of them to make them unique.

2 Likes

Try to output something like

System.DebugLog "Database Columns Count: "+row.ColumnCount.ToString

inside the loop to see how many actually are in there.

Ahh great spot…

Hi DerkJ, this gives me: Database Columns Count: 9

Tim found the the inconsistency. RowSet returns any columns, even with duplicate names, don’t know the behavior of a Field(name).Value; DatabaseRows fuses columns with the same name causing unknown results, also what would be the prevalent column?

You can modify your query to change the names like:

"SELECT items.barcod, kinds.name AS kindsName, items.type, producers.name AS producerName, etc… "

That may get you all the columns.

1 Like

I think that the RowSet behavior should be the correct one, and an exception should be raised when we would try to manipulate a field by name without unique names in the same record. Such user fail should not fail silently.

1 Like

Hi Tim!
Thank you very much, that was the fault. I didn’t gave the columns an alias because I didn’t car at this stage. But you are right, as soon I gave all columns unique names it worked like expected.

Also many thanks to everyone else who replied.

Here now the working Code:

sql = “SELECT items.barcode, kinds.name As kindname, items.type, producers.name As producername, items.serialnumber, items.puchase_date, items.invoicenumber, items.warranty_time, items.change_date, users.surname FROM items LEFT JOIN producers ON items.producer_id = producers.ID LEFT JOIN suppliers ON items.supplier_id = suppliers.ID LEFT JOIN users ON items.user_id = users.ID LEFT JOIN kinds ON items.kind_id = kinds.ID ORDER BY change_date;”
Var data As RowSet

Try
data = App.mDB.SelectSQL(sql)
Catch e As DatabaseException
MessageBox("DB Error: " + e.Message)
Return
End Try

If data <> Nil Then
For Each row As DatabaseRow In data
EditListBox.AddRow(row.ColumnAt(0).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(1).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(2).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(3).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(4).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(5).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(6).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(7).StringValue.DefineEncoding(Encodings.UTF8), _
row.ColumnAt(8).StringValue.DefineEncoding(Encodings.UTF8), row.ColumnAt(9).StringValue.DefineEncoding(Encodings.UTF8))
//System.DebugLog "Database Columns Count: "+row.ColumnCount.ToString
Next
data.Close
End If

Yes, but as said, Xojo behavior is wrong and needs fixing. Seems they are creating silent points of failure in both cases, fusing fields of same name or having cloned names and “working” by name. What happens if you ask for the field “name” in the rowset? In the DatabaseRow one column (who knows which of the names) is just silently missing.

Hi Rick!
when I use “System.DebugLog row.Column(“name”).StringValue” inside the loop with my “old” faulty code then it returns all “name” values from one joined table. Despite there is also a second table with “name” values. It seems to be the first mentioned table in the SQL statement.

I don’t get it. Are saying that you are getting both kinds.name and producers.name mixed in one column?

I think he’s saying he gets the values from one table but not the other. Getting the value from the first field when addressing by field name where there are more than one field with that name has always yielded the value of the first field with that name. You had to use IDXField in the past to access them. The fact that RowSet drops the second field entirely seems like a bug.

It also appears that RowSet is getting the last field with that name, which is inconsistent.

2 Likes

Yes Tim is right, that’s what I meant. When I have 4 entries in the items table then I get 4 name-values from the joined table.
The fields are not mixed, but a table is simply missing. In my case it will be the first table called in the SQL statement. The second table is simply missing.

The “join” is not important here, the names are. I understand, you mean you have 4 rows (records) with 2 columns (fields, with the same key “name”), and the second column is missing (and should not). I’ve seem systems dealing with this question auto renaming clashing columns adding a sequence number to the field name, so the first column should be “name” and the next one “name1” (if a “name1” does not explicitly exist listed too, if so, it will try the next value, “name2”, and so on). Someone should open a bug report with this fix request.