RS.IdxField v RS.Field("FieldName")

  1. 3 months ago

    Hi all,

    I read an article recently about the advantages/disadvantages of using RS.IdxField v RS.Field("FieldName"). I've searched but can't for the life of me find it.

    I'm sure it came to a conclusion that one was much better than the other, particulrly in relation to performance.

    Can anyone confirm which is considered to be the best one to use?

    Im using macOS, Xojo 2019r1.1 and SQLite.

    Thanks

    I've written about it a time or two at www.bkeeneybriefs.com. There is one huge disadvantage of idxField and it is dependent upon the SQL string. So if you change the order or the fields then idxField is no longer valid whereas using FieldName will always work. IdxField is also 1-based not 0-based which is an aberration in the eyes of the coding gods IMO.

    The problem with FieldName is that it's quite easy to fat finger the field name and not find it until runtime (where it will generate an error you have to check for). You can get around this by having constants for the field names (but you still need to check for the database error - just because you never know if some DBA is messing with you).

    Of the two the ONLY time I use idxField is if I am querying exactly one field in the query. All other instances I will use FieldName. It's explicit on which field you are referring too.

  2. Bob K

    May 22 Pre-Release Testers, Xojo Pro Answer Kansas City

    I've written about it a time or two at www.bkeeneybriefs.com. There is one huge disadvantage of idxField and it is dependent upon the SQL string. So if you change the order or the fields then idxField is no longer valid whereas using FieldName will always work. IdxField is also 1-based not 0-based which is an aberration in the eyes of the coding gods IMO.

    The problem with FieldName is that it's quite easy to fat finger the field name and not find it until runtime (where it will generate an error you have to check for). You can get around this by having constants for the field names (but you still need to check for the database error - just because you never know if some DBA is messing with you).

    Of the two the ONLY time I use idxField is if I am querying exactly one field in the query. All other instances I will use FieldName. It's explicit on which field you are referring too.

  3. Thankyou Bob, thats what I was looking for :)

  4. Bob K

    May 22 Pre-Release Testers, Xojo Pro Kansas City

    https://www.bkeeneybriefs.com/2012/02/recordset-idxfield-vs-recordset-field/

    Oh, and there is no speed difference (or at least not enough to matter) between the two.

  5. Norman P

    May 22 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    @Bob K So if you change the order or the fields then idxField is no longer valid whereas using FieldName will always work.

    Unless you remove columns from the SQL ........ adding them is no issue since they will just not be fetched without matching code to grab the values

    overall rs.Field("fieldname") is more resilient to changes in the sql

  6. Bob K

    May 22 Pre-Release Testers, Xojo Pro Kansas City

    @Norman P Unless you remove columns from the SQL ........ adding them is no issue since they will just not be fetched without matching code to grab the values

    Yup. Since I used ActiveRecord so much I forget about this one.

  7. Thanks chaps, Im using rs.Field("FieldName") now.

    :)

or Sign Up to reply!