helpinghand
search
needassistance
 
How To
Obtaining additional diagnostics information in JDBC/ODBC
Categories: JDBC, ODBC, Programming Examples, SQL
Introduction

Mimer SQL version 9 includes (among other things) a JDBC and an ODBC driver. As of version 9.1 the JDBC driver implements the JDBC 2 specification while the ODBC driver implements the ODBC 3.51 API. Neither the JDBC- nor the ODBC-specification includes support for the more advanced diagnostics information available in SQL-99. This is somewhat unfortunate since the Mimer SQL server, which complies to SQL-99, can provide information on which trigger that failed, if any. Or which routine that failed or which integrity constraint that was violated.

Diagnostics attributes for this kind of information is indeed specified in the SQL Call Level Interface (SQL/CLI-99) and in the SQL Object-Language Bindings (SQL/OLB-99) for C and Java interfaces respectively, but they are not accessible through the JDBC and ODBC interfaces. Future Mimer SQL releases will implement features beyond the JDBC and ODBC specifications to be in par with the SQL-99 standard.

This document discusses how JDBC and ODBC programmers can obtain this kind of information using the JDBC and ODBC interfaces currently available.

Note! The Embedded SQL interfaces for C, Cobol and Fortran fully support SQL 99 diagnostic fields through the GET DIAGNOSTICS statement.


Description

To obtain this diagnostics information from JDBC and ODBC you should use stored procedures. The trick is that all diagnostic fields are available in procedures. We can therefore embed a statement in a procedure that in turn can return the diagnostics information in an output parameter.

For example, the below procedure embeds an INSERT statement into a compound which includes an exception handler which in turn (if activated) retrieves the name of the constraint that was violated. It is returned in the last parameter.

create procedure UNSUP_DEMO.INSERT_FINE (in PVEHICLE char(6),
in PDRIVER integer,
in PAMOUNT integer,
in PPAYMENT char(4),
out PINFO varchar(30))
modifies sql data
begin
declare exit handler for sqlexception
begin
get diagnostics exception 1 PINFO = constraint_name;
end;

insert into UNSUP_DEMO.FINES values (current_timestamp,
PVEHICLE,
PDRIVER,
PAMOUNT,
PPAYMENT);
set PINFO = '';
end

Example

A code sample!

The attached C example uses ODBC to obtain information on which integrity constraint that was violated upon an INSERT. There are several other useful diagnostic fields that may be seen in the SQL Reference part of the Mimer SQL Documentation Set.

Note! The supplied makefile works only with Microsoft Visual Studio.

By reading the 'schema.sql' script into BSQL the database is created. Use the READ command:
SQL> read 'schema.sql';

For our sample application we have three tables full with check constraints and referential integrity. So, if the INSERT fails, it is particularly messy to find out which constraint that was violated.

The example also shows how a database system can keep and maintain data quality in a centralized place, the database server, and leave to the presentation layer to tell the user, in a human readable form, what went wrong.

The below is a transcript of a session with the sample application. The application is supposed to register fines on cars and drivers. The first attempt runs fine, a fine of 2 euros paid in cash is registered to driver licence 1 with the car registration number NAA544. The other attempts fail one way or the other. A more advanced application may do something more useful with the name of the violated constraint. This application merely displays it on standard output.

c:\> sample
Vehicle registration number: NAA544
Driver SSN: 1
Euros: 2
Payment method: CASH
Again? (yes)
Vehicle registration number: NAA
Driver SSN: 1
Euros: 2
Payment method: CASH
Constraint VEHICLE_EXISTS was violated.
Again? (yes)
Vehicle registration number: NAA544
Driver SSN: 238947
Euros: 50
Payment method: CASH
Constraint DRIVER_EXISTS was violated.
Again? (yes)
Vehicle registration number: NAA544
Driver SSN: 1
Euros: -15
Payment method: CASH
Constraint AMOUNT_POS was violated.
Again? (yes)
Vehicle registration number: NAA544
Driver SSN: 1
Euros: 75
Payment method: DJKF
Constraint PAYMENT_VALID was violated.
Again? (yes)

Last updated: 2003-11-21

 

Powered by Mimer SQL

Powered by Mimer SQL