how can select with multiple output from sqlite

Question

I want to do an sql query and only get a response, although it should be five.

only the frist is put to channel

bind pub "-|-" !sqlpre pub:test:sqlpre
proc pub:test:sqlpre { nick host handle channel arg } {

set searchname [lindex [split $arg] 0]

sqlite3 data /home/local/test.db
set result [data eval {SELECT * FROM folders WHERE name LIKE $searchname}]


if {[llength $result] == 0} {
    putnow "PRIVMSG $channel :Nothing found";
    return 0;
} else {
    set id [lindex $result 0];
    set source [lindex $result 1];
    set path [lindex $result 2];
    set name [lindex $result 3];

}
putnow "PRIVMSG $channel :$id $source $path $name"
}

the issue is now here:

1 aa /tmp searchtest1

but it should be like this here:

1 aa /tmp searchtest1
4 ab /tmp searchtest1
17 ac /tmp searchtest1
18 ad /tmp searchtest1
9 ae /tmp searchtest1

Assuming your table has 4 columns... well yeah. You only print out the first row that gets returned and don't do anything with the rest of the list of values. — Shawn, Apr 19 '20 at 13:30
The same way you would any list in tcl - `foreach` or whatever. Or use one of the other forms of the sqlite3 package's eval command. See the docs. — Shawn, Apr 19 '20 at 13:54

score 1 · Answer 1 · answered Apr 19 '20 at 17:18

You need the iterative form of the database connection's eval method. You probably should also explicitly name your columns rather than using * as SQL in general does not guarantee anything about the order that they're in by default. (The primary key probably comes first.)

package require sqlite3
# Put this outside the procedure; it can be shared
sqlite3 data /home/local/test.db

bind pub "-|-" !sqlpre pub:test:sqlpre
proc pub:test:sqlpre { nick host handle channel arg } {
    set searchname [lindex [split $arg] 0]
    set found no

    # For each matching row...
    data eval {
        -- You can use “sqlExpr AS theName” to change the names of things
        SELECT id, source, path, name 
        FROM folders 
        WHERE name LIKE $searchname
    } row {
        # The 'row' above names a Tcl array that holds the result row data.
        # This means we can use the values out of it directly.
        putnow "PRIVMSG $channel :$row(id) $row(source) $row(path) $row(name)"

        # Remember that we have found something
        set found yes
    }

    # If we didn't find anything, tell the user
    if {!$found} {
        putnow "PRIVMSG $channel :Nothing found"
    }
}

Note that we're sharing the database connection across many user actions. This is good practice as it means we're sharing resources. And the SQL query statement is now over multiple lines for readability. Readability's good.

how can select with multiple output from sqlite

1 Answers1