jpdbi

Owned by Kianusch Sayah Karadji
Last updated: Apr 18, 2010Version comment
5 min read

NAME

jpdbi - Display and optionally manipulate DCM4CHE Database entries.

SYNOPTICS

jpdbi [options] [<LastName> [<FirstName> [<Birthdate>]]]

DESCRIPTION

jpdbi will query DCM4CHE DB - via jdbc - so you will have access to the DCM4CHE data from command line and even if JBOSS ist not running.

If configured it will also allow updates/changes to the data. e.g. one can change the ISSUER on the PATIENT level both in the database and the DICOM Database BLOB.

jpdbi will display the entries beginning at the PATIENT level down to FILES/FILESYSTEM level.

PREREQUSITS

jpdbi needs following components:

  • dcm4che.jar and log4j.jar from the DCM4CHE.
  • commons-cli-1.2.jar from Apache.
  • a jdbc driver matching the database used.

OPTIONS

Help and Version

  • -v|--version
    display version information. More v's display more information.
  • -h|--help
    display extended help.
  • --jdbcurlhelp
    display information to the jdbc connection Url.
  • -U|--url <jdbcUrl>
    jdbcURL for the destination database to connect to.
  • --db <alias>
    JDBC-connect-url-alias as defined in properties-file.

Search Options

The first three optional command line arguments are defined as Firstname, Lastname and Birthdate. Those three can be combined with any other search option. Searching without any search criteria is not a good idea - specially for large archives - so it is deactivated. If you really want to display all patients, use a corresponding wildcard.

SQL-Wildcards are supported on most fields. Use % for a multi-character wildcard and _ for a single character wildcard.

  • -s|--studyiuid <studyiuid>
    study-IUID to search for.
  • -S|--seriesiuid <studyiuid>
    series-IUID to search for.
  • --issuer <issuer>
    patient issuer to search for.
  • --patid [<issuer>:]<patid>
    (issuer and) patient-ID to search for.
  • -d|--date <date>|<n>
    if study date is give as "YYYY-MM-DD" - this date is searched for - if a number from 0 to 999 is supplied, studies from the last <n> days is search for. You can also use "today" and "yesterday" as a date string.
  • -m|--modaliy <modality>
    modality to search for.
  • -q|--query <statement>
    extended query statement. (See corresponding section.)

Display Options

  • -F
    include fieldnames in output. Those names are only for orientation - and are not related to the DB columns nor to the dicom tags.
  • --aet
    include AE-Titles (source-aet, retreive-aet and external retreive-aet) in output.
  • --pks
    include DCM4CHE DB-primary keys in output.
  • --status
    include additional information, such as avaiability, status, file-size, etc.

Display Level Options

Default level to be displayed is PATIENT level - one can choose the desired level to display - the chosen level and all levels above will be included in the output.

  • --study-level
    display patient and study
  • --series-level
    display patient, study and series
  • --instance-level
    display patient, study, series and instances
  • --path [ --fs]
    display patient, study, series, instances and object-path. Optionally include filesystem group and PK.
  • -a|--all
    if selected - patients with missing studies (series or instances) are also displayed - otherwise those entries are not displayes.

Output Options

  • -O|--out <file> [-z|--gzip]
    output is redirected to (gezipp-ed) <file>
  • -E|--err <file>
    error/debug (stderr) is redirected to <file>
  • --debug
    debug output on stderr

Database / Dataset manipulation

Warning

The following option should probably not exist, since it makes it possible, to manipulate the DCM4CHE database and the dataset BLOB's. So use it with care - and be aware that you might break things.

Fields to be updated have to be configured in the jpdbi-properties file.

  • --update "<field>=<value>|REMOVE"
    updates the database and/or dataset of selected entries. If _REMOVE_ is supplied as thr value, the database entry is set to null and the corresponding tag ist removed from the dataset.
  • --count <n>
    As a security measure only one entry can be updated per call - if the selection returns more than one match a message is displayed showing the count that would be updated - but not update is performed. If your are absolutely sure, that you do want to update thoses entries, re-execute the command, suppling the number of updates with the --count option.

CONFIG FILES / SETUP

DATABASE LINK

jpdbi can be linked to various kinds of database types supported by jdbc.
For that, the corresponding correct jdbc-driver and jdbc-url has to be used (in the properties file and/or command line). Documenting different database setups goes beyond the scope of this document - but there is plenty of information regarding jdbc urls on the internet. For Oracle URL's try jpdbi --jdbcurlhelp.

PROPERITIES

