DBD::XBase(3pm)

💖 DBD::XBase — bestie, it's a DBI driver for XBase files 💖

okay so. hi. welcome to DBD::XBase(3pm), which is a User Contributed Perl Documentation page, and yes it's exactly what it sounds like: a DBI driver for XBase compatible database files. that's the NAME, that's the whole vibe. let's go 🦋

---

✨ SYNOPSIS ✨

here's the tea, straight up, no edits:

use DBI;
my $dbh = DBI->connect("DBI:XBase:/directory/subdir")
                            or die $DBI::errstr;
my $sth = $dbh->prepare("select MSG from test where ID != 1")
                            or die $dbh->errstr();
$sth->execute() or die $sth->errstr();

my @data;
while (@data = $sth->fetchrow_array())
            { ## further processing }

$dbh->do('update table set name = ? where id = 45', {}, 'krtek');

like. that's it. that's the whole flow. connect, prepare, execute, fetchrow_array in a loop, done. 💅

---

🌸 DESCRIPTION 🌸

so this is a DBI compliant driver for the module XBase. if you wanna actually use it, bestie, go read the DBI(3) documentation, because that's where the how-to lives. 📖

in the connect call, you specify the directory containing the dbf files (and other, memo, etc.) as the third part of the connect string. and if you don't? it defaults to the current directory. easy. 🎀

now here's the thing you need to internalize: with dbf, there is no database server that the driver would talk to. none. zero. DBD::XBase calls methods from the XBase.pm module to read and write the files on the disk directly, so any limitations and features of XBase.pm apply to DBD::XBase as well. basically, DBD::XBase adds SQL, DBI compliant interface to XBase.pm. that's the whole job. 🫶

and a lil heads up: DBD::XBase doesn't make use of index files at the moment. if you really need indexed access, go check XBase(3) for notes about support for variour index types. (yes, "variour". i'm quoting, i'm not fixing, i'm not that kind of girl 💀)

---

🔥 SUPPORTED SQL COMMANDS 🔥

the SQL commands currently supported by DBD::XBase's prepare are, in order:

👑 select

select fields_or_expressions from table [ where condition ]
                                    [ order by field ]

Fields_or_expressions is a comma separated list of fields or arithmetic expressions, or a "*" for all fields from the table. the "where" condition specifies which rows will be returned — you can have arbitrary arithmetic and boolean expression here, compare fields and constants, and use "and" and "or". match using "like" is also supported. examples, periodt:

select * from salaries where name = "Smith"
select first,last from people where login = "ftp"
                                            or uid = 1324
select id,first_name,last_name from employ
            where last_name like 'Ki%' order by last_name
select id + 1, substr(name, 1, 10) from employ where age > 65
select id, name from employ where id = ?

you can use bind parameters in the where clause, like that last example is showing off. the actual value has to be supplied via bind_param or in the call to execute or do — see DBI(3) for details. and to check for NULL values in the "where" expression, use "id is null" and "id is not null", not "id == null". do not do that. i'm serious. 🙈

heads up: you can only select from one table. joins are not supported and are not planned to be supported. if you need them, get a real RDBMS (or send me a patch). 💀

in the arithmetic expressions you can use a couple of SQL functions — currently supported are concat, substr (and substring), trim, ltrim and rtrim, length. the author does not have an exact idea of which and how many functions they want to support. it's easy to write them in a couple of minutes now the interface is there (check the XBase::SQL module if you want to send a patch containing support for more), it's just that they don't really need them and sometimes it's hard to tell what is useful and what is SQL92 compatible. comment welcome, bestie. ✨

the select command may contain an order by clause. only one column is supported for sorting at the moment, patches are welcome. 🎀

the group by clause is not supported (and they do not plan them), nor are the aggregate functions. nope. none. 💅

🦋 delete

delete from table [ where condition ]

the "where" condition is the same as for select. examples:

delete from jobs            ## empties the table
delete from jobs where companyid = "ISW"
delete from jobs where id < ?

(that first one? yeah. ## empties the table. it's giving "i have no regrets" 💀)

🌈 insert

insert into table [ ( fields ) ] values ( list of values )

here fields is a (optional) comma separated list of fields to set, and list of values is a list of constants to assign. if the fields are not specified, sets the fields in the natural order of the table. you can use bind parameters in the list of values. examples:

insert into accounts (login, uid) values ("guest", 65534)
insert into accounts (login, uid) values (?, ?)
insert into passwd values ("user","*",4523,100,"Nice user",
                            "/home/user","/bin/bash")

💜 update

update table set field = new value [ , set more fields ]
                                    [ where condition ]

example:

update passwd set uid = 65534 where login = "guest"
update zvirata set name = "Jezek", age = 4 where id = 17

again, the value can also be specified as bind parameter. 🔥

update zvirata set name = ?, age = ? where id = ?

🎀 create table

create table table name ( columns specification )

Columns specification is a comma separated list of column names and types. example:

create table rooms ( roomid int, cat char(10), balcony boolean )

the allowed types are:

char num numeric int integer float boolean blob memo date time
datetime

some of them are synonyms. they are of course converted to appropriate XBase types. 🧚

⭐ drop table

drop table table name

example:

drop table passwd

and it's gone. bye. 💅

---

✨ ATTRIBUTES ✨

besides standard DBI attribudes, DBD::XBase supports database handle attribute xbase_ignorememo:

$dbh->{'xbase_ignorememo'} = 1;

setting it to 1 will cause subsequent tables to be opened while ignoring the memo files (dbt, fpt). so you can read dbf files for which you don't have (you have lost them, for example) the memo files. 🫶 and the memo fields will come out as nulls.

---

🌸 VERSION 🌸

1.08

---

🦋 AVAILABLE FROM 🦋

http://www.adelton.com/perl/DBD-XBase/

---

👑 AUTHOR 👑

(c) 1997--2017 Jan Pazdziora.

contact the author at jpx dash perl at adelton dot com.

---

💖 SEE ALSO 💖

• perl(1)
• DBI(3), XBase(3)
• dbish(1)

translation into Japanese (older version) at http://member.nifty.ne.jp/hippo2000/perltips/DBD/XBase.htm by Kawai Takanori. 🌸

---

okay that's the whole page, bestie, you read all of it, slay 🎀 this was perl v5.34.0, dated 2022-06-13, and it's still out here being DBD::XBase(3pm). go forth and query your dbf files. no cap. 💜