dbView Quick View Guide

What Is dbView?

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. Connections to multiple databases within a single session. You can provide login information directly or define a login description and then use the MDMS secure password server to provide database passwords from a Kerberos server.

2. Named connection handles can be defined and used to quick connect to a new database or to move from one database connection to another in the same session.

3. The "show db" command shows schema information, so you can quickly view the fields in a table or view other information about objects in a database, including the contents of stored procedures that you use.

4. Different query result display types are supported. The standard table format is supported. A list format, that gives an orthogonal view of data, useful for long data records also is supported. For example:

   	1> select mission, scId, objective, description
   	2> from missions
   	3> go
   
   	Row 1>
           mission = Cassini
           scId = 72
           objective = Saturn
           description = The Cassini Mission to Saturn
   	

   An 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.)

5. Real number display supports settings of decimal, scientific notation or mixed mode and the precision is setable for single and double precision values.

6. A history list is supported for recall of commands. Many other options to speed-up database access to or personally configure your database environment also are supported. Some examples are: timing queries, editor access to change commands, saving commands, data, or part of a session to files, an escape command for running other programs form within dbView.

7. Database connection information and settings made in a session are saved to a file, so your working environment is the same in subsequent sessions.

8. DbView can operating in both interactive and batch modes. In batch mode, a file name containing a script of commands is supplied on the command line.

9. A macro language is included. Complicated queries can be assigned a macro name and then accessed by name alone. Macros can be saved to files. The files can be automatically loaded in a new session so the macros are always available. Macros can contain variables. When you run a macro, you are prompted for variables values. Macros can contain persistent default values that are displayed whenever the macro is first run. The last value typed is kept as the default. The next time the macro is run, you can accept the default by pressing <enter> at the prompt. Macros can simple shell executed commands as well as SQL commands, so you can extend dbView's capabilities. Global variables that persist across macros are supported. Using database connection handles, a single macro can access different databases.

10. Formatted reports, including columns that wrap text in a particular column on the screen can be created and saved for later execution. Reports can include printing specifications and email list for automatic distribution of reports.

Getting Started

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 []: Sybase
	

Your 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.

Executing Commands

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.)

	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.

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:

	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.

set format list

This is a dbView environment command. It sets the display format to list for all subsequent command until we change the format again.

go last

We've already seen the go command. DbView keeps a history list of recently executed commands. By entering the command go last, we tell dbView to execute the last SQL command on the history list. Note: It's not the last command that gets executed, but the last SQL command.

We can find out more about dbView commands with help. Let's see what display formats are available by typing:

	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 a dbView data file that writes out the results returned from queries.
2. Execute the stored procedure, genFeiinterfacesFile that returns the information we need.
3. Close the dbView data file.

   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:

directory

This displays dbView's current working directory

directory /home/jake/fei

This changed the current working directory to the directory specified after the command.

escape ls

From the Unix C shell, runs the ls utility in the current working directory.

escape cat feiinterfaces.new

Displays the contents of our file.

escape mv feiinterfaces.new feiinterfaces

The file looks good, so we rename it to the default used by FEI.

Note: If you're using FEI, and you need a new interfaces file, you just follow the examples shown above.

Macro Commands

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:

   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 table
	

The 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 = FEI
	

If 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> done
	

We'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:700PM
   

The 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:850PM
	

If we had exited dbView between these two executions of the macro,the default value would be null in both cases.

Summing Up

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.

---

Copyright © 1997 The California Institute Of Technology