Updated on 18th October 2021

Google drive have security requirements changed & you have to log on to your google account to download the file attached with the post. I’ve edited the code sections, so that you can copy the scripts from the post without downloading.

Hi guys

Recently we changed our age old SAGEM finger print machines with cheaper and affordable VIRDI devices & bit the bullet when it was about configuring UNIS Remote Manager software to our Oracle ERP database server.

Virdi openly states they prefer MSACCESS database and other databases (SQL and Oracle)are “supported”, you read it, supported by the CUSTOMER

The FAQ available with Virdi website must be the work of a GENIUS, I have never seen such a structured FAQ with any other products & a wonderful close button that is a page big itself!

Oracle database setup is done for Virdi UNIS Remote Manager 3.2.3.6

Make sure your UNIS software matches the above version or later.

Default installation source includes a folder called “Database”

and within the “Database” folder you will find Oracle, SQL Server folders

Inside the Oracle folder you should see another folder “SQL Script”, within which you will find the SQL file for creating Oracle database elements for the UNIS software. Virdi wants a DBA to carefully analyze the script, execute sets one after another…

I’ve split the SQL into two portions which you can download from here. Please execute the scripts like following

(Please note the attached SQL script for creating the user is not updated, if you are going to use the downloaded scripts, please update the script using the one provided below)

As System or a user with DBA

“UNIS_User.sql”

create tablespace unis_db
datafile 'unis_db.dbf' 
size 10M
autoextend on;

create user unisuser
identified by unisamho
default tablespace unis_db
temporary tablespace temp;

GRANT connect, resource to unisuser;

Please remove DBA privileges once after the initial data migration etc are over! (Useful when you are migrating from SQL to Oracle to carry out different DBA activities ;) )

Once the unisuser account created. Run the

“UNIS_Tables.sql” as unisuser

CREATE TABLE iUserFinger(
    L_UID int NOT NULL,
    L_IsWideChar int NULL,
    B_TextFIR blob NULL
);

ALTER TABLE iUserFinger ADD PRIMARY KEY ( L_UID    ) ;

CREATE TABLE tClientLog(
    C_EventTime char(14) NULL,
    L_LogonID int NULL,
    L_Type int NULL,
    L_Result int NULL
);

CREATE TABLE tTerminalLog(
    C_EventTime char(14) NULL,
    L_TID int NULL,
    L_Type int NULL
) ;
/*
CREATE TABLE tEnter(
    C_Date char(8) NOT NULL,
    C_Time char(6) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Card varchar(24) NULL,
    L_UserType int NULL,
    L_Mode int NULL,
    L_MatchingType int NULL,
    L_Result int NULL,
    L_IsPicture int NULL
);
*/

CREATE TABLE UNISUSER.TENTER
(
  C_Date          CHAR(8 BYTE)                NOT NULL,
  C_Time          CHAR(6 BYTE)                NOT NULL,
  L_TID             INTEGER                     NOT NULL,
  L_UID             INTEGER                     NOT NULL,
  C_Name          VARCHAR2(30 BYTE),
 C_Unique        VARCHAR2(20 BYTE),
  C_Office        VARCHAR2(30 BYTE),
  C_Post          VARCHAR2(30 BYTE),
  C_Card          VARCHAR2(24 BYTE),
  L_UserType      INTEGER,
  L_Mode          INTEGER,
  L_MatchingType  INTEGER,
  L_Result        INTEGER,
  L_IsPicture     INTEGER,
  L_Device        INTEGER,
  L_OverCount     INTEGER,
  C_Property      VARCHAR2(8 BYTE),
  L_JobCode       INTEGER,
  L_Etc           INTEGER,
  L_Trans         INTEGER,
  L_NvrChannel1   INTEGER,
  L_NvrChannel2   INTEGER,
  L_NvrChannel3   INTEGER,
  L_NvrChannel4   INTEGER
);

ALTER TABLE tEnter ADD PRIMARY KEY 
    (
        C_Date,
        C_Time,
        L_TID,
        L_UID
    )  ;


