Index of /tcl/ftparchive/sorted/databases/dbitotcl/0.1

 Name                   Last modified     Size  Description

 Parent Directory       06-Dec-00 10:49      -  
 README.html            06-Dec-00 06:24     7k  
 dbitotcl-0.1.tar.gz    06-Dec-00 10:49     7k

dbitotcl - Use Perl DBI from Tcl

dbitotcl is a Tcl extension for using Perl DBI (Database independent interface) from tcl. This extension allow to use nearly all functions of Perl DBI from Tcl as new Tcl commands. The use of Perl is hidden from the Tcl-User. Perl is loaded as embedded interpreter in the dbitotcl library and the library map all commands between Perl and Tcl.

It is in principle better way to use special Tcl-Extension for your Database (mysqltcl,oratcl,pgtcl,adatcl) but by using this library you can access to database, which have no Tcl-Extension and you have more generic API (like ODBC) and some special functions as Meta-Data (Catalog) access. So you can use very good Perl-DBI interface from Tcl.

Requirements

Working tcl and perl interpreter (standard by all Linux Distributions)
Working perl DBI Module. You must install suitable DBI interface for your Database.
to compile: (C-compiler), Tcl-Headers (Suse-user must install tcld.rpm) and Perl developing stuff (standard with all Perl distributions).

Downloads:

dbitotcl-0.1.tar.gz

dbitotcl API

Perl DBI is object oriented designed. Also dbitotcl Extension have this behavior. There are two generic function "invoke" and "attribute" to use Perl-DBI. Beware of use unproper method or attributes the system will be exit. Another functions like "do" or "execute" causes an Tcl Error if the underlying Perl function return a undef value. The result will be set to DBI->errstr.

Pleas Read Perl DBI manuals how to use this objects (all Methods and Attributes). man DBI

Generic Function

dbi::connect dbistring ?arg1? ?arg2?

This function is mapped to DBI->connect($dbistring,$arg1,$arg2);. By success database handle is returned
set dbh [dbi::connect dbi::mysql::table user password]

dbi::invoke method ?arg1? ...

This function is mapped to DBI->method($arg1,...);. The result will be mapped to tcl structures (lists).
print [dbi::invoke available_drivers]

dbi::setnull nullvalue

There is no corresponding value of Database NULL Value in Tcl. Perl use undef for it. You can set your special value for NULL (default "NULL").
dbi::setnull __NULL

database handle methods

$dbh prepare sqlstatment

This function is mapped to $dbh->prepare($sqlstatment);. By success statement handle is returned.
set sth [$dbh prepare {select * from persons}]

$dbh do sqlstatment

This function is mapped to $dbh->do($sqlstatment);. By success number of affected rows is returned.
$dbh do {delete form persons where name="Artur"}

$dbh table_info

This function is mapped to $dbh->table_info();. It returns a pseudo statement handle for fetching table informations.
set tsth [$dbh table_info]

$dbh quote sting

This function is mapped to $dbh->quote($string);. Returns quoted string.

$dbh invoke method ?arg1? ...

This function is mapped to $dbh->method(arg1,...);. The result will be mapped to tcl structures (lists).
$dbh invoke tables
$dbh invoke func _ListTables

$dbh attribute attr

This function is mapped to $dbh->{attr};. The result will be mapped to tcl structures (lists).
$dbh attribute Name

$dbh disconnect attr

This function is mapped to $sth->disconnect();.
$dbh disconnect

statment handle methods

$sth execute

This function is mapped to $sth->execute();. You must use this function before fetching rows.
puts [$sth fetchrow]

$sth fetchrow

This function is mapped to $sth->fetchrow();. The row is returned as Tcl_List if there are no more rows empty list will be returned
puts [$sth fetchrow]

$sth invoke method ?arg1? ...

This function is mapped to $sth->method(arg1,...);. The result will be mapped to tcl structures (lists).
set rows_count [$sth invoke rows]

$sth attribute attr

This function is mapped to $sth->{attr};. The result will be mapped to tcl structures (lists).
$sth attribute NUM_OF_FIELDS

$sth finish attr

This function is mapped to $sth->finish();.

Please write a mail if you are not happy with this API or you need additional functionality.

Problems

If perl invoke internal "die" function the system will immediately exit. It is possible to catch this function but it is not done yet.
By Errors Perl DBI produce some output to stdout.
The system was tested only with DBD::Mysql
The objects attributes can be only read. Setting are possibly too, but I have not need it yet.
If there Perl-Hash as result. It will be mapped to tcl-List
{ key1=>"val1", key2=>"val2" } will be {{key1 val1} {key2 val2}}
All Perl-undef values by "invoke" or "attribute" functions will be returned as empty tcl-list.
All Attributes passed to Perl are treated as SCALAR (no ref, no lists, no hashes)
I am not Perl C-API expert. The Perl C-API seems to be same way confused like Perl language. Probably memory leaks (Perl C-API Expert are welcome to take a look on the code).

Example Usage

#!/usr/bin/tcl

load ./libdbitotcl.so

# perl DBI->available_drivers;
puts "drivers: [dbi::invoke available_drivers]"

# perl DBI->connect("DBI:mysql:uni","root");
set conn [dbi::connect DBI:mysql:uni root]

# try some procedures on database handler
puts "tables: [$conn invoke tables]"
puts "errstr: [$conn invoke errstr]"
puts "err: [$conn invoke err]"
puts "state: [$conn invoke state]"

puts "attributes of on database handle"
puts "name: [$conn attribute Name]"


# catch a SQL-Query
set result [$conn prepare {select * from Student}]
$result invoke execute
set rows [$result invoke rows]

puts "NUM_OF_FIELDS: [$result attribute NUM_OF_FIELDS]"
puts "NAME: [$result attribute NAME]"
puts "PRECISION: [$result attribute PRECISION]"
puts "SCALE: [$result attribute SCALE]"
puts "NULLABLE: [$result attribute NULLABLE]"
puts "TYPE: [$result attribute TYPE]"

for {set x 0} {$x<$rows} {incr x} {
    puts [$result fetchrow]
}

# clean up
$result finish 
$conn disconnect

Artur Trzewik

Last modified: Wed Dec 6 13:23:34 CET 2000