Sorry Jakob, this hurts me! I have to say, that you’re using a very bad programing style regarding to security (SQL Injection) but also in matters like object reusing and code maintenance.
Just a suggestion (please keep in mind i am writing this just down, so no warranty and keep also in mind that DatabaseRecord <> Recordset)
Create an Object Class for your Table e.g. when using a Table named “Contacts” create same Object Class in Xojo with Properties referenced to each your database fields (Street, Name, ZIP, Phone etc.)
and 1 additional method :
CreateFromDatabaseRecord(rs as Recordset)
and 2 additional functions:
LoadFromDB(id as Integer) as Boolean
ToDatabaseRecord as DatabaseRecord
SaveToDB as Boolean
So the magic comes here:
LoadFromDB(id as Integer)
This function loads a recordset from your Database with given ID value.
Additionally I am using for all my DB IO stuff another object class named “MyCore” where I just throw my SQL Query in and it handles the DB Provider automatically, so I can switch easily databases (SQLlite, mysql, ODBC) :
dim WasSuccessful as Boolean = false
rs = myCore.SQL("SELECT * FROM YourTable WHERE Identifier=" + str(id))
if rs <> nil then WasSuccessful = CreateFromRecordset(rs)
return WasSuccessful
Return value shows me if loading of recordset was successful or not. So I am using this class like this:
if MyObject.LoadFromDB(4711) then
// do some stuff like filling input fields ...
InputField.Text = MyObject.MyPropertName
end if
CreateFromDatabaseRecord(rs as Recordset)
This method basically transfers your DB fields from referenced Recordset to your object properties.
Me.PropertyName = rs.Field("DBFIeldname").DoubleValue (or Stringvalue depends on your type)
Dont forget to define Encodings here when reading strings:
Me.PropertyName = DefineEncoding(rs.Field("DBFIeldname").getString, Encodings.UTF8)
And now back to your database. In your main program just call this method in order to save with current values:
if myObject.SaveToDB then
// Show info for successful saving or reset your form or do sth else
else
// show error Message
end if
SaveToDB as Boolean
This functions saves your object back to DB. It first checks if your ID Property is not empty (=0) because when this is empty I assume that the object is a new one without unique ID:
try
dim WasSuccessful as Boolean = false
if Me.Identifier = 0 then
// insert new recordset into DB
db.InsertRecord("YourTable", Me.ToDatabaseRecord)
WasSuccessful = not db.Error
else
// update existing recordset
dim rs as Recordset
rs = db.SQLSelect("SELECT * FROM YourTable WHERE Identifier=" + str(Me.Identifier))
if rs <> nil then
rs.Edit
rs.Field("PropertyName").StringValue = Me.PropertyName
// all your Properties and DataTypes
rs.Update
WasSuccessful = not db.Error
end if
end if
return WasSuccessful
catch err as NilObjectException
Finally
end try
ToDatabaseRecord as DatabaseRecord
This function transfers all your Properties back to DatabaseRecord for Updating.
dim rs as new DatabaseRecord
rs.DoubleColumn("DBFIeldname") = MePropertyName
...
return rs
Reuse this snippets als kinda framework for all your DB projects, extent it with other functions like seamless DB Provider switching, checking against timestamp if recordset altered meantime or more optimizations.
Thats far better way to deal with database operations.
