Platforms to show: All Mac Windows Linux Cross-Platform
/Java/JavaDatabase/JavaDatabase SQLite
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.xojo_binary_project"
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:
- /Java/JavaDatabase/JavaDatabase MDB
- /Java/JavaDatabase/JavaDatabase MySQL Blob and Clob
- /Java/JavaDatabase/JavaDatabase MySQL test
- /Java/JavaDatabase/JavaDatabase MySQL test with Threading
- /Java/JavaDatabase/JavaDatabase MySQL with JavaInputStreamMBS
- /Java/JavaDatabase/JavaDatabase SQLite blob
- /Java/JavaDatabase/JavaDatabase to JSON
The items on this page are in the following plugins: MBS Java Plugin.