Using Oracle Sequence

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Sunday, October 12, 2008PrintSubscribe
Using Oracle Sequence

Oracle database server provides support for sequences of unique values that are typically used to generate unique IDs for primary key columns or new records. Data Aquarium Framework and Aquarium Express are supporting automatic execution of static SQL statements just before a table record insertion takes place.

Consider the application created from HR demo schema that comes with Oracle database servers. The DEPARTMENTS table has a matching DEPARTMENTS_SEQ sequence that can be used to supply unique values for the primary key DEPARTMENT_ID.

Form createForm1 is displayed below. You can see that the primary key field is visible on the form and can be changed by user.

image

Let's modify the data controller descriptor ~/Controllers/HR_DEPARMENTS.xml to use the sequence for primary key column values.

The first step is a definition of command that executes just before a new record is inserted into database.

<command id="GetSequence" type="Text" event="Inserting">
  <text><![CDATA[select departments_seq.nextval from dual]]></text>
  <output>
    <fieldOutput fieldName="DEPARTMENT_ID"/>
  </output>
</command>

Attribute event tells the framework about the timing of execution, element text defines the actual SQL statement, and element fieldOutput directs the framework to copy the first column in the first row of command result set to the DEPARTMENT_ID field.

The second and last step of modification is to hide the DEPARMENT_ID field from user interface.

<view id="createForm1" type="Form" commandId="command1" 
  label="New Hr Departments">
  <headerText>Please fill this form and click OK button to create 
    a new hr departments record. Click Cancel to return to the 
    previous screen.</headerText>
  <categories>
    <category headerText="New Hr Departments">
      <description>Complete the form. Make sure to enter all 
        required fields.</description>
      <dataFields>
        <dataField fieldName="DEPARTMENT_ID" columns="15" hidden="true"/>
        <dataField fieldName="DEPARTMENT_NAME" columns="30" />
        <dataField fieldName="MANAGER_ID" aliasFieldName="MANAGER_LAST_NAME" />
        <dataField fieldName="LOCATION_ID" aliasFieldName="LOCATION_CITY" />
      </dataFields>
    </category>
  </categories>
</view>

Attribute hidden on the field DEPARMENT_ID does the job.

Here is how the form is changed.

image

The new department is displayed in the list below.

image