I keep a schema dictionary of field types and use the following to prepare arbitrary selects, inserts, or updates (trimmed out other dbms code). I generally already have the arsFieldName() array which I join for the sql statement. Not as convenient as a parmarray though.
[code]Private Function GetRecordsSQLite(sql As String, arsFieldName() As String, arvFieldValue() As Variant) As RecordSet
Dim ps As PreparedSQLStatement = SQLitePreparedStatement(oDBWrapper.Prepare(sql))
[code]Function BindType(fieldName as String) As Integer
dim sTable as Text = NthField(fieldName,".",1).ToText
dim sField as Text = NthField(fieldName,".",2).ToText
dim sFieldType as String = xojo.Core.Dictionary(SchemaDict.Value(sTable)).Value(sField)
Select Case sFieldType
Case "String", "Date", "Time", "TimeStamp"
Return SQLitePreparedStatement.SQLITE_TEXT
Case "Integer"
Return SQLitePreparedStatement.SQLITE_INT64
Case "Double"
Return SQLitePreparedStatement.SQLITE_DOUBLE
Case "Boolean"
Return SQLitePreparedStatement.SQLITE_BOOLEAN
else
Return SQLitePreparedStatement.SQLITE_TEXT
end Select
[quote=234532:@Kimball Larsen]For anyone interested, I’ve knocked up a quick method that extends the sqliteDatabase class to allow for an array of Text up to 50 elements large to be passed to sqlExecute. I’ve tested it lightly and it seems to work for my specific use-case.
I’d be happy to know if there are improvements that could be made to this approach, as I hate this method. A lot.
Thanks for going to the effort of writing that out and sharing it, Kimball: I was hoping that by the time I started using the iOS framework for real that we’d have a better solution, but you’ve saved me a bunch of time and suicidal urges by sharing that
I have a solution that’s less “ewww”. This was from a quick proof-of-concept so modify as needed:
Sub SQLExecuteWithArray(Extends db As SQLiteDatabase, sql As Text, values() As Auto)
static method as Introspection.MethodInfo = GetMethod( db, "SQLExecute" )
dim params() as Auto
params.Append sql
params.Append values
method.Invoke( db, params )
End Sub
Function SQLSelectWithArray(Extends db As SQLiteDatabase, sql As Text, values() As Auto) As SQLiteRecordSet
static method as Introspection.MethodInfo = GetMethod( db, "SQLSelect" )
dim params() as Auto
params.Append sql
params.Append values
dim result as auto = method.Invoke( db, params )
return SQLiteRecordSet( result )
End Function
Private Function GetMethod(o As Object, name As Text) As Introspection.MethodInfo
dim r as Introspection.MethodInfo
dim ti as Introspection.TypeInfo = Introspection.GetType( o )
for each method as Introspection.MethodInfo in ti.Methods
if method.Name = name then
r = method
exit for
end if
next
return r
End Function