TCL and Databases
|
To
use
TCL to connect to databases (Oracle, MYSQL, ACCESS etc) you
require an API/library that enables the connection. Therefore,
a connection to MYSQL database using TCL script requires a
library which enables TCL to connect to MYSQL database. This
is similar to ODBC library that enable database connectivity
to Java or C. MYSQLTCL is a library (simple API) that enables
database connectivity using TCL script to mysql databases. In
this 'howto' tutorial, I will explain the necessary steps to
get MYSQLTCL working on a Fedora Core 3 personal computer
version with TCL installed.
|
|
- Download
To download mysqltcl library,
please refer to your operating system to determine which
code to download. Widely available mysqltcl API are Unix,
Linux, windows or MAC distributions. Please, also verify
which mysqltcl version you wish to run on your system. Note:
This 'howto' is specific for installing mysqltcl2.04 on
Linux 2.6.9-1.667smp #1, for a personal computer use. To
have mysqltcl working fine, you need to have installed
MYSQL
and TCL on your
system. Although, this tutorial is for mysqltcl2.04, all the
steps are same for installing other versions of mysqltcl
including the most recent version -
mysqltcl3.0.
|
|
- Installation
1) Install mysql devel library
from the fedora CD #3. On the mysql directory, you must have
a mysql.h file.
2) Check the path to
your systems tcl.h file (check for the library and
include directories)
3) To unzip and untar the
mysqltcl downloaded, type
4) tar -zxvf
./mysqltcl-xxxxxx.tar.gz
5) cd
./mysqltcl-xxxxx
6) ./configure
--with-tcl=/usr/local/activetcl/lib/tcl8.4
--with-tclinclude=/usr/local/activetcl/include/tcl8.4
--with-mysql-include=/usr/include/mysql
--with-mysql-lib=/usr/lib/mysql
7) make
8)
make install
9) Please check for the file
libmysqltclx.xx.so in the directory where you
installed the library.
Note: You need to load the
libmysqltclx.xx.so file before you can use the
mysqltcl library (for example: to use your mysqltcl API do
the following:
a) type tclsh, then,
b) type load
./libmysqltclx.xx.so, (where x.xx is the version
corresponding to the version you have installed)
c) type
package require mysqlltcl
d) type package require
Tclx
I recommend you copy the libmysqltclx.xx.so file to the directory where you
have TCL installed (for example /usr/local/activetcl or
/usr/bin/tcl8.4 etc). Please check you default TCL directory
with this command: Type: which tcl (enter). But for my case, I installed
activetcl, so my directory was
/usr/local/activetcl/bin
. |
|
- Commands
#1: which tcl (to display the path
to your tcl)
#2: tclsh
#3: load
libmysqltclx.xx.so
#4: package require Tclx
#5:
package require mysqltcl
Examples
#!/usr/local/activetcl/bin/tclsh8.4
package
require Tclx
package require mysqltcl
global
mysqlstatus
set port {3306}
set host
{research-series.ipowermysql.com}
puts -nonewline "username:"; gets stdin
username
if {$username == "exit"} {
puts "This
program will be exiting now!!!"
exit
} else {
set
user $username
}
puts -nonewline "password:"; gets
stdin pass
puts "database {snort, ossim
ossim_acl}:"
gets stdin database
#handler for
database connection
set handler [mysqlconnect -host $host
-port $port -user $username -password $pass -db
$database]
#host = host to connect (localhopst or
ipaddress)
#port = port to connect to mysql (unique port
is 3306, except where different)
#user = username to
login to mysql database
#pass = corresponding password to
username
#db = database in mysql, example, test, snort
etc
#this statement catches mysql connection error,
such as invalid username/password pair, database not
specified etc.
if [catch {mysqlconnect -host $host
-port $port -user $username -password $pass -db $database}
handler] {
puts stderr $mysqlststus(message)
exit
}
else {
set sig_list [mysqlsel $handler {select sig_id,
sig_name from signature} -list]
foreach {id type}
$sig_list {
puts "$id $type"
}
mysqlclose
$handler
}
mysqlconnect (makes a database connection
to mysql). the parameters are -port, -host, -user,
-password, -db:
port: is the default mysql port
3306 (unless otherwise its changed during the
setup)
host: is the system running the
database, use research-series.ipowermysql.com or the ip address of the
system
user: the username to use for authentication to
the database
password: the password pair to the
username
db: the database created in mysql
mysqlsel: is a mysql select command for the API.
Usage:
1: mysqlsel $handler {select * from column}
or
2: mysqlsel $handler {select * from column}
-list/flatlist. Where -list creates a list of each item from
the handler, while -flatlist creates just a list of the
result from the handler. I strongly recommend using
-flastlist unless you wish to have each element of the list
as list items.
Note: Most normal sql select or query
statement are permissible with mysqlsel. For example:
mysqlsel $handler {select sid, signature, name, sensor from
events where timestamp="2006-14-05
00:00:00"};
OR
mysqlsel
$handler "select sid, signature, name from events where
signature = 10"; |
|
|
|
|
|