how open cerod database using sqlite-jdbc drivers
Raj Kamal
Grace B
connection = DriverManager.getConnection("jdbc:sqlite::cerod:passwd:sample_cerod.db")
Regards,Kamal.
Raj Kamal
Raj Kamal
Grace B
Raj Kamal
Raj Kamal
Hi Grace,
I tried to build the JAR file with your change and also had to change below code in JDBC.java:
static String extractAddress(String url) {
// if no file name is given use a memory database
if( PREFIX.equalsIgnoreCase(url) )
return ":memory:" ;
else if(url.contains(PREFIX_CEROD))
return url.substring(PREFIX_CEROD.length());
else
return url.substring(PREFIX.length());
}
And used above modified SNAPSHOT.jar to test below code
try{
Properties sqliteProps = new Properties();
//PRAGMA activate_extensions='see-7bb07b8d471d642e';
sqliteProps.setProperty("activate_extensions", "7bb07b8d471d642e");
sqliteProps.setProperty("enable_load_extension", "7bb07b8d471d642e");
SQLiteConfig sqliteConfig = new SQLiteConfig(sqliteProps);
//sqliteConfig.setPragma("enable_load_extension", "true");
sqliteConfig.enableLoadExtension(true);
SQLiteDataSource sds = new SQLiteDataSource(sqliteConfig);
sds.setLoadExtension(true);
sds.setUrl( "jdbc:sqlite:cerod::abc.db.cerod");
con = (SQLiteConnection) sds.getConnection();
System.out.println("Successfully connected to CEROD db ["+sds.getUrl()+"] using Bitbucket sqlite JDBC Drivers version 3.7.15: ");
Statement statement = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
boolean loadedExtn = statement.execute("SELECT load_extension('libcalm-sqlite3.so','sqlite3CalmFtsInit')");
System.out.println("loadExtn returned: "+loadedExtn);
}catch(SQLException sqle){
System.out.println("found error: "+sqle);
}
It gave below output:
[root@ca-elm2 iTechnology]# java -cp ".:testSQLiteCEROD.jar:sqlite-jdbc-3.7.15-M1.jar " testSQLiteCEROD
KAMMU02: the props are: {open_mode=70, date_precision=MILLISECONDS, date_string_format=yyyy-MM-dd HH:mm:ss.SSS, date_class=INTEGER, transaction_mode=DEFFERED, enable_load_extension=true}
Successfully connected to CEROD db [jdbc:sqlite:cerod::abc.db.cerod] using Bitbucket sqlite JDBC Drivers version 3.7.15:
Select query of sqlite is: select * from sqlite_master;
found error: java.sql.SQLException: [SQLITE_NOTADB] File opened that is not a database file (file is encrypted or is not a database)
basically the extension loading mechanism seems to be working after the connection is established, but in my case, the extension should be already available in JAR/JNI environment to query the connection itself.
Could you please point me on how I can overcome this challenge ?
When I try to load the extension before querying sqlite_master table using below code:
SQLiteDataSource sds = new SQLiteDataSource(sqliteConfig);
sds.setLoadExtension(true);
sds.setUrl( "jdbc:sqlite:cerod::abc.db.cerod");
con = (SQLiteConnection) sds.getConnection();
boolean loadedExtn =
statement.execute("SELECT load_extension('libcalm-sqlite3.so','sqlite3CalmFtsInit')");
the JVM crashes with below crash log(/hs_err_pid27383.log)!!
Native frames: (J=compiled Java code, j=interpreted, Vv=VM code, C=native code)
C [libpthread.so.0+0x7c6d] pthread_mutex_lock+0x1d
C [libcalm-sqlite3.so+0x8ef22] short+0x1d
C [libcalm-sqlite3.so+0x2d4ee] short+0x20
C [libcalm-sqlite3.so+0x7c93d] short+0x80
C [libcalm-sqlite3.so+0x8e862] short+0x139
C [sqlite-3.7.15-i386-libsqlitejdbc.so+0x473b2] Java_org_sqlite_NativeDB_shared_1cache+0x41662
C [sqlite-3.7.15-i386-libsqlitejdbc.so+0x4759e] Java_org_sqlite_NativeDB_shared_1cache+0x4184e
C [sqlite-3.7.15-i386-libsqlitejdbc.so+0x6f492] Java_org_sqlite_NativeDB_shared_1cache+0x69742
C [sqlite-3.7.15-i386-libsqlitejdbc.so+0x5b524] Java_org_sqlite_NativeDB_shared_1cache+0x557d4
C [sqlite-3.7.15-i386-libsqlitejdbc.so+0x58e5] Java_org_sqlite_NativeDB_step+0x25
j org.sqlite.NativeDB.step(J)I+0
j org.sqlite.DB.execute(Lorg/sqlite/Stmt;[Ljava/lang/Object;)Z+102
j org.sqlite.Stmt.exec()Z+45
j org.sqlite.Stmt.execute(Ljava/lang/String;)Z+39
j testSQLiteCEROD.getCERODConnection()V+122
j testSQLiteCEROD.main([Ljava/lang/String;)V+0
looks like, creation of statement was not completely successful and executing a query with an invalid Statement reference is leading to this crash !
Below are some details of the CEROD extension that we use:
[root@ca-elm2 iTechnology]# nm -g libcalm-sqlite3.so |grep sqlite3CalmFtsInit
000000000007f5a0 T sqlite3CalmFtsInit
Our libcalm-sqlite3.so is compiled with following options:
gcc -DNDEBUG -Wall -pthread -fPIC -O3 -ggdb -m32 -D_IGLINUX -D_LINUX -DSQLITE_CORE -DSQLITE_DLL -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=0 -DSQLITE_HAS_CODEC=1 -DSQLITE_USE_RC4 -DTEMP_STORE=1 -DSQLITE_ENABLE_CEROD -DSQLITE_ENABLE_FTS3 -I/root /thirdparty/zlib/1.2.3/include -I../include -I../src/fts3 -I../src/calmfts3
our binary is created with following compiler arguments:
g++ -shared -z defs -z combreloc -fPIC -rdynamic -m32 *.o lib/release.linux_x86.gnu-4.1.2/libcalm-sqlite3.so
And it is statically loaded as done in main.c
/* Statically load calmfts2 module */
if( !db->mallocFailed && rc==SQLITE_OK ){
rc = sqlite3CalmFtsInit(db);
}
Our working CPP code opens a CEROD using
extern "C" void sqlite3_activate_cerod(const char*);
// Initialize the cerod extensions to Sqlite
sqlite3_activate_cerod("7bb07b8d471d642e");
rc = sqlite3_open(“:cerod::”+dbName.c_str(), &ppdb);
Grace B
diff --git a/Makefile b/Makefile--- a/Makefile+++ b/Makefile@@ -50,7 +50,8 @@perl -p -e "s/sqlite3_api;/sqlite3_api = 0;/g" \$(SQLITE_AMAL_DIR)/sqlite3ext.h > $(SQLITE_OUT)/sqlite3ext.h# insert a code for loading extension functions- perl -p -e "s/^opendb_out:/ if(!db->mallocFailed && rc==SQLITE_OK){ rc = RegisterExtensionFunctions(db); }\nopendb_out:/;" \+ perl -p -e "s/^opendb_out:/ if(!db->mallocFailed && rc==SQLITE_OK){ rc = RegisterExtensionFunctions(db); }\n \+ if(!db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3CalmFtsInit(db); }\nopendb_out:/;" \$(SQLITE_AMAL_DIR)/sqlite3.c > $(SQLITE_OUT)/sqlite3.ccat src/main/ext/*.c >> $(SQLITE_OUT)/sqlite3.c$(CC) -o $@ -c $(CFLAGS) \