Class App Inherits Application

Const kEditClear = "&Delete"

Const kFileQuit = "&Quit"

Const kFileQuitShortcut = ""

End Class

Class Window1 Inherits Window

EventHandler Sub Open() If DebugBuild Then run End If End EventHandler

Sub Run() CheckModule.init CheckModule.List = ListBox1 TestModule.run CheckModule.List = Nil Listbox1.AddRow OKCount.ToString+" tests okay and "+FailedCount.ToString+" failed." End Sub

End Class

MenuBar MainMenuBar

MenuItem FileMenu = "&File"

MenuItem FileQuit = "#App.kFileQuit"

MenuItem EditMenu = "&Edit"

MenuItem EditUndo = "&Undo"

MenuItem EditSeparator1 = "-"

MenuItem EditCut = "Cu&t"

MenuItem EditCopy = "&Copy"

MenuItem EditPaste = "&Paste"

MenuItem EditClear = "#App.kEditClear"

MenuItem EditSeparator2 = "-"

MenuItem EditSelectAll = "Select &All"

MenuItem HelpMenu = "&Help"

End MenuBar

Module CheckModule

Sub CheckFalse(value as Boolean, message as string) If value Then List.AddRow message FailedCount = FailedCount + 1 If DoBreak Then Break Else // okay OKCount = OKCount + 1 End If End Sub

Sub CheckTrue(value as Boolean, message as string) If value Then // okay OKCount = OKCount + 1 Else List.AddRow message FailedCount = FailedCount + 1 If DoBreak Then Break End If End Sub

Sub Init() OKCount = 0 FailedCount = 0 today = new date today.TotalSeconds = Round(today.TotalSeconds-0.5) // remove milliseconds End Sub

Property DoBreak As Boolean

Property FailedCount As Integer

Property List As listbox

Property OKCount As Integer

Property Today As Date

End Module

Sign

End Sign

Module TestModule

Sub Check1(db as SQLDatabaseMBS) // insert with Dictionary, SQLSelect with RecordSet // now add a row Dim mem As New MemoryBlock(8) mem.DoubleValue(0) = 123.456 Dim dic As New Dictionary dic.Value("FieldInt") = 123 dic.Value("FieldTimeStamp") = today dic.Value("FieldText") = "Täst" dic.Value("FieldDouble") = 123.456 dic.Value("FieldBLOB") = mem db.InsertRecord("TestTable", dic) CheckFalse db.Error, "Error should be false" // select check SelectCount db, 1 // select records Dim sql As String Dim r As RecordSet sql = "SELECT * from TestTable" r = db.SQLSelect(sql) CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.FieldCount = 5, "FieldCount should be 5" CheckFalse r.EOF, "EOF should be false." CheckTrue r.IdxField(1).name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.IdxField(2).name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.IdxField(3).name = "FieldText", "Field 3 should be FieldText." CheckTrue r.IdxField(4).name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.IdxField(5).name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim i As Integer = r.Field("FieldInt").IntegerValue Dim d As date = r.Field("FieldTimeStamp").dateValue Dim t As String = r.Field("FieldText").StringValue Dim v As Double = r.Field("FieldDouble").DoubleValue Dim m As String = r.Field("FieldBLOB").StringValue CheckTrue i = 123, "FieldInt should be 123" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Täst", "FieldText should be Täst" CheckTrue v = 123.456, "FieldDouble should be 123.456" CheckTrue m = mem, "FieldBLOB should be mem" t = r.Field("FieldDouble").StringValue CheckTrue t = "123.456", "FieldDouble should be 123.456" t = r.Field("FieldInt").StringValue CheckTrue t = "123", "FieldInt should be 123" t = r.Field("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today: "+t+" "+today.SQLDateTime r.MoveNext CheckFalse db.Error, "Error should be false" CheckTrue r.EOF, "EOF should be true." r = Nil End Sub

Sub Check2(db as SQLDatabaseMBS) // insert with Dictionary, SQLSelect with RecordSet using SQLPreparedStatementMBS // now add a row Dim dic As New Dictionary dic.Value("FieldInt") = -123 dic.Value("FieldTimeStamp") = today dic.Value("FieldText") = "" dic.Value("FieldDouble") = -123.456 dic.Value("FieldBLOB") = Nil db.InsertRecord("TestTable", dic) CheckFalse db.Error, "Error should be false" // select records Dim sql As String dim r as RecordSet sql = "SELECT * from TestTable where FieldInt = :FieldInt" Dim p As SQLPreparedStatementMBS = db.Prepare(sql) p.Bind("FieldInt", -123) CheckTrue p.BoundValues.Count = 1, "BoundValues.count should be 1" r = p.SQLSelect CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.FieldCount = 5, "FieldCount should be 5" CheckFalse r.EOF, "EOF should be false." CheckTrue r.IdxField(1).name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.IdxField(2).name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.IdxField(3).name = "FieldText", "Field 3 should be FieldText." CheckTrue r.IdxField(4).name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.IdxField(5).name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim i As Integer = r.Field("FieldInt").IntegerValue Dim d As date = r.Field("FieldTimeStamp").dateValue Dim t As String = r.Field("FieldText").StringValue Dim v As Double = r.Field("FieldDouble").DoubleValue Dim m As String = r.Field("FieldBLOB").StringValue CheckTrue i = -123, "FieldInt should be -123" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "", "FieldText should be Täst" CheckTrue v = -123.456, "FieldDouble should be -123.456" CheckTrue m = "", "FieldBLOB should be nil" t = r.Field("FieldDouble").StringValue CheckTrue t = "-123.456", "FieldDouble should be -123.456" t = r.Field("FieldInt").StringValue CheckTrue t = "-123", "FieldInt should be -123" t = r.Field("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" r.MoveNext CheckFalse db.Error, "Error should be false" CheckTrue r.EOF, "EOF should be true." r = Nil End Sub

