| View previous topic :: View next topic |
| Author |
Message |
Madalin
Master
Joined: 24 Jun 2005
Posts: 310
Location: Constanta, Romania
|
 Posted: Mon Jan 30, 2017 2:58 am Post subject: How to list & close active mysqltcl sessions |
 |
|
I am reading a mysql database in the following way
| Code: |
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.
_________________
https://github.com/MadaliNTCL - To chat with me: https://tawk.to/MadaliNTCL |
|
| Back to top |
|
 |
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Mon Jan 30, 2017 12:23 pm Post subject: |
|
|
Why don't you close the session when you no longer need it?
Something like:
| Code: |
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.
_________________
Once the game is over, the king and the pawn go back in the same box.
Last edited by caesar on Mon Jan 30, 2017 12:35 pm; edited 1 time in total |
|
| Back to top |
|
|
Madalin
Master
Joined: 24 Jun 2005
Posts: 310
Location: Constanta, Romania
|
| Posted: Mon Jan 30, 2017 12:35 pm Post subject: |
|
|
I rewrote the script ... and used closed at the end .. here is an example ..
| Code: |
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?
_________________
https://github.com/MadaliNTCL - To chat with me: https://tawk.to/MadaliNTCL |
|
| Back to top |
|
|
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Mon Jan 30, 2017 12:51 pm Post subject: |
|
|
Let's put the above code in theory and see what really happens inside.
| Code: |
% 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!
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: |
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: |
% 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.
| Quote: | | 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: |
if {![string is integer $number]} return
|
or something.
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
Madalin
Master
Joined: 24 Jun 2005
Posts: 310
Location: Constanta, Romania
|
| Posted: Mon Jan 30, 2017 2:39 pm Post subject: |
|
|
Thanks sticking out the return in the foreach loop. That was the problem solved it. Thanks again.
_________________
https://github.com/MadaliNTCL - To chat with me: https://tawk.to/MadaliNTCL |
|
| Back to top |
|
|
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Mon Jan 30, 2017 3:49 pm Post subject: |
|
|
That's great, but what about the select?
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
Madalin
Master
Joined: 24 Jun 2005
Posts: 310
Location: Constanta, Romania
|
| Posted: Mon Jan 30, 2017 3:59 pm Post subject: |
|
|
That i need.
Another question do you have any code to modify "ă" into 'a'
_________________
https://github.com/MadaliNTCL - To chat with me: https://tawk.to/MadaliNTCL |
|
| Back to top |
|
|
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Tue Jan 31, 2017 1:49 am Post subject: |
|
|
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: |
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: |
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.
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
Madalin
Master
Joined: 24 Jun 2005
Posts: 310
Location: Constanta, Romania
|
| Posted: Tue Jan 31, 2017 3:50 am Post subject: |
|
|
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
_________________
https://github.com/MadaliNTCL - To chat with me: https://tawk.to/MadaliNTCL |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