CREATE TABLE tAuditTerminal(
    C_EventTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_AdminID int NOT NULL,
    C_AdminName varchar(30) NULL,
    L_Type int NOT NULL,
    L_UserID int NOT NULL,
    C_UserName varchar(30) NULL
) ;


ALTER TABLE tAuditTerminal ADD PRIMARY KEY
    (
        C_EventTime, 
        L_TID,
        L_AdminID,
        L_Type,
        L_UserID  
    )  ;


CREATE TABLE tAuditServer(
    C_EventTime char(14) NOT NULL,
    L_LogonID int NOT NULL,
    L_Section int NULL,
    C_Target varchar(30) NULL,
    L_Process int NULL,
    L_Detail int NULL
) ;

CREATE TABLE cHoliday(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cHoliday ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE cOffice(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cOffice ADD PRIMARY KEY 
    (
        C_Code 
    )  ;

CREATE TABLE cPost(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cPost ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE cStaff(
    C_Code varchar(30) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE cStaff ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE iCantTerminal(
    L_UID int NULL,
    L_TID int NULL
) ;

CREATE TABLE iHoliday(
    C_Code char(4) NOT NULL,
    C_Holiday char(4) NULL,
    C_DayName varchar(30) NULL
) ;

CREATE TABLE iTerminalAdmin(
    L_TID int NOT NULL,
    L_UID int NOT NULL
) ;

ALTER TABLE iTerminalAdmin ADD PRIMARY KEY
    (
        L_TID, 
        L_UID 
    )  ;

CREATE TABLE tCmdDown(
    C_RegTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Time char(14) NULL,
    B_Data blob NULL,
    L_Retry int NULL
) ;

ALTER TABLE tCmdDown ADD PRIMARY KEY
    (
        C_RegTime,
        L_TID,
        L_UID
    )  ;

CREATE TABLE tMealType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE tMealType ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE tMoney(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Unit int NULL,
    L_Early int NULL,
    L_Basic int NULL,
    L_Over int NULL,
    L_Night int NULL,
    L_Holi int NULL
) ;

ALTER TABLE tMoney ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE tWorkType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_BasicDay char(8) NULL,
    L_Spin int NULL,
    C_WorkCode varchar(60) NULL,
    L_InoutMode int NULL,
    L_AutoIn int NULL,
    L_AutoOut int NULL,
    L_LateMargin int NULL,
    L_LackMargin int NULL,
    C_HoliCode char(4) NULL
) ;

ALTER TABLE tWorkType ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE iUserPicture(
    L_UID int NOT NULL,
    B_Picture blob NULL
) ;

ALTER TABLE iUserPicture ADD PRIMARY KEY
    (
        L_UID
    )  ;

CREATE TABLE tConfig(
    C_MasterPwd varchar(30) NULL,
    L_UniqueType int NULL,
    L_AutoDown int NULL,
    C_DownTime char(4) NULL,
    L_AutoUp int NULL,
    L_RegSameFp int NULL,
    L_FpNum int NULL,
    L_UidCipher int NULL,
    L_TidCipher int NULL,
    L_UniqueCipher int NULL,
    L_MinVID int NULL,
    L_MaxVID int NULL,
    L_tSockPort int NULL,
    L_PollTime int NULL,
    L_SaveMode int NULL,
        C_PicturePath varchar(255)
) ;

CREATE TABLE iTimezone(
    C_Code char(4) NOT NULL,
    C_Timezone char(8) NOT NULL
) ;

CREATE TABLE cTimezone(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NOT NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cTimezone ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessTime(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Holiday char(4) NULL,
    C_Sun char(4) NULL,
    C_Mon char(4) NULL,
    C_The char(4) NULL,
    C_Wed char(4) NULL,
    C_Thu char(4) NULL,
    C_Fri char(4) NULL,
    C_Sat char(4) NULL,
    C_Hol char(4) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessTime ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessArea(
    C_Code char(4) NOT NULL,
    L_TID int NOT NULL
) 
;
ALTER TABLE iAccessArea ADD PRIMARY KEY 
    (
        C_Code,
        L_TID
    )  ;

CREATE TABLE cAccessArea(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessArea ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessGroup(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessGroup ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessGroup(
    C_Code char(4) NOT NULL,
    L_Type int NOT NULL,
    C_AccessCode char(4) NOT NULL
) ;

CREATE TABLE tUser(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    L_Type int NULL,
    C_RegDate char(14) NULL,
    L_OptDateLimit int NULL,
    C_DateLimit char(16) NULL,
    L_AccessType int NULL,
    C_Password varchar(8) NULL,
    L_Identify int NULL,
    L_VerifyLevel int NULL,
    C_AccessGroup char(4) NULL,
    C_PassbackStatus char(4) NULL
) ;

ALTER TABLE tUser ADD PRIMARY KEY
    (
        L_ID 
    )  
;
CREATE TABLE tEmploye(
    L_UID int NOT NULL,
    C_IncludeDate char(8) NULL,
    C_RetiredDate char(8) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Staff varchar(30) NULL,
    C_Authority char(4) NULL,
    C_Work char(4) NULL,
    C_Money char(4) NULL,
    C_Meal char(4) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tEmploye ADD PRIMARY KEY
    (
        L_UID 
    )  ;

CREATE TABLE tVisitor(
    L_UID int NOT NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Target varchar(30) NULL,
    C_Goal varchar(255) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tVisitor ADD PRIMARY KEY
    (
        L_UID 
    )  ;

CREATE TABLE tVisited(
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_RegDate char(14) NULL,
    C_LastDate char(8) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

CREATE TABLE tTerminal(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    L_FnWork int NULL,
    L_FnMeal int NULL,
    L_FnSchool int NULL,
    C_Office varchar(30) NULL,
    C_Place varchar(255) NULL,
    C_RegDate char(14) NULL,
    L_CommType int NULL,
    C_IPAddr varchar(255) NULL,
    L_IPPort int NULL,
    L_ComPort int NULL,
    L_Baudrate int NULL,
    L_Passback int NULL,
    C_AreaIn char(4) NULL,
    C_AreaOut char(4) NULL,
    C_lastup char(14) NULL,
    C_Version varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tTerminal ADD PRIMARY KEY
    (
        L_ID 
    )  ;

CREATE TABLE iCardInfo(
    L_CardSize int NULL,
    L_CardType int NULL,
    L_ReadType int NULL,
    L_TemplateSize int NULL,
    L_TemplateCount int NULL
) ;

CREATE TABLE iCardLayout(
    L_Index int NULL,
    L_Sector int NULL,
    L_Block int NULL,
    L_Start int NULL,
    L_Length int NULL,
    L_KeyType int NULL,
    C_KeyValue char(12) NULL
) 
;
CREATE TABLE iUserCard(
    C_CardNum varchar(24) NOT NULL,
    L_UID int NULL
) ;

ALTER TABLE iUserCard ADD PRIMARY KEY
    (
        C_CardNum 
    )  
;
CREATE TABLE cAuthority(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_SetLocal int NULL,
    L_RegInfo int NULL,
    L_DataBackup int NULL,
    L_MgrTerminal int NULL,
    L_RegControl int NULL,
    L_SetControl int NULL,
    L_RegEmploye int NULL,
    L_ModEmploye int NULL,
    L_OutEmploye int NULL,
    L_RegVisitor int NULL,
    L_OutVisitor int NULL,
    L_RegMoney int NULL,
    L_RegWork int NULL,
    L_SetWork int NULL,
    L_ModWork int NULL,
    L_RegMeal int NULL,
    L_SetMeal int NULL,
    L_ModMeal int NULL,
    L_DelResult int NULL,
    L_DelWork int NULL,
    L_DelMeal int NULL,
    L_MgrScope int NULL
) ;

ALTER TABLE cAuthority ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cPassback(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
);

ALTER TABLE cPassback ADD PRIMARY KEY
    (
        C_Code
    )  ;


CREATE TABLE tMapImage(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_FileName varchar(255) NULL,
    L_FileSize int NULL,
    B_FileData blob NULL
)
;
ALTER TABLE tMapImage ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iMapDrawing(
    C_MapCode char(4),
    L_PosX int NULL,
    L_PosY int NULL
);

CREATE TABLE iMapTerminal(
    C_MapCode char(4),
    L_TID int NULL,
    L_Type int NULL,
    L_PosX int NULL,
    L_PosY int NULL
);

Insert into tconfig values ('1',1,0,'0000',1,1,3,4,4,20,7000,9999,9870,10,0,'');
Insert into cAuthority values ('****', 'User', 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
Insert into cAuthority values ('1000', 'Department Admin', 0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0);
Insert into cAuthority values ('2000', 'Terminal Admin', 0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1);
Insert into cAuthority values ('3000', 'Branch Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
Insert into cAuthority values ('4000', 'All Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2);
Insert into cOffice values ('****', 'Unassigned');
Insert into cPost values ('****', 'Unassigned');
Insert into cStaff values ('****', 'Unassigned');
Insert into cAccessGroup values ('****', 'Unassigned',0,'');
Insert into cPassback values ('****', 'Unassigned','');
Insert into cTimezone values ('****', 'Unassigned', 1, '');
Insert into cHoliday values ('****', 'Unassigned', '');
Insert into tMoney values ('****', 'Unassigned', 0,0,0,0,0,0);
Insert into tMealType values ('****', 'Unassigned');
Insert into tWorkType values ('****', 'Unassigned', '', 0, '', 0,0,0,0,0,'');

If you use the Virdi provided Oracle SQL script, be sure you will not able to use the log monitoring screen as the tenter table misses many vital columns.

Sequence of Oracle software installation

Scenario #1

1. 64Bit Windows Server 2008 r2 or later with ONE STATIC IP address
2. Oracle 10g R2 64Bit (You can try 11g or 12c databases,provided you are pretty sure aware of the password complexity & expiry parts)
3. Oracle 11g 32Bit client, full administrator installation (UNIS Remote manager is strictly 32Bit software & if you don’t have Oracle client 32bit version installed, won’t able to communicate with the 64bit database). A hacked installation of Oracle 10g 31bit on Windows 10 will list the drivers under 32bit ODBC, however I strongly discourage using the driver (2006) as it is not at all compatible with Windows 10. If your business strictly needs to use Oracle 10g database, please make sure that you have either 11g or 12c 32bit client installed for the System DSN part.

As I clearly mentioned, the scripts I have provided are for UNIS remote manager version 3.2.3.6. In addition to it, I have modified the table creation script for tEnter table, which holds the attendance details as per the SQL table structure so that the log monitor part of UNIS remote manager could work. I don’t guarantee or answerable for any data loss that caused by slightly hacked scripts. If you are truly concerned about data safety and reliability, please contact Virdi for support (as if they care!)

(I have tried the same against a 12c 64bit installation, unfortunately, there are some errors which fail to fetch tables after the database opened. I need reconfirm it is nothing related to the case sensitive password or complex password related mess-ups)

Windows 10 has two entries for odbc with the control panel, while Windows 2008 R2 you have to startup the 32bit odbc from the following place (Assuming Windows is installed in default location)

C:\Windows\SysWOW64\odbcad32.exe

Restart the services (Incase if you setup the Oracle part once after the UNIS software was installed)

UNIS Software instalation

Install the software using setup & and no specific settings changes are necessary to make the software communicate with the Oracle database. Said, there are no settings available with the UNIS Remote Manager to setup the database. UNIS software looks for a system DSN with the names UNIS and UNIS_Temp and if connections could be initiated, works against the available database through the DSN(s)

Try to start UNIS Remote Manager software and if your configurations were correct in the place, should start without giving any errors

(Check the Master Logon, that will put “0000” in Admin ID and the default password for Admin account “0000” is “1”)

I have populated the Oracle table tenter with data extracted from SQL table in order to make sure that UNIS is communicating with Oracle database properly.