Have desktop app that I would like to convert to use ActiveRecord, using ARGen.
App has over 50 SQLite tables. Would not like to convert all tables at once but a few at a time.
Is this possible or is it best to all at once.
Any recommended AR conversion strategies would be welcome.
Have already used AR for a few new web and desktop apps. MySql and SQLite.
I’ve done it both ways, to be honest. Piecemeal might be a bit easier from an existing app perspective.
The advantage of doing it piecemeal is that you’re not changing everything at once. Biting it off a table at a time isn’t as daunting a task as doing them all at once.
I’ve chose a smaller app to convert first, one with only 9 tables.
I’ve run into a problem. It appears to me that ActiveRecord expect all the tables to have ‘id’ as the Primary KEY. All the tables in my app use other fields as Primary key, except one. Can read records using the key ok, but updating result in the record being added as a new record.
Think my solution is to copy tables to new database making id primary key.
Think adding routines to “FindbyKey” and “SavebyKey” would be posible but require more effort.
Suggestion please.
That is incorrect. It will use whatever primary key field is on the field.
The table class will have a property for the primary key and will also have “ID” which is mapped to the primary key field so you don’t need to know which field is the primary key.
We generally created a shared method in each table that will load by record id. Example:
[code]Shared Function FindByID(id as Integer) As Data.customer
//Usage:
//dim customer as Data.customer = Data.customer.FindByID( id )
dim s as string
s = "Select * from customer WHERE customer_id = " + str(id)
dim rs as RecordSet = Data.db.SQLSelectRaiseOnError(s)
if rs.RecordCount = 0 then return nil
dim customer as new Data.customer
customer.ReadRecord(rs)
return customer
End Function[/code]
FWIW, ARGen will create these shared methods for you.
Don’t understand. If the primary key is not an integer. What would the following be converted to:
dim key as string
key = (LBApt.cell(LBApt.listIndex,0))
dim rs as RecordSet
dim ps as SQLitePreparedStatement
ps = gDB.Prepare("SELECT * FROM Appoint WHERE Appoint_key = ? " )
ps.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT )
rs = ps.SQLSelect( key )
if gDB.Error then
displayDatabaseError (true,CurrentMethodName+"(E02)")
return
end if
Oh, you’re saying that the primary key is NOT an integer. Right, well, ActiveRecord assumes an integer. You can accomplish the same thing by creating a unique index on the text field and still having an integer primary key.
I didn’t know I could modify then primary key… I use the app Base to:
remove the primary key status from the old “KEY” field.
add new field “id” integer primary, etc. (it number the records …
Thanks Bob. Now let see what I can get into next.
Bob:
I’ve run into an: “UnsupportedFormatException”, in the code as shown below, error marked at: <<<--------ERROR---------<<<<<<<<<<<<<<<
[code]Private Sub ReadRecord(rs as RecordSet, oTableInfo as BKS_ActiveRecord.P.TableInfo, byref dictSavedPropertyValue as Dictionary)
'Read current record out of rs into properties
dim dictFieldValue as new Dictionary
for each oFieldInfo as BKS_ActiveRecord.P.FieldInfo in oTableInfo.aroField
dim oField as DatabaseField = rs.Field( oFieldInfo.sFieldName )
dim pi as Introspection.PropertyInfo = oFieldInfo.piFieldProperty
if oField is nil then
BKS_Debug.Assert( false, _
"A field needed to populate this record wasn't provided: " + _
oFieldInfo.sFieldName )
continue
end if
dim vProperty as Variant
if pi.PropertyType.Name = "Double" then
if oField.Value=nil then
vProperty = BKS_ActiveRecord.kDoubleNullSentinal
else
vProperty = oField.Value
end if
elseif pi.PropertyType.Name = "String" then
dim s as string = oField.StringValue
if Encoding(s) is nil then
s = DefineEncoding(s, Encodings.UTF8)
end if
vProperty = s
elseif pi.PropertyType.IsPrimitive then
vProperty = oField.Value
elseif pi.PropertyType = GetTypeInfo(Date) then
dim v as Variant = oField.Value
if v = nil or v = "" then
vProperty=nil
else
dim dt as new Date
dim dOffset as double = dt.GMTOffset
dt.GMTOffset = 0
dt.SQLDateTime = v.StringValue <<<--------ERROR---------<<<<<<<<<<<<<<<
dt.GMTOffset = dOffset
vProperty=dt
end if
else
vProperty= oField.Value
end if
pi.Value(self) = vProperty
dictFieldValue.Value(pi.Name) = vProperty
next
dictSavedPropertyValue = dictFieldValue
End Sub[/code]
v contains: 11/1/2013
Could it be because the variable v contain just the date without the time?
The database field it’s working on is defined as “Date” and is saved with only the date and no time.
Do you have any suggestion how to fix this?
“11/1/2013” is not SQLDate format. That format is YYYY-MM-DD.
So if I had been using ActiveRecord in the first place it would have prevented me from making this type of error.
Thanks Bob.
heh. Well, probably. This a SQLite database? That’s another part of the issue since it will pretty much save anything in any field with no errors.
Probably what I’d do is create a conversion app that converts it all from the mm/dd/yyyy format to the proper SQLDate format. Or, you could attempt to trap the error in the ReadRecord method and attempt to convert it that way. The former is better than the latter, IMO.
Date has been changed.
Next problem. Have record(s) with an apostrophe for example “Joe’s Bar” (without quotes). Getting gdb.error in the List method. The parm for list has a Criteria with a string with a apostrophe in it’
I am not getting an error is what I have been using which may make a double apostrophe in the key/criteria.
Any suggestions?
Probably the best way to do that is instead of using straight SQL use a prepared statement. That way you don’t have to worry about the escaping the apostrophe’s (the prepared statement does it for you).
The reason why we haven’t automatically done that from ARGen is that complex SQL statements via prepared statements are kind of hard and tend to be custom made. Any automation on our part would probably be wrong.
You could also try escaping the string yourself. We have a string extends method that escapes it properly and puts the single quotes around the string so you don’t have to do much else. This might be the easier way to do it but probably the least secure/safe way.
I’m going to think about that.
Using prepared statement my be secure/safe, but then I would not have the advantage of using ActiveRecord.
Need to check what/how the key is generated. Making sure injection something bad is not possible. Need to research this.
Using ActiveRecord has uncovered some of my bad coding, not just the one mention so far, I’m sure some will popup.
Thanks again.
Well, no. Behind the scenes in save (update and insert) it’s using PreparedStatements. I was thinking more of the shared List method that ARGen creates. That’s really the only place I ever end up using PreparedStatements and Occasionally a FindByX shared method.
For list forms we often have the dim aro() as Data.SomeTable = Data.SomeTable.List(sFilter, sOrder) and sFilter is the WHERE clause. That’s where a prepared statement is hard to fit in. If you can’t do a prepared statement at least use proper string escaping. This is what I use.
[code]Function SQLizeText(Extends sText as string) As String
dim sReturn as string
’ Change all single apostrophes to double apostrophes.
sReturn = ReplaceAll(sText, “'”, “‘’”)
’ Return the new string with apostrophe’s around it already
Return “'” + sReturn + “'”
End Function
[/code]
It would be used like this:
dim sFilter as string
sFilter = "username = " + txtusername.text.SQLizeText
sFilter then is “username = ‘O’‘Neil’” and you don’t have to worry about the data. Of course, that’s still not safe in the best context of the word but at least it solves the apostrophe issue.
We all have it. Own it and try to teach someone else not to do the same mistakes you’ve made. 
We all have it. Own it and try to teach someone else not to do the same mistakes you’ve made. :)[/quote]
it has done that for many of us. it has helped me with my database/SQL programming but taking parts of it away from me.