How To Pass Unknown Number Of Parameters to SQLUpdate

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))

For i as integer = 0 to arsFieldName.Ubound

ps.BindType(i, oDBWrapper.BindType(arsFieldName(i)))
ps.Bind(i,arvFieldValue(i))

next

Return ps.SQLSelect
End Function
[/code]

[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

End Function
[/code]

This thread is about SQLite in iOS.

Wrap me up and paint me stupid. Sorry for the noise.

[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.

It’s apparently too large to paste here, so here’s a pastebin.[/quote]

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 :wink:

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

As usual, Kem has an elegant solution. Nice!