Platforms to show: All Mac Windows Linux Cross-Platform

/Java/JavaDatabase/JDBCMultipleDrivers


Required plugins for this example: MBS Java Plugin

You find this example project in your Plugins Download as a Xojo project file within the examples folder: /Java/JavaDatabase/JDBCMultipleDrivers

This example is the version from Thu, 6th Apr 2016.

Project "JDBCMultipleDrivers.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Delete"
Const kFileQuit = "&Quit"
Const kFileQuitShortcut = ""
EventHandler Sub Open() Dim JDBCDriver as FolderItem = FindFile("JDBCDriver") // Folder JDBCDriver is scanned looking for JAR files if JDBCDriver.Exists then GetJarList(JDBCDriver) end if // put in files like this: // // + JDBCDriver // + SQLite // sqlite-jdbc-3.7.2.jar // + MSAccess // ucanaccess-2.0.9.5.jar // jackcess-2.1.0.jar // hsqldb.jar // commons-logging-1.1.1.jar // commons-lang-2.6.jar // build list of jars Dim Libraries() as String Dim Sep as String #If TargetMacOS Then Sep = ":" #Else Sep = ";" #EndIf For Each Element as FolderItem in JarList dim path as string = Element.NativePathX Libraries.Append path Next // VM creation with all path of JAR files dim LibraryList as string = Join(Libraries, sep) VM = New JavaVMMBS(LibraryList) // now you can use databases End EventHandler
Sub GetJarList(Folder as FolderItem) For i as Integer = 1 To Folder.Count dim item as FolderItem = folder.TrueItem(i) if item.Directory Then GetJarList(item) elseif Right(Folder.Item(i).Name, 3) = "JAR" Then JarList.Append item end if Next End Sub
Note "About"
This is an example showing how to use JavaDatabaseMBS class with several JDBC drivers. based on work from Pietro Beccegato
Property JarList() As FolderItem
Property VM As JavaVMMBS
End Class
MenuBar MenuBar1
MenuItem FileMenu = "&File"
MenuItem FileQuit = "#App.kFileQuit"
MenuItem EditMenu = "&Edit"
MenuItem EditUndo = "&Undo"
MenuItem UntitledMenu1 = "-"
MenuItem EditCut = "Cu&t"
MenuItem EditCopy = "&Copy"
MenuItem EditPaste = "&Paste"
MenuItem EditClear = "#App.kEditClear"
MenuItem UntitledMenu0 = "-"
MenuItem EditSelectAll = "Select &All"
End MenuBar
Class Window1 Inherits Window
Control PBSqlite Inherits PushButton
ControlInstance PBSqlite Inherits PushButton
EventHandler Sub Action() LoadDataFromSQLite End EventHandler
End Control
Control Listbox1 Inherits Listbox
ControlInstance Listbox1 Inherits Listbox
End Control
Control PBClearGrid Inherits PushButton
ControlInstance PBClearGrid Inherits PushButton
EventHandler Sub Action() Listbox1.DeleteAllRows End EventHandler
End Control
Control PBAccess Inherits PushButton
ControlInstance PBAccess Inherits PushButton
EventHandler Sub Action() LoadDataFromAccess End EventHandler
End Control
EventHandler Sub Open() 'Dim f as FolderItem 'Dim Library as String 'Dim Sep as String = ":" 'f = FindFile("").Child("sqlite-jdbc-3.7.2.jar") 'Library = Library + f.NativePath + Sep ' 'Dim AccessDriverFolder as FolderItem = FindFile("").Child("JdbcMsAccess") ' 'For i as Integer = 1 to AccessDriverFolder.Count 'f=AccessDriverFolder.Item(i) 'if Uppercase(Right(f.Name, 3))="JAR" Then 'Library = Library + f.NativePath + Sep 'End If 'Next ' 'v = New JavaVMMBS(Library) End EventHandler
Sub LoadDataFromAccess() Listbox1.DeleteAllRows dim d as new JavaDatabaseMBS(App.VM,"net.ucanaccess.jdbc.UcanaccessDriver") Dim DbFile as FolderItem = FindFile("nwind.mdb") dim j as JavaConnectionMBS = d.getConnection("jdbc:ucanaccess://"+DbFile.NativePathX) if j<>Nil then // check all rows dim r as JavaResultSetMBS = j.MySelectSQL("Select * From Customers") if r<>Nil then while r.NextRecord Listbox1.AddRow(r.getString("CompanyName")) wend end if else MsgBox "not connected" end if Exception e as JavaExceptionMBS MsgBox e.message+" errorcode: "+str(e.ErrorNumber) End Sub
Sub LoadDataFromSQLite() Listbox1.DeleteAllRows dim d as new JavaDatabaseMBS(App.VM,"org.sqlite.JDBC") Dim DbFile as FolderItem = FindFile("test.sqlite") dim j as JavaConnectionMBS = d.getConnection("jdbc:sqlite:"+DbFile.NativePathX) if j<>Nil then // check all rows dim r as JavaResultSetMBS = j.MySelectSQL("Select * From Requests") if r<>Nil then while r.NextRecord Listbox1.AddRow(r.getString(1)) wend end if else MsgBox "not connected" end if Exception e as JavaExceptionMBS MsgBox e.message+" errorcode: "+str(e.ErrorNumber) End Sub
End Class
Module JavaUtil
Sub ExecuteSQL(extends c as JavaConnectionMBS, sql as string) try dim s as JavaStatementMBS s=c.createStatement if s<>nil then call s.executeUpdate sql end if catch d as JavaExceptionMBS MsgBox d.message+" ErrorCode: "+str(d.errornumber) end try End Sub
Sub MyExecuteSQL(extends j as javaconnectionMBS, sql as string) try j.ExecuteSQL sql catch d as JavaExceptionMBS MsgBox d.message+" ErrorCode: "+str(d.errornumber) end try End Sub
Sub MyExecuteSQLwithPreparedStatement(extends j as javaconnectionMBS, sql as string) try dim p as JavaPreparedStatementMBS p=j.prepareStatement(sql) if p<>Nil then call p.execute end if catch d as JavaExceptionMBS MsgBox d.message+" ErrorCode: "+str(d.errornumber) end try End Sub
Function MySelectSQL(extends j as javaconnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS try return j.SelectSQL(sql,editable) catch d as JavaExceptionMBS MsgBox d.message end try End Function
Function MySelectSQLwithPreparedStatement(extends j as javaconnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS try dim p as JavaPreparedStatementMBS p=j.prepareStatement(sql) if p<>Nil then dim r as JavaResultSetMBS r=p.executeQuery r.Tag=p // keep a reference to the statement Return r end if catch d as JavaExceptionMBS MsgBox d.message end try End Function
Function SelectSQL(extends c as JavaConnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS try dim mode as integer = c.CONCUR_READ_ONLY dim s as JavaStatementMBS s=c.createStatement(c.TYPE_FORWARD_ONLY, mode) if s<>nil then dim r as JavaResultSetMbs r=s.executeQuery(sql) if r<>Nil then // you need to keep the statement with the r.Tag=s Return r end if end if catch d as JavaExceptionMBS MsgBox d.message+" ErrorCode: "+str(d.errornumber) end try End Function
End Module
Module Module1
Function FindFile(name as string) As FolderItem // Look for file in parent folders from executable on dim parent as FolderItem = app.ExecutableFile.Parent while parent<>Nil dim file as FolderItem = parent.Child(name) if file<>Nil and file.Exists then Return file end if parent = parent.Parent wend End Function
Function NativePathX(extends f as FolderItem) As string #if RBVersion >= 2013 then Return f.NativePath #else return f.UnixpathMBS #endif End Function
End Module
End Project

The items on this page are in the following plugins: MBS Java Plugin.


The biggest plugin in space...