Xojo Conferences
MBSSep2018MunichDE
XDCMay2019MiamiUSA

Platforms to show: All Mac Windows Linux Cross-Platform

/Java/JavaDatabase/JavaDatabase SQLite
Function:
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/JavaDatabase SQLite
This example is the version from Sun, 9th Mar 2013.
Project "JavaDatabase SQLite.rbp"
Class App Inherits Application
Const kEditClear = "&Delete"
Const kFileQuit = "&Quit"
Const kFileQuitShortcut = ""
EventHandler Sub Open() dim j as JavaConnectionMBS dim d as JavaDatabaseMBS dim r as JavaResultSetMBS dim f as FolderItem=SpecialFolder.desktop.Child("sqlitejdbc-v056.jar") if not f.Exists then MsgBox "Missing sqlite connector classes!" Return end if if TargetLinux then // change path for your linux PC! JavaVMMBS.SetLibraryPath("/home/cs/jre1.6.0_05/lib/i386/client/libjvm.so") end if dim v as new JavaVMMBS(f) d=new JavaDatabaseMBS(v,"org.sqlite.JDBC") j=d.getConnection("jdbc:sqlite:test.db") if j<>Nil then // call j.prepareStatement("CREATE DATABASE JunkDB").execute j.MyExecuteSQL "DROP TABLE myTable" j.MyExecuteSQL "CREATE TABLE myTable(test_id int, test_val char(15) not null, PRIMARY KEY (test_id))" j.MyExecuteSQL "INSERT INTO myTable(test_id, test_val) VALUES(1,'One')" j.MyExecuteSQL "INSERT INTO myTable(test_id, test_val) VALUES(2,'Two')" j.MyExecuteSQL "INSERT INTO myTable(test_id, test_val) VALUES(3,'Three')" j.MyExecuteSQL "INSERT INTO myTable(test_id, test_val) VALUES(4,'Four')" j.MyExecuteSQLwithPreparedStatement "INSERT INTO myTable(test_id, test_val) VALUES(5,'Five')" // check second entry r=j.MySelectSQLwithPreparedStatement("SELECT * from myTable") if r<>Nil then MsgBox str(R.getInt("test_id"))+" "+r.getString("test_val") else MsgBox "r is nil" end if r=nil // check all rows r=j.MySelectSQL("SELECT * from myTable") if r<>Nil then while r.NextRecord MsgBox str(R.getInt("test_id"))+" "+r.getString("test_val") wend else MsgBox "r is nil" end if r=nil MsgBox "OK" else MsgBox "not connected" end if Exception e as JavaExceptionMBS MsgBox e.message+" errorcode: "+str(e.ErrorNumber) End EventHandler
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
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
End Project

See also:

Feedback, Comments & Corrections

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





Links
MBS FileMaker tutorial videos