Making parameter position in sqlite3_bind commands dynamic-CodePudding

In the project I am not be allowed to use third-party code (i.e. GRDB, Swift.sqlite), so I need to use swift's c-wrapper sqlite3 functions directly.

My SQL statement is variable, based on certain flags.

var sql = "SELECT * FROM myTable WHERE "
sql  = " colA = ? "
sql  = " AND (colB > ? OR colB < ?)  "

if flagC == true {
  sql  = " AND colC = ? "
}

sql  = " AND colD = ? "

var stmt = OpaquePointer?

guard sqlite3_prepare_v2(dbPointer, sql, -1, &stmt, nil) == SQLITE_OK
else { print("error") }

guard sqlite3_bind_text(stmt, 1, (myVarA as NSString).utf8String, nil) == SQLITE_OK &&
      sqlite3_bind_double(stmt, 2, myVarB, nil) == SQLITE_OK &&
      sqlite3_bind_double(stmt, 3, myVarB1, nil) == SQLITE_OK 
else { print("error") }

var nextPosition = 4
if flagC == true {
  guard sqlite3_bind_int(stmt, nextPosition, myVarC, nil) == SQLITE_OK
  else { print("error") }
  nextPosition  = 1
}

guard sqlite3_bind_double(stmt, nextPosition, myVarD, nil) == SQLITE_OK
else { print("error") }

while sqlite3_step(stmt) == SQLITE_ROW {
  // deal with result
}

This works, however it feels pretty clunky through the interrupting of the guard statements to accommodate potential parameters.

The only way I could think of make the parameter position bindings "dynamic" was to use a nextPosition helper-variable.

It feels wrong to "hardcode" the position of the bindings. Is there a way to solve this without hardcoding these positions?

CodePudding user response：

Hard-coded numerical indexes are as much hard-coded as the raw SQL string itself - not better, but not worse either. I would not worry too much about them. 1, 2, 3? OK: those are the first, second and third parameter - that's pretty clear.

I agree that we could enhance your sample code if the need for nextPosition would dissapear. I enumerate a few options below.

One possible improvement is to move the flagC test to the end, when you build the SQL query:

var sql = "SELECT * FROM myTable WHERE "
sql  = " colA = ? "
sql  = " AND (colB > ? OR colB < ?)  "
sql  = " AND colD = ? "

if flagC == true {
  sql  = " AND colC = ? "
}

You would still use hard-coded indexes (1, 2, 3, 4), but you would no longer need nextPosition.

One other technique is to use named parameters:

var sql = "SELECT * FROM myTable WHERE "
sql  = " colA = :a "
sql  = " AND (colB > :minB OR colB < :maxB)  "

if flagC == true {
  sql  = " AND colC = :c "
}

sql  = " AND colD = :d "

Instead of providing numerical indexes for your parameters (1, 2, 3, nextPosition), you would use sqlite3_bind_parameter_index in order to turn parameter names (:a, :minB, etc.) into indexes.

Finally, another approach entirely is to test for flagC early, and handle both SQL requests completely independently. This would remove your need for nextPosition.