Sub Check3(db as SQLDatabaseMBS) // insert with DatabaseRecord, SQLSelect with RecordSet // now add a row Dim record As New DatabaseRecord record.IntegerColumn("FieldInt") = 123456789 record.DateColumn("FieldTimeStamp") = today record.Column("FieldText") = "Hello World!" record.DoubleColumn("FieldDouble") = 123456789 record.BlobColumn("FieldBLOB") = ChrB(1) db.InsertRecord("TestTable", record) CheckFalse db.Error, "Error should be false" // select records Dim sql As String Dim r As RecordSet sql = "SELECT * from TestTable where FieldInt = 123456789" r = db.SQLSelect(sql) CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.FieldCount = 5, "FieldCount should be 5" CheckFalse r.EOF, "EOF should be false." CheckTrue r.Field("FieldInt").name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.Field("FieldTimeStamp").name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.Field("FieldText").name = "FieldText", "Field 3 should be FieldText." CheckTrue r.Field("FieldDouble").name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.Field("FieldBLOB").name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim i As Integer = r.Field("FieldInt").IntegerValue Dim d As date = r.Field("FieldTimeStamp").dateValue Dim t As String = r.Field("FieldText").StringValue Dim v As Double = r.Field("FieldDouble").DoubleValue Dim m As String = r.Field("FieldBLOB").StringValue CheckTrue i = 123456789, "FieldInt should be 123456789" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Hello World!", "FieldText should be Hello World" CheckTrue v = 123456789, "FieldDouble should be 123456789" CheckTrue m = ChrB(1), "FieldBLOB should be ChrB(1)" t = r.Field("FieldDouble").StringValue Dim n As Integer = Val(t) CheckTrue n = 123456789, "FieldDouble should be 123456789" t = r.Field("FieldInt").StringValue CheckTrue t = "123456789", "FieldInt should be 123456789" t = r.Field("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" r.MoveNext CheckFalse db.Error, "Error should be false" CheckTrue r.EOF, "EOF should be true." r = Nil End Sub

Sub Check4(db as SQLDatabaseMBS) // insert with DatabaseRow, SelectSQL with RowSet // now add a row Dim record As New DatabaseRow record.Column("FieldInt").IntegerValue = 5678 record.Column("FieldTimeStamp").DateValue = today record.Column("FieldText").StringValue = "Hello World!" record.Column("FieldDouble").DoubleValue = 5678 record.Column("FieldBLOB").BlobValue = ChrB(2) db.AddRow("TestTable", record) CheckFalse db.Error, "Error should be false" // select records Dim sql As String Dim r As RowSet sql = "SELECT * from TestTable where FieldInt = 5678" r = db.SelectSQL(sql) CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.ColumnCount = 5, "FieldCount should be 5" CheckTrue r.LastColumnIndex = 4, "LastColumnIndex should be 4" CheckTrue r.ColumnType(0) = 3, "ColumnType(0) should be 3 for integer: "+r.ColumnType(0).ToString CheckTrue r.ColumnType(1) = 10, "ColumnType(1) should be 10 for timestamp: "+r.ColumnType(1).ToString CheckTrue r.ColumnType(2) = 5, "ColumnType(2) should be 5 for text: "+r.ColumnType(2).ToString CheckTrue r.ColumnType(3) = 7, "ColumnType(3) should be 7 for double: "+r.ColumnType(3).ToString CheckTrue r.ColumnType(4) = 14, "ColumnType(4) should be 14 for binary: "+r.ColumnType(4).ToString CheckFalse r.AfterLastRow, "AfterLastRow should be false." CheckFalse r.BeforeFirstRow, "BeforeFirstRow should be false." CheckTrue r.ColumnAt(0).name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.ColumnAt(1).name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.ColumnAt(2).name = "FieldText", "Field 3 should be FieldText." CheckTrue r.ColumnAt(3).name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.ColumnAt(4).name = "FieldBLOB", "Field 5 should be FieldBLOB." CheckTrue r.Column("FieldInt").name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.Column("FieldTimeStamp").name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.Column("FieldText").name = "FieldText", "Field 3 should be FieldText." CheckTrue r.Column("FieldDouble").name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.Column("FieldBLOB").name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim i As Integer = r.Column("FieldInt").IntegerValue Dim d As date = r.Column("FieldTimeStamp").dateValue Dim t As String = r.Column("FieldText").StringValue Dim v As Double = r.Column("FieldDouble").DoubleValue Dim m As String = r.Column("FieldBLOB").StringValue CheckTrue i = 5678, "FieldInt should be 5678" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Hello World!", "FieldText should be Hello World" CheckTrue v = 5678, "FieldDouble should be 5678" CheckTrue m = ChrB(2), "FieldBLOB should be ChrB(1)" t = r.Column("FieldDouble").StringValue Dim n As Integer = Val(t) CheckTrue n = 5678, "FieldDouble should be 5678" t = r.Column("FieldInt").StringValue CheckTrue t = "5678", "FieldInt should be 5678" t = r.Column("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" r.MoveToNextRow CheckFalse db.Error, "Error should be false" CheckTrue r.AfterLastRow, "AfterLastRow should be true." CheckFalse r.BeforeFirstRow, "BeforeFirstRow should be false." // try move first r.MoveToFirstRow CheckFalse db.Error, "Error should be false" CheckFalse r.AfterLastRow, "AfterLastRow should be false." CheckFalse r.BeforeFirstRow, "BeforeFirstRow should be false." i = r.Column("FieldInt").IntegerValue CheckTrue i = 5678, "FieldInt should be 5678" r = Nil End Sub

