mysqltcl help proc and bind with 2 variables

[nightshade2109]

Hi guys perhaps some could help I cant seem to figure this out.

Basically what I'm trying to do is look up 2 id's in a database on different rows with a public command: .get username1 username2 with the column "name" then have the 2 id's displayed in the channel.

MySQL tables looks like this

tablename
id name
1 username1
2 username2


This is what i have so far although its not working at all.

if someone could help it would be appreciated.

Code: Select all

package require mysqltcl

proc command_connect { } {
	global db_handle
		
	set db_handle [mysqlconnect -host localhost -user root -password mypass -db mydb]

	if {$db_handle != ""} {
		return 1
	} else {
		return 0
	}
}

bind pub "-|-" .get fetch

proc ping { } {
	global db_handle

	if [::mysql::ping $db_handle] {
		return 1
	} else {
		return [command_connect]
	}
}


proc fetch { nick host handle text1 text2} {
  global db_handle chan
  set chan "#channel"
  set sql "SELECT * FROM table WHERE name='$text1' AND (name='$text2')"
  set result [mysqlquery $db_handle $sql]
  
  if {[set row [mysqlnext $result]] != ""} {
        set text1out [lindex $row 0]
	set text2out [lindex $row 0]
      
	  putquick "PRIVMSG $chan :$text1out $text2out"
     
 }
     
  mysqlendquery $result
}


command_connect

[nml375]

Step 1: Patience... aka bumping within 12h will certainly end up in the Junk Yard... Period.

Step 2: read the docs (doc/tcl-command.doc) and look up the pub binding:

(4) PUB
bind pub <flags> <command> <proc>
procname <nick> <user@host> <handle> <channel> <text>

Description: used for commands given on a channel. The first word
becomes the command and everything else is the text argument.
Module: irc

This tells us that when the binding is triggered, your command is called with 5 arguments, being the nickname of the caller, the user@host identifier of the caller, the handle (or * if not recognized) of the caller, channel the text was posted in, and the text that followed the keyword.
Thus, your proc should start something like this:

Code: Select all

proc fetch {nick host handle channel text} {
...

Next, we need to extract the first two words of "text". There are plenty of different approaches, though using lists is what most people prefer:

Code: Select all

proc fetch {nick host handle channel text} {
  set tmp [split $text] #convert text into a list, and store in tmp
  set text1 [lindex $tmp 0] #extract the first item from the list, and store in text1
  set text2 [lindex $tmp 1] #extract the second item...

Further, since this is coming from irc, we really should make sure both text1 and text2 are safe - to prevent SQL injection exploits:

Code: Select all

proc fetch {nick host handle channel text} {
  set tmp [split $text] #convert text into a list, and store in tmp
  set text1 [::mysql::escape [lindex $tmp 0]]
  set text2 [::mysql::escape [lindex $tmp 1]]

Next, implement the database code.. Though your SQL-query really makes no sense at all... If you want two rows, either use OR (not AND), or consider using the FIELD() function

Code: Select all

proc fetch {nick host handle channel text} {
  global db_handle
  set tmp [split $text] #convert text into a list, and store in tmp
  set text1 [::mysql::escape [lindex $tmp 0]]
  set text2 [::mysql::escape [lindex $tmp 1]]
  set sql "SELECT * FROM table WHERE `name`='$text1' OR `name`='$text2'"
  set data [::mysql::sel $db_handle $sql -list]
  set text1out [lindex $data 0 0]
  set text2out [lindex $data 1 0]
  puthelp "PRIVMSG $chan :$text1out $text2out"
}

Using the mysql namespace is the preferred way since mysqltcl v3, but if you are using an older version of mysqltcl that does not support the mysql namespace, simply remove the :: from the commands (ie ::mysql::sel becomes mysqlsel)

[nightshade2109]

Thanks for the reply, it's working

This is my first attempt at writing a TCL script thanks for the pointers.