InterBase command line tools tutorial

Abstract: This article gets you set up to have all the InterBase line tools set up in a simple manner on your system

The InterBase command line tools are isql, gfix, gstat, gsec and gbak. IBConsole provides the functionality of all these commands in a graphic way. However, if you are familiar with command line tools, you can usually get jobs done faster with command line tools than IBConsole. Command line tools can also be automated, whereas IBConsole cannot. The InterBase Operations Guide manual is the definitive source for all documentation on using the command line tools. However, with the examples below, you can do must things you need to do.

Before you set up an environment for command line tools, you need to know where InterBase is installed. To know where InterBase is installed, run InterBase Server Manager from Embarcadero InterBase group in the start menu.

Where InterBase is installed is specified by the Root Directory setting in InterBase Server Manager.

Now that you know where InterBase is installed, go to a command prompt. To go to a command prompt, press Windows key-R, The Windows key looks like this on your keyboard:

data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAAkGBwgHBgkIBwgKCgkLDRYPDQwMDRsUFRAWIB0iIiAdHx8kKDQsJCYxJx8fLT0tMTU3Ojo6Iys/RD84QzQ5OjcBCgoKDQwNGg8PGjclHyU3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3Nzc3N//AABEIAIcAhwMBIgACEQEDEQH/xAAcAAEAAQUBAQAAAAAAAAAAAAAAAQIEBQYHAwj/xABBEAABAwIDAwYLBwIHAQAAAAABAAIDBBEFITEGEkETMlFxcrEHFCIjNGFigZGSwRckQkNTY4JS0RYzRHOh8PEV/8QAFwEBAQEBAAAAAAAAAAAAAAAAAAEDAv/EABsRAQEAAwEBAQAAAAAAAAAAAAABAhFBMRIh/9oADAMBAAIRAxEAPwDuCIiglERUEREBERAREQEREBQpUICIigIiIAUqApQERFQRRdLoJRQl0Eoo3h0pcIJRRcJdQEREBERAREQApUBCgpkkZEwvkcGNGpcbALXa7bTCYWSspallRUMGTGG4v6ytE8Km0lTJiZwqnlcyCIDlA0842WnYEfPS9n6ri58dTF0n/FddI/edORf8LcgFlqHEK6qY17nkNOhJK56xx3mj1rolMA2njboAwWtwTH9MvFw6WUXvMCBrkf7q3knkF/La73L2fewIB3iDbPgrWTot7gu9OVL6uRotdtuyvI4hMNCPlVEnQrd+qmhcHFKgHnD4L0hrpJnBpeQVjXKqmznY0mwJsorNMkkhcHmqc0jgDdbLh1W2sphINQbHrWkF5Oq2fZUnxKX/AHPoukZpERAREQFBNgT0KV5VJ3YJD0NKD552wn8Y2kr5CdZSPgvDAfSZB7H1XhjL9/FKp3Eyu7164F6W4ewsOteNhHOHWF0emzhj3RY8mDcX6AuccRbpXSaIE08Btqxud+FuH9lpg4yVkHds0cTk06BWk7db69a9cRlkghJhEZcwFz+U3rWA4WvxA/8AVquKYjXPglz5GPk5uZG5puGAjynes6gLRyzE0jG33nNHWV4uIObSCPUtUxPDMPbS4dPOQ+WeWMzmWW9wRnqcld4VBDR45NBRvPi5p2uDBIXC9zmFBm3Kqk9Ki7Y71S4qaU/eou2O9Qe4WzbKn7rMP3PotZ4nrWybKn7vP2x3KjPBFAKIJREQFb4gd2inPRGV7hWWNO3MKqj+07uQfN+IHerZz0vPernAvTT2CrOpN55D0uKu8C9N/gVh1txsh1C6RQj7lTGxPmwcuPUuctAyXScKG9h9KBY+aBIutcWVWmKNkMDjFbmOu07+m67o0Wm4oJWU8szwG7wl8rcLb+ZHF53vgt+r4JZacsifGCTZwkabEW6xxtqtak2fLo+TnnjaDzvF4gzeuzcOZJ6MvUu0a3iVFhUdJh0pEfLSSRcqS8m7SM8idFcYbBS0+0sraHc5F1KHeQ7eF94rLv2ewtoO/SRyuyDnSDeJVFLg1BQVLp6SAROLd0hpy+CguX6KKb0mInTeHepfmogNp4+0FBdO1PWtg2VPmage0FgX89/WVnNlebUj1t+qsGwjRSFSFI0VEogUIJWL2nfuYFWO/ad3LKLB7Zv3Nm60/tFSj54lze4+tXuB+m/xKsXalXuCenDslYNuNpboF0vBgDhVM64J5Pj1rme8GsuVv+ylbFWYRCyN4L4gWuZxGfctcPWdZSYu3XbwOXTmrGp8kndsQDYW4rIy5MIuRu5XBv7rLHVAztx4kcf+haOWOmFibG+V1bvV1Kczb/hWr9SuaPF2ipj/AM5naCqcFSxzY5WvdazSDnxUF9KLSSD2is1srrUjs/Va/HNyl3cTmVn9lufVdTfqukbEEQaJfNFSihQgrutc2/du7MVlv6VsKx+PUAxLDJqY/jaQpfB82nVXmDG1cOyV7Y9g9Rg9a+GoY4C/kuIyKsaaYwTNkHDUepYNeNjqZicm6KaGWemk5SCV0bvZVtTyRzDeDx8VdCWJo1A960jisu3aHF2iwrH5dJUHHcTIsZ8upYrxmH+tvzBPGotN9nzBXblkjjNefzR8FQcUrnfmj3BY81UP6jPmCp8cgH5rPe4K7F86uqz+c5RHUyF3nHud1lWJraf9WP5wo8eg/Vj+YJsbNR1Oi3HZJ+++pI6G/VaThGEYrWMjkp6R5ifo85Nt1ro+A4Z/8ukLHuD5n5yOGnUFYjKcECi6KibooRFVIEQKDF47s9QY3SmGsiBPBw1C0mXwTUznkx18gbwuAumBVKXGVd1zBvglpxzsQk9wCl3gloz/AK+VdORPmG65d9kdHf06X4BT9kVB+KtmXT0T5ibcy+yLDONXOVI8EeFA51M/xXTFQVfmG3Om+CbBxrPOf5K+w/wZ4BRztldG+YtNwHuuFuqhNQUxsZExrI2hrGiwA4KpQiqJREQEUcFKCq6lQiiqgVVdEVC6XUIgXS6IggqgqUQUoiIiFCIgm6IiAiIg/9k=

