UserGuide

Microsoft SQL Server Database

From Xojo Documentation

Microsoft SQL Server (MSSQL) is a powerful database server that is commonly used in large companies that rely on Microsoft tools. It works on Windows and both free and commercial versions are available. To use it, you need to ensure the MSSQLServerPlugin file is in the Plugins folder (it is there by default). The plugin supports connecting to Microsoft SQL Server from Windows.

You can connect to Microsoft SQL Server from Desktop, Web and Console projects but not iOS projects.

For more information about Microsoft SQL Server, refer to their web site: www.microsoft.com/sqlserver

Connecting to Microsoft SQL Server

To connect to Microsoft SQL Server (MSSQL), you need to have a MSSQL server installed on either your computer or an accessible server. You’ll need to know several things about this installation, including:

  • The Host IP address or name
  • The name of the database on the server
  • The username to use to connect to the server
  • The password to use to connect to the server

With this information, you can connect to the database on the server using the MSSQLServerDatabase class:

Dim db As New MSSQLServerDatabase
db.Host = "192.168.1.172"
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
If db.Connect Then
// Use the database
End If

Creating a Table

This SQL creates the Team table used in previous examples: 

CREATE TABLE Team (ID INT NOT NULL IDENTITY PRIMARY KEY, Name TEXT, Coach TEXT, City TEXT);

In place of the TEXT data type, which allows an unlimited length string, you might also use the VARCHAR data type which allows you to specify a maximum size for the string: 

CREATE TABLE Team (ID INT NOT NULL IDENTITY PRIMARY KEY, Name VARCHAR(100), Coach VARCHAR(100), City VARCHAR(100));

Auto-Incrementing Primary Keys

If a table has the IDENTITY attribute assigned to a primary key, then that column auto-increments when a row is added to the table. When you INSERT data into a table with a primary key, you omit the primary key from the INSERT SQL: 

INSERT INTO Team (Name)
VALUES ('Seagulls');

After adding a row to the database, you can get the value of the last primary key value by accessing the special column @@IDENTITY in a SELECT statement:

db.SQLExecute("SELECT @@IDENTITY;")

See Also

MSSQLServerDatabase class; UserGuide:Framework, UserGuide:Database Overview, UserGuide:Database Operations topics

Retrieved from "http://docs.xojo.com/index.php?title=UserGuide:Microsoft_SQL_Server_Database&oldid=60166"