Sub Check5(db as SQLDatabaseMBS) // insert with SQLPreparedStatementMBS, SelectSQL with RowSet using DatabaseRow // now add a row Dim blob As String = ChrB(3)+ChrB(0)+ChrB(4) Dim r As RowSet Dim sql As String sql = "INSERT INTO TestTable (FieldInt, FieldTimeStamp, FieldText, FieldDouble, FieldBLOB) VALUES (:FieldInt, :FieldTimeStamp, :FieldText, :FieldDouble, :FieldBLOB)" Dim p As SQLPreparedStatementMBS = db.Prepare(sql) for i as integer = 1 to 3 p.Bind("FieldInt", 34567) p.bind("FieldTimeStamp", today) p.Bind("FieldText", "Hello World!") p.Bind("FieldDouble", 34567.25) p.Bind("FieldBLOB", blob) p.SQLExecute Next CheckFalse db.Error, "Error should be false" r = nil // select records sql = "SELECT * from TestTable where FieldInt = 34567" r = db.SelectSQL(sql) CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.ColumnCount = 5, "FieldCount should be 5" CheckTrue r.LastColumnIndex = 4, "LastColumnIndex should be 4" CheckTrue r.ColumnType(0) = 3, "ColumnType(0) should be 3 for integer: "+r.ColumnType(0).ToString CheckTrue r.ColumnType(1) = 10, "ColumnType(1) should be 10 for timestamp: "+r.ColumnType(1).ToString CheckTrue r.ColumnType(2) = 5, "ColumnType(2) should be 5 for text: "+r.ColumnType(2).ToString CheckTrue r.ColumnType(3) = 7, "ColumnType(3) should be 7 for double: "+r.ColumnType(3).ToString CheckTrue r.ColumnType(4) = 14, "ColumnType(4) should be 14 for binary: "+r.ColumnType(4).ToString CheckFalse r.AfterLastRow, "AfterLastRow should be false." CheckFalse r.BeforeFirstRow, "BeforeFirstRow should be false." Dim RowCount As Integer = 0 For Each row As DatabaseRow In r CheckTrue row.ColumnAt(0).name = "FieldInt", "Field 1 should be FieldInt." CheckTrue row.ColumnAt(1).name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue row.ColumnAt(2).name = "FieldText", "Field 3 should be FieldText." CheckTrue row.ColumnAt(3).name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue row.ColumnAt(4).name = "FieldBLOB", "Field 5 should be FieldBLOB." CheckTrue row.Column("FieldInt").name = "FieldInt", "Field 1 should be FieldInt." CheckTrue row.Column("FieldTimeStamp").name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue row.Column("FieldText").name = "FieldText", "Field 3 should be FieldText." CheckTrue row.Column("FieldDouble").name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue row.Column("FieldBLOB").name = "FieldBLOB", "Field 5 should be FieldBLOB." CheckTrue row.LastColumnIndex = 4, "LastColumnIndex should be 4" CheckTrue row.ColumnCount = 5, "ColumnCount should be 5" dim cols as string For Each s As String In row cols = cols + s Next Dim colValues As String = row.ColumnAt(0).StringValue+row.ColumnAt(1).StringValue+row.ColumnAt(2).StringValue+row.ColumnAt(3).StringValue+row.ColumnAt(4).StringValue CheckTrue cols = colValues, "cols wrong: "+cols Dim i As Integer= row.Column("FieldInt").IntegerValue Dim d As date = row.Column("FieldTimeStamp").dateValue Dim t As String = row.Column("FieldText").StringValue Dim v As Double = row.Column("FieldDouble").DoubleValue Dim m As String = row.Column("FieldBLOB").StringValue CheckTrue i = 34567, "FieldInt should be 34567" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Hello World!", "FieldText should be Hello World" CheckTrue v = 34567.25, "FieldDouble should be 34567" CheckTrue m = blob, "FieldBLOB should be ChrB(1)" t = row.Column("FieldDouble").StringValue Dim n As Integer = Val(t) CheckTrue n = 34567, "FieldDouble should be 34567" t = row.Column("FieldInt").StringValue CheckTrue t = "34567", "FieldInt should be 34567" t = row.Column("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" RowCount = RowCount + 1 Next CheckTrue RowCount = 3, "n should be 3." CheckFalse db.Error, "Error should be false" CheckTrue r.AfterLastRow, "AfterLastRow should be true." CheckFalse r.BeforeFirstRow, "BeforeFirstRow should be false." r = Nil End Sub

