Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Platforms to show: All Mac Windows Linux Cross-Platform

/SQL/SQLDatabaseMBS Microsoft SQL Server Data Types
Function:
Required plugins for this example: MBS SQL Plugin
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /SQL/SQLDatabaseMBS Microsoft SQL Server Data Types
This example is the version from Tue, 3rd Aug 2015.
Project "SQLDatabaseMBS Microsoft SQL Server Data Types.rbp"
Class App Inherits Application
Const kEditClear = "&Delete"
Const kFileQuit = "&Quit"
Const kFileQuitShortcut = ""
End Class
Class Window1 Inherits Window
Const HasODBCPlugin = false
Control pbSQLdbMBS Inherits PushButton
ControlInstance pbSQLdbMBS Inherits PushButton
EventHandler Sub Action() if list.ListCount >0 then list.AddRow dim db as new SQLDatabaseMBS // where is the library? db.DatabaseName="SQLserver:"+tfHost.text+"@" +tfDb.text db.UserName=tfUser.Text db.Password=tfPass.Text if not db.Connect then lbStatus.text = db.ErrorMessage + EndOfLine else dim rs as recordset = db.SQLSelect (taSql.Text) for col as integer = 1 to rs.FieldCount dim f as DatabaseField = rs.IdxField(col) dim type as integer = rs.ColumnType(col-1) list.AddRow "SQLDatabaseMBS" list.Cell(list.LastIndex,1) = f.name list.Cell(list.LastIndex,2) = str(type) + ": " + GetColumnTypeAsText(type) list.Cell(list.LastIndex,3) = f.StringValue next end if End EventHandler
End Control
Control pbSQLConMBS Inherits PushButton
ControlInstance pbSQLConMBS Inherits PushButton
EventHandler Sub Action() if list.ListCount > 0 then list.AddRow dim con as SQLConnectionMBS = new SQLConnectionMBS dim cs as string = "DRIVER={SQL Server};Server="+tfHost.text+";UId="+tfUser.Text +";PWD="+ tfPass.text+";Database=" + tfDb.Text con.Option("UseAPI") = "ODBC" con.Connect(cs,"","",SQLConnectionMBS.kODBCClient) if con.isConnected then lbStatus.text = "connected" else lbStatus.text = "Err : " + con.ErrorMessage end if dim cmd as new SQLCommandMBS cmd.Connection = con cmd.setCommandText taSql.Text cmd.Execute if cmd.isResultSet then if cmd.FetchNext then for col as integer = 1 to cmd.FieldCount dim f as SQLFieldMBS = cmd.Field(col) dim type as integer = f.DataType dim nativetype as integer = f.NativeType list.AddRow "SQLConnectionMBS" list.Cell(list.LastIndex,1) = f.name list.Cell(list.LastIndex,2) = str(type) + ": " + GetMBSColumnTypeAsText(type)+" ("+str(nativetype) + ": " + GetMicrosoftODBCTypeAsText(nativetype)+")" list.Cell(list.LastIndex,3) = f.asStringValue next end if end if End EventHandler
End Control
Control lbStatus Inherits Label
ControlInstance lbStatus Inherits Label
End Control
Control pbXojoRS Inherits PushButton
ControlInstance pbXojoRS Inherits PushButton
EventHandler Sub Action() if list.ListCount >0 then list.AddRow #if HasODBCPlugin then dim db as new ODBCDatabase db.DataSource = "Driver=SQL Server;Server="+tfHost.Text+";Database="+tfDb.text+"; Uid="+tfUser.Text+";Pwd="+ tfPass.text if not db.Connect then lbStatus.Text = db.ErrorMessage + EndOfLine else dim sql as string = taSql.text dim rs as recordset = db.SQLSelect (sql) rs.MoveFirst for col as integer = 1 to rs.FieldCount list.AddRow "XOJO odbc" // field 1 based // columntype 0 based dim f as DatabaseField = rs.IdxField(col) dim type as integer = rs.ColumnType(col-1) list.Cell(list.LastIndex,1) = f.name list.Cell(list.LastIndex,2) = str(type) + ": " + GetColumnTypeAsText(type) list.Cell(list.LastIndex,3) = f.StringValue next end if #else MsgBox "Xojo ODBC Plugin installed? Check HasODBCPlugin constant, please." #endif End EventHandler
End Control
Control tfHost Inherits TextField
ControlInstance tfHost Inherits TextField
End Control
Control tfDb Inherits TextField
ControlInstance tfDb Inherits TextField
End Control
Control tfUser Inherits TextField
ControlInstance tfUser Inherits TextField
End Control
Control tfPass Inherits TextField
ControlInstance tfPass Inherits TextField
End Control
Control Label1 Inherits Label
ControlInstance Label1 Inherits Label
End Control
Control Label2 Inherits Label
ControlInstance Label2 Inherits Label
End Control
Control Label3 Inherits Label
ControlInstance Label3 Inherits Label
End Control
Control Label4 Inherits Label
ControlInstance Label4 Inherits Label
End Control
Control list Inherits Listbox
ControlInstance list Inherits Listbox
End Control
Control Label5 Inherits Label
ControlInstance Label5 Inherits Label
End Control
Control taSql Inherits TextArea
ControlInstance taSql Inherits TextArea
End Control
Control Label6 Inherits Label
ControlInstance Label6 Inherits Label
End Control
Function GetColumnTypeAsText(t as integer) As String Select Case t Case 0 Return "null" Case 1 Return "byte" Case 2 Return "SmallInt" Case 3 Return "Integer" Case 4 Return "Text" Case 5 Return "Text" Case 6 Return "Float" Case 7 Return "Double" Case 8 Return "Date" Case 9 Return "Time" Case 10 Return "TimeStamp" Case 11 Return "Currency" Case 12 Return "Boolean" Case 13 Return "Float" Case 14 Return "Binary" Case 15 Return "Text" Case 16 Return "Blob" Case 17 Return "Blob" Case 18 Return "Text" Case 19 Return "Int64" Case 255 Return "unknown" // Unknown Case Else Return "unknown" // Unknown End End Function
Function GetMBSColumnTypeAsText(t as integer) As String select case t case SQLFieldMBS.kDataTypeUnknown return "unknown" case SQLFieldMBS.kDataTypeBool return "bool" case SQLFieldMBS.kDataTypeShort return "SmallInt" case SQLFieldMBS.kDataTypeUShort return "ushort" case SQLFieldMBS.kDataTypeLong return "long" case SQLFieldMBS.kDataTypeULong return "ulong" case SQLFieldMBS.kDataTypeDouble return "double" case SQLFieldMBS.kDataTypeNumeric return "Numeric" // double, int64 or similar case SQLFieldMBS.kDataTypeDateTime return "datetime" case SQLFieldMBS.kDataTypeInterval return "interval" case SQLFieldMBS.kDataTypeString, SQLFieldMBS.kDataTypeLongChar,SQLFieldMBS.kDataTypeCLob return "text" case SQLFieldMBS.kDataTypeBytes, SQLFieldMBS.kDataTypeLongBinary, SQLFieldMBS.kDataTypeBLob return "blob" case SQLFieldMBS.kDataTypeCursor return "cursor" case SQLFieldMBS.kDataTypeSpecificToDBMS return "specific" else return "?" end Select End Function
Function GetMicrosoftODBCTypeAsText(t as integer) As string Select Case t Case 0 Return "Null" Case 1 Return "Char" Case 2 Return "Numeric" Case 3 Return "Decimal" Case 4 Return "Integer" Case 5 Return "SmallInt" Case 6 Return "Float" Case 7 Return "Double" Case 8 Return "Double" Case 9 Return "Date" Case 10 Return "Interval" Case 11 Return "TimeStamp" Case 12 Return "Text" Case 91 Return "Date" Case 92 Return "Time" Case 93 Return "TimeStamp" Case 99 Return "Default" Case -7 Return "Bit" Case -6 Return "TinyInt" Case -5 Return "BigInt" Case -4 Return "BLOB" // long varbinary Case -3 Return "BLOB" // varbinary Case -2 Return "BLOB" // binary Case -1 Return "Text" Case -11 Return "GUID" Case -8 Return "Text" // WCHAR Case -9 Return "Text" // WVARCHAR Case -10 Return "Text" // WLONGVARCHAR End End Function
Function now() As String dim d as new date return d.SQLDateTime End Function
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"
End MenuBar
End Project

See also:

Feedback, Comments & Corrections

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




Links
MBS Xojo PDF Plugins