Windows key-R brings up the Run Dialog:

Enter cmd in the run dialog and click OK.

Now, enter the following at the command prompt:

cd C:\Program Files\Embarcadero\InterBasexe3u2\bin
set path=%cd%;%path%
set isc_user=sysdba
set isc_password=masterkey

Finally, you need to use the CD command to change directory to where your database resides. In this example, we'll change directory to where the employee sample database is installed for InterBase. If you have a version of InterBase prior to XE3, or you have InterBase XE3 and it is not installed under Program Files, you can use the cd command as follows to get the sample  directory:

cd ..\examples\database

If you have InterBase XE3 or later, you can use the cd command as follows to get to the sample directory:

cd %programdata%\embarcadero\interbase\gds_db

Here is a screen shot of all that has been done so far from the command prompt:

Now that you have your paths set up correctly, using InterBase command line tools is pretty simple.

Using ISQL

Entering:

isql employee.gdb

will connect you to InterBase employee sample database. All isql commands end in a semicolon (;). This is because commands can be multiple lines. If you forget to enter a semicolon, you get a CON>prompt. Here are some example commands you enter when connected to the employee sample database (note that you can copy the text below and paste it into isql to save typing):

show tables;
show table country;
select * from country;
commit;
set transaction read committed;
set list on;
select * from country where country='USA';
rollback;
create database 'foo.ib';
create table t1 (
objid int not null primary key,
first_name varchar(10),
last_name varchar(10)
);
insert into t1 values (1, 'John', 'Smith');
delete from t1 where objid=1;
commit;
drop table t1;
drop database;
exit;

Here is a screen shot of what all the these commands executed in isql look like:

So far, we've completed a local connection to a database via a relative path. Here are some other connection examples.

Connect via a complete path:

isql %cd%\employee.gdb

Connect via TCP/IP using localhost:

isql localhost:%cd%\employee.gdb

Connect via TCP/IP using the machine name:

isql %computername%:\%cd%\employee.gdb

Now a screen shot of all these commands:

'

Notice Database in the last example. You can use this same path on any remote computer to connect. In other other words, your isql connection from a remote computer would look like:

isql NGYRN-64:C:\ProgramData\Embarcadero\InterBase\gds_db\examples\database\employee.gdb

Using gbak

Gbak is used to backup and restore database. Below are some example you can copy and paste into a command prompt to try. All run fast because the employee database is small. For larger databases, the -se option is much faster.

gbak employee.gdb employee.gbk -v
gbak -se service_mgr %cd%\employee.gdb %cd%\employee.gbk -v
gbak -se localhost:service_mgr %cd%\employee.gdb %cd%\employee.gbk -v
gbak -se %computername%:service_mgr %cd%\employee.gdb %cd%\employee.gbk -v
gbak -c employee.gbk employee.ib -v
gbak -r -se service_mgr %cd%\employee.gbk %cd%\employee.ib -v
gbak -r -se localhost:service_mgr %cd%\employee.gbk %cd%\employee.ib -v
gbak -r -se %computername%:service_mgr %cd%\employee.gbk %cd%\employee.ib -v

Using gfix

Validating a database and mending a database

A common taks is to validate a database and then mend it if errors are reported.  Below are examples of validating and mending using relative and absolute paths that you can copy and paste into the command prompt to test. Because your copy of employee.gdb will be unlikely to need repair, these commands will appear to do nothing. Note that after mending a database, you must backup and restore the database using gbak as shown above to repair a database.

gfix -v -f -n employee.gdb
gfix -v -f -n %cd%\employee.gdb
gfix -mend employee.gdb
gfix -mend %cd%\employee.gdb

Below are some other gfix commands you can try:

gfix -buffers 50000 employee.gdb
gfix -write direct employee.gdb
gfix -write sync employee.gdb
gfix -write async employee.gdb
gfix -sql_dialect 3 employee.gdb
gfix -sweep employee.gdb
gfix -housekeeping 20000 employee.gdb
gfix -mode read_only employee.gdb
gfix -mode read_write employee.gdb

Using gstat

Use Gstat to get statisics about your database. Here are a few examples:

gstat -h employee.gdb

 gstat employee.gdb -t COUNTRY

 gstat employee.gdb -r -t COUNTRY

 

Using gsec

Use Gsec to maintain users in your security database and maintain aliases. Some examples:

gsec -add someuser -pw theirpassword
gsec -display
gsec -delete someuser
gsec -alias_add emp -alias_dbpath %cd%\employee.gdb
gsec -alias_dis
gsec -alias_del emp
 

Finally, look carefully again at the command to add an alias named emp. Here it is again:

gsec -alias_add emp -alias_dbpath %cd%\employee.gdb

With this alias added your system, you no longer need to worry about paths when pointing to your database! Example: