Neil Stockton Neil Stockton - 1 year ago 46
SQL Question

Any way to check for the existence of a SEQUENCE using JDBC?

I need to programmatically generate sequences in a datastore, but need to be able to detect their existence and not create if they are already there. Does anybody know of the JDBC metadata necessary to extract this information?

A cursory scan of DatabaseMetadata doesn't reveal a suitable method; I can get all tables/views and the associated keys/indexes etc but not sequences for that schema. Does anybody know of a way, preferably database-independent, but if not then for as many databases as possible (think oracle has a user_sequence table? but this is only one database and I need to support others).

Thanks in advance

Answer Source

There isn't any direct way as I am aware of. Because each database has it's own way of generating/handling sequence. When it's sequence in Oracle, it's auto_incriment(not a sequence but close to it or achieve somewhat same results) in mysql, Identity Columns in SQL Server etc.

I would do somethign like this - You'll have to make an interface:

interface ISequenceChecker{ // or some name which suits you
    SequenceObject getSequence();
}

Implementation for different database/store (E.G for oracle given below):

public class OracleSequenceChecker implements ISequenceChecker{
   OracleSequenceObject getSequence(){
    // some jdbc or similar call
    // to get SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL
   }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download