dbitotcl - Use Perl DBI from Tcl

XOTclIDE - new life for Tcl.
  • interactive
  • introspective
  • object oriented
  • syntax checking
  • version control
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.

How it works.


Requirements


Downloads:

dbitotcl-0.14.tar.gz
Version 0.14 can handle binary data that contains null values (requires Tcl8.1)

dbitotcl-0.12.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 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


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: Mon Mar 22 21:31:37 CET 2004