Sub Check6(db as SQLDatabaseMBS) // trying transactions // now add a row Dim blob As String = ChrB(3)+ChrB(0)+ChrB(4) Dim r As RowSet Dim sql As String db.AutoCommit = db.kAutoCommitOff CheckFalse db.Error, "Error should be false" db.BeginTransaction CheckFalse db.Error, "Error should be false" sql = "INSERT INTO TestTable (FieldInt, FieldText) VALUES (:FieldInt, :FieldText)" Dim p As SQLPreparedStatementMBS = db.Prepare(sql) sql = "SELECT COUNT(*) FROM TestTable" r = db.SelectSQL(sql) Dim count0 As Integer = r.ColumnAt(0).IntegerValue CheckTrue Count0 = 7, "Count is not 7" r = nil p.SQLExecute 123, "test3" CheckFalse db.Error, "Error should be false" p.SQLExecute 124, "test4" CheckFalse db.Error, "Error should be false" p.SQLExecute 125, "test5" CheckFalse db.Error, "Error should be false" sql = "SELECT COUNT(*) FROM TestTable" r = db.SelectSQL(sql) Dim count1 As Integer = r.ColumnAt(0).IntegerValue CheckTrue count1 = 10, "Count is not 10" r = Nil db.RollbackTransaction CheckFalse db.Error, "Error should be false" sql = "SELECT COUNT(*) FROM TestTable" r = db.SelectSQL(sql) Dim count2 As Integer = r.ColumnAt(0).IntegerValue CheckTrue count2 = 7, "Count is not 7" r = Nil db.AutoCommit = db.kAutoCommitOn CheckFalse db.Error, "Error should be false" End Sub

Sub Check7(db as SQLDatabaseMBS) // insert with SQLPreparedStatementMBS, SelectSQL with RowSet using DatabaseRow // now add a row Dim sql As String sql = "INSERT INTO TestTable (FieldInt, FieldTimeStamp, FieldText, FieldDouble, FieldBLOB) VALUES (:FieldInt, :FieldTimeStamp, :FieldText, :FieldDouble, :FieldBLOB)" Dim p As SQLPreparedStatementMBS = db.Prepare(sql) p.SQLExecute 45676, today, "Just a test1", 45676.25, Nil p.SQLExecute 45676, today, "Just a test2", 45676.25, Nil CheckFalse db.Error, "Error should be false" p = Nil // select with SQLCommandMBS using RecordSet If True Then Dim r As RecordSet sql = "SELECT * from TestTable where FieldInt = 45676" Dim con As SQLConnectionMBS = db.Connection Dim cmd As New SQLCommandMBS(con, sql) CheckFalse db.Error, "Error should be false" CheckFalse cmd.isResultSet, "isResultSet should be false" CheckFalse cmd.isOpened, "isResultSet should be false" CheckFalse cmd.isExecuting, "isExecuting should be false" CheckFalse cmd.isExecuted, "isExecuted should be false" CheckTrue cmd.FieldCount = 0, "FieldCount should be 0" CheckTrue cmd.ParamCount = 0, "ParamCount should be 0" CheckTrue cmd.isEOF, "isEOF should be true" CheckFalse cmd.isBOF, "isBOF should be false" cmd.Execute CheckFalse cmd.isExecuting, "isExecuting should be false" CheckTrue cmd.isExecuted, "isExecuted should be true" CheckTrue cmd.isResultSet, "isResultSet should be true" CheckTrue cmd.isOpened, "isOpened should be true" CheckTrue cmd.FieldCount = 5, "FieldCount should be 5" CheckTrue cmd.ParamCount = 0, "ParamCount should be 0" CheckFalse cmd.isEOF, "isEOF should be false" CheckTrue cmd.isBOF, "isBOF should be true" // now we ask for RecordSet here r = cmd.AsRecordSet CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.FieldCount = 5, "FieldCount should be 5" CheckFalse r.EOF, "EOF should be false." CheckTrue r.Field("FieldInt").name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.Field("FieldTimeStamp").name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.Field("FieldText").name = "FieldText", "Field 3 should be FieldText." CheckTrue r.Field("FieldDouble").name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.Field("FieldBLOB").name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim RowCount As Integer = 0 While Not r.EOF Dim i As Integer = r.Field("FieldInt").IntegerValue Dim d As date = r.Field("FieldTimeStamp").dateValue Dim t As String = r.Field("FieldText").StringValue Dim v As Double = r.Field("FieldDouble").DoubleValue Dim m As String = r.Field("FieldBLOB").StringValue CheckTrue i = 45676, "FieldInt should be 45676" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Just a test1" or t = "Just a test2", "FieldText should be Hello World" CheckTrue v = 45676.25, "FieldDouble should be 45676" CheckTrue m = "", "FieldBLOB should be nil" t = r.Field("FieldDouble").StringValue Dim n As Integer = Val(t) CheckTrue n = 45676, "FieldDouble should be 45676" t = r.Field("FieldInt").StringValue CheckTrue t = "45676", "FieldInt should be 45676" t = r.Field("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" r.MoveNext CheckFalse db.Error, "Error should be false" RowCount = RowCount + 1 If RowCount = 2 Then CheckTrue r.EOF, "EOF should be true." Else CheckFalse r.EOF, "EOF should be false." End If Wend CheckTrue RowCount = 2, "RowCount should be 2." CheckFalse db.Error, "Error should be false" CheckTrue r.EOF, "EOF should be true." end if // select with SQLCommandMBS using RowSet If True Then Dim r As RowSet sql = "SELECT * from TestTable where FieldInt = 45676" Dim con As SQLConnectionMBS = db.Connection Dim cmd As New SQLCommandMBS(con, sql) CheckFalse db.Error, "Error should be false" CheckFalse cmd.isResultSet, "isResultSet should be false" CheckFalse cmd.isOpened, "isResultSet should be false" CheckFalse cmd.isExecuting, "isExecuting should be false" CheckFalse cmd.isExecuted, "isExecuted should be false" CheckTrue cmd.FieldCount = 0, "FieldCount should be 0" CheckTrue cmd.ParamCount = 0, "ParamCount should be 0" CheckTrue cmd.isEOF, "isEOF should be true" CheckFalse cmd.isBOF, "isBOF should be false" cmd.Execute CheckFalse cmd.isExecuting, "isExecuting should be false" CheckTrue cmd.isExecuted, "isExecuted should be true" CheckTrue cmd.isResultSet, "isResultSet should be true" CheckTrue cmd.isOpened, "isOpened should be true" CheckTrue cmd.FieldCount = 5, "FieldCount should be 5" CheckTrue cmd.ParamCount = 0, "ParamCount should be 0" CheckFalse cmd.isEOF, "isEOF should be false" CheckTrue cmd.isBOF, "isBOF should be true" // now we ask for RowSet here r = cmd.AsRowSet CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." CheckTrue r.ColumnCount = 5, "FieldCount should be 5" CheckFalse r.AfterLastRow, "AfterLastRow should be false." CheckTrue r.Column("FieldInt").name = "FieldInt", "Field 1 should be FieldInt." CheckTrue r.Column("FieldTimeStamp").name = "FieldTimeStamp", "Field 2 should be FieldTimeStamp." CheckTrue r.Column("FieldText").name = "FieldText", "Field 3 should be FieldText." CheckTrue r.Column("FieldDouble").name = "FieldDouble", "Field 4 should be FieldDouble." CheckTrue r.Column("FieldBLOB").name = "FieldBLOB", "Field 5 should be FieldBLOB." Dim RowCount As Integer = 0 While Not r.AfterLastRow Dim i As Integer = r.Column("FieldInt").IntegerValue Dim d As date = r.Column("FieldTimeStamp").dateValue Dim t As String = r.Column("FieldText").StringValue Dim v As Double = r.Column("FieldDouble").DoubleValue Dim m As String = r.Column("FieldBLOB").StringValue CheckTrue i = 45676, "FieldInt should be 45676" CheckTrue d = today, "FieldTimeStamp should be today" CheckTrue t = "Just a test1" Or t = "Just a test2", "FieldText should be Hello World" CheckTrue v = 45676.25, "FieldDouble should be 45676" CheckTrue m = "", "FieldBLOB should be nil" t = r.Column("FieldDouble").StringValue Dim n As Integer = Val(t) CheckTrue n = 45676, "FieldDouble should be 45676" t = r.Column("FieldInt").StringValue CheckTrue t = "45676", "FieldInt should be 45676" t = r.Column("FieldTimeStamp").StringValue CheckTrue t = today.SQLDateTime, "FieldTimeStamp should be today" r.MoveToNextRow CheckFalse db.Error, "Error should be false" RowCount = RowCount + 1 If RowCount = 2 Then CheckTrue r.AfterLastRow, "AfterLastRow should be true." Else CheckFalse r.AfterLastRow, "AfterLastRow should be false." End If Wend CheckTrue RowCount = 2, "RowCount should be 2." CheckFalse db.Error, "Error should be false" CheckTrue r.AfterLastRow, "AfterLastRow should be true." End If End Sub

