The Oracle Lite Blog

The Rekounas Blog about Oracle's Mobile Enterprise Application Platform

The Oracle Lite Blog header image 2

Getting started with Berkeley DB and Oracle Mobile Server

August 26th, 2010 · 16 Comments

In this post, I will try to give a quick overview of setting up Berkeley DB to Sync with Mobile Server. It is important to note that we will be overriding the SQLite deployment.

Step 1: Building the Berkeley DB
Here is the document to get you started.

Step 2: Rename Binaries
Once you have built the binaries (libdb_sql50.dll and dbsql.exe) rename those files to sqlite3.dll and sqlite3.exe.

Step 3: Copy File to the Mobile Server
Copy those files to the Mobile Server under the following path:
$ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/setup/common/win32/sqlite

Modify the following file:
$ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/setup/dmc/common/sqlite_win32.inf

Add the following entry that is in bold lettering:

<item>
<src>/common/win32/sqlite/sqlite3.dll</src>
<des>$APP_DIR$\bin\sqlite3.dll</des>
</item>
<item>
<src>/common/win32/sqlite/sqlite3.exe</src>
<des>$APP_DIR$\bin\sqlite3.exe</des>
</item>

This is optional.  The sqlite3.exe is equivalent to msql or sql*plus.

You should now be able to run the setup.exe for the SQLite Win32 and the client will be set up for Berkeley DB SQL API.

If you perform a sync with the SQLite client, your directory structure will look something like this:

08/26/2010 01:25 PM 41,984 default.db
08/26/2010 08:42 PM 67,584 fod.db
08/26/2010 08:42 PM 20,480 OSE$default.db
08/26/2010 08:42 PM 34,816 OSE$fod.db

By using the Berkeley DB SQL API, your structure will differ by having <database>-journal folders.

08/26/2010 09:44 PM 32,768 default.db
08/26/2010 09:44 PM <DIR> default.db-journal
08/26/2010 09:44 PM 32,768 fod.db
08/26/2010 09:44 PM <DIR> fod.db-journal
08/26/2010 09:44 PM 32,768 OSE$default.db
08/26/2010 09:44 PM <DIR> OSE$default.db-journal
08/26/2010 09:44 PM 32,768 OSE$fod.db
08/26/2010 09:44 PM <DIR> OSE$fod.db-journal

For a lot of the Oracle SQL Developers, some things you will need to brush up on while using the new SQLite environment. Lack of Stored Procedures… but since Berkeley DB source code is availble as open source, a developer should be able to add functions to be able to perform the same tasks. SQLite doesn’t support sequences in the same way Oracle supports sequences. So, to have the ability to sync your sequences to the client, you will need to performance the following sql statements to keep your client and server in sync for sequences.

To get the nextval of a sequence
SELECT IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
FROM C$SEQ_CLIENTS
WHERE NAME = 'MY_SEQ';

To update the sequence with the current value
UPDATE C$SEQ_CLIENTS
SET CUR_VALUE = IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
WHERE NAME = 'MY_SEQ';

Other differences are obvious ones like SQL functions. As I do a little bit more development with Berkeley DB, I will try to benchmark it against the SQLite flavour and the Oracle Lite database to see how everything stacks up.

Share

Tags: Berkeley DB · Oracle Lite · SQLite

