How to list & close active mysqltcl sessions

[Madalin]

I am reading a mysql database in the following way

Code: Select all

	set num [catch {::mysql::connect -host $my(host) -user $my(user) -password $my(pass) -db $my(db) -port $my(port)} mysocket]

	if {$num eq "0" && [string match mysql* $mysocket]} {
		set num [catch {mysqlsel $mysocket "SELECT `pk_i_id`,`fk_i_category_id`,`dt_pub_date` FROM `an_t_item` ORDER BY `dt_pub_date` DESC LIMIT 0, 1" -list} select];
		if {$num eq "0" && $select ne ""} {

			foreach item $select {

..and from time to time the database is not accesible until i restart it and i wanted to know how manny active session i have with that specific database.

[caesar]

Why don't you close the session when you no longer need it?
Something like:

Code: Select all

set dbh [::mysql::connect -host $my(host) -user $my(user) -password $my(pass) -db $my(db) -port $my(port)]
foreach res [::mysql::sel $dbh {"SELECT pk_i_id, fk_i_category_id, dt_pub_date FROM an_t_item ORDER BY dt_pub_date DESC LIMIT 0, 1"} -list] {
	# do whatever with the data
}
::mysql::close $dbh

Untested.

[Madalin]

I rewrote the script ... and used closed at the end .. here is an example ..

Code: Select all

proc sql:category {number} {
	global my

	set handle [::mysql::connect -host $my(host) -user $my(user) -password $my(pass) -db $my(db) -port $my(port)]

	set go [::mysql::sel $handle "SELECT `fk_i_category_id`,`s_name`,`s_description`,`s_slug` FROM `an_t_category_description` ORDER BY `fk_i_category_id`" -list]

	foreach item $go {
		foreach {fk_i_category_id s_name s_description s_slug} $item {
			if {$number eq $fk_i_category_id} {
				return "$s_slug"
			}
		}
	}
	::mysql::close $handle
}

The problem is that i checked the mysql phpmyadmin and i see that the connection stays open... and multiplies each time i read data. So the connection is not closing from the eggdrop

There is no way to read what mysql connections i have opened in eggdrop?

[caesar]

Let's put the above code in theory and see what really happens inside.

Code: Select all

% set data [list "1" "3" "5" "2" "9" "7"]
1 3 5 2 9 7
% proc foo {a} {
        foreach i $a {
                if {$i eq 2} {
                        return $i
                }
        }
        puts "done?"
}

Considering the above code, what happens when I execute "foo $data" ? Will i get to see the "done" message? Answer is NO!

Code: Select all

% foo $data
2

Why don't we see it? Umm.. Because of the return statement maybe?

Let's see. If we change the above code a bit to add something so we know we had a match and break the loop when we got this match (if this was what we where looking for in the first place).

Code: Select all

proc foo {a} {
	set match 0
	foreach i $a {
		if {$i eq 2} {
			incr match
			break
		}
	}
	puts "done?"
	return $match
}

then the result is:

Code: Select all

% foo $data
done?
1

OMG! It works!

Bottom line is: never, ever use a return inside a loop. Break it if your proc matched something or use continue if it didn't.

Long story told short, you get this issue because of the return that stops the process before reaching the part where it closes the session to the mysql database.

PS: You should drop the -list at the end if you just want to see the number of rows returned as the result of the query.

If sql-statement is a SELECT statement and no -list or -flatlist option is specified, the command returns the number of rows returned as the result of the query. The rows can be obtained by the ::mysql::fetch and/or the ::mysql::map commands.

Taken from ::mysql:sel's documentation

As it stands right now your select statement looks rather dumb. What exactly do you want to achive there? Want to see if there's something in the database with a fk_i_category_id that is matching the given number? Do you sanitize that number btw?

I would consider doing at least a:

Code: Select all

if {![string is integer $number]} return

or something.

[Madalin]

Thanks sticking out the return in the foreach loop. That was the problem solved it. Thanks again.

[caesar]

That's great, but what about the select?

[Madalin]

That i need.

Another question do you have any code to modify "ă" into 'a'

[caesar]

If you want to get only the s_slug of a record that has fk_i_category_id equal with the given number and limit yourself to a single result then why not change the select and drop the unnecessarily loops? Something like:

Code: Select all

set data [::mysql::sel $handle "SELECT s_slug FROM an_t_category_description WHERE fk_i_category_id = $number LIMIT 0, 1" -list]

and then $data should be a list containing the s_slug you are looking for. Give this a try and let me know the result.

If you want to see all matches then remove the limit and use a single loop like i mentioned in first post to go over all results.

Sure:

Code: Select all

set text [string map -nocase [list "ă" "a" "ş" "s" ... ] $text]

you get the idea. Not 100% sure if the -nocase will also work with the upper-case so you should check this out and if doesn't work add them as well.

[Madalin]

With string match i tryed yet when the result comes to mIRC it has '?' instead of the letter maybe thats why is not being modifyed.

I will try with WHERE in the next version, my problem now is that some use "ă" "a" "ş" "s" in TITLE and the link is not accesible