Sub CheckBlob() Dim mem As New MemoryBlock(100) mem.Byte(0) = 123 mem.byte(95) = 234 Dim b As New SQLBlobMBS(mem) CheckTrue b.BinaryLength = 100, "BinaryLength should be 100." CheckTrue b.Length > 0, "Length should be >0." CheckFalse b.IsEmpty, "IsEmpty should be false" CheckTrue mem = b.CopyMemoryBlock, "memory doesn't match" End Sub

Sub CheckField(db as SQLDatabaseMBS) Dim con As SQLConnectionMBS = db.Connection Dim cmd As New SQLCommandMBS(con, "SELECT * FROM Test WHERE 1 < :Feld") Dim field As SQLParamMBS = cmd.Param("Feld") field.setAsNull CheckTrue field.IsNull, "isNull should be true" CheckTrue field.IsInput, "IsInput should be true" CheckFalse field.IsOutput, "IsOutput should be false" // 32-bit field.setAsInt32 123456789 CheckTrue field.DataType = field.kDataTypeLong, "Type should be kDataTypeLong: "+field.DataType.ToString CheckTrue field.asInt32 = 123456789, "Int32 should be 123456789: "+field.asInt32.ToString CheckTrue field.asStringValue = "123456789", "Int32 as string should be 123456789: "+field.asStringValue CheckTrue field.asDouble = 123456789, "Int32 as double should be 123456789: "+field.asDouble.ToString field.setAsInt32 -123456789 CheckTrue field.DataType = field.kDataTypeLong, "Type should be kDataTypeLong: "+field.DataType.ToString CheckTrue field.asInt32 = -123456789, "Int32 should be -123456789: "+field.asInt32.ToString CheckTrue field.asStringValue = "-123456789", "Int32 as string should be -123456789: "+field.asStringValue CheckTrue field.asDouble = -123456789, "Int32 as Double should be -123456789: "+field.asDouble.ToString field.setAsUInt32 123456789 CheckTrue field.DataType = field.kDataTypeULong, "Type should be kDataTypeULong: "+field.DataType.ToString CheckTrue field.asUInt32 = 123456789, "UInt32 should be 123456789: "+field.asInt32.ToString CheckTrue field.asStringValue = "123456789", "UInt32 as string should be 123456789: "+field.asStringValue CheckTrue field.asDouble = 123456789, "UInt32 as Double should be 123456789: "+field.asDouble.ToString // 64-bit field.setAsInt64 123456789 CheckTrue field.DataType = field.kDataTypeInt64, "Type should be kDataTypeInt64: "+field.DataType.ToString CheckTrue field.asInt32 = 123456789, "Int32 should be 123456789: "+field.asInt32.ToString CheckTrue field.asStringValue = "123456789", "Int64 as string should be 123456789: "+field.asStringValue CheckTrue field.asDouble = 123456789, "Int32 as Double should be 123456789: "+field.asDouble.ToString field.setAsInt64 -123456789 CheckTrue field.DataType = field.kDataTypeInt64, "Type should be kDataTypeInt64: "+field.DataType.ToString CheckTrue field.asInt32 = -123456789, "Int32 should be -123456789: "+field.asInt32.ToString CheckTrue field.asStringValue = "-123456789", "Int64 as string should be -123456789: "+field.asStringValue CheckTrue field.asDouble = -123456789, "Int32 as Double should be -123456789: "+field.asDouble.ToString // 16bit field.setAsShort 12345 CheckTrue field.DataType = field.kDataTypeShort, "Type should be kDataTypeShort: "+field.DataType.ToString CheckTrue field.asInt32 = 12345, "Int32 should be 12345: "+field.asInt32.ToString CheckTrue field.asStringValue = "12345", "Int32 as string should be 12345: "+field.asStringValue CheckTrue field.asDouble = 12345, "Int32 as Double should be 12345: "+field.asDouble.ToString field.setAsShort -12345 CheckTrue field.DataType = field.kDataTypeShort, "Type should be kDataTypeShort: "+field.DataType.ToString CheckTrue field.asInt32 = -12345, "Int32 should be -12345: "+field.asInt32.ToString CheckTrue field.asStringValue = "-12345", "Int32 as string should be -12345: "+field.asStringValue CheckTrue field.asDouble = -12345, "Int32 as Double should be -12345: "+field.asDouble.ToString field.setAsUShort 12345 CheckTrue field.DataType = field.kDataTypeUShort, "Type should be kDataTypeUShort: "+field.DataType.ToString CheckTrue field.asUInt32 = 12345, "UInt32 should be 12345: "+field.asInt32.ToString CheckTrue field.asStringValue = "12345", "UInt32 as string should be 12345: "+field.asStringValue CheckTrue field.asDouble = 12345, "UInt32 as Double should be 12345: "+field.asDouble.ToString // double field.setAsDouble 12345 CheckTrue field.DataType = field.kDataTypeDouble, "Type should be kDataTypeDouble: "+field.DataType.ToString CheckTrue field.asInt32 = 12345, "Int32 should be 12345: "+field.asInt32.ToString CheckTrue field.asStringValue = "12345", "Double as Int32 as string should be 12345: "+field.asStringValue CheckTrue field.asDouble = 12345, "Double should be 12345: "+field.asInt32.ToString field.setAsDouble -12345 CheckTrue field.DataType = field.kDataTypeDouble, "Type should be kDataTypeDouble: "+field.DataType.ToString CheckTrue field.asInt32 = -12345, "Double as Int32 should be -12345: "+field.asInt32.ToString CheckTrue field.asStringValue = "-12345", "Int32 as string should be -12345: "+field.asStringValue CheckTrue field.asDouble = -12345, "Double should be -12345: "+field.asInt32.ToString // string field.setAsString "Hello" CheckTrue field.DataType = field.kDataTypeString, "Type should be kDataTypeString: "+field.DataType.ToString CheckTrue field.asStringValue = "Hello", "String should be Hello: "+field.asStringValue field.setAsString "" CheckTrue field.DataType = field.kDataTypeString, "Type should be kDataTypeString: "+field.DataType.ToString CheckTrue field.asStringValue = "", "String should be empty: "+field.asStringValue // unknown field.setAsUnknown CheckTrue field.DataType = field.kDataTypeUnknown, "Type should be kDataTypeUnknown: "+field.DataType.ToString CheckTrue field.asStringValue = "", "String should be empty: "+field.asStringValue // boolean field.setAsBool True CheckTrue field.DataType = field.kDataTypeBool, "Type should be kDataTypeBool: "+field.DataType.ToString CheckTrue field.asStringValue = "true", "String should be true: "+field.asStringValue CheckTrue field.asBool, "Boolean should be true: "+field.asStringValue CheckTrue field.asInt32 = 1, "Boolean as Int32 should be 1: "+field.asInt32.ToString CheckTrue field.asDouble = 1, "Boolean as Double should be 1: "+field.asDouble.ToString field.setAsBool False CheckTrue field.DataType = field.kDataTypeBool, "Type should be kDataTypeBool: "+field.DataType.ToString CheckTrue field.asStringValue = "false", "String should be false: "+field.asStringValue CheckTrue field.asBool = False, "Boolean should be false: "+field.asStringValue CheckTrue field.asInt32 = 0, "Boolean as Int32 should be 0: "+field.asInt32.ToString CheckTrue field.asDouble = 0, "Boolean as Double should be 0: "+field.asDouble.ToString // MemoryBlock Dim mem As New MemoryBlock(8) mem.DoubleValue(0) = 1234.25 field.setAsBlob mem CheckTrue field.DataType = field.kDataTypeBlob, "Type should be kDataTypeBlob: "+field.DataType.ToString CheckTrue field.asBLobMemory = mem, "Memory should match: "+field.asStringValue CheckTrue field.asStringValue = "0000000000499340", "Memory should match: "+field.asStringValue field.setAsBlob "Hello" CheckTrue field.DataType = field.kDataTypeBlob, "Type should be kDataTypeBlob: "+field.DataType.ToString CheckTrue field.asBLobString = "Hello", "Memory should match: "+field.asStringValue CheckTrue field.asStringValue = "48656c6c6f", "Memory should match: "+field.asStringValue // CLob field.setAsClob "Hello" CheckTrue field.DataType = field.kDataTypeClob, "Type should be kDataTypeClob: "+field.DataType.ToString CheckTrue field.asStringValue = "Hello", "String should be Hello: "+field.asStringValue field.setAsClob "" CheckTrue field.DataType = field.kDataTypeClob, "Type should be kDataTypeClob: "+field.DataType.ToString CheckTrue field.asStringValue = "", "String should be empty: "+field.asStringValue // Date field.setAsDate today CheckTrue field.DataType = field.kDataTypeDateTime, "Type should be kDataTypeDateTime: "+field.DataType.ToString CheckTrue field.asStringValue = today.SQLDateTime.Replace(" ", "T"), "String should be today: "+field.asStringValue CheckTrue field.asDate = today, "Date should match: "+today.SQLDateTime+" "+field.asDate.SQLDateTime field.setAsDate Nil CheckTrue field.DataType = field.kDataTypeDateTime, "Type should be kDataTypeDateTime: "+field.DataType.ToString CheckTrue field.asStringValue = "0000-00-00T00:00:00", "String should be 0000-00-00T00:00:00: "+field.asStringValue // DateTime Dim now As DateTime = DateTime.Now field.setAsDateTime today CheckTrue field.DataType = field.kDataTypeDateTime, "Type should be kDataTypeDateTime: "+field.DataType.ToString CheckTrue field.asStringValue = today.SQLDateTime.Replace(" ", "T"), "String should be today: "+field.asStringValue CheckTrue field.asDate = today, "DateTime should match: "+today.SQLDateTime+" "+field.asDate.SQLDateTime field.setAsDateTime DateTime(Nil) CheckTrue field.DataType = field.kDataTypeDateTime, "Type should be kDataTypeDateTime: "+field.DataType.ToString CheckTrue field.asStringValue = "0000-00-00T00:00:00", "String should be 0000-00-00T00:00:00: "+field.asStringValue Dim dt As New SQLDateTimeMBS(2024, 11, 21, 23, 24, 25, 123456789, "GMT") CheckTrue dt.Year = 2024, "DateTime should have correct year: "+dt.StringValue CheckTrue dt.month = 11, "DateTime should have correct month: "+dt.StringValue CheckTrue dt.day = 21, "DateTime should have correct day: "+dt.StringValue CheckTrue dt.hour = 23, "DateTime should have correct hour: "+dt.StringValue CheckTrue dt.Minute = 24, "DateTime should have correct Minute: "+dt.StringValue CheckTrue dt.second = 25, "DateTime should have correct second: "+dt.StringValue CheckTrue dt.Fraction = 123456789, "DateTime should have correct fraction: "+dt.StringValue CheckTrue dt.TimeZone = "GMT", "DateTime should have correct timezone: "+dt.StringValue CheckTrue dt.StringValue = "2024-11-21T23:24:25.123456789 GMT", "DateTime should have correct StringValue: "+dt.StringValue CheckTrue dt.hasTime, "DateTime should have correct hasTime: "+dt.StringValue CheckTrue dt.hasDate, "DateTime should have correct hasDate: "+dt.StringValue CheckTrue dt.DateValue.SQLDateTime = "2024-11-21 23:24:25", "DateTime should have correct DateValue: "+dt.StringValue CheckTrue dt.DateTimeValue.SQLDateTime = "2024-11-21 23:24:25", "DateTime should have correct DateTimeValue: "+dt.StringValue End Sub

