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:

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


The biggest plugin in space...