16 responses so far ↓

  • 1 Marc Charpentier wrote on Sep 3, 2010 at 11:28 pm

    Hi Greg,
    The Path for the step 3 is not correct, if you want to download these files using the setup.exe, you have to use this Path:
    $ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/setup/dmc/common/sqlite_win32.inf

    $ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/setup/common/win32/sqlite

    Very Similar but not the same.

    I tested it and msync fails with:
    “ERROR”,-12044,”09/03/10 17:07:38″,”oseException(-12044): Could not find plugin library ”ospsqlite””,”MARC”

    Maybe I didn’t compile the BDB library properly as it does not fike the ospsqlite.

    Regards.
    Marc


  • 2 rekounas wrote on Sep 4, 2010 at 2:28 am

    Hi Marc,

    Did you try creating a regular database?

    Which download of BDB did you use? I tried the msi one first and I found it was incomplete. I then tried the zip one and had no issue with the compilation and it worked as expected.

    BTW, thanks for the correction on the path.


  • 3 gladson wrote on Mar 1, 2011 at 8:07 am

    Hi,
    I have Berkeley DB 11gR2 5.1.25 installled and able to use dbsql, can create database, tables and all others.

    I have Oracle Database Lite 10g has been installed
    Mobile Manager
    Mobile Server: earl0145:443

    How do I link Berkeley DB and Database Lite?

    Any help would be greatly appreciated.

    Thanks,
    Gladson


  • 4 gladson wrote on Mar 1, 2011 at 8:08 am

    and my OS is windows XP


  • 5 rekounas wrote on Mar 1, 2011 at 8:32 am

    Hi Gladson,

    Step 3 of this post outlines the things that you have to do to Mobile Server to replace the sqlite binaries with Berkeley DB.

    Let me know exactly what you are getting hung up on.


  • 6 gladson wrote on Mar 1, 2011 at 1:30 pm

    Thank you so much for your response, Rekounas.

    I have installed Mobile server, configured the repository on Oracle database. And also Installed Berkeley database, created a database so as created couple of tables.

    How do I connect the berkeley database to my handheld device (which takes/place an order) and
    how do I move the data from Brekeley DB to my Oracle database server.

    Once again thank you so much for your time.

    Gladson

    Thanks,
    Gladson


  • 7 rekounas wrote on Mar 1, 2011 at 3:10 pm

    Hi Gladson,

    Are you familiar with SQLite? Basically, all the connection information and drivers that you need to connect to a BDB database are the same as the ones you use to connect to SQLite.

    What mobile device are you working with?


  • 8 gladson wrote on Mar 2, 2011 at 5:01 am

    Hi Rekounas,

    I am looking for the information from you is, please
    the flow/setup

    handheld(mobile device) to berkeley database (both in windows xp a) and my oracle database server (b)

    how do I configure all three into the flow and what are the steps I need to take and what are the connection information and where I use

    Please let me know if you need more information

    The mobile device is I am using the one handheld, which takes(reports) an order from the warehouse

    Once again thanks for your time.
    Gladson


  • 9 rekounas wrote on Mar 2, 2011 at 6:55 am

    Ok… your question is pretty broad. Have you looked at the tutorials provided by Oracle?

    http://st-curriculum.oracle.com/obe/jdev/obe11jdev/ps1/mobile/mobile.htm

    That one uses JDeveloper and ADF on Blackberry and Windows Mobile devices.


  • 10 gladson wrote on Apr 6, 2011 at 10:25 am

    “You should now be able to run the setup.exe for the SQLite Win32 and the client will be set up for Berkeley DB SQL API.”

    This is from your publish, I could not get that, what exactly that setup.exe for the SQLite Win32 and where do I gte it(download) from

    Please give me some information about that line,

    thanks


  • 11 rekounas wrote on Apr 7, 2011 at 4:15 am

    Hi Gladson,

    You will need to go to http://yourserver/webtogo/setup. You should be able to find the available download clients from there.

    Good point on the setup file. I will include it in the original post.


  • 12 gladson wrote on Apr 7, 2011 at 4:57 am

    Thank you Rekounas. Yeah, I was able to figured it out and got struck with other error as

    ‘/common/win32/sqlite/sqlite3.exe’ not found in the server

    I am just trying to find the solution but if you know this issue, please let me knwo and as always thank you.


  • 13 rekounas wrote on Apr 7, 2011 at 7:34 am

    Does the file sqlite3.exe exist under the path
    $ORACLE_HOME/mobile_oc4j/j2ee/mobileserver/applications/mobileserver/setup/common/win32/sqlite


  • 14 Newguy wrote on Apr 27, 2011 at 8:53 am

    Hmmm, thanks for the OBE link, I had forgotten about it. At one time I had tried to peruse as many as possible. Gladson maybe wants to use BDB on his handheld ? And currently, it is not supported on all handhelds, for instance Blackberry uses Java ME, whereas BDB needs Java SE. So, this (main) POST is about setting up BDB on a laptop, desktop or something, by way of replacing SQLite necessary files with likewise properly compiled and parallel to the same BDB files, it seems. So, I think he is stuck with using SQLite or Oracle DB lite on the handheld isn’t he ? Could look at the Oracle Lite document for the Mobile Client and the doc that describes the single process workings.


  • 15 Newguy wrote on Apr 27, 2011 at 8:58 am

    Oh, just curious about the http://yourserver/webtogo/setup you mentioned. Are those precompiled clients for your platform of Oracle DB lite database engine itself, for say versions of windows, linux maybe ? How about for handhelds ? Since the handhelds come with some SQLite on them. I guess the http://yourserver/webtogo/setup. could have something, some ability to download to a particular handheld the mobile client items ?
    Must be in the Oracle docs somewhere.


  • 16 rekounas wrote on Apr 27, 2011 at 10:28 am

    Newguy, you are correct. For the mobile devices, at least for Android 2.2 or less anyhow, he would have to use SQLite. I think Android 2.3 allows for installation onto SD card thus allowing for installation of other products such as BDB. The other option is rooting the phone, that really isn’t a feasible option. But I do believe BDB will work on those mobile devices, I just don’t think sync will work with BDB unless the devices is rooted.


Leave a Comment

*