view release on metacpan or  search on metacpan

DtfSQL.pm  view on Meta::CPAN

* There is a limitation (bug?) concerning foreign keys consisting of multiple columns. Basically, a foreign 
key is a column or group of columns within a (dependant) table that references a primary key in some other 
(parent) table. Foreign keys provide a way to enforce the referential integrity of a database. The check for 
referential integrity works fine in dtF/SQL as long as the foreign key consists of a single column: A record 
will *not* be accepted if the foreign key doesn't exist (as primary key) in the parent table; you will 
get an error message. But for multi-column foreign keys dtF/SQL cannot check the referential integrity. A 
record will be accepted even if the foreign key doesn't exist (as primary key) in the parent table. Don't rely 
on this feature.

* The dtF/SQL cascaded delete feature (ON DELETE DELETE or ON DELETE CASCADE action-constraint/trigger), 
which helps to preserve the referential integrity of a database, doesn't work properly. Let's say, table 
A is the parent table with primary key [A.id]. Table B is the dependent table, i.e. contains a foreign key 
[B.id] which is the primary key of table A. The primary key of table B is a multi-column key [B.id, B.id2], 
i.e. the foreign key [B.id] of table B is part of its primary key. Table B was created with the cascaded 
delete action-constraint set for this foreign key [B.id]. If you now delete a record in table A, dtF/SQL will, 
as you expect, delete the corresponding records (where A.id = B.id) in table B too. *But*, if you try to 
insert one of the just deleted records into table B again (same multi-column primary key), you will get a 
referential integrity error, saying "UNIQUE value exists for column 'B.id' ", i.e. dtF/SQL hasn't realized, 
that the primary key is free for use again. This doesn't happen if you delete the corresponding records in 
table B by hand, i.e. with a DELETE statement. Because this cascaded delete behavior is not what you might 
expect, don't rely on this feature.

* dtF/SQL is also a bit weak on documentation. It contains some errors (e.g. wrong cross-references in 
the C/C++/Java Reference manual), but generally it's not that bad that one can't work with it. All in 
all, it doesn't go into deep detail and is very brief on some relevant topics (auto-commit behavior,  
for example). 

There may be other limitations that I haven't detected yet -- comments welcome. 

blib/lib/Mac/DtfSQL.pm  view on Meta::CPAN

* There is a limitation (bug?) concerning foreign keys consisting of multiple columns. Basically, a foreign 
key is a column or group of columns within a (dependant) table that references a primary key in some other 
(parent) table. Foreign keys provide a way to enforce the referential integrity of a database. The check for 
referential integrity works fine in dtF/SQL as long as the foreign key consists of a single column: A record 
will *not* be accepted if the foreign key doesn't exist (as primary key) in the parent table; you will 
get an error message. But for multi-column foreign keys dtF/SQL cannot check the referential integrity. A 
record will be accepted even if the foreign key doesn't exist (as primary key) in the parent table. Don't rely 
on this feature.

* The dtF/SQL cascaded delete feature (ON DELETE DELETE or ON DELETE CASCADE action-constraint/trigger), 
which helps to preserve the referential integrity of a database, doesn't work properly. Let's say, table 
A is the parent table with primary key [A.id]. Table B is the dependent table, i.e. contains a foreign key 
[B.id] which is the primary key of table A. The primary key of table B is a multi-column key [B.id, B.id2], 
i.e. the foreign key [B.id] of table B is part of its primary key. Table B was created with the cascaded 
delete action-constraint set for this foreign key [B.id]. If you now delete a record in table A, dtF/SQL will, 
as you expect, delete the corresponding records (where A.id = B.id) in table B too. *But*, if you try to 
insert one of the just deleted records into table B again (same multi-column primary key), you will get a 
referential integrity error, saying "UNIQUE value exists for column 'B.id' ", i.e. dtF/SQL hasn't realized, 
that the primary key is free for use again. This doesn't happen if you delete the corresponding records in 
table B by hand, i.e. with a DELETE statement. Because this cascaded delete behavior is not what you might 
expect, don't rely on this feature.

* dtF/SQL is also a bit weak on documentation. It contains some errors (e.g. wrong cross-references in 
the C/C++/Java Reference manual), but generally it's not that bad that one can't work with it. All in 
all, it doesn't go into deep detail and is very brief on some relevant topics (auto-commit behavior,  
for example). 

There may be other limitations that I haven't detected yet -- comments welcome. 

samples/dbi_4_fetch-cycle.pl  view on Meta::CPAN

						$dsn, 
						'dtfadm', 
						'dtfadm', 
						{PrintError => 1, RaiseError => 1, AutoCommit => 0} 					
					  ) || die "Can't connect to database: " . DBI->errstr; 
print " ok.\n\n";


# First, we delete the order with orderid #5503 in table torder, which may exist from a  
# previous run of this script. Due to the faulty dtF/SQL implementation of (automatic) 
# cascaded delete, we have to delete all records with id #5503 in torder's dependent 
# table ordered_articles (its FOREIGN KEY orderid has a reference set to the PRIMARY KEY 
# orderid of table torder) by hand, before we can delete record id #5503 in parent table torder. 
 

my $rowcount;
my $orderid = 5503;
my $statement = qq{ DELETE FROM ordered_articles WHERE orderid = $orderid 
				  };
$rowcount = $dbh->do($statement) ;
if ($rowcount > 0) {