Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 13:51 GMT+01:00 Peter Da Silva :

> Sqlite will perform the substitution of Tcl variables in a query. You can
> flag the variable with a ‘$’ or with a ‘:’ (which makes it more like other
> SQL APIs).
>

​Yes, I found that. The disadvantage is that you have to have a variable
with the correct name, but I do not mind.​


>
> So you can write:
>
> $db eval {
> SELECT   Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT   :nrToFetch;
> } {
> ...
> }
>

​I always want to define my queries in one place. So I have:
proc getTeaList {{limitNr 5}} {
global getTeasToDrinkStr

set teaList []
db eval ${getTeasToDrinkStr} {


And the definition:
set getTeasToDrinkStr "
SELECT *
FROM (
SELECT   *
,abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser
FROM teaInStock
ORDER BY LastUsed
)
ORDER BY randomiser + IFNULL(JULIANDAY(LastUsed), 0) ASC
LIMIT:limitNr
"

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 12:43 GMT+01:00 Cecil Westerhof :

> I have the following:
> set getLatestTeasStr {
> SELECT   Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT5
> ;
> }
>
> 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
SETlastUsed= 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Richard Hipp]

On 11/17/17, Peter Da Silva  wrote:
>
> $db eval {
> SELECT   Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT   $nrToFetch;
> } {
> ... do something with $Tea ...
> }
>
> This latter case works because the query is surrounded by {} so Tcl won’t
> substitute the variable, it will be seen and securely inserted into the
> query by SQLite.

FWIW, this is the reason that SQLite, uniquely among all modern SQL
database engines, understands $nrToFetch as a bound parameter.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Peter Da Silva]

Sqlite will perform the substitution of Tcl variables in a query. You can flag 
the variable with a ‘$’ or with a ‘:’ (which makes it more like other SQL APIs).

So you can write:

$db eval { 
SELECT   Tea
FROM teaInStock 
ORDER BY LastUsed DESC
LIMIT   :nrToFetch;
} {
...
}

or even

$db eval { 
SELECT   Tea
FROM teaInStock
ORDER BY LastUsed DESC
LIMIT   $nrToFetch;
} {
... do something with $Tea ...
}

This latter case works because the query is surrounded by {} so Tcl won’t 
substitute the variable, it will be seen and securely inserted into the query 
by SQLite.

This is rather nifty, which is why I recently added pretty much exactly this 
functionality to Pgtcl (though due to differences between PostgreSQL and SQLite 
syntax I had to restrict it to using “:”). The equivalent code would be:

pg_select $db -variables {
SELECT   Tea
FROM teaInStock 
ORDER BY LastUsed DESC
LIMIT   :nrToFetch;
} row {
... do something with $row(Tea) ...
}


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

I have the following:
set getLatestTeasStr {
SELECT   Tea
FROM teaInStock
ORDER BY LastUsed DESC
LIMIT5
;
}

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?​

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

​In Bash I can use:
continue 2
​to continue not the current loop, but the loop surrounding it.

This does not work in TCL. Is there another way to do this?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 9:57 GMT+01:00 Dan Kennedy :

>
> This gives:
>> wrong # args: should be "for start test next command"
>>  while executing
>> "for {t last_used loc} $teaChoices {
>>  puts $t
>> }
>>
>
> Sorry - [for] should be [foreach].
>
> So with your query as above, you want:
>
>   set teaChoices [db eval $getTeasToDrinkStr]
>   foreach t $teaChoices {
> puts $t
>   }
>

​That solved it, yes.

​


>
> There is something going wrong, because:
>>  puts [llength teaChoices]
>> gives:
>>  1
>> while it should give:
>>  5​
>>
>
> Missing $ sign. Should be:
>
>   puts [llength $teaChoices]
>
> Without the $, it's returning the length of the literal "teaChoices" - one
> element. Not the length of the list contained in the variable named
> "teaChoices".


​That was the problem.


I am trying a bit different route:
set teaList []
db eval ${getTeasToDrinkStr} {
lappend teaList [dict create\
 Tea${Tea}  \
 LastUsed   ${LastUsed} \
 Location   ${Location} \
 Randomiser ${Randomiser}]
}
set i 0
while {${i} < [llength ${teaList}]} {
puts [format "%d: %-30s %-10s %2s %d" [expr ${i} + 1] \
  [dict get [lindex $teaList ${i}] Tea]   \
  [dict get [lindex $teaList ${i}] LastUsed]  \
  [dict get [lindex $teaList ${i}] Location]  \
  [dict get [lindex $teaList ${i}] Randomiser]]
incr i
}

This does what I want (I need the value of Tea to update the database), but
is there a better way?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Simon Slavin]

On 16 Nov 2017, at 6:58pm, Richard Hipp  wrote:

> Everything in TCL is a function.

On 17 Nov 2017, at 8:38am, Dan Kennedy  wrote:

> `In Tcl, array means associative array - a key-value structure like an STL 
> map. A list is a flat vector of values, like an STL vector or an array in 
> plain old C.

On 17 Nov 2017, at 9:52am, Clemens Ladisch  wrote:

> In Tcl, all values are strings.  

Fight !  Fight !  Fight !  Sliderules at dawn !

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Clemens Ladisch]

Simon Slavin wrote:
> I thought in Tcl everything was a function ?

In Tcl, all values are strings.  

A list is a string with entries as words according to the Tcl syntax rules.
A dictionary is a list with an even number of elements (key/value pairs).
(An array is not a value; it's a special kind of variable.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Dan Kennedy]

​Nope, this one is:
 SELECT   Tea
 FROM teaInStock
 ORDER BY "Last Used" DESC
 LIMIT5
 ;
​



then $teaChoices contains three elements for each row returned by the
query. The first of each set of three is the "tea", the second the "last
used" value and the third the "location". So:

   set teaChoices [db eval $getTeasToDrinkStr]
   for {t last_used loc} $teaChoices {
 puts $t
   }

will print the list of teas.


​This gives:
wrong # args: should be "for start test next command"
 while executing
"for {t last_used loc} $teaChoices {
 puts $t
}


Sorry - [for] should be [foreach].

So with your query as above, you want:

  set teaChoices [db eval $getTeasToDrinkStr]
  foreach t $teaChoices {
puts $t
  }


There is something going wrong, because:
 puts [llength teaChoices]
gives:
 1
while it should give:
 5​


Missing $ sign. Should be:

  puts [llength $teaChoices]

Without the $, it's returning the length of the literal "teaChoices" - 
one element. Not the length of the list contained in the variable named 
"teaChoices".


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Simon Slavin]

On 17 Nov 2017, at 8:38am, Dan Kennedy  wrote:

> Not sure whether or not you really want an "array". In Tcl, array means 
> associative array - a key-value structure like an STL map. A list is a flat 
> vector of values, like an STL vector or an array in plain old C.

I thought in Tcl everything was a function ?

(don’t know Tcl, not really interested, just funnin’)

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 9:38 GMT+01:00 Dan Kennedy :

> On 11/17/2017 03:20 PM, Cecil Westerhof wrote:
>
>> The folowing works:
>>  db eval ${getTeasToDrinkStr} {
>>  puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location}
>> ${Randomiser}]
>>  }
>> ​
>> But I want to reuse what I get, so I tried the following:
>> ​set teaChoices [db eval ${getTeasToDrinkStr}]
>>  foreach tea [array names teaChoices] {
>>  puts ${teaChoices}(${tea})
>>  }
>>
>> But that does not work. teaChoices is filled, but not as an array. When
>> using:
>>  puts ${teaChoices}
>>  puts [array size teaChoices]
>>
>> I see in the first line what I expect in teaChoices, but the size is zero.
>> So teaChoices is a string instead of an array. How do I get it filled as
>> an
>> array?
>>
>
> $teaChoices is a Tcl list. Assuming your query is still:
>
>   SELECT tea, "last used", location FROM teaInStock;
>

​Nope, this one is:
SELECT   Tea
FROM teaInStock
ORDER BY "Last Used" DESC
LIMIT5
;
​


> then $teaChoices contains three elements for each row returned by the
> query. The first of each set of three is the "tea", the second the "last
> used" value and the third the "location". So:
>
>   set teaChoices [db eval $getTeasToDrinkStr]
>   for {t last_used loc} $teaChoices {
> puts $t
>   }
>
> will print the list of teas.
>

​This gives:
wrong # args: should be "for start test next command"
while executing
"for {t last_used loc} $teaChoices {
puts $t
}

There is something going wrong, because:
puts [llength teaChoices]
gives:
1
while it should give:
5​




> Not sure whether or not you really want an "array". In Tcl, array means
> associative array - a key-value structure like an STL map. A list is a flat
> vector of values, like an STL vector or an array in plain old C.


​Nope, I want a list then. (Or maybe a list of arrays.)​


-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Dan Kennedy]

On 11/17/2017 03:20 PM, Cecil Westerhof wrote:

The folowing works:
 db eval ${getTeasToDrinkStr} {
 puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location}
${Randomiser}]
 }
​
But I want to reuse what I get, so I tried the following:
​set teaChoices [db eval ${getTeasToDrinkStr}]
 foreach tea [array names teaChoices] {
 puts ${teaChoices}(${tea})
 }

But that does not work. teaChoices is filled, but not as an array. When
using:
 puts ${teaChoices}
 puts [array size teaChoices]

I see in the first line what I expect in teaChoices, but the size is zero.
So teaChoices is a string instead of an array. How do I get it filled as an
array?


$teaChoices is a Tcl list. Assuming your query is still:

  SELECT tea, "last used", location FROM teaInStock;

then $teaChoices contains three elements for each row returned by the 
query. The first of each set of three is the "tea", the second the "last 
used" value and the third the "location". So:


  set teaChoices [db eval $getTeasToDrinkStr]
  for {t last_used loc} $teaChoices {
puts $t
  }

will print the list of teas.

Not sure whether or not you really want an "array". In Tcl, array means 
associative array - a key-value structure like an STL map. A list is a 
flat vector of values, like an STL vector or an array in plain old C.


Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

The folowing works:
db eval ${getTeasToDrinkStr} {
puts [format "%-30s %-10s %2s %d" ${Tea} ${Last Used} ${Location}
${Randomiser}]
}
​
But I want to reuse what I get, so I tried the following:
​set teaChoices [db eval ${getTeasToDrinkStr}]
foreach tea [array names teaChoices] {
puts ${teaChoices}(${tea})
}

But that does not work. teaChoices is filled, but not as an array. When
using:
puts ${teaChoices}
puts [array size teaChoices]

I see in the first line what I expect in teaChoices, but the size is zero.
So teaChoices is a string instead of an array. How do I get it filled as an
array?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-17 5:38 GMT+01:00 Cecil Westerhof :

> setsqliteVersion [sqlite3 -version]
>

​By the way, I think it is a good idea to amend:
https://sqlite.org/tclsqlite.html
to show this possibility.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-16 22:20 GMT+01:00 Richard Hipp :

> On 11/16/17, Cecil Westerhof  wrote:
> > ​Is it possible to get the library version before connecting to a
> database?
>
> puts [sqlite -version]
>

​Combining yours and Eric's version, I made:
#!/usr/bin/env tclsh

package require sqlite3

setsqliteVersion [sqlite3 -version]
puts   ${sqliteVersion}
sqlite db ~/Databases/general.sqlite

In real life I will use it to checkthe SQLite verion if necessary.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Eric]

On Thu, 16 Nov 2017 21:28:10 +0100, Cecil Westerhof  
wrote:
> Is it possible to get the library version before connecting to a database?
> Now I do the following:
> #!/usr/bin/env tclsh
> 
> package require sqlite3
> 
> 
> sqlite3 db ~/Databases/general.sqlite
> 
> puts [db version]
> 
> 
> But I would prefer to check the version before connecting to a database. Is
> this possible?

Yes:

set ver [package require sqlite3]

puts $ver

Eric
-- 
ms fnd in a lbry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Richard Hipp]

On 11/16/17, Cecil Westerhof  wrote:
> ​Is it possible to get the library version before connecting to a database?

puts [sqlite -version]

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

​Is it possible to get the library version before connecting to a database?
Now I do the following:
#!/usr/bin/env tclsh

package require sqlite3


sqlite3 db ~/Databases/general.sqlite

puts [db version]


But I would prefer to check the version before connecting to a database. Is
this possible?

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Cecil Westerhof]

2017-11-16 18:44 GMT+01:00 Peter Da Silva :

>
> On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
> > When I use:
> > db eval {SELECT * FROM teaInStock} {
> >puts $Tea, $Location
> > }
>
> puts takes a single string, so you can do {puts “$Tea\t$Location”.
> Arguments are separated by space, comma has no intrinsic meaning, and puts
> takes two arguments: the file handle to write on and the string to print.
> So it’s interpreting “$Tea,” as the name of a file handle.
>
> You probably want something like:
>
> db eval {SELECT * FROM teaInStock} {
> puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
> }
>

​This is what I use:
puts [format "%-30s %-10s %2s" $Tea ${Last Used} $Location]

​Thanks.​

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Richard Hipp]

On 11/16/17, Cecil Westerhof  wrote:
> puts $Tea, $Location

Everything in TCL is a function.  The syntax is "FUNCTIONNAME ARG1
ARG2 ARG3 ..." where the arguments are separated by white space.  The
"puts" function takes either one or two arguments.  The one-argument
form of "puts" outputs ARG1 to standard-output.  The two-argument form
of "puts" sends ARG2 to output stream specified by ARG1.

Your code above tries to invoke the two-argument form of "puts".
Equivalent javascript code would be:

 puts(Tea + ",", Location)

The error arises because there is no output channel named by the
result of Tea+",".  What you want is the one-argument form, equivalent
to this JS:

puts(Tea + ", " + Location)

To get that using TCL syntax, you can use quoting to make the two
separate arguments into one:

puts "$Tea, $Location"

The key point is that everything in TCL is of the format FUNCTION ARG1
ARG2   The processing steps are like this:

(1) Identify arguments separated by whitespace.  Note that all text
within "..." and within nested {...} is a single argument.

(2) Resolve quotes.  This means remove the outermost {...} from
arguments quoted using {...}.  Remove the "..." around double-quoted
argments, and also resolve any $variable name within the double
quotes.  The $variable name resolution does not happen with {...}

(3) Invoke the function with its arguments.

Note that *everything* is a function.  Even "control" statements.  In
Tcl when you see:

 if {$i<10} {
puts "yes"
 } else {
puts "no"
 }

That really is invoking the "if" function with 4 arguments.   Since
everything is a function, everything follows exactly the same quoting
rules.  This is an important feature of Tcl that programmers whose
prior experience has been exclusively using Algol-derived languages
such as C, Java, Javascript, and Python may have difficulty getting
their heads around.  But once you do "get it", it starts to seem very
natural.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Starting with TCL

[Peter Da Silva]

On 11/16/17, 11:37 AM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:
> When I use:
> db eval {SELECT * FROM teaInStock} {
>puts $Tea, $Location
> }

puts takes a single string, so you can do {puts “$Tea\t$Location”. Arguments 
are separated by space, comma has no intrinsic meaning, and puts takes two 
arguments: the file handle to write on and the string to print. So it’s 
interpreting “$Tea,” as the name of a file handle.

You probably want something like:

db eval {SELECT * FROM teaInStock} {
puts [format “%12s %12s %s” $Tea ${Last Used} $Location]
}
 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users