|
The following are some examples of how to use DBIx::Recordset. The Examples are
from the test.pl. The examples show the DBIx::Recordset call first, followed by the
generated SQL command. *set = DBIx::Recordset-> Setup ({'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table"}) ;Setup a DBIx::Recordset for driver $Driver, database $DB to access table $Table. $set -> Select () ;
SELECT * from <table> ; $set -> Select ({'id'=>2}) ;
is the same as
$set1 -> Select ('id=2') ;
SELECT * from <table> WHERE id = 2 ; $set -> Search({ '$fields' => 'id, balance AS paid - total ' }) ;
SELECT id, balance AS paid - total FROM <table> $set -> Select ({name => "Second Name\tFirst Name"}) ;
SELECT * from <table> WHERE name = 'Second Name' or name = 'First Name' ; $set1 -> Select ({value => "9991 9992\t9993",
'$valuesplit' => ' |\t'}) ;
SELECT * from <table> WHERE value = 9991 or value = 9992 or value = 9993 ; $set -> Select ({'+name&value' => "9992"}) ;
SELECT * from <table> WHERE name = '9992' or value = 9992 ; $set -> Select ({'+name&value' => "Second Name\t9991"}) ;
SELECT * from <table> WHERE (name = 'Second Name' or name = '9991) or
(value = 0 or value = 9991) ; $set -> Search ({id => 1,name => 'First Name',addon => 'Is'}) ;
SELECT * from <table> WHERE id = 1 and name = 'First Name' and addon = 'Is' ; $set1 -> Search ({'$start'=>0,'$max'=>2, '$order'=>'id'}) or die "not ok
($DBI::errstr)" ;
SELECT * from <table> ORDER BY id ;
B<Note:> Because of the B<start> and B<max> only records 0,1 will be returned $set1 -> Search ({'$start'=>0,'$max'=>2, '$next'=>1, '$order'=>'id'}) or die "not ok
($DBI::errstr)" ;
SELECT * from <table> ORDER BY id ;
B<Note:> Because of the B<start>, B<max> and B<next> only records 2,3 will be
returned $set1 -> Search ({'$start'=>2,'$max'=>1, '$prev'=>1, '$order'=>'id'}) or die "not ok
($DBI::errstr)" ;
SELECT * from <table> ORDER BY id ;
B<Note:> Because of the B<start>, B<max> and B<prev> only records 0,1,2 will be
returned $set1 -> Search ({'$start'=>5,'$max'=>5, '$next'=>1, '$order'=>'id'}) or die "not ok
($DBI::errstr)" ;
SELECT * from <table> ORDER BY id ;
B<Note:> Because of the B<start>, B<max> and B<next> only records 5-9 will be
returned *set6 = DBIx::Recordset -> Search ({ '!DataSource' => "dbi:$Driver:$DB",
'!Table' => "t1, t2",
'!TabRelation' =>
"t1.value=t2.value",
'!Fields' => 'id, name, text',
'id' => "2\t4" }) or die "not ok
($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (id=2 or id=4) and t1.value=t2.value ; $set6 -> Search ({'name' => "Fourth Name" }) or die "not ok
($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (name = 'Fourth Name') and
t1.value=t2.value
; $set6 -> Search ({'id' => 3,
'$operator' => '<' }) or die "not ok ($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (id < 3) and t1.value=t2.value ; $set6 -> Search ({'id' => 4,
'name' => 'Second Name',
'*id' => '<',
'*name' => '<>' }) or die "not ok ($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (id<4 and name <> 'Second Name') and
t1.value=t2.value ; $set6 -> Search ({'id' => 2,
'name' => 'Fourth Name',
'*id' => '<',
'*name' => '=',
'$conj' => 'or' }) or die "not ok ($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (id<2 or name='Fourth Name') and
t1.value=t2.value ; $set6 -> Search ({'+id|addon' => "7\tit",
'name' => 'Fourth Name',
'*id' => '<',
'*addon' => '=',
'*name' => '<>',
'$conj' => 'and' }) or die "not ok ($DBI::errstr)" ;
SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and ( ((name <>
Fourth
Name)) and ( ( id < 7 or addon = 7) or ( id < 0 or addon = 0))) $set6 -> Search ({'+id|addon' => "6\tit",
'name' => 'Fourth Name',
'*id' => '>',
'*addon' => '<>',
'*name' => '=',
'$compconj' => 'and',
'$conj' => 'or' }) or die "not ok ($DBI::errstr)" ; SELECT id, name, text FROM t1, t2 WHERE (t1.value=t2.value) and ( ((name =
Fourth
Name)) or ( ( id > 6 and addon <> 6) or ( id > 0 and addon <> 0))) ; *set7 = DBIx::Recordset -> Search ({ '!DataSource' => "dbi:$Driver:$DB",
'!Table' => "t1, t2",
'!TabRelation' => "t1.id=t2.id",
'!Fields' => 'name, typ'}) or die "not ok
($DBI::errstr)" ;
SELECT name, typ FROM t1, t2 WHERE t1.id=t2.id ; %h = ('id' => 22,
'name2' => 'sqlinsert id 22',
'value2'=> 1022) ; *set9 = DBIx::Recordset -> Insert ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]")}) or die "not ok
($DBI::errstr)" ;
INSERT INTO <table> (id, name2, value2) VALUES (22, 'sqlinsert id 22', 1022) ; %h = ('id' => 22,
'name2' => 'sqlinsert id 22u',
'value2'=> 2022) ; $set9 -> Update (\%h, 'id=22') or die "not ok ($DBI::errstr)" ;
UPDATE <table> WHERE id=22 SET id=22, name2='sqlinsert id 22u', value2=2022 ; %h = ('id' => 21,
'name2' => 'sqlinsert id 21u',
'value2'=> 2021) ;
*set10 = DBIx::Recordset -> Update ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id')}) or die "not ok
($DBI::errstr)" ;
UPDATE <table> WHERE id=21 SET name2='sqlinsert id 21u', value2=2021 ; %h = ('id' => 21,
'name2' => 'Ready for delete 21u',
'value2'=> 202331) ; *set11 = DBIx::Recordset -> Delete ({%h,
('!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id')}) or die "not ok
($DBI::errstr)" ;
DELETE FROM <table> WHERE id = 21 ; *set12 = DBIx::Recordset -> Execute ({'id' => 20,
'*id' => '<',
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[1]",
'!PrimKey' => 'id'}) or die "not ok
($DBI::errstr)" ;
SELECT * FROM <table> WHERE id<20 ; *set13 = DBIx::Recordset -> Execute ({'=search' => 'ok',
'name' => 'Fourth Name',
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "$Table[0]",
'!PrimKey' => 'id'}) or die "not ok ($DBI::errstr)" ;
SELECT * FROM <table> WHERE ((name = Fourth Name)) $set12 -> Execute ({'=insert' => 'ok',
'id' => 31,
'name2' => 'insert by exec',
'value2' => 3031,
# Execute should ignore the following params, since it is already setup
'!DataSource' => "dbi:$Driver:$DB",
'!Table' => "quztr",
'!PrimKey' => 'id99'}) or die "not ok ($DBI::errstr)" ;
SELECT * FROM <table> ; $set12 -> Execute ({'=update' => 'ok',
'id' => 31,
'name2' => 'update by exec'}) or die "not ok ($DBI::errstr)" ;
UPDATE <table> SET name2=update by exec,id=31 WHERE id=31 ; $set12 -> Execute ({'=insert' => 'ok',
'id' => 32,
'name2' => 'insert/upd by exec',
'value2' => 3032}) or die "not ok ($DBI::errstr)" ; INSERT INTO <table> (name2,id,value2) VALUES (insert/upd by exec,32,3032) ; $set12 -> Execute ({'=delete' => 'ok',
'id' => 32,
'name2' => 'ins/update by exec',
'value2' => 3032}) or die "not ok ($DBI::errstr)" ;
DELETE FROM <table> WHERE id=32 ;
|