Sub CheckOptions() Dim db As New SQLDatabaseMBS Dim c As Integer c = db.Options.Count CheckTrue c = 0, "Option() not working?: "+c.ToString db.Option("Hello") = "World" CheckTrue db.Option("Hello") = "World", "Option() not working?" CheckTrue db.Options.Key(0) = "Hello", "Option() not working?" c = db.Options.Count CheckTrue c = 1, "Option() not working?: "+c.ToString Dim con As New SQLConnectionMBS c = con.Options.Count CheckTrue c = 0, "Option() not working?: "+c.ToString con.Option("Hello") = "World" CheckTrue con.Option("Hello") = "World", "Option() not working?" c = con.Options.Count CheckTrue c = 1, "Option() not working?: "+c.ToString CheckTrue con.Options.Key(0) = "Hello", "Option() not working?" Dim cmd As New SQLCommandMBS(con, "SELECT 1") c = cmd.Options.Count CheckTrue c = 0, "Option() not working?: "+c.ToString cmd.Option("Command") = "World" CheckTrue cmd.Option("Command") = "World", "Option() not working?" CheckTrue cmd.Options.Key(0) = "Command", "Option() not working?" c = cmd.Options.Count CheckTrue c = 1, "Option() not working?: "+c.ToString cmd.Option("Command") = "" CheckTrue cmd.Option("Command") = "", "Option() not working for setting empty?" cmd.Option("World") = "World" CheckTrue cmd.Option("World") = "World", "Option() not working?" // from commnad to connection CheckTrue cmd.Option("Hello") = "World", "Option() not working?" c = cmd.Options.Count CheckTrue c = 2, "Option() not working?: "+c.ToString c = db.Connection.Options.Count CheckTrue c = 1, "Option() not working?: "+c.ToString End Sub

