mysql tcl and variables woes

[inaba]

I'm trying to get a variable by using sql, and then go on to use said variable in the next sql query, but things dont go as planned

this is my current proc:

Code: Select all

proc to_shoutbox {nick uhost hand chan text} {
global db botid botnm idr
set message [lrange $text 0 end]
if { $message == "" } {
putquick "NOTICE $nick : !shout <message>"
} else {
set added [clock seconds]
set id "select id from users where username = '$nick'";
set db_hand [mysqlconnect -host $db(host) -user $db(user) -password $db(pass) -db $db(name)]
set idr [mysqlexec $dbhand $id]
if { $idr == 0 } {
putquick "NOTICE $nick : UID grab failed!"
} else {
putquick "NOTICE $nick : UID grab possible success!"
}
mysqlclose $dbhand
set query "INSERT INTO chat(id, uid, time, name,text) VALUES('','$idr','$added','$botnm - $nick','$message')";
set db_hand [mysqlconnect -host $db(host) -user $db(user) -password $db(pass) -db $db(name)]
set result [mysqlexec $db_hand $query]
if { $result == 1 } {
putquick "NOTICE $nick : Shout sent!"
} else {
putquick "NOTICE $nick : There was an error!"
}
mysqlclose $db_hand
}
}

I cant for the life of me see whats going wrong, the sql works fine for SSH, and returns the expected value, but in the tcl, it doesnt cause an error, but it always returns with 0, so when I call on $idr in the second query it will always input 0, regardless of what it should be.

Any help would be appreciated :3

[nml375]

Please read the documentation for the mysqlexec command:

::mysql::exec handle sql-statement
Send sql-statement, a MySQL non-SELECT statement, to the server. The handle must be in use (through ::mysql::connect and ::mysql::use).

::mysql::exec implicitly cancels any previous result pending for the handle.

If sql-statement is a valid MySQL SELECT statement, the statement is executed, but the result is discarded. No Tcl error is generated. This amounts to a (potentially costly) no-op. Use the ::mysql::sel command for SELECT statements.

::mysql::exec returns the number of affected rows (DELETE, UPDATE). In case of multiple statement ::mysql::exec returns a list of number of affected rows.

As suggested, use the mysqlsel command instead, or the mysqlquery command along with mysqlresult.

[inaba]

okays, change the select query and still getting the same behaviour:

Code: Select all

set r [mysqlconnect -host $db(host) -user $db(user) -db $db(name) -password $db(pass)]
 mysqluse $r $db(name)
 foreach idr [mysqlsel $r {select id from users WHERE username = '$nick'} -flatlist] {
     puts $idr
 }
 mysqlclose $r

Thanks for the help, and as is evident, I'm pretty new to tcl >.<

[nml375]

One more thing, you can't use variable- or command-substitutions within {}. You'll have to use "" instead for this..

[inaba]

Got it working as expected thank you very much ^-^

Code: Select all

set r [mysqlconnect -host $db(host) -user $db(user) -db $db(name) -password $db(pass)] 
 mysqluse $r $db(name) 
 foreach idr [mysqlsel $r "select id from users WHERE username = '$nick'" -list] { 
     puts $idr 
 } 
 mysqlclose $r