DbPrompt Free Universal Multi-Database SQL Prompt

DbPrompt can execute queries on multiple databases, transfer data between databases, upload result files on ftp servers, execute complex SQL scripts using Java Groovy and cron-schedule scripts for execution and report failures per email. DbPrompt supports all SQL and NoSQL databases, like MySql, Cassandra, PostgreSql, MongoDb, Redshift, SqlServer, Azure, Oracle, Teradata and more.

DbPrompt can work on all operating systems. DbPrompt is free of charge.


DbPrompt Multi-Database SQL Client

DbPrompt by Tasks

Task 1: Define Database Connections

First time you start DbPrompt will create a file init.sql in the user home directory .DbPrompt folder. The exact location will show in DbPrompt console. Edit this file to create database connections using this two commands:

  • register driver <rdbms> <driverClass> <URL> <"default_parameters">
  • connection <name> <rdbms> <"parameters">

Example:

register driver PostgreSql org.postgresql.Driver jdbc:postgresql://<host>:<port>/<db> "port=5432"

connection pos1 PostgreSql "user=postgres password=secret host=localhost db=sakila"

For each used Rdbms (MySql,Postgres,Oracle...) the register driver command defines the Java driver class and the JDBC URL with parameters as <...> tags. In the example above the default port value is passed in the end. This way of defining connections give more flexibility in managing large number of connections. In the advanced section you will see the benefits.

The connection command defines the connection by setting the real values for each URL parameter. The rdbms from the register driver command should match the rdbms from the connection command.

DbPrompt already include JDBC drivers for MySql, MariaDb, PostgreSQL, Oracle, SqlServer, Sqlite, Sybase, Teradata, Vertica, Derby, Exasol, Firebird, H2, HSql and Ingres. For the other databases you can download the driver using the command (rdbms is case sensitive):

download driver <rdbms>

The complete list of known drivers is the same as for DbSchema. Alternative download the JDBC driver .jar files by yourself and place them in the /home/users/.dbprompt/drivers/<rdbms>

Task 2: Execute Query on Multiple Databases

This operation assumes that the same table exists on each database you connect to. Also the connections pos1 and pos2 should be already defined.

connect pos1,pos2
SELECT * FROM sakila.city;

Group connections into groups.

connection group production pos1,pos2
connect production
SELECT * FROM sakila.city;
Spool result to a file.
connect production exclude my3
spool /tmp/result.csv
SELECT * FROM sakila.city;
spool off

Task 3: Upload Reports on [s]ftp Server

Produced .csv report files can be uploaded on ftp or sftp servers. The command is upload [-d] ftpURL fileName. -d states for removing the local file. We are using Apache Commons VFS and all protocols used there are supported. Example:
upload sftp://ftpusser@ftp.ftphost.com/intoFolder report.csv

Task 4: Use Variables

Variables can be defined using command vset. Few variables are system variables, used by DbPrompt. Any other user-defined variable will be replaced in SQL text if the system variable replace_variables=true: Use help to list all system variables. Execute show variables to list the already defined variables.
vset spool.separator=|
vset format.date=YYYY-MM-dd'T'hh:mm:ss'Z'
vset format.timestamp=YYYY-MM-dd'T'hh:mm:ss'Z'
vset replace_variables=true

vset myid=6230
connect db4
execute export.sql
Where the export.sql script is :
spool /tmp/export_&myid.csv
select * from some_table where id=&myid;

Task 5: Transfer Data Between Databases

Next command transfers data into the currently connected database from the specified databases using a reader query (executed on the from databases). The column names should match between the source and the target. There can be multiple source databases.
connect pos1
transfer into_table from pos2,pos3 using
select id, firstname, lastname from persons;

Task 6: Write Complex Scripts using Java Groovy

Many database tasks require more logic than simple SQLs. We do this using Groovy, a pure Java scripting language with closures. Groovy code can be started with the keyword groovy and ends with the first line with only a slash '/'.

Everything which works in Java works also in Groovy, with this three major improvements:

  • Multiline Strings (also called GString, started and end with """ )
  • Variables in GStrings, ${...} are replaced
  • Closures sql.eachRow(...){r->...} are similar with Java Lambda
Here is an example with Groovy:
connect pos1

groovy
int days = 5
sql.eachRow( """
    SELECT personid, firstname, lastname FROM persons p WHERE p.created < sysdate-${days} AND
      EXISTS ( SELECT 1 FROM sport_club s WHERE s.personid=p.personid )
      ORDER BY personid ASC
    """.toString() ) { r ->
    println "${r.personid} ${r.firstname} ${r.lastname}"
}
/
Groovy scripts receive two objects :

  • sql is the database connection as Sql object.
  • connector is the DbPrompt object for a defined connection

Task 7: Write Cronjob Scripts

Using DbPrompt you can execute database scripts on a regular basis with a minimal resolution of one hour. If a script is failing, an email will be sent to the configured emails.
vset email.server.host=internal.mailserver
vset email.server.user=admin
vset email.server.password=secret
vset email.from=DbPrompt
vset email.alert=admin@dbschema.com
vset cron.folder=/dbprompt/cronscripts
On the machine where DbPrompt is running, add in /etc/crontab this line, fixing fist the path to DbPrompt
01 * * * * /usr/local/bin/dbprompt -cron
                        
And in /dbprompt/cronscripts/ save files like
customer_report.daily0-24hour.sql
customer_report2.monthly1hour.sql
customer_report3.1day_of_week1hour.sql
DbPrompt is checking in the file names for this pattern in the end of the file name:
  • .<daily|weekly|monthly|yearly>[<number_or_range>hour].
  • .<number_or_range><day_of_week|day_of_month|day_of_year|month_of_year>[<number_or_range>hour]
If the scripts are getting any error, an email will be sent to the configured email server. Files can also have the .groovy extension. In this case the file content is interpreted as Groovy script.

Advanced Features and Scripts

Need more scripts and features ? Check the advanced features page.

DbPrompt Support

For any issue or feature request please write us. Please write us also about any database-specific scripts you wrote, this will help us to improve the database specific management commands.

Navigation