Today's function turned out to be well suited for a macro. (create-record) takes a couple of arguments. The first is a string which holds the name of the table you are updating. The rest is kind of cool. You put in a bunch of variable names--any number is fine--and what you do is make the variable name the same as the FIELD name in your table. Then the macro takes the names and values of these variables and constructs a valid SQL statement (after filtering each value for SQL injection issues)
So, for example, to add a post in this blog, I just have to write:
(create-record "Posts" Id PosterId PostDate PostSubject PostContent)
and it generates a valid SQLite query to insert the values into the field names that are the variables Id, PosterId, etc.
The magic comes from newLISPs (sym) function, which converts a string into a valid symbol, and the (args) feature of newLISP macros, which allow a dynamic number of arguments to be passed to the macro.
Here's the code that converts the variable names into symbols:
(dolist (s (rest (args)))
(set 'temp-index-num (string 'idx)) ; we need to number the symbols to keep them in the correct order
(if (= (length temp-index-num) 1) (set 'temp-index-num (string "0" temp-index-num))) ; leading 0 keeps the max at 100.
(sym (string temp-index-num s) 'DB))