Sub CheckString() Dim s As New SQLStringMBS("Hello") Dim o As New SQLStringMBS(s) CheckTrue s.Operator_Convert = "Hello", "Text should be Hello" CheckTrue o.Operator_Convert = "Hello", "Text should be Hello" CheckTrue s.Length = 5, "Length should be 5." CheckTrue o.Length = 5, "Length should be 5." CheckTrue s.Left(4).Operator_Convert = "Hell", "Left(4) should be Hell" CheckTrue s.Right(4).Operator_Convert = "ello", "Right(4) should be ello" CheckTrue s.Mid(0).Operator_Convert = "Hello", "Mid(0) should be Hello" CheckTrue s.Mid(2,2).Operator_Convert = "ll", "Mid(2,2) should be ll" CheckTrue s.Mid(4,2).Operator_Convert = "o", "Mid(4,2) should be o" // no exception for going over the end of string! CheckTrue s.Compare("Hello") = 0, "Compare should work" CheckTrue s.Compare("Hallo") <> 0, "Compare should work" CheckFalse s.IsEmpty, "IsEmpty should be empty" CheckFalse o.IsEmpty, "IsEmpty should be empty" CheckTrue s.DebugText = "Hello", "DebugText should be Hello" s = New SQLStringMBS(" Hello ") CheckTrue s.Length = 7, "Length should be 7." s.TrimLeft CheckTrue s.Operator_Convert = "Hello ", "TrimLeft failed" CheckTrue s.Length = 6, "Length should be 6." s.TrimRight CheckTrue s.Operator_Convert = "Hello", "TrimRight failed" CheckTrue s.Length = 5, "Length should be 5." s.MakeUpper CheckTrue StrComp(s.Operator_Convert, "HELLO", REALbasic.StrCompCaseSensitive) = 0, "MakeUpper failed" s.MakeLower CheckTrue StrComp(s.Operator_Convert, "hello", REALbasic.StrCompCaseSensitive) = 0, "MakeLower failed" CheckTrue s.Length = 5, "Length should be 5." End Sub

