Hello all,
In my first round of working with Xojo, I created classes for each database table that I used. I added new Methods for each type of function that I wanted, below is a sample
Constructor
Db = passed_Db
osql = New cSmartSQL
setTableFieldNames
SID = SiteID
get function
osql.StatementType = eStatementType.Type_Select
osql.AddTable Tbl
getTableFields
osql.ClearWhereClause
osql.AddComplexWhereClause "relays.address = "+ sAdd + " AND relays.module_device_no = "+ sModDevNo
#if TargetWeb
RsRelays = Session.db.SQLSelect(osql.SQL)
if Session.db.Error = true then
dbMessage= Session.db.ErrorMessage
dbErrorNo = Session.db.ErrorCode
Return 0
end if
#Else
RsRelays = db.SQLSelect(osql.SQL)
if db.Error = true then
dbMessage= db.ErrorMessage
dbErrorNo = db.ErrorCode
Return 0
end if
#endif
RecordsRetrieved = RsRelays.RecordCount
if RecordsRetrieved = 1 and FilVars = True then FillVars
Return RecordsRetrieved
Then FilVars
Address = 0
Slot =0
ModuleDeviceNo= 0
ModuleTypeNo = 0
Relay_Zone =0
OnTime = 0
RelayFunction =""
FireOnAlarm =""
State = 0
DelayTime = 0
StartTime = ""
StopTime = ""
RowID = 0
IsOnOff =0
KP_Zone = 0
UserDefined = ""
DeviceStatus = ""
MonitorEnabled = ""
UseAddressToFire = 0
UseChannelToFire = -1
DefaultRelayDirection = "C"
RelayEnabled = "N"
SID = RsRelays.Column("sid").IntegerValue
RelayEnabled = RsRelays.Column("relay_enabled").StringValue
UseAddressToFire= RsRelays.Column("use_addr").IntegerValue
UseChannelToFire= RsRelays.Column("use_channel").IntegerValue
DefaultRelayDirection= RsRelays.Column("default_direction").StringValue
DeviceStatus = RsRelays.Column("device_status").StringValue
MonitorEnabled = RsRelays.Column("monitor_enabled").StringValue
Address= RsRelays.Column( "address").IntegerValue
Slot= RsRelays.Column("slot").IntegerValue
ModuleDeviceNo= RsRelays.Column("module_device_no").IntegerValue
ModuleTypeNo = RsRelays.Column("module_type_no").IntegerValue
Relay_Zone = RsRelays.Column("Zne").IntegerValue
OnTime = RsRelays.Column("on_time").IntegerValue
RelayFunction = trim(RsRelays.Column("relay_function").StringValue)
FireOnAlarm = RsRelays.Column("fire_onalarm").StringValue
State = RsRelays.Column("state").IntegerValue
DelayTime = RsRelays.Column("delaytime").IntegerValue
StartTime = trim(RsRelays.Column("start_time").StringValue)
StopTime = trim(RsRelays.Column("stop_time").StringValue)
//RowID = RsRelays.Column("rowid").Int64Value
IsOnOff = RsRelays.Column("ison_off").IntegerValue
KP_Zone = RsRelays.Column("kp_zone").IntegerValue
UserDefined = trim(RsRelays.Column("user_defined").StringValue)
That works pretty good and is reasonably fast. However, now I need to use the same basic database but add a field to each one of the classes/function etc, so > 1 “company” may use it. The new field is SID. So each querie will have to be modified to add this variable.
I had thought that passing the SID when the object is instantiated, but I still need to edit all of the various methods to add the use of SID. Further, down the road, I may need to get all of the rows for all SIDs, which may complicate this…
That is a LOT of work! There are around 100 tables to be modified.
Can anyone give me a bit of direction to make this easier? I like the class use since it keeps all of the work within each table class.
This is for a Web2 project that is just starting. The code sample was from a Web1 project that works quite well.
Thanks,
Tim
