DBLX Database Clients

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

There are five database clients available for DBLX:

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


The DBLX command-line client


DBLX 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 DBLX 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 xmldbclient.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 DBLX.
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 DBLX command-line client:

dblxclient 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 DBLX you will get a specific error message letting you know the feature is not implemented. Please see the DBLX 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 the command-line client to import SQL scripts


It may be necessary to import sql scripts from a text file. DBLX 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.


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


DBLXClient3 for C# .NET


DBLX is delivered with a C# .NET API client. This client allows someone to make queries from a C# program and retrieve the results in a class instance that represents a data set, or as a set of comma-separated values.

The C# client communicates with the DBLX database server using a TCP-IP port. Therefore the client can be located on any system that can communicate via a TCP-IP port with the database server.
To use the C# client in an application you will need to include the file DBLXClient3.dll as a reference in your C# application or library. The file comes with distribution of DBLX.

To use the C# client you need to reference the DBLXClient3.client.DBLXClient class in your project. Create an instance of the class with the correct user name, pass value, server name, and port number. Once this instance has been created it can be reused for multiple queries.


DBLXClient3 for Java


DBLX is delivered with a Java-based API client. This client allows someone to make queries from a java program and retrieve the results in a class instance that represents a data set, or as a set of comma-separated values.

The Java client communicates with the DBLX database server using a TCP-IP port. Therefore the Java client can be located on any system that can communicate via a TCP-IP port with the database server.
To use the Java client in an application you will need to include the file DBLXClient3.jar in your classpath. The file comes with distribution of DBLX.

To use the Java client client you need to reference the org.saladforkranch.dblxclient3.client.DBLXClient class in your project. Create an instance of the class with the correct user name, pass value, server name, and port number. Once this instance has been created it can be reused for multiple queries.


A code sample that executes a simple SELECT query using either DBLX client is as follows:

     DBLXClient dbClient = new DBLXClient(userName, passValue, "servername", 7169);
     DBLXRow[] dbRows = null;

     try
     {
          String sql = "SELECT * From myTable where col2='the value'";
          dbRows = dbClient.executeQuery(sql);
     }
     catch (DBLXAuthorizationException authex)      {
          // an error occurred authorizing the user
          System.out.println(authex.getMessage());
     }
     catch (DBLXConnectException connex)
     {
          // an error occurred connecting to, or communicating with the server
          System.out.println(connex.getMessage());
     }
     catch (DBLXSqlException sqlex)
     {
          // an error occurred processing the SQL statement
          System.out.println(sqlex.getMessage());
     }
     catch (DBLXInvalidOperationException invalidex)
     {
          // the SQL statement content did not match the method
          System.out.println(invalidex.getMessage());
     }

There are two types of return values that you can get as the result of a select query.
When you use the executeQuery() method you will get back an array of objects of type DBLXRow that represent the rows returned from a table or query.
When you use the executeQueryCSV() method you will get back a string object that represents the query result as a comma-separated set of values with the column names listed in the first row. The content of the string returned by the executeQueryCSV() method looks similar to the following:

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

    4 Rows.


DBLXjdbc JDBC Driver


The DBLX Database comes with a Type-4 JDBC driver for use in multithreaded Java applications.

The JDBC driver can also be used with a Database Management or SQL Query application to facilitate the administration of DBLX database instances.

Driver Libraries:
The following jar files comprise the DBLXjdbc driver:

    - DBLXClient3.jar
    - DBLXjdbc3.jar

Both files must be in the classpath for the JDBC driver to work properly.

Driver Connection String:
The driver connection string follows the jdbc convention:
prefix://[serverName]:[portNumber][;[parameter=value];[parameter=value]]

A sample of the DBLXjdbc driver connection string looks like this:
jdbc:dblx://serverOrIpAddr:7169;username=dbAdmin;password=changeMe00;encrypt=false

The DBLXjdbc driver only accepts these parameter values:
    - username: The database username or login
    - password: The password for the supplied username or login
    - encrypt: Client-to-server communications are encrypted if this is set to a value of 'true'. Any other value, or a lack of a value is defaulted to 'false' and communications will not be encrypted.

Driver Class:
The DBLXjdbc3 driver class is as follows:
   org.saladforkranch.dblxjdbc3.driver.DBLXDriver


The DBLX ADO.NET client


DBLX is delivered with a C#-based ADO.NET database client. This client allows someone to make queries from a C# program and retrieve the results in a class instance that represents a data set, or as a set of comma-separated values.

The ADO.NET client communicates with the DBLX database server using a TCP-IP port. Therefore the C# client can be located on any system that can communicate via a TCP-IP port with the database server.
To use the ADO.NET client in an application you will need to reference the DBLX3_ADO.dll libnrary. The file comes with distribution of DBLX.

The ADO.NET client depends on DBLXClient3.dll, which is included with a distribution of DBLX.


 
 

DBLX Database Clients