Sub Run() Dim SQLiteActive As Boolean = InternalSQLiteLibraryMBS.Use CheckTrue SQLiteActive, "InternalSQLiteLibraryMBS should be loading" CheckTrue InternalSQLiteLibraryMBS.SourceID.Len > 0, "SourceID should be there: "+InternalSQLiteLibraryMBS.SourceID CheckTrue InternalSQLiteLibraryMBS.Version.Len > 0, "Version should be there: "+InternalSQLiteLibraryMBS.Version Dim db As New SQLDatabaseMBS CheckFalse db.RaiseExceptions, "RaiseExceptions enabled" CheckFalse db.isAlive, "isAlive should be false" CheckFalse db.isConnected, "isConnected should be false" CheckTrue db.Client = 0, "db.Client should be zero." CheckTrue db.DatabaseName = "", "db.DatabaseName should be empty." CheckTrue db.UserName = "", "db.UserName should be empty." CheckTrue db.Password = "", "db.Password should be empty." CheckTrue db.LastStatement = "", "db.LastStatement should be empty." CheckTrue db.Host = "", "db.Host should be empty." db.Client = SQLConnectionMBS.kSQLiteClient db.DatabaseName = ":memory:" db.RaiseExceptions = True CheckTrue db.RaiseExceptions, "RaiseExceptions enabled" CheckTrue db.Client = SQLConnectionMBS.kSQLiteClient, "Client should be kSQLiteClient" CheckTrue db.DatabaseName = ":memory:", "DatabaseName should be memory." db.Connect CheckFalse db.Error, "Error should be false" CheckTrue db.isAlive, "isAlive should be true" CheckTrue db.isConnected, "isConnected should be true" CheckTrue db.IsolationLevel = -1, "IsolationLevel should be default" CheckTrue db.AutoCommit = -1, "AutoCommit should be default" CheckFalse db.Scrollable, "Scrollable should be false" // Create Table Dim sql As String sql = "CREATE TABLE TestTable (FieldInt INTEGER, FieldTimeStamp TIMESTAMP, FieldText VARCHAR, FieldDouble DOUBLE, FieldBLOB BLOB)" db.ExecuteSQL sql CheckFalse db.Error, "Error should be false" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." // Check Version Dim r As RecordSet sql = "SELECT sqlite_version();" r = db.SQLSelect(sql) CheckFalse db.Error, "Error should be false" CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." Dim s As String = r.IdxField(1).stringValue CheckFalse db.Error, "Error should be false" CheckTrue r.FieldCount = 1, "FieldCount should be 1" CheckTrue s <> "", "SQLite version missing" r = Nil CheckOptions CheckString CheckBlob CheckField db SelectCount db, 0 Check1 db SelectCount db, 1 Check2 db SelectCount db, 2 Check3 db SelectCount db, 3 Check4 db SelectCount db, 4 Check5 db SelectCount db, 7 Check6 db SelectCount db, 7 Check7 db SelectCount db, 9 End Sub

Sub SelectCount(db as SQLDatabaseMBS, CheckCount as Integer) Dim sql As String dim r as RecordSet // select count sql = "SELECT COUNT(*) as Counter FROM TestTable" r = db.SQLSelect(sql) CheckTrue r <> Nil, "Recordset should not be nil" CheckTrue db.LastStatement = sql, "db.LastStatement should be set." Dim RecordCount As Integer = r.RecordCount CheckTrue RecordCount = -1, "Recordcount should be -1" CheckFalse r.EOF, "EOF should be false" CheckTrue r.FieldCount = 1, "FieldCount should be 1" CheckTrue r.IdxField(1).name = "Counter", "Field(1) should be named Counter." Dim n As Integer = r.IdxField(1).integerValue CheckTrue n = CheckCount, "n should be "+CheckCount.ToString r.MoveNext CheckTrue r.EOF, "EOF should be true" r = Nil End Sub

End Module