tclsh, aside from the inconvenience of prefixing every sql statement with
"db eval {", looks like a great way to gain the equivalent functionality of
scalar output stored procedures compared to the plain vanilla sqlite shell.Is there anything in the works for the tcl bindings to define/export eponymous table definitions? Or alternatively, notwithstanding rough at the edges problems in the type system like typeof(1/0)->null, is there anything in the works to return row valued scalars from tcl defined extensions? Thanks in advance for your insights. On Thu, Mar 9, 2017 at 6:03 AM, Richard Hipp <[email protected]> wrote: > On 3/8/17, Vermes Mátyás <[email protected]> wrote: > > I rewrote the program for you to C. > > Thank you for the translation. > > Below is the equivalent program in 34 lines of TCL. (Compare to 101 > lines of Ruby and 430 lines of C++. Everybody: If you are unfamiliar > with the TCL programming language, you would do well to learn it, and > start using it. It will save you a lot of work.) > > -------------------------------------- cut here > ---------------------------- > package require sqlite3 > sqlite3 db :memory: > db eval { > CREATE TABLE proba( > szamla VARCHAR(24) COLLATE rtrim NOT NULL, > devnem CHAR(3) COLLATE rtrim, > megnevezes VARCHAR(30) COLLATE rtrim, > osszeg NUMERIC(17,2), > tulmenflag BOOLEAN, > konyvkelt DATE, > megjegyzes BLOB > ); > CREATE UNIQUE INDEX proba_primarykey ON proba(szamla, devnem); > CREATE INDEX proba_nev ON proba(megnevezes, szamla); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('000000000000000000000000','HUF','BLACK'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('111111111111111111111111','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('222222222222222222222222','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('333333333333333333333333','HUF','BLACK'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('444444444444444444444444','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('555555555555555555555555','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('666666666666666666666666','HUF','BLACK'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('777777777777777777777777','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('888888888888888888888888','HUF','WHITE'); > INSERT INTO proba(szamla,devnem,megnevezes) > VALUES('999999999999999999999999','HUF','BLACK'); > } > db eval BEGIN > set cnt 0 > db eval {SELECT szamla, megnevezes FROM proba} { > incr cnt > puts "$cnt: $szamla $megnevezes" > db eval {UPDATE proba SET megnevezes=megnevezes||'*' WHERE > szamla=$szamla} > } > db eval COMMIT > -------------------------------------- End ------------------------------ > ------ > > As Dan already observed, the problem results because you are modifying > an index in the middle of a scan of that index, thereby messing up the > scan. Don't do that. The safest approach is to run the query to > completion, then go back and start the loop over UPDATEs. > > If you add "ORDER BY +rowid" to the query, that forces the query to > run to completion first and then go through the sorter, before you get > any results back, which solves the problem. > > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
