I am wondering is there any code to check for field in the recordset ?? maybe capture the error code or something.
You can Database.FieldSchema Page Not Found — Xojo documentation to see if a field exists in a table.
If you want to check to see if the field exists in a recordset you can use the Name property when iterating through the fields in the recordset.
For i As Integer = 1 To rs.FieldCount
if rs.IdxField(i).Name = MyTestCase then return true //It exists
Next
If you’re trying to capture an error then you can wrap it in a tray catch statement specifically looking for a Nil Object Exception. That way if you do something like
dim s as string = rs.field(“SomeFieldName”).stringvalue
and you get the NOE then you can tell it doesn’t exist.
Personally, I would test for the presence of fields right after you connect to the database. If you’re worried about whether a database is upgraded or not you should test before the app really gets going. If it’s not there then abort immediately.
We have two ways of doing this: The first is to have a SysVar table that tracks the schema version. If it’s higher/lower than we expected then abort (or update db).
The second is to use ActiveRecord that doesn’t use the built in Real Studio recordset class in the traditional sense. That’s done behind the scene and allows you to get table/field autocomplete, data type compile errors and a number of other things. The OTHER thing AR does is check to make sure your data classes have all of the properties matched up with the fields in the corresponding table. More info on ActiveRecord for Xojo at https://www.bkeeney.com/rbinto/activerecord/
If you are using this with MySQL/PostgreSQL we have an internal build that fixes a couple of things we haven’t released yet. Contact me offline to get that version. Also, ARGen lets you generate all of the classes for ActiveRecord and it’s at https://www.bkeeney.com/allproducts/argen/
I am in the process of writing something like that… Did not realize that you had it… It’s pretty easy to write out classes that you can then import.
Yup. ARGen does that fairly well. The ActiveRecord classes themselves are no cost and 100% un-encrypted so you can modify as you see fit.
I looked at active record but in my naivety decided to take a different tack… I figured If I am going to be writing out classes for tables anyway, I did not see the need for the overhead introspection introduces as all info needed is known when the classes are generated against a specific DB.
What is the advantage of your approach do you think when autogenerating classes?
- Karen
i did this instead at the end of my method.
Exception err
DIM Message AS STRING = Introspection.GetType(err).FullName
IF Message= "NilObjectException"
msgbox "This field [" + vCtrlName + "] does not exist in the table"
END IF[quote=25282:@Karen Atkocius]I looked at active record but in my naivety decided to take a different tack… I figured If I am going to be writing out classes for tables anyway, I did not see the need for the overhead introspection introduces as all info needed is known when the classes are generated against a specific DB.
What is the advantage of your approach do you think when autogenerating classes?[/quote]
The Introspection is nice (for us) because on our large projects we’re adding fields to a lot of tables on a regular basis (multiple developers and a DBA). It will throw an exception when the corresponding class for the table doesn’t have a property for the field. So at least the developer knows something is wrong/missing if it catches a missing field at startup time.
The one thing it doesn’t do is catch the other way if a field was deleted. This was mainly because we add additional properties into our classes that aren’t fields but logic driven.
But otherwise, if you’re tables are fairly static the introspection isn’t needed. I just wanted the extra developer protection and the only way to get it was to use Introspection. There are a number of ways to skin the cat but it made sense at the time (3 years ago) and we’re still plugging away with it.
The data classes are pretty minimal (events, properties, and some shared methods). It’s the backend AR classes that do all the heavy lifting. So while the AR implementation is fairly complicated the part you mess with as a developer are pretty easy.
You can run ARGen on 5 tables (I think) in demo mode so you can see what it generates. And ActiveRecord is free and you can modify as you need.
[quote=25285:@Richard Duke]i did this instead at the end of my method.
[code]
Exception err
DIM Message AS STRING = Introspection.GetType(err).FullName
IF Message= “NilObjectException”
msgbox “This field [” + vCtrlName + “] does not exist in the table”
END IF
[/code][/quote]
Instead of using a catch-all exception block and invoking the overhead of introspection, why not do
Exception err as NilObjectException
msgbox "This field [" + vCtrlName + "] does not exist in the table"Easy
Never use “select *” and if you get a SQL error you can tell what the error was (like asking for a column that does not exist)
Otherwise you get a value for the column - even though it may be a NULL
I am planning on using ActiveRecord and ARGen, but is there a way to create a method so that the data could be saved in the record on add/update by iterating thru all the fields and matching the field name with the table field name, so I wouldn’t have to do a bunch of statements to save the data back into the table? Or maybe I’m missing something but I believe that’s what needs to be done on save.
If you use Active Record & ARGen you dont write ANY sql to save
Thats all generated code so its just handled for you through the classes
I could have sworn on one of Bob’s videos showing AR that he had code to save the data into the record.
I will look again. Fabulous if it’s automatic.
There is a Register method with ActiveRecord that matches the field with class property. In debug mode it will throw an exception if the property is missing. We dont mark properties in any way so theres no way to do the reverse to see if we have a property thats being treated as a field thats not in the database.
After the Register method is doing adding a record is as simple as this:
dim oRecord as new Data.TMyTableName
oRecord.sFirstField = txtFirstField.text
oRecord.iSecondField = txtSecondField.text.val
oRecord.Save
The class knows how to match the properties with the database fields and adds them via an Insert statement. Same with using updating a record.
Or, maybe Im misunderstanding what youre really asking. Have you watched the videos we have up at http://www.bkeeney.com/activerecord-getting-started/ and http://www.bkeeney.com/activerecord-and-argen/?
Let me know if you have any additional questions.
What I was referring to was in this video at 40:31 into this video
If I have 50 fields in my form, I think that means I have to have 50 lines of code to save it. I was trying to see if there was a reasonable way to get the data saved.
dim oRecord as new Data.TMyTableName
oRecord.sFirstField = txtFirstField.text
oRecord.iSecondField = txtSecondField.text.val
oRecord.Save
So if I have 50 fields in my form, on an add, I need 50 lines of code. Just trying to find a better easier way.
There’s no magic 
I’m okay with that, just wondering if there was a way to work a bit of magic that would make it more automatic…
“better easier way” is a relative thing. If you were doing this via traditional means you’d have fifty lines of code anyway. AR eliminates all of the Load, Insert, Update, Delete code. And at least now you’ll get auto complete and compiler warnings which you don’t get with standard code.
Now, you could create your own control subclasses and use Introspection to match the Field name to the control and do it that way. That doesn’t sound too bad. But in the long run you’ll spend way more time, in my opinion, to do that automation when all you had to do was do 50 lines of code. Make sense?
FWIW, our next version of ARGen will do some automatic UI placement with automation in Load/Save methods but will still require you to go in and finish the hookup. There are just too many ways to do things to make this an easy process.
As Norman said, there is no magic cure all.
Sounds good. Thanks for the info and insight.