I am working on a feature that would allow clients pick a unique identifier (ci_name). The code below is a generic version that gets expanded to the right sql depending on the vendor. Hopefully it makes sense.
#include "sql.h"
create table client_identification
(
ci_id TYPE_ID IDENTITY,
ci_name varchar(64) not null,
constraint ci_pk primary key (ci_name)
);
go
CREATE_SEQUENCE(ci_id)
There will be simple stored procedures for adding, retrieving, and deleting these user records. This will be used by several admins. This will not happen very frequently, but there is still a possibility that something will be added or deleted since the list was initially retrieved. I have not yet decided if I need to detect the case of a double delete, but the user name cannot be created twice - primary key constraint will object. I want to be able to detect this particular case and display something like: "you snooze - you loose." :) I would like to leverage the pk constraint instead of doing some extra sql gymnastics. So, how can I detect this case cleanly, so that it works in MS SQL 2008, Sybase, and Oracle? I hope to do better than catch a general ODBC exception and parse out the text and look for what Sybase, Oracle, and MSSQL would give me back.
Oracle is a little different. We actually prepend these variables to the Oracle version of stored procedures because they are not available otherwise:
Vret_val out number,
Vtran_count in out number,
Vmessage_count in out number,
Thanks. General helpful tips and comments are welcome, except for naming convention ones ( I do not have a choice here, plus I mangled the actual names a bit).
