The Oracle Lite Blog

The Rekounas Blog about Oracle's Mobile Enterprise Application Platform

The Oracle Lite Blog header image 2

How to create an auto increment field in Oracle Lite

October 24th, 2009 · 12 Comments

I bet you thought you could make an auto increment field in Oracle Lite the same way you make it in Oracle right? Well, you can’t do it the same way because Oracle Lite does not support PL/SQL. It does however support C, C++, C#, and Java stored procedures. We will review creating auto-increment fields in both the Enterprise and the Lite databases.

Lets review the way you create a auto-increment field in Oracle:

//Step 1:
CREATE TABLE TEST_TABLE (
COL1 NUMBER,
COL2 VARCHAR2(20),
CONSTRAINT TEST_TABLE_PK PRIMARY KEY (COL1)
);


//Step 2:
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1;


//Step 3:
CREATE TRIGGER TEST_TRIGGER
BEFORE INSERT ON TEST_TABLE
FOR EACH ROW
BEGIN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.COL1 FROM DUAL;
END;

And we are done.

Now here are the steps for doing it in Oracle Lite:

//Step 1: Create and Compile the Java Code.  Call the Java file JavaTrigger.java.  You may also have to compile using JDK 1.4 as even Oracle 10g is known not to support 1.5 yet.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaTrigger {
   public static void autoIncrement(Connection conn, int newNum[]) throws SQLException {
      try {
         Statement stmt = null;
         ResultSet rs = null;
         stmt = conn.createStatement();
         rs = stmt.executeQuery("SELECT TEST_SEQ.NEXTVAL FROM DUAL");
         if (rs.next()) {
            newNum[0] = rs.getInt(1);
         }
      } catch (SQLException e) {
         System.err.println(e.getMessage());
      }
   }
}


//Step 2: Load the file into the database
CREATE JAVA CLASS USING BFILE ('C:\OracleLiteJava\StoredProcedure\Sample\classes\','JavaTrigger.class');


//Step 3: Create a Stored Procedure in the database
CREATE OR REPLACE PROCEDURE AUTO_INCREMENT(NEW_ID IN OUT INT) AS LANGUAGE JAVA NAME 'JavaTrigger.autoIncrement(java.sql.Connection, int[])';


//Step 4:
CREATE OR REPLACE TRIGGER TEST_TABLE_INSERT_TRIG BEFORE INSERT ON TEST_TABLE FOR EACH ROW AUTO_INCREMENT( NEW.COL1);


//Working Example:
SQL> INSERT INTO TEST_TABLE( COL2) VALUES ('GREG');
1 row(s) created
SQL> DIR TEST_TABLE;
COL1 | COL2
-----+-----
6340 | GREG

For more documentation on Stored Procedures in Oracle Lite, check out the online documentation here.

Share

Tags: Oracle Lite

12 responses so far ↓

  • 1 the yukon wrote on Jan 7, 2010 at 1:29 pm

    Great tip on creating autoincrement fields! I am using this in my own code, but have run into a problem. My olite database synchs with a main database. How do I stop this trigger from executing when the insert is due to downloaded data from the main database?


  • 2 rekounas wrote on Jan 7, 2010 at 1:44 pm

    The answer to that would be to check if the primary key field has a value. If it is null, then execute the trigger. I can update the example to show this.

    Thanks for the comment.


  • 3 the yukon wrote on Jan 9, 2010 at 7:42 pm

    When the trigger is executed due to a synchronization (using msync) I get an “error in calling a Java method” error. I have confirmed that this is due to the Connection conn parameter on the autoIncrement method (by removing this parameter and all code utilizing the connection, the error disappears. If there is NO code in the autoIncrement method, but the Connection conn parameter is included, then the error occurs). Do you have an idea what the problem is and a possible solution?


  • 4 rekounas wrote on Jan 12, 2010 at 1:22 pm

    I will play with it. There is another way of doing this method and that is by getting the connection from a datasource. If you can do it inside the java method instead of passing the object in, then you may be able to add error handling, basically a try/catch that does returns you out of the method if you get any connection errors. I was able to get that method working in enterprise but I think I was getting the error that you are getting now. I will have to refresh and see what the exact issue was.


  • 5 the yukon wrote on Jan 12, 2010 at 4:34 pm

    Thanks for the reply, Rekounas.

    I had thought of doing what you suggested (creating the connection within the method instead of passing the Connection object as a parameter), but I decided not to go that way. I have several mobile workstations, each with its own Oracle Lite database and thus its own connection parameters (different system password on each because the system password is set by initial synch to be the same as the mobile server user’s password). I don’t want to code something unique to each individual mobile client if I can avoid that.

    I’ve posted my original question on this topic into your “Ask a question” section since we are getting a bit of back-and-forth on this topic. I appreciate your help with this!


  • 6 rekounas wrote on Jan 12, 2010 at 4:44 pm

    No problem Yukon.

    Actually, you don’t need the username and password for the connection.

    Here is an example of what I am talking about.

    http://www.oracle.com/technology/sample_code/tech/java/jsp/samples/javastoredprocedure/RoomsInformationProcedure.java.html


    connection = new OracleDriver().defaultConnection();

    The OracleDriver is loaded in the Enterprise database. I will have to do some digging and see if there is something for Oracle Lite.

    Here is a question for you. Does this happen on the initial sync? If it doesn’t, have you thought about using Queue Based Sync?


  • 7 the yukon wrote on Jan 13, 2010 at 10:01 am

    The problem does not happen on the initial sync. All the data from the main database is successfully downloaded to the oracle lite client. The error occurs on subsequent synchronizations. Does that indicate that the initial synch is NOT executing the triggers? If not, then why are the subsequent syncs calling the triggers? Do you have an example of Queue Based Sync and how I could make use of it to avoid this problem?


  • 8 the yukon wrote on Jan 13, 2010 at 1:58 pm

    Rekounas,
    I was going to try your suggestion of using “defaultConnection”, since this could give me a reasonable solution. However, I was unable to determine what/whether the equivalent to the OracleDriver class would be for Oracle Lite. I looked at the olite40.jar archive, but it doesn’t have an obvious equivalent to OracleDriver. Is this what you were alluding to in your comment, “I will have to do some digging…”.


  • 9 rekounas wrote on Jan 13, 2010 at 2:35 pm

    Hi Yukon,

    I haven’t had a chance to look at this today and I might not get a chance tomorrow either. I am working with Queue refresh items tomorrow, so I may be able to give you some guidance towards that solution.


  • 10 www.oracledba.in wrote on May 8, 2010 at 6:26 am

    i like your blog and your article.


  • 11 LastSherlene wrote on Jan 4, 2018 at 2:10 pm

    I see you don’t monetize your website, don’t waste your traffic, you
    can earn additional bucks every month because you’ve got
    high quality content. If you want to know how to make extra money,
    search for: Mertiso’s tips best adsense alternative


  • 12 BestMarlon wrote on Aug 13, 2018 at 3:15 pm

    I see you don’t monetize your blog, don’t waste your traffic, you can earn extra
    bucks every month. You can use the best adsense alternative
    for any type of website (they approve all websites), for more info simply search in gooogle: boorfe’s tips monetize your website


Leave a Comment

*