-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello, everybody:
First let me say that I'm no expert neither with TCL nor with
SQLite.
I'm trying to convert the underlying database of a TCL script to
SQLite.
I'm using 3.2.3 (also tried 3.2.8 with the same result)
I don't think that all the following information will be needed, but
just in case I'm wrong here goes:
The database is set as follows:
proc create_db { cargs } {
if { $cargs == "redo" } {
db eval { DROP INDEX `byname` }
db eval { DROP TABLE `auction_items` }
db eval { DROP TABLE `auction_bids` }
db eval { VACUUM }
}
db eval { CREATE TABLE `auction_items` (`id` INTEGER PRIMARY KEY
AUTOINCREMENT, `seller` TEXT NOT NULL DEFAULT '', `createtime` TEXT
NOT
NULL DEFAULT CURRENT_TIMESTAMP, `endtime` TEXT NOT NULL DEFAULT
'0000-00-00 00:00:00', `item_id` INTEGER NOT NULL DEFAULT 0,
`item_name`
TEXT NOT NULL DEFAULT '', `item_icon` TEXT NOT NULL DEFAULT '',
`item_count` INTEGER NOT NULL DEFAULT 0, `item_category` INTEGER NOT
NULL DEFAULT 0, `item_quality` INTEGER NOT NULL DEFAULT 0,
`duration`
INTEGER NOT NULL DEFAULT 0, `start_price` INTEGER NOT NULL DEFAULT
0,
`buy_price` INTEGER NOT NULL DEFAULT 0, `item_level` INTEGER NOT
NULL
DEFAULT 0) }
db eval { CREATE INDEX `byname` ON `auction_items` (`seller`) }
db eval { CREATE TABLE `auction_bids` (`id` INTEGER PRIMARY KEY
AUTOINCREMENT, `bidder` TEXT NOT NULL DEFAULT '', `auc_id` INTEGER
NOT
NULL DEFAULT 0, `money` INTEGER NOT NULL DEFAULT 0) }
return "database setup done."
}
The problem procedure is:
proc searchitems { full } {
set sdata [ split $full "#" ]
set criteria [ lindex $sdata 1 ]
set minlevel [ lindex $sdata 2 ]
set maxlevel [ lindex $sdata 3 ]
set quality [ lindex $sdata 4 ]
set where "WHERE ("
if { $criteria != "NULL" } {
append where "`item_name` LIKE %$criteria%"
}
if { $minlevel != "NULL" } {
if { $where != "WHERE (" } {
append where " AND "
}
append where "`item_level` >= $minlevel"
}
if { $maxlevel != "NULL" } {
if { $where != "WHERE (" } {
append where " AND "
}
append where "`item_level` <= $maxlevel"
}
if { $quality != "NULL\n" && $quality != "-1" } {
if { $where != "WHERE (" } {
append where " AND "
}
append where "`item_quality` = $quality"
}
append where ")"
set pname [ GetName $player ]
set ret "AUCTION_ITEM_LIST:"
if { $where == "WHERE ()" } {
set where ""
}
puts "SELECT * FROM `auction_items` $where ORDER BY `endtime`
DESC
LIMIT 15"
db eval { SELECT * FROM `auction_items` "$where" ORDER BY
`endtime`
DESC LIMIT 15 } {
# `id` `seller` `createtime` `endtime` `item_id` `item_name`
`item_icon` `item_count`
# `item_category` `item_quality` `duration` `start_price`
`buy_price` `item_level`
set ptime [ expr ( ( [ clock scan $endtime ] - [ clock
seconds ] ) / 60 ) ]
set money 0
db eval { SELECT `money` FROM `auction_bids` WHERE (`auc_id`
=
$id) ORDER BY `money` DESC LIMIT 1 } {
puts "money=\"$money\""
}
append ret
"\n#$item_id#$item_name#$item_icon#$item_count#$item_quality#1#$item_level#$start_price#50#$buy_price#$money#$pname#$ptime#$id#$seller"
}
return "$ret\n"
}
The problem:
Regarding the $where variable, which is constructed at the begining
of
the above procedure, the database call eval simply ignores it and
retrieves all the entries from the database.
The puts command just before the call to eval prints out correctly,
it's
the eval command that ignores the WHERE statement. I tried '$where',
"$where" and [ { $where } ] without success, if I try entering
$where
without quoting it I get a call error and the script exits.
Can anyone please put me on the right track to figure this out? I
don't
thing it's TCL related, rather SQLite eval syntax, but I may be
wrong.
Thank you very much in advance.
Best regards,
- --
Lazarus Long
<lazarus (dot) long (at) bigfoot (dot) com>
GNUPG:
<http://wwwkeys.pgp.net/pks/lookup?op=index&search=0x5C1DC205>
Please do not send me attachments in proprietary formats
without request (i.e. Word, PowerPoint or Excel documents),
see <http://www.fsf.org/philosophy/no-word-attachments.html>
Por favor no me envie anexos em formatos proprietrios sem que
os tenha pedido (p.e. documentos em Word, PowerPoint ou Excel),
veja <http://www.fsf.org/philosophy/no-word-attachments.pt.html>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32) - GPGOE 0.4.1
iD8DBQFD19M3CXRGvVwdwgURAs5HAJ4zUv+rY9nOsSA3GGPT3JD3DqOwJACcC5tH
Xxl4G3tOP/rGrn0ERQIIaJM=
=equ1
-----END PGP SIGNATURE-----
