2017-11-17 12:43 GMT+01:00 Cecil Westerhof <[email protected]>:
> I have the following:
> set getLatestTeasStr {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT 5
> ;
> }
>
> But because I want to define the limit at runtime I want to change it to:
> set getLatestTeasStr {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT ?
> ;
> }
>
> In Java I would do something like:
> psSel.setInt(1, nrToFetch);
>
> How do I do this in TCL?
>
You have to work with named parameters in your queries if I understand it
correctly. For updating (which is a bit more interesting) I have now:
set updateLastBrewed "
UPDATE selectRandom
SET lastUsed = DATE()
WHERE description = :teaToBrew
"
And my ‘main’ is:
while {1} {
emptyLines
displayLatestTeas 7
emptyLines 1
set teaToBrew [chooseTea [getTeaList 7]]
if {${teaToBrew} == "#r"} {
continue
}
puts [format "Need to update the database with: %s" ${teaToBrew}]
db eval ${updateLastBrewed}
puts [format "Number of records changed: %d" [db changes]]
break
}
I display the last teas I drank.
Then I choose a tea I am going to brew now.
I display it and update the database and make sure that exactly one record
was modified.
chooseTea:
proc chooseTea {teaList} {
set nrOfTeas [llength ${teaList}]
set i 0
while {${i} < ${nrOfTeas}} {
puts [format "%d: %-30s %-10s %2s" \
[expr ${i} + 1] \
[dict get [lindex $teaList ${i}] Tea] \
[dict get [lindex $teaList ${i}] LastUsed] \
[dict get [lindex $teaList ${i}] Location]]
incr i
}
set refresh 0
while {1} {
puts -nonewline "Which tea: "
flush stdout
gets stdin choice
if {${choice} == "#q"} {
exit
} elseif {${choice} == "#r"} {
return ${choice}
} elseif {(${choice} >= 1) && (${choice} <= ${nrOfTeas})} {
incr choice -1
return [dict get [lindex ${teaList} ${choice}] Tea]
}
puts "Input incorrect."
}
}
getTeaList:
proc getTeaList {{limitNr 5}} {
global getTeasToDrinkStr
set teaList []
db eval ${getTeasToDrinkStr} {
lappend teaList [dict create \
Tea ${Tea} \
LastUsed ${LastUsed} \
Location ${Location} \
Randomiser ${Randomiser}]
}
return ${teaList}
}
It is a bit to get used to, but it is not to difficult.
Any tips how things could be done better are welcome of-course.
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
