BearDB Database Clients

This part of the documentation covers the setup and use of the BearDB database clients.

There are five database clients available for BearDB:

    Command-line client
    BearDBClient3 for C# .NET
    BearDBClient3 for Java
    JDBC Driver
    ADO.NET Driver

BearDB can also support web-based applications via its REST Server.

For more information on the BearDB REST Server, checkout the documentation link above.

The documentation for the database clients can be found here.

The BearDB command-line client

BearDB is delivered with a command-line client. This client allows someone to make queries from a command-prompt or shell and view the results as a set of comma-separated values returned via standard-out (stdout).

The command-line client communicates with the BearDB database server using a TCP-IP port. Therefore the command-line client can be located on any system that can communicate via a TCP-IP port with the database server.
To use the command-line client you will need to create a directory on the client computer and copy in the file BearDBclient3.jar. You will also want to copy in the file dbclient.bat or dbclient.sh based on the type of computer you are using. The files comes with distribution of BearDB.
To use the command-line client, just open a command-prompt in Windows or a terminal session in OS/X, Linux, or UNIX and type the following command to get a usage message:

     dbclient -usage

You should get the following output, which describes the usage of the BearDB command-line client:

BearDBclient Usage:

     -v: The passphrase for access to the database.
     -u: The username for access to the database.
     -s: The name or IP address of the database server.
     -q: The query to run. A query must be surrounded in double-quotes.
     -help: Optional. Show Usage
     -p: The port number to use.
     -?: Optional. Show Usage
     -h: Optional. Show Usage
     -f: Optional. Path and filename of a text file containing the query to run.      -i: Optional. Path to the file containing CSV data to import.
         Must supply the table name to load into using the -t parameter.
     -t: Optional. Name of the table to import CSV data
         Must only be used with the -i parameter.

Most queries through the command-line client will utilize many of the required parameters.
To perform a standard SQL Select query use the following syntax with the command-line client:

dbclient -u admin -v m00k -s 124.126.2.1 -p 7169 -q "SELECT * FROM myTable WHERE column1 = 'value'"

Where:

    -u admin: The username to access the server is admin
    -v m00k: The passphrase for the user to access the server is m00k
    -s 124.126.2.1: The I.P. address of the database server is 124.126.2.1.
    -p 7169: The TCP-IP port that the server is configured to listen on.
    -q "SELECT * FROM myTable WHERE column1 = 'value'": The query to execute on the database server. Note that the entire SQL statement is surrounded with double-quotes, and that the value in the WHERE clause is surrounded with single-quotes.

When you use the command-line client to perform a query you will always get a response from the server with a status indicating success or failure. If you attempt to use a SQL feature which is not supported in BearDB you will get a specific error message letting you know the feature is not implemented. Please see the BearDB SQL documentation for the details on the few SQL features which are not supported in this release.

When you perform a select query using the command-line client you will receive the query results via standard-out on the command line. The results will be in a comma-separated format, with the column names on the first line.

Using the previous command-line query as an example, the results of the select query would similar to the following:

    LastName,FirstName,OrderNo
    Hansen,Ola,22456
    Hansen,Ola,24562
    Pettersen,Kari,77895
    Pettersen,Kari,44678

    4 Rows.


Using BearDB Shell

BearDB has a shell environment which works the same on all platforms and releases.
The BearDB Shell provides an interactive SQL command interpreter which allows someone to administer or perform queries against the database.

To start BearDB Shell, run dbclient with no command-line parameters and the following will appear on the console:

C:\Path> dbclient

BearDB Console: BearDBClient .NET v.3.3

--> Enter SQL commands at the prompt below.
--> After entering a SQL command, press ENTER and type 'go'

--> Type quit to quit BearDBConsole


When BearDB Shell is started the first time, the user is prompted to enter all the details to connect to the database:

Server address: 127.0.0.1

Port [7169]: 7169

Encrypt communications [y/n]: n

Username: dbUser

Password: yattayatta

BearDB>


Notice that some of the prompts provide values in square brackes (i.e. [7169]). This notation is to show the default value if the user does not enter a port number in BearDB Shell. In the case of the Port value, if the user does not enter anything the port will default to 7169.

After starting BearDB Shell and entering the database connection details, the user is presented with the BearDB Shell Prompt:

BearDB>

The prompt indicates that the Shell is connected to the database and ready to accept any SQL queries.

To perform any database operations with BearDB Shell, you just type in SQL queries at the BearDB Shell Prompt and then type go when you are ready to execute the SQL query. Here is an example of a SELECT query with BearDB Shell:

BearDB> SELECT * FROM aTestTable
BearDB> go

aTestTable.autonumcol,aTestTable.col2,aTestTable.col3,aTestTable.col4
2,col2Data,4,4
1,column two data,column3 3 data as well,479.21

2 Rows.


When the user is done using BearDB Shell, just type quit to exit at any time.


Using the command-line client to import SQL scripts


It may be necessary to import sql scripts from a text file. BearDB supports SQL statements in text files which are delimited by a semicolon.
To import sql scripts from a text file, use the following syntax with the command-line client:

dbclient -u userOne -v m00k -s myDBServer -p 7169 -f "x:\path\subpath\sqlQueryFile.sql" -t tableName

Where:

    -u userOne: The username to access the server is userOne
    -v m00k: The passphrase for the user to access the server is m00k
    -s myDBServer: The name of the database server is myDBServer.
    -p 7169: The TCP-IP port that the server is configured to listen on.
    -f "x:\path\subpath\sqlQueryFile.sql": The local path to the SQL file, surrounded with double-quotes.

Using the command-line client to import data from a CSV file.


BearDB supports the loading of data into an existing table via a text file containing a comma-separated list of values.
dbclient -u dbUser -v m00k -s myDBServer -p 7169 -i "x:\path\subpath\csvfile.csv" -t tableName

Where:

    -u dbUser: The username to access the server is dbUser
    -v m00k: The passphrase for the user to access the server is m00k
    -s myDBServer: The name of the database server is myDBServer.
    -p 7169: The TCP-IP port that the server is configured to listen on.
    -i "x:\path\subpath\csvfile.csv": The local path to the CSV file, surrounded with double-quotes.
    -t tableName: The name of the existing table to load the CSV file data into.

The CSV file is required to contain the database table column names on the first row.
If the names are incorrect or are omitted when the import process will fail.
Here is the contents of a CSV file with three rows of data for the table TestTable:

userFirstName,userLastName,userAddress,userCity,userState,userZipCode
Fred,Flintstone,1234 Bedrock Road,Rockridge,Boulder,Colorado,44490
Mickey,Mouse,4579 Disney Way,Anahiem,California,95011
Homer,Simpson,742 Evergreen Tr.,Springfield,Nostate,10101


Here is the table definition of TestTable:

CREATE TABLE TestTable
(
   userFirstName  varchar(50) not null,
   userLastName  varchar(60) not null,
   userAddress  varchar(60) not null,
   userCity  varchar(30) not null,
   userState  varchar(30) not null,
   userZip  varchar(10) not null
)


The column names can be in any order in the CSV file.
The column names are case-sensitive and must be the same in the CSV file as they are in the table.