Platforms to show: All Mac Windows Linux Cross-Platform
/Java/JavaDatabase/JavaDatabase MySQL Blob and Clob
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 Blob and Clob
This example is the version from Sun, 17th Mar 2012.
Project "JavaDatabase MySQL Blob and Clob.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
blob=r.getBlob("data")
dim a as string = str(R.getInt("test_id"))
dim b as string =clob.getSubString(1,clob.length)
dim c as string =blob.getBytes(1,blob.length)
MsgBox a+" "+b+" "+c
end if
end if
r=nil
// edit records
r=j.MySelectSQL("SELECT data,text,test_id from myTable",true)
if r<>Nil then
while r.NextRecord
clob=r.getClob("text")
call clob.setString(3,"Hello Text "+str(r.getInt("test_id")))
MsgBox clob.getSubString(1,clob.length)
r.updateClob("text", clob)
blob=r.getBlob("data")
call blob.setBytes(1,"Hello World "+str(r.getInt("test_id")))
MsgBox blob.getBytes(1,blob.length)
r.updateBlob("data", blob)
r.updateRow
wend
end if
r=nil
dim lines(-1) as string
// check all rows
r=j.MySelectSQL("SELECT * from myTable")
if r<>Nil then
while r.NextRecord
lines.append str(R.getInt("test_id"))+" "+r.getString("text")+" "+r.getString("data")
wend
end if
MsgBox Join(lines,EndOfLine)
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 test
- /Java/JavaDatabase/JavaDatabase MySQL test with Threading
- /Java/JavaDatabase/JavaDatabase MySQL with JavaInputStreamMBS
- /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.