CODEWITHSUNDEEP
javaderby
Matthias
Matthias

Reputation: 61

Apache Derby: Create SQL Dump with data

is there any easy way to create a complete SQL Dump from an apache Derby DB? Using the dblook tool, I managed to dump the database schema to a sql file, however there seems to be no way to get the data included.

Upvotes: 6

Views: 11335

Answers (3)

crocodile2u
crocodile2u

Reputation: 413

This unix shell script dumps the data of all tables in APP database, to ~/tmp/export/[TABLE].del files. The assumption here is that the database is in folder "db" in the current working directory and that derby is installed under ~/derby. So you see, it's pretty custom (built for my own specific needs) but easily adjustable. And no, it does NOT make SQL dumps, they will all be in derby CSV export format. However, you can use this format to import data into PostgreSQL with the COPY statement, or into MySQL with LOAD DATA INFILE. Hope this helps.

UPD: I put my derby scripts together in a docker image, enjoy! It has SQL dumps ;-)

#!/bin/sh

PATH=$PATH:$HOME/derby/bin
DIR="$HOME/tmp/export"

printf "connect 'jdbc:derby:db';\nshow tables in APP;" > show.sql

TABLES=$(ij show.sql | grep ^APP | awk '{print $2}' | sed 's/|//g')

echo $TABLES
for TABLE in $TABLES;
do      
        echo $TABLE
        printf "connect 'jdbc:derby:db';\nCALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'$TABLE','$DIR/$TABLE.del',null,null,null);" > export.sql
        ij export.sql
        if [ $? -ne "0" ]; then
                exit 1
        fi
done

Upvotes: 1

McDowell
McDowell

Reputation: 108879

Derby has procedures for bulk import/export. Here's an example of using the ij tool to export the APP.MESSAGES table:

>ij
ij version 10.3
ij> connect 'jdbc:derby:dbName';
ij> CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE('APP', 'MESSAGES',
                                  'myfile.del', null, null, 'UTF-8');
0 rows inserted/updated/deleted

See the Derby Tools and Utilities Guide for your version.

This is not in the format you're asking for. Beyond that, you may need to resort to external tools.

Upvotes: 7

Axel
Axel

Reputation: 14159

SquirrelSQL can create INSERT statements for your data. Would that work for you? I Don't know whether a library exists to do the same from within your application,

Upvotes: 0

Related Questions