(INTERNAL/DRAFT) Example using third part JDBC library UCanAccess with IDL-Java Import Bridge to interact with Microsoft Access database file
Anonym
I was able to successfully use the IDL-Java Import Bridge with an open source Java JDBC Driver library called "UCanAccess". I obtained a distribution from the web site:
http://ucanaccess.sourceforge.net/site.html
If this is something that you can use, I have attached and example program ("ex_java_import_jdbc.pro") that illustrates the use of the IDL-Java import bridge to interact with a Access database file using the "UCanAccess" package.
The comments in the program file provides instructions about how set up your environment and then use the program.
In theory, IDL should be able to use other JDBC libraries to access other databases using a similar approach. Implementation details will vary based the library package that you are using.
;+
; :Description:
;
; Exelis VIS Technical Support example program illustrating the use
; of a third party JDBC library to access a Microsoft Access file
; (*.mdb) database from IDL, through the use of the IDL-Java Import
; Bridge functionality.
;
; This example utilizes "UCanAccess", an open source Java JDBC Driver
; implementation that (at the time of that this program was written)
; can be found at: "http://ucanaccess.sourceforge.net/site.html".
;
; =====================================================================
; NOTE:
; Use of the "UCanAccess" library with this programn is for illustrative
; purposes only and does not imply an endorsement of this library or
; any guarantee that this example will work correctly with third party
; software.
; =====================================================================
;
; An Example Java class is included in the UCanAccess library
; distribution. The source code version of the UCanAccess distribution
; includes the "Example.java" file which may be a useful to see examples
; of how to use the Java API for this library package. For example:
;
; UCanAccess-2.0.0-src\example\net\ucanaccess\example\Example.java
;
; The database access commands in his example are based on the Java
; commands used in the UCanAccess Example.jave file. This program was
; tested using UCanAccess 2.0.0 with 64-bit IDL 8.2.3 on Windows 7.
;
; :Dependencies:
;
; This example IDL program assumes:
;
; 1.) A compiled version of the UCanAccess library is available to the
; user of this program.
;
; 2.) The presence of an example *.mdb database file that has been
; created by running the UCanAccess Example.class Java program that is
; included with the compiled UCanAccess library distribution. See the
; UCanAccess Example.java file for database file contents details.
;
; For example, from the top level "UCanAccess-2.0.0-bin" directory of
; a UCanAccess binary distribution, depending on the directory location
; of your UCanAccess distribution, on Windows, you might run the
; provided Example program with 2 commands like this (where the first
; command is actually one very long line):
;
; set CLASSPATH=.;C:\Users\joeuser\lib\java\UCanAccess-2.0.0-bin
; \ucanaccess-2.0.0.jar;C:\Users\joeuser\lib\java\UCanAccess-2.0.0-bin
; \lib\commons-lang-2.4.jar;C:\Users\joeuser\lib\java\UCanAccess-2.0.0-bin
; \lib\commons-logging-1.0.4.jar;C:\Usersjoeuser\lib\java
; \UCanAccess-2.0.0-bin\lib\hsqldb.jar;C:\Users\joeuser\lib\java
; \UCanAccess-2.0.0-bin\lib\jackcess-2.0.1.jar
;
; java net.ucanaccess.example.Example C:\Users\joeuser\data\mytest.mdb
;
; 3.) A CLASSPATH configuration that references all of jar file
; dependencies for UCanAccess so that IDL will be able to utilize these
; files. For example:
;
; UCanAccess-2.0.0-bin\ucanaccess-2.0.0.jar
; UCanAccess-2.0.0-bin\lib\commons-lang-2.4.jar
; UCanAccess-2.0.0-bin\lib\commons-logging-1.0.4.jar
; UCanAccess-2.0.0-bin\lib\hsqldb.jar
; UCanAccess-2.0.0-bin\lib\jackcess-2.0.1.jar
;
; For CLASSPATH configuration tips, please see the "idljavabrc"
; configuration file comments for the section labeled:
;
; # Java CLASSPATH setting
;
; For example, for 64-bit IDL 8.2 on Windows, the default copy of the
; "idljavabrc" file is located at:
;
; C:\Program Files\Exelis\IDL82\resource\bridges\import\java\idljavabrc
;
; See also the "IDL Connectivity Bridges" help PDF document for IDL 8.2
; located with your installation at idl82/help/pdf/bridges.pdf. The
; section beginning on page 75 titled "Initializing the IDL-Java Bridge"
; provides additional information "idljavabrc" file and how to configure
; settings such as for the "JVM Classpath".
;
; 4.) Before running the program, modify the program below to define the
; variable:
;
; mdbfile
;
; to reflect a valid path and desired output file name destination in a
; writeable location on your system.
;
; :Author:
; Exelis Visual Information Solutions, Technical Support (ju, 27-nov-2013)
;-
PRO ex_java_import_jdbc
COMPILE_OPT idl2
; Instantiate a UCanAccessDriver object and a DriverManager object
jd = obj_new('IDLjavaObject$NET_UCANACCESS_JDBC_UCANACCESSDRIVER','net.ucanaccess.jdbc.UcanaccessDriver')
jdm = obj_new('IDLjavaObject$Static$JAVA_SQL_DRIVERMANAGER','java.sql.DriverManager')
; Name of the Access database file to access
mdbfile = 'C:\Users\joeuser\data\mytest.mdb'
mdbfile = 'C:\Users\jimu\Downloads\mydb.mdb'
; Change backslash directory separators to forward slashes
mdbfilemod = STRJOIN(STRSPLIT(mdbfile, '\', /EXTRACT), '/')
; Create a connection
jconn = jdm.getConnection('jdbc:ucanaccess://'+mdbfilemod)
; Create and execute a query statement
jst = jconn.createStatement()
jrs = jst.executeQuery("select descr from example2")
; Get and display data
WHILE jrs.next() DO BEGIN
jmeta = jrs.getMetaData()
ncols = jmeta.getColumnCount()
FOR i=1, ncols DO BEGIN
jobj = jrs.getObject(1) ; In this case, returns a java.lang.String object
;HELP, /STRUCTURES, jobj
PRINT, jobj.toString() ; Get string scalar value from Java Atring object
ENDFOR
ENDWHILE
jst.close
END
==========================
IDL> j=obj_new('IDLjavaObject$NET_UCANACCESS_JDBC_UCANACCESSDRIVER','net.ucanacc
ess.jdbc.UcanaccessDriver')
IDL> jdm = obj_new('IDLjavaObject$Static$JAVA_SQL_DRIVERMANAGER','java.sql.Drive
rManager')
IDL> jconn = jdm.getConnection('jdbc:ucanaccess://C:/Users/jimu/Desktop/junk/Fak
eTest.mdb')
IDL> jst = jconn.createStatement()
IDL> jrs = jst.execute("CREATE TABLE example1 (id COUNTER PRIMARY KEY,descr tex
t(400), number numeric(12,3), date0 datetime) ")
IDL> jst.close
IDL> jst = jconn.createStatement()
IDL> jrs = jst.execute("CREATE TABLE example2 (id COUNTER PRIMARY KEY,descr tex
t(400))")
IDL> jst.close
IDL> jst = jconn.createStatement()
IDL> jrs = jst.executeQuery("select descr from example2 where descr like 'P%'")
!!!! see below
>>>>>
==============================
set CLASSPATH=.;C:\Users\jimu\Downloads\UCanAccess-2.0.0-bin\ucanaccess-2.0.0.jar;C:\Users\jimu\Downloads\UCanAccess-2.0.0-bin\lib\commons-lang-2.4.jar;C:\Users\jimu\Downloads\UCanAccess-2.0.0-bin\lib\commons-logging-1.0.4.jar;C:\Users\jimu\Downloads\UCanAccess-2.0.0-bin\lib\hsqldb.jar;C:\Users\jimu\Downloads\UCanAccess-2.0.0-bin\lib\jackcess-2.0.1.jar
java net.ucanaccess.example.Example C:\Users\jimu\Downloads\mydb.mdb
"C:\Program Files\exelis\idl82\bin\bin.x86_64\idl.exe"
IDL Version 8.2.3, Microsoft Windows (Win32 x86_64 m64). (c) 2013, Exelis Visual
Information Solutions, Inc.
Trial version expires on 31-jan-2014.
Licensed for personal use by ITTVISJim Uba only.
All other use is strictly prohibited.
IDL> jd=obj_new('IDLjavaObject$NET_UCANACCESS_JDBC.UCANACCESSDRIVER','net.ucanaccess.jdbc.UcanaccessDriver')
IDL> jdm = obj_new('IDLjavaObject$Static$JAVA_SQL_DRIVERMANAGER','java.sql.DriverManager')
IDL> jconn = jdm.getConnection('jdbc:ucanaccess://C:/Users/jimu/Downloads\mytest.mdb')
IDL> jst = jconn.createStatement()
IDL> jrs = jst.executeQuery("select descr from example2 where descr like 'P%'")
IDL> jrs.next
IDL> jobj = jrs.getObject(1)
IDL> help,/struct,jobj
** Object IDLJAVAOBJECT$JAVA_LANG_STRING, 4 tags, length=40, data length=40, heap_id=6, refcount=2:
IDL_JBJAVAOBJ_TOP
LONG64 0
JAVAOBJ_ID STRING 'IDLJAVAOBJECT$java_lang_String'
JB_HJAVAOBJ LONG64 63149696
IDL_JBJAVAOBJ_BOTTOM
LONG64 0
IDL> print,jobj.toString()
PB123
IDL> print,jobj.tochararray()
80 66 49 50 51
IDL>