Database RowSet and Database Row?

General
1

Greetings Xojo People - Using Xojo2019R3.2 for just a bit longer.

I have an SQLite database with a table Components. One of the columns is PartNumber. Each entry is unique by design. I want to return the row in which the PartNumber is “xyz”. The SelectSQL operation appears to return only a RowSet.

Do I have to go through the whole iteration song-and-dance to get the one row in that RowSet?

Many thanks
Jim Wagner, Oregon Research Electronics

2

Do not use DatabaseRow, the bugs with those iterators never end. The two things I do not and will never trust are DatabaseRow and the Currency class.

RecordSet navigation is one at a time. It has always been like that.

while not rs.EOF
  if rs.Field("PartNumber").StringValue = "xyz" then
    // Do xyz stuff
  end

  rs.MoveNext

wend

RowSet in API 2.0 works the same, you just need the more verbose names.

1 Like
3

Well you might not get any rows back, so you need to handle that also.


Var  reg as Rowset

reg = db.SelectSQL ("select stockcount from Components where partnumber=?", partnum)
if  (reg<>Nil and reg.RowCount>0)  then
// Get the columns here, e.g.:
  stockcount = reg.Column("stockcount").IntegerValue
else
//OK what now
End if
1 Like
4

Thanks for the great tips.

No rows should not be an issue because the desired PartNum is always generated by clicking a listbox that is populated directly from the table. The user (e.g. me) cannot just type in a random value. That said, I do strongly believe in defensive programming so I will take that into account.

I’ve not had any obvious problems with iterating through a RowSet, but I also was not aware that there might be problems. I’ll go back and fix those instances.

Tim: Hmmm I just looked and RecordSet was deprecated in 2019R2. is it still safe to use? Never mind, I understand you closing comment now!

Appreciate your help!
Jim

5

If you’re working in 2019 I’d stick to API 1. API 2 took a few releases before getting it’s current naming, and they are still renaming things just to make life difficult.

RecordSet will only ever be nil if the SQL is bad.
RowSet that would never happen because it would raise a DatabaseException.

If you construct your loops such that they don’t execute if there are no rows, you don’t have to check RowCount. My example will do that.

6

Think I understand more clearly.

Jim

7

API1 doesn’t give you simple SQLinjection protection, you have to do all the bind stuff which is a bore. For me, that made the switch to API2 database conventions well worth while.

8

It’s a great reason to use API 2, but with the 2019 releases API 2 was still very new. There have been at least three revisions since (I call it API 2.3 internally). There were some changes in 2023r3, so I might start even start calling it API 2.4.

I consider the 2019 releases “API 1” because they still have Web 1. To each their own.

9

it use a cursor so by default the cursor is at the first row if data was found.
row ← cursor
row
row
row

thats why you can access the data there by column name.

see also
.BeforeFirstRow
.AfterLastRow (similar to EOF)

https://documentation.xojo.com/api/databases/rowset.html#rowset

10

Just a general FYI - with the current sale, will be updating to to 2023R3.1

Jim