jpdbi can be configured with the jpdbi.properites or the jdb.properties. Both files are located in the /etc directory.

Those file are java properties files.

Following configuration options can be set:

  • jdbc.driver e.g. jdbc.driver=oracle.jdbc.driver.OracleDriver
    will define the used JDBC driver.
  • jdbc.url e.g. jdbc.url=jdbc:oracle:thin:PACS/PACS@localhost:1521:PACS
    will define the jdbcurl used to connect to the databse. This can be overruled by the --url option.
  • jdbc.url.<ALIAS> e.g. jdbc.url.vza=jdbc:oracle:thin:PACS1/PACS1@pacsdb1:1521:PACS1
    You can have more than one ALIAS entries in your properties file - for each of your DCM4CHE DB instances. Those ALIAS-url's can be used by call --db <ALIAS> option.
  • pre214=true|false
    set it to true for DCM4CHE version 2.13.x and older.
  • update.<ALIAS>=<LEVEL>:<DBFIELD>:<DCMFIELD>:<TYPE>:<MULTI>
    define an update that can be used for thr --update option.
    • ALIAS to be used for the field part of the option
    • LEVEL to be used for the option - should one of PATIENT, STUDY, SERIES or INSTANCE
    • DBFILED Dtabase Field to be updated (must match LEVEL) - if only dataset should be updated, set this to NULL
    • DCMFILED Dicom-Tag to be updated (must match LEVEL) - if only database should be updated, set this to NULL. DCMFIELD can be given as a Dicom TagName or as a Dicom Tag ID (xNNNNNNNN).
    • TYPE currently ony VARCHAR is supported
    • MULTI can only be true or false - if set to true multiple results can be updated in one run, if set to false only one entry at the time can be updated.

Example

This is the supplied properties included in the standard installation:

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:PACS/PACS@localhost:1521:PACS
jdbc.url.vza=jdbc:oracle:thin:PACS1/PACS1@pacsdb1:1521:PACS1
pre214=false

!update.ALIAS=LEVEL:DBFIELD:DCMFIELD:TYPE:MULTI{t|f}
update.patname=PATIENT:PAT_NAME:PatientName:VARCHAR
update.issuer=PATIENT:PAT_ID_ISSUER:IssuerOfPatientID:VARCHAR:true
update.patsex=PATIENT:PAT_SEX:PatientSex:VARCHAR:false
update.birthdate=PATIENT:PAT_BIRTHDATE:0x00100030:VARCHAR:false
!
update.modality=SERIES:MODALITY:Modality:VARCHAR:true
!
update.patient.charset=PATIENT:none:SpecificCharacterSet:VARCHAR:true
update.study.charset=STUDY:none:SpecificCharacterSet:VARCHAR:true
update.series.charset=SERIES:none:SpecificCharacterSet:VARCHAR:true
update.instance.charset=INSTANCE:none:SpecificCharacterSet:VARCHAR:true
!admittingdiagnosesdescription
update.00081080=STUDY:none:x00081080:VARCHAR:false

EXTENDED QUERY

If you want to search for DCM4CHE database fields, not included in the standard search option, you can use --query option.

Simply use --query "<table>.<field>='<value>'".

    • --query "series.src_aet='DCM4CHE'" would search for DCM4CHE in src_aet field.

You can also use SQL wildcards.

    • --query "series.src_aet like '%CHE%'" would search for any src_aet containing the string CHE.

And you can combine queries

    • --query "series.src_aet like '%CHE%' or series.src_aet like '%DCM%'"
    • --query "series.src_aet like '%CHE%' and series.ext_retr_aet like '%DCM%'"

Look at the DCM4CHE documentation for the database layout.

SCRIPTS

One way to use jpdbi is to install the required libraries (see also PREREQUESITS) into a a directory, e.g. /opt/java/lib and use follwing script ...

#! /bin/sh

CP=$(echo /opt/java/lib/*.jar | sed -e "s/ /:/g")

java -cp ${CP} com.agfa.db.tools.Jpdbi "$@"

... but there are probably better ways to do this.

BUGS

This tool should not exists in first place - specialy the update part - whenever you an use the official DCM4CHE web interface.

Second, I do not know that much about DICOM, databases, and never learn java or even read a book about java programming - keep that in mind, if you look at the sourcecode - and when you use jpdbi.

This tool has mainly been testen with an oracle setup - specially the *update*s might not work with other databases.

AUTHOR

Kianusch Sayah Karadji kianusch+jpdbi@gmail.com

SEE ALSO

jdbexp is a generic command line tool to query (and update) database tables