Platforms to show: All Mac Windows Linux Cross-Platform
/Java/JavaDatabase/JavaDatabase MySQL with JavaInputStreamMBS
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 MySQL with JavaInputStreamMBS
This example is the version from Sun, 9th Mar 2013.
Project "JavaDatabase MySQL with JavaInputStreamMBS.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Delete"
Const kFileQuit = "&Quit"
Const kFileQuitShortcut = ""
End Class
Class Window1 Inherits Window
EventHandler Sub Open()
dim j as JavaConnectionMBS
dim d as JavaDatabaseMBS
dim r as JavaResultSetMBS
dim f as FolderItem
dim blob as JavaBlobMBS
dim clob as JavaClobMBS
f=SpecialFolder.desktop.Child("mysql-connector-java-5.1.6-bin.jar")
if not f.Exists then
MsgBox "Missing mysql 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
v=new JavaVMMBS(f)
d=new JavaDatabaseMBS(v,"com.mysql.jdbc.Driver")
j=d.getConnection("jdbc:mysql://localhost:3306", "root", "")
if j<>nil then
MsgBox "connected"
j.MyExecuteSQL "CREATE DATABASE JunkDB"
j.MyExecuteSQL "USE JunkDB"
j.MyExecuteSQL "DROP TABLE myTable"
j.MyExecuteSQL "CREATE TABLE myTable(test_id int, data LONGBLOB, text LONGTEXT, PRIMARY KEY (test_id))"
j.MyExecuteSQL "INSERT INTO myTable(test_id, text, data) VALUES(1,'One','data1')"
j.MyExecuteSQL "INSERT INTO myTable(test_id, text, data) VALUES(2,'Two','data2')"
j.MyExecuteSQL "INSERT INTO myTable(test_id, text, data) VALUES(3,'Three','data3')"
j.MyExecuteSQL "INSERT INTO myTable(test_id, text, data) VALUES(4,'Four','data4')"
j.MyExecuteSQL "INSERT INTO myTable(test_id, text, data) VALUES(5,'Five','data5')"
// check second entry
r=j.MySelectSQL("SELECT * from myTable")
if r<>Nil then
DisplayMetaData r
if r.absolute(2) then
MsgBox str(R.getInt("test_id"))+" "+r.getString("text")+" "+r.getString("data")
clob=r.getClob("text")
if clob=nil then
MsgBox "clob=nil"
Return
end if
// try input stream
dim ips as JavaInputStreamMBS = r.getBinaryStream("data")
// check how much is available
dim a as integer = ips.available
// make new array in required size
dim byteArray as JavaByteArrayMBS = v.NewByteArray(a)
// read
dim count as integer = ips.read(byteArray)
// make memroyblock
dim mem as MemoryBlock = byteArray.Region(0, byteArray.Length)
// and convert to string
dim dstr as string = mem
dstr = DefineEncoding(dstr, encodings.ASCII)
MsgBox dstr // shows data2
break
end if
end if
r=nil
r=nil
else
MsgBox "not connected"
end if
End EventHandler
Sub DisplayMetaData(r as javaresultSetMBS)
dim m as JavaResultSetMetaDataMBS
m=r.getMetaData
dim i,c as integer
dim s(-1) as string
c=m.getColumnCount
for i=1 to c
s.Append m.getColumnName(i)+" as "+m.getColumnTypeName(i)
next
MsgBox Join(s,EndOfLine)
End Sub
Property v 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
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 SQLite
- /Java/JavaDatabase/JavaDatabase SQLite blob
- /Java/JavaDatabase/JavaDatabase to JSON
The items on this page are in the following plugins: MBS Java Plugin.