• Help!!! when select from a sqlite3 database, if the key have space in i

    From luocl@21:1/5 to All on Sat Nov 6 11:14:03 2021
    in the following script, I can store a title with a space in it, but I
    cann't select content from title which have a space in it.



    package require sqlite3
    catch [exec rm --force note.db]
    namespace eval db::note {
    sqlite3 db [file join [file dirname [file dirname [file join [info
    script] ...]]] note.db]
    variable colnames
    lappend colnames {title text} {parent text} {content text}
    variable tblname note
    proc gettbl {} {
    variable tblname
    return $tblname
    }
    proc getcolname col {
    variable colnames
    return [lindex $colnames $col 0]
    }
    proc create {} {
    variable colnames
    db eval "CREATE TABLE [gettbl]([join $colnames ,])"
    }
    proc query {} {
    db eval [list SELECT * FROM [gettbl]] {
    puts "title=$title parent=$parent content:$content"
    }
    }
    proc insert { title parent content} {
    db eval [list INSERT INTO [gettbl] VALUES(:title,:parent,:content)]
    }
    proc content {title} {
    db eval [list SELECT content FROM [gettbl] where title=':title'] {
    return $content
    }
    }
    }

    db::note::create
    db::note::insert {de bug} {} {please help me!!}
    db::note::query
    puts cotent:[db::note::content {de bug}]
    db close

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Rich@21:1/5 to luocl on Sat Nov 6 04:26:13 2021
    luocl <vimperatorluo@qq.com> wrote:
    in the following script, I can store a title with a space in it, but
    I cann't select content from title which have a space in it.

    What do you get back from your script? Because it works just fine
    here.

    package require sqlite3
    catch [exec rm --force note.db]

    It is unnecessary to exec an 'rm' when Tcl has "file delete" as a
    builtin command.

    variable tblname note
    proc gettbl {} {
    variable tblname
    return $tblname
    }

    Why a proc to return a table name from a variable? Why not just use
    the variable in the other proc's?

    A slightly simplified version of your script that I am using:

    Changes are:
    1) uses file delete instead of exec rm
    2) inserts delimiters <> around the variables in the puts in the query
    proc.

    $ cat luocl
    package require sqlite3
    file delete -force note.db
    namespace eval db::note {
    sqlite3 db note.db
    variable colnames
    lappend colnames {title text} {parent text} {content text}
    variable tblname note
    proc gettbl {} {
    variable tblname
    return $tblname
    }
    proc getcolname col {
    variable colnames
    return [lindex $colnames $col 0]
    }
    proc create {} {
    variable colnames
    db eval "CREATE TABLE [gettbl]([join $colnames ,])"
    }
    proc query {} {
    db eval [list SELECT * FROM [gettbl]] {
    puts "title=<$title> parent=<$parent> content?<$content>"
    }
    }
    proc insert { title parent content} {
    db eval [list INSERT INTO [gettbl] VALUES(:title,:parent,:content)]
    }
    proc content {title} {
    db eval [list SELECT content FROM [gettbl] where title=':title'] {
    return $content
    }
    }
    }

    So, now I run the script:

    $ rlwrap tclsh
    % source luocl
    % db::note::create
    % db::note::insert {de bug} {} {please help me!!}
    % db::note::query
    title=<de bug> parent=<> content?<please help me!!>
    % exit

    And the title returns perfectly, embedded spaces and all.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Christian Gollwitzer@21:1/5 to All on Sat Nov 6 07:59:02 2021
    Am 06.11.21 um 04:14 schrieb luocl:
      proc content {title} {
        db eval [list SELECT content FROM [gettbl] where title=':title'] {

    I haven't tried it, but why are there quotes '' around the title? The
    parameter insertion mechanism behind :title should take care of
    everything so that title can be arbitrary including quotes, dollar signs
    etc. In fact it doesn't do "quoting" but byte compiles the query and
    links to the variable.


    Christian

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From luocl@21:1/5 to Christian Gollwitzer on Sat Nov 6 16:03:18 2021
    On 11/6/21 2:59 PM, Christian Gollwitzer wrote:
    Am 06.11.21 um 04:14 schrieb luocl:
       proc content {title} {
         db eval [list SELECT content FROM [gettbl] where title=':title'] {

    I haven't tried it, but why are there quotes '' around the title? The parameter insertion mechanism behind :title should take care of
    everything so that title can be arbitrary including quotes, dollar signs
    etc. In fact it doesn't do "quoting" but byte compiles the query and
    links to the variable.


        Christian

    yes, delete the quotes '', the content proc is corrected.

    Thank you

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From luocl@21:1/5 to All on Sat Nov 6 16:01:33 2021
    Thanks Rich.
    Thanks Christian Gollwitzer.

    This is bug in my little program https://chiselapp.com/user/LuoChunlei/repository/scrapnote

    I found the bug, it took me a long time. it's not in the database side,
    but in the ttk::treeview side.

    ttk::treeview .tv
    set item [.tv selection]

    the item is a list, when item has no space, direct use item as a string
    just ok; but when item has a space I need to use [lindex $item 0].

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ralf Fassel@21:1/5 to All on Sat Nov 6 13:50:44 2021
    * luocl <vimperatorluo@qq.com>
    | ttk::treeview .tv
    | set item [.tv selection]

    | the item is a list, when item has no space, direct use item as a
    | string just ok; but when item has a space I need to use [lindex $item 0].

    More specifically: whenever a TCL special char is in the item, you get
    TCL quoting in the string representation of the list to preserve the
    list structure.

    It is *never* ok to use a list directly when a string representation of
    the first (and probably only) list element is required.
    Sometimes you just get away with it.

    R'

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Rich@21:1/5 to luocl on Sat Nov 6 14:49:44 2021
    luocl <vimperatorluo@qq.com> wrote:
    Thanks Rich.
    Thanks Christian Gollwitzer.

    This is bug in my little program https://chiselapp.com/user/LuoChunlei/repository/scrapnote

    I found the bug, it took me a long time. it's not in the database side,
    but in the ttk::treeview side.

    ttk::treeview .tv
    set item [.tv selection]

    the item is a list, when item has no space, direct use item as a string
    just ok; but when item has a space I need to use [lindex $item 0].

    No, the return from treeview selection *is* a list, of zero or more
    elements, it is *never* ok to directly use it as a string. You should
    *always* use lindex (or other list operators) to access the elements of
    "item", even if there is only one element.

    Many times you can "get away with" ignoring the above and using it
    directly as a string. But all that does is leave a trap waiting to
    spring on you when the data in the list changes.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)