Platforms to show: All Mac Windows Linux Cross-Platform
/SQL/SQLDatabaseMBS Microsoft SQL Server Data Types
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.xojo_binary_project"
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:
- /SQL/SQLDatabaseMBS Firebird Connect
- /SQL/SQLDatabaseMBS Microsoft Access Connect
- /SQL/SQLDatabaseMBS Microsoft SQL Connect
- /SQL/SQLDatabaseMBS Microsoft SQL cross platform
- /SQL/SQLDatabaseMBS MySQL Dump
- /SQL/SQLDatabaseMBS PostgreSQL Connect
- /SQL/SQLDatabaseMBS SQLite Connect
- /SQL/SQLDatabaseMBS SQLite Create Encrypted
- /SQL/SQLDatabaseMBS SQLite insert record
- /SQL/SQLDatabaseMBS SQLite load extension
The items on this page are in the following plugins: MBS SQL Plugin.
