Tcl - Re: using TCL with sqlite database...

Author

Re: using TCL with sqlite database...

janvanbelle_blabla@yahoo_dot.com

2006-11-29, 7:08 pm

I am now able to read from and write to my database.

I am still struck with something: I'd want to get my results
from a query as a list...

eg: CREATE TABLE t_test (
dummy1 varchar(35),
dummy2 varchar(35) )
... insert some data ...
SELECT * FROM t_test

gives me a list with ALL values in the table, but not something like
{{{dummy1_1}{dummy2_1}}{{dummy1_2}{dummy
2_2}}}
-> a list of all rows in the table, presented as a list itself (referring to
the berkeley db TCL interface)

I find lots of references of making an array out of it, but my application
is built around using lists with data... :-( (and to be honest, I never
tried working with arrays, because lists seems to be much more supported.)

Regards,

Jan

Jan Van Belle wrote:

> Hello all,
>
> I'm trying to access an SQLite database via TCL.
> Creating/opening/create tables etc. succeeds when doing it in 1 procedure,
> but fails when I open the db in proc open_sqlite_db {} and try to write
> to it in proc write_sql_db {}
>
> example code:
>
> proc general_calling_procedure {} {
> # sqlite library is already loaded...
>
> open_sqlite_db
> write_sqlite_db { "t_test" "some data" }
> ...
> }
>
> proc open_sqlite_db {} {
> sqlite testdb "./my_example.db"
> testdb eval { create table t_test ( dummy varchar(30) ) }
> }
>
> proc write_sqlite_db { table data } {
> testdb eval { insert into $table values($data) }
> }
>
> ####
>
> If I read back the data which should have been written, there is
> just nothing... Quite frustrating...
> Most probably it has nothing to do with sqlite itself, just the way to
> access the command 'testdb'.
> That's the reason why I first try it here...
>
> Kind regards,
>
> Jan

Christian Nassau

2006-11-29, 7:08 pm

janvanbelle_blabla@yahoo_dot.com wrote:
> I am still struck with something: I'd want to get my results
> from a query as a list...
>
> eg: CREATE TABLE t_test (
> dummy1 varchar(35),
> dummy2 varchar(35) )
> ... insert some data ...
> SELECT * FROM t_test
>
> gives me a list with ALL values in the table, but not something like
> {{{dummy1_1}{dummy2_1}}{{dummy1_2}{dummy
2_2}}}
> -> a list of all rows in the table, presented as a list itself (referring to
> the berkeley db TCL interface)

One solution is to register a function with the database which assembles
its arguments into a list:

% sqlite3 db :memory:
% db function mklist ::list
% db eval {create table mytab(a int, b int);}
% for {set i 0} {$i<10} {incr i 2} {db eval {insert into mytab
values($i,$i+1)}}

This then seems to give you what you're looking for:

% db eval {select mklist(a,b) from mytab}
{0 1} {2 3} {4 5} {6 7} {8 9}

Hope this helps,
C.

>
> I find lots of references of making an array out of it, but my application
> is built around using lists with data... :-( (and to be honest, I never
> tried working with arrays, because lists seems to be much more supported.)
>
> Regards,
>
> Jan
>
>
>
> Jan Van Belle wrote:
>
>

--
=> Christian Nassau, http://www.nullhomotopie.de

Neil Madden

2006-11-29, 7:08 pm

janvanbelle_blabla@yahoo_dot.com wrote:
> I am now able to read from and write to my database.
>
> I am still struck with something: I'd want to get my results
> from a query as a list...
>
> eg: CREATE TABLE t_test (
> dummy1 varchar(35),
> dummy2 varchar(35) )
> ... insert some data ...
> SELECT * FROM t_test
>
>
> gives me a list with ALL values in the table, but not something like
> {{{dummy1_1}{dummy2_1}}{{dummy1_2}{dummy
2_2}}}
> -> a list of all rows in the table, presented as a list itself (referring to
> the berkeley db TCL interface)
>
> I find lots of references of making an array out of it, but my application
> is built around using lists with data... :-( (and to be honest, I never
> tried working with arrays, because lists seems to be much more supported.)

If you really want to get the result of the query as a list of lists,
then the following should do that (from http://wiki.tcl.tk/14972):

set result [list]
$db eval {SELECT * FROM t_test} row {
set tmp [list]
foreach col $row(*) { lappend tmp $row($col) }
lappend result $tmp
}

However, if you are then just going to iterate through the list of
results using [foreach] then it is much better to use sqlite's built-in
looping capabilities:

$db eval {SELECT * FROM t_test} row {
puts "dummy1 = $row(dummy1)"
puts "dummy2 = $row(dummy2)"
}

-- Neil

Jan Van Belle

2006-11-30, 7:04 pm

Christian Nassau schreef:

>
> One solution is to register a function with the database which assembles
> its arguments into a list:
>
> % sqlite3 db :memory:
> % db function mklist ::list
> % db eval {create table mytab(a int, b int);}
> % for {set i 0} {$i<10} {incr i 2} {db eval {insert into mytab
> values($i,$i+1)}}
>
> This then seems to give you what you're looking for:
>
> % db eval {select mklist(a,b) from mytab}
> {0 1} {2 3} {4 5} {6 7} {8 9}
>
> Hope this helps,
> C.

Thanks!!

That's exactly what I need !

Jan

Sponsored Links