Last update on October 8, 1997
Send questions, comments and corrections to MDMS
DbView is a command line interface to databases. Currently it supports the Sybase relation database management system. The major functions provided by dbView include:
1> select mission, scId, objective, description 2> from missions 3> go Row 1> mission = Cassini scId = 72 objective = Saturn description = The Cassini Mission to SaturnAn export format, used to export selected data to another database, spreadsheet, etc., is supported. In this format field in database records are separated by a tab or some other defined character. This format is also useful for creating tables within word processor documents that support the creation of tables from tab delimited data sets. (Very handy for creating or updating a SYS document from data stored on-line in a database.)
In this section we show how to start dbView, how to exit from it, and how to connect to a database.
Since dbView is a command line utility, we run it from some sort of Shell, like the Unix C shell which we shall use in this document. (On Microsoft systems, dbView is run from a DOS window.) To start dbView just type the name dbView - with a capital 'V'. DbView responds by displaying its version information and copyright line followed by a set of prompts to connect you to a database.The square brackets following each prompt, are your default values saved from the last time you logged on. Just press <enter> key to accept a default. Since this is our first time, there are no defaults, so we have to type in a response at each prompt, something like this:
> dbView dbView, version 1.9, (dblib), 20 Jan 1997 Copyright 1993, The Jet Propulsion Laboratory. All rights reserved. User name []: jake password: <jake's password> server []: FEI database []: fei DBMS Type []: SybaseYour Database Administrator provides you with the values you need to provide.
If you log on correctly, you'll see the dbView command prompt:
1>You're ready to begin. We'll start with the most important command, how to exit from dbView. We don't actually want to exit just yet, but it's simple to do, just type exit and press the <enter> key:
1> exit<enter> >and you're back at the shell prompt. If you were unable to connect, you'll need to get more information about the dbView environment which is in Database Connections.
Lets assume we're connected to the fei database. Lets start by seeing what's in the database by using the show db. (All commands are executed when you press the <enter> key. We'll just assume you know this from now on.)
Now let's edit the command to add two more field to the SELECT clause. We do this in an editor. By default, dbView uses the Unix vi editor in Unix environments and edit in Microsoft environments. (If you want to change the editor used, see the section dbView Environment.) For now just type edit on the command line and added two field so the SELECT clause of the SQL statement looks like this:
We can find out more about dbView commands with help. Let's see what display formats are available by typing:
Note: If you're using FEI, and you need a new interfaces file, you just follow the examples shown above.
If you've tried the examples so far, you may have found all the typing tedious. Let's make it easier by creating some dbView macros. For example:
Executing Commands
1> show db
Show db returns all of the objects in the database organized by object type. We show just part of a listing for user tables and views:
user tables :
accessRoles feiServers
fileKeywordSets fileLinks
fileStatus fileTypeLinks
fileTypeRPCs fileTypeRoles
fileTypes files
files_new images
keywords landSatImages
privilegedUsers sigEvents
userRoles
views :
fileView
We can get a description of a particular object in the listing, like the fileTypes table, like this:
1> show db fileTypes
Table : fileTypes
Owner : dbo
colid column data type length nulls?
----- -------------------- ------------ ------ ------
1 ftIdx numeric 6 No
2 name varchar 30 No
3 host varchar 30 No
4 directory varchar 128 No
5 fileFormat varchar 10 Yes
6 proprietaryTime datetime 8 Yes
7 compressionType varchar 20 Yes
8 feiServerIdx numeric 3 No
9 notes varchar 255 Yes
10 modified datetime 8 No
11 crcFlag tinyint 1 No
12 encryptFlag tinyint 1 No
13 compressFlag tinyint 1 No
14 conferenceFlag tinyint 1 Yes
With this information, we can create our first SQL command. We'll retrieve information from the fileTypes table for two of the columns listed:
1> select name, modified
2> from fileTypes
3> go
name modified
------------------------------ --------------------------
text Jun 23 1995 9:31:06:903AM
image Jun 23 1995 9:31:20:003AM
(2 row(s) affected)
dbView commands, like show db get executed as soon you press the <enter> key, but database commands can be multiple lines long. You signal the end of a database command by typing go on a line by itself.
1> edit
...
1> select name, modified, host, directory
2> from fileTypes
3> go
Adding the final go command to execute the statement, results in something like this:
name modified host directory
------------------------------ -------------------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
text Jun 23 1995 9:31:06:903AM judith /home/fei/text
image Jun 23 1995 9:31:20:003AM judith /home/fei/image
The result is hard to read and not very pretty. We can fix that by displaying in list format and then re-executing the command:
1> set format list
1> go last
1> select name, modified, host, directory
2> from fileTypes
Row 1>
name = text
modified = Jun 23 1995 9:31:06:903AM
host = judith
directory = /home/fei/text
Row 2>
name = image
modified = Jun 23 1995 9:31:20:003AM
host = judith
directory = /home/fei/image
Now each row comes back in list format and is much easier to read. We used two new command which we should explain.
1> help set format
Topic:
set format
set format { table | list | export }
The display format for data returned by a database query.
Default: table
We just mentioned that dbView has a history list. Try typing:
1> help history
to find out more about it.
DbView allows us to save commands, display results and part or all of a session to files. For an introduction, we'll get FEI interfaces file from the database and save it to a file. (The information returned is used to connect to FEI servers.) We'll also show that stored procedures can be used to access the database as well as dynamic SQL statements. We follow these steps:
1> open dataFile /home/jake/fei/feiinterfaces.new
1> genFeiinterfacesFile
2> go
# feiinterfaces file.
# Generated on Feb 25 1997 11:07AM by dbo
image
judith fei1 12005 JPL-MDMS.JPL.NASA.GOV
text
judith fei1 12005 JPL-MDMS.JPL.NASA.GOV
(return status = 0)
1> close dataFile
DbView has an escape command that executes what follows it in your shell. So to check on our file, we can execute commands like this:
1> directory
/home/jake
1> directory /home/jake/fei
1> escape ls
feiinterfaces.new
1> escape cat feiinterfaces.new
# feiinterfaces file.
# Generated on Feb 25 1997 11:25AM by dbo
image
judith fei1 12005 JPL-MDMS.JPL.NASA.GOV
text
judith fei1 12005 JPL-MDMS.JPL.NASA.GOV
1> escape mv feiinterfaces.new feiinterfaces
Let review the commands we just executed:
Macro Commands
1> macro dir
---Command
1> directory
2> done
A macro begins with the command macro followed by the name of the macro. Following the command prompt, we enter the macro's contents, in this case just the word "directory". We complete a macro by typing "done" on a line by itself. Now let's execute the macro:
1> dir
/home/jake/fei
Let's try one more:
1> macro ls
---Command
1> # This is the Unix 'ls' command
2>
3> escape ls
4> done
This macro has an comment line beginning with a '#' character. It also shows that we can have blank lines in a macro. Now let's execute the macro. DbView adds anything we type after a macro name to the end of the macro, so our command can now be something like this:
1> ls fei*
feiinterfaces
Let's go back to the show db command for a moment to see another use of macros - it makes commands easier to remember. To show just the table names we could type:
1> show db (user tables)
We're probably going to forget that we need the word "user" in the command, so we make a macro:
1> macro tables
---Command
1> show db (user tables)
2> done
1> tables
user tables :
accessRoles feiServers
fileKeywordSets fileLinks
fileStatus fileTypeLinks
fileTypeRPCs fileTypeRoles
fileTypes files
files_new images
keywords landSatImages
privilegedUsers sigEvents
userRoles
This is easier to remember. Also, notice that we started to construct a dbView environment that meets our own needs using the names we find convenient to use. We can save our macros, have dbView automatically install them, edit them, change their names and do some other useful things. For more information, see
dbView Macros.
To complete our look at macros, we'll look at two last examples. Both show how you can include SQL statements in macros. The first is an example of hiding complexity. If you don't like using SQL, build some macros, or get them from someone else, and use them. The second example shows a simple use of variables in macros.
The first example mixes dbView environmental commands with a SQL statement. Since we've already built this macro, we just show it to you using the show macro command:
1> show macro who ---Command # Displays information about who you are within a # database server context. Note: This macro resets # the table format at the end to "table". # Example: # who # set format list set header off select "Logged in as" = suser_name(), "Name in this database" = user_name(), "Accessing database" = db_name(), "On server" = @@servername go set header on set format tableThe macro comment tells us what the macro does. We've emphasized the SQL statement to set it apart from the rest of the macro's contents. Don't worry if you don't understand it. That's the point. Once you've got it, just use it:
1> who Row 1> Logged in as = jake Name in this database = dbo Accessing database = fei On server = FEIIf you use several databases and servers in a session, you can forget where you are. This simple macro, tells you.
Now we look at putting variables in a macro. Here's a problem:
We're an FEI user,and each day we want to see what new image files have entered the FEI system. We know there's a view fileView that shows this information. We want to create an SQL statement that will display only the most resent files.To solve this problem we use a macro with an SQL statement in it that has a dbView variable in the SQL statement. We begin by looking at the object fileView and building our SQL statement from the list of columns:
1> show db fileView View : fileView Owner : dbo colid column data type length nulls? ----- -------------------- ------------ ------ ------ 1 fileType varchar 30 No 2 file varchar 60 No 3 contributor varchar 30 No 4 receivedAt datetime 8 No 5 status varchar 60 No 6 crcValue binary 16 Yes 7 errMsg varchar 255 Yes 1> macro images ---Command 1> select file, receivedAt, status, crcValue 2> from fileView 3> where fileType = "image" 4> and receivedAt > "$since" 5> doneWe've emphasized the macro variable $since in the example. DbView does simple string substitution for variables, so $since will be replaced with whatever value we give it. To give it a value, we execute the macro and supply a value at the prompt:
1> images since []: 11 Feb 1997 file receivedAt --------------- ---------------------------- andromeda.tiff Feb 1 1997 5:18:27:783PM pavo.tiff Feb 3 1997 11:10:19:850AM vela.tiff Feb 8 1997 3:45:07:140PM orion.tiff Feb 10 1997 4:02:04:700PMThe last value input becomes the default value, but we want to change the date, so we supply a new one the next time we execute the macro:
1> images since [11 Feb 1997]: 12 Feb 1997 file receivedAt --------------- ---------------------------- hydr.tiff Feb 11 1997 5:24:27:783PM caelum.tiff Feb 11 1997 8:18:19:850PMIf we had exited dbView between these two executions of the macro,the default value would be null in both cases.
That concludes the introduction to dbView. Some topics, like generating reports and running in batch mode, weren't discussed at all; and none of the topics were covered in detail. But the introduction should get you started and give you some hints about those features you want to know about. The follow-on parts of the dbView Guide provide this information.