Connecting C4 to an SQL database with SQLAPI

From C4 Engine Wiki

Overview

Connecting C4 to a Micosoft SQL Server Database using SQLAPI

This article shows how to plug in SQLAPI into C4. The following will also create a basic user login screen with some error detection and also go into very basic details of how to create a Microsoft SQL Server database ready for C4 use on your local machine. This tutorial goes into some detail on how to set up SQL server, but previous knowledge is preferable. The password fields work very basicly and need much more refinement. I'm not sure if Eric is planning to create a password text element.

Important user Information

Version v1.0 Released on wiki.

Author

--Tjheldna 16:58, 1 March 2008 (PST)

Level of Difficulty:

Moderate

Notes

See SQL API site for purchase details. The basic version costs US $249.00

Pre requisites:

Basic knowledge of setting up an SQL database and tables.
No programming experience necessary, but an advantage.
Some compiler knowledge is assumed - setting up C4 with visual C++ or there of, and compiling.
Setting up of the SQLAPI .lib

Tools Required:

Microsoft VC++ 2005 or higher
Internet connection
SQLAPI Download SQLAPI Trial here.. The Trial is full featured and doesn't have a time period, but a popup appears when a connection to the database is made. Click this link SQLAPI++ Win32 on the page.
SQL server enterprise manager 2005 Download SQL here.. Select the SQL Server 2005 Express Edition with Advanced Services SP2 (234 MB) download.
Build 149 or higher and Licence of C4. Previous versions not tested, although may work.

The following files are modified for this tutorial:

MGGame.cpp
MGGame.h
MGInterface.cpp
MGInterface.h

Source Code Additions:

None

Library Additions:

SQLAPI.lib Downloaded from here.

Files included:

None

Where to modify:

Look for the START and FINISH comments as shown below. Any code outside these tags is existing code and is left there to help you find the correct location of the modification and does not need to be added in.

existing code here, DONT ADD......
 
//SQL START 

code to add here......

//SQL FINISH. 

existing code here, DONT ADD......

First things first

Install SQL server

There are no need for screen shots here it's pretty straight forward, but I thought I'd do a brief run down of the installation process of SQL Server.

Run The SQLEXPR_ADV.exe. The files will extract.

Accept the EULA agreement, click Next >. The Prerequisites window click Install. Wait while the program configures. click Next. Wait while the program configures some more (You may get a firewall warning. Allow SQL Server to access the internet).

The setup window will appear, click Next >. The installer will perform a check for problems. If it is a success click Next >

In the Registration Information window, type in your details, click Next. The default feature section your best bet is to enable all features for installation click Next >.

The Instance Name window will appear. You can leave the Named Instance radio button checked and you can leave SQLExpress as the text, however if you change this it will effect all subsequent naming for the rest of the tutorial. Click Next >.

The Existing components window will appear. You may have existing components listed. Click Next >.

The Error and Usage Report Settings window will appear, just click Next >.

The Ready to Install window will appear, click Install. Wait while the program installs.

When the installation is finished click Next >>, click Finish.

Now lets set up a database, database user, and table!

SQL Server Management Studio Express

Open and Logon to SQL server

Open up SQL Server Management Studio Express. Start->All Programs->Microsoft SQL Server->SQL Server Management Studio Express. It will configure for first time use.

The Connect to Server dialog will appear see fig 1.0. Leave everything as is for now, note that Server Name will be YOUR computer name plus the Named Instance from the installation. This is important when it comes the the connection string in code.

fig 1.0

Click Connect. You are now connected to SQL. Time to create a database

Create a C4 database

In the Object Explorer on the LHS expand YOUR_COMPUTER_NAME\SQLEXPRESS ...etc. Right click Databases, click New Database... see fig 1.1

fig 1.1

The New Database window will appear. Under Database name type C4. . Leave the Owner as <default> for now. We will create a new owner later. Click OK. see fig 1.2

fig 1.2

Create a New Administrator User

In the Object Explorer, expand the Security folder, expand logins folder. Right Click the logins folder click New Login.... The login - New window will appear.

fig 1.3

Take note of the settings in figure 1.3. First thing to do is to create the login name. In the Login name text box type 'C4user' set the radio buttons to SQL Server authentication unless you wish to use a windows user, which will not be covered in this article.

Uncheck the user must change password at next login check box.

In the password and password confirm text boxes type your password. For this tutorial I have used the password c4.

In the Default database drop down select your newly created database C4.

Leave the Default language as it is.

You have now assigned a user to the database. Click OK

The last step is to create a table in your database. This table will store all of your user records and all of the information you wish to store for them.

Expand you C4 database folder. right click Tables. Select New Table...

fig 1.4

Match the field names and data type to fig 1.5.

To set the primary key Right Click the userID field. Select Set Primary Key. The userID field is going to be the unique identifire for the field.

In the Column Properties below. Expand Identity Specification, change (Identity Specification) to Yes (fig 1.5). This sets the primary key to increment evertime a new record is added

Close the table and you will be asked to save. Click Yes.

The Choose Name Dialog box will appear. Now you need to name your table. For this exercise name it tbl_users. Click OK.

You have now created a table to store user logins. NB that you can add as many fields as you like.

fig 1.5

Modifying the Source code

In MGGame.cpp

MainWindow::Open();

//SQL START

//Load in the login window

TheMainWindow->AddSubwindow(new LogOnWindow());

//SQL Finish

In MGInterface.h


class LogOnWindow: public GameWindow, public Singleton<LogOnWindow>
{
private:
			
    TextElement					*headerText;
    TextElement					*errorText;
    TextElement					*userText;
    TextElement					*passwordText;

    EditableTextElement				*userEditableText;
    EditableTextElement				*passwordEditableText;
    EditableTextElement				*passwordEditableText2;

    PushButtonElement				*enterButton;
    TextButtonElement				*newUserButton;

    String<10>					password;

public:
    LogOnWindow();
    ~LogOnWindow();
			
    static void Open(void);
					
    void HandleElementTrigger(Element *element);
    bool HandleKeyboardEvent(EventType eventType, long key, unsigned long modifiers);
    bool ValidateUser(void);
};
		
class NewUserWindow: public GameWindow, public Singleton<NewUserWindow>
{
private:
			
    TextElement					*headerText;
    TextElement					*errorText;
    TextElement					*userText;		
    TextElement					*passwordText;
    TextElement					*passwordTextConfirm;


    EditableTextElement				*userEditableText;
    EditableTextElement				*passwordEditableText;
    EditableTextElement				*passwordConfirmEditableText;

			
    PushButtonElement				*enterButton;
    TextButtonElement				*newUserButton;

    String<10>					password;
    String<10>					passwordConfirm;
		
public:
    NewUserWindow();
    ~NewUserWindow();
			
    static void Open(void);
					
    void HandleElementTrigger(Element *element);
    bool HandleKeyboardEvent(EventType eventType, long key, unsigned long modifiers);
    bool AddUser(void);
};

Place these with the other window definitions.

//SQL Start

//
extern LogOnWindow *TheLogOnWindow;
extern NewUserWindow *TheNewUserWindow;
//SQL Finish

In MGInterface.cpp

Add this include line at the top with the rest of the includes NB This include MUST appear before any other include.

//SQL Start
#include <SQLAPI.h>
//SQL Finish

#include "C4FilePicker.h"
#include "MGConfiguration.h"
</pre


* Add this line underneith the C4 namespace.
<pre>
using namespace C4;

//SQL Start
using namespace std;
//SQL Finish

This is the long part create the class definitions

LogOnWindow::LogOnWindow() :
		GameWindow(300.0F, 200.0F, nullptr, kWindowTitleBar),
		Singleton<LogOnWindow>(TheLogOnWindow)
{

	SetElementPosition(Point3D(200.0F, 200.0F,0.0F));

	Font *font = Font::Get("Sword");
	password = "";

	headerText = new TextElement("--Log On--", font);
	headerText->SetElementPosition(Point3D(125.0F, 0.0F, 0.0F));
	headerText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(headerText);

	userText = new TextElement("User Name", font);
	userText->SetElementPosition(Point3D(20.0F, 20.0F, 0.0F));
	userText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(userText);

	userEditableText = new EditableTextElement(100.0F, 15.0F, 10, font);
	userEditableText->SetElementPosition(Point3D(100.0F, 20.0F, 0.0F));
	userEditableText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(userEditableText);

	passwordText = new TextElement("Password", font);
	passwordText->SetElementPosition(Point3D(20.0F, 50.0F, 0.0F));
	passwordText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordText);

	
	passwordEditableText = new EditableTextElement(100.0F, 15.0F, 10, font);
	passwordEditableText->SetElementPosition(Point3D(100.0F, 50.0F, 0.0F));
	passwordEditableText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordEditableText);

	enterButton = new PushButtonElement(" Log On ", font);
	enterButton->Build();
	enterButton->SetElementPosition(Point3D(140.0F, 90.0F, 0.0F));
	AddSubnode(enterButton);

	errorText = new TextElement("", font);
	errorText->SetElementPosition(Point3D(25.0F, 150.0F, 0.0F));
	errorText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(errorText);
	
	newUserButton = new TextButtonElement("Add new user", font);
	newUserButton->Build();
	newUserButton->SetElementPosition(Point3D(100.0F, 130.0F, 0.0F));
	AddSubnode(newUserButton);

	font->Release();
}



//With this window you can log on if the use is in the database
LogOnWindow::~LogOnWindow(){}

void LogOnWindow::Open(void)
{
	if (TheLogOnWindow) TheInterfaceMgr->SetActiveWindow(TheLogOnWindow);
	else TheGame->AddWindow(new LogOnWindow);
}

void LogOnWindow::HandleElementTrigger(Element *element)
{
	if (element == enterButton)
	{
		if(ValidateUser())
		{
			delete this;
		}
	}
	else if(element == newUserButton)
	{
		AddSubwindow(new NewUserWindow);
	}
}

bool LogOnWindow::HandleKeyboardEvent(EventType eventType, long key, unsigned long modifiers)
{
	if (eventType == kEventKeyDown)
	{
		Element *element = GetFocusElement();

		if(key == 13) //Enter key
		{
			enterButton->TriggerElement(); //Trigger the OK button when enter is pressed
			return true;
		}
		else if(element == passwordEditableText && key != 8)// If the element is the password element and its not backspace
		{	
			char ch = key; 
			password += ch;	//Save the entered text to a string.
			key = 63; // set the key to a question mark.
		}
		else if(key == 8) //If its the backspace delete the last element in the string
		{
			short index = password.Length() - 1;
			password[index] = nullptr;
		}
	}

	return (GameWindow::HandleKeyboardEvent(eventType, key, modifiers));
}

bool LogOnWindow::ValidateUser(void)
{
	SAConnection con; //connection object
	SACommand cmd(&con); //command object

        //change <YOURCOMPUTERNAME> to your computer name...obviously. Looking at the parameters after the @
        //symbol you see the database you created. C4user is the user that is assigned to the database, and c4 the password
        //associated with the user name.
	con.Connect("<YOURCOMPUTERNAME>\\SQLEXPRESS@C4", "C4user", "c4", SA_SQLServer_Client);
	SAString userName = userEditableText->GetText();

        // Create a query using the text entered in the fields of the window. 'tbl_users' below is the table you created 
        //in the previous exercise.
	SAString connStr = "SELECT * FROM tbl_users WHERE '" + userName + "' = userName"; 
	cmd.setCommandText(connStr);
        
	cmd.Execute();

	bool userFound = false;

	if(cmd.isResultSet()) // If there are results...
	{
		if(cmd.FetchNext()) // If a row returns and the userName is already In the Database.
		{
			if(password == cmd.Field("userPassword"))
			{
				errorText->SetText("Welcome Back");
				userFound = true;
			}
			else
			{
				passwordEditableText->SetText("");
				password = "";
				errorText->SetText("Invalid password");
			}
		}
		else
		{
			errorText->SetText("User Name Not Found");
		}
	}
	con.Disconnect();*/

	return userFound;
}


//With this window you can add a new user into your database.
NewUserWindow::NewUserWindow() :
		GameWindow(300.0F, 200.0F, nullptr, kWindowTitleBar),
		Singleton<NewUserWindow>(TheNewUserWindow)
{

	SetElementPosition(Point3D(200.0F, 200.0F,0.0F));

	Font *font = Font::Get("Sword");
	password = "";
	passwordConfirm = "";

	headerText = new TextElement("--Add New User --", font);
	headerText->SetElementPosition(Point3D(125.0F, 0.0F, 0.0F));
	headerText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(headerText);

	userText = new TextElement("User Name", font);
	userText->SetElementPosition(Point3D(20.0F, 20.0F, 0.0F));
	userText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(userText);

	userEditableText = new EditableTextElement(100.0F, 15.0F, 10, font);
	userEditableText->SetElementPosition(Point3D(100.0F, 20.0F, 0.0F));
	userEditableText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(userEditableText);

	passwordText = new TextElement("Password", font);
	passwordText->SetElementPosition(Point3D(20.0F, 50.0F, 0.0F));
	passwordText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordText);

	passwordEditableText = new EditableTextElement(100.0F, 15.0F, 10, font);
	passwordEditableText->SetElementPosition(Point3D(100.0F, 50.0F, 0.0F));
	passwordEditableText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordEditableText);

	passwordTextConfirm = new TextElement("Confirm Password", font);
	passwordTextConfirm->SetElementPosition(Point3D(20.0F, 80.0F, 0.0F));
	passwordTextConfirm->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordTextConfirm);

	passwordConfirmEditableText = new EditableTextElement(100.0F, 15.0F, 10, font);
	passwordConfirmEditableText->SetElementPosition(Point3D(100.0F, 80.0F, 0.0F));
	passwordConfirmEditableText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(passwordConfirmEditableText);

	enterButton = new PushButtonElement("Add User", font);
	enterButton->Build();
	enterButton->SetElementPosition(Point3D(140.0F, 120.0F, 0.0F));
	AddSubnode(enterButton);

	errorText = new TextElement("", font);
	errorText->SetElementPosition(Point3D(25.0F, 150.0F, 0.0F));
	errorText->SetTextColor(ColorRGB(0.5F, 0.5F, 0.5F));
	AddSubnode(errorText);
	
	font->Release();
}

NewUserWindow::~NewUserWindow(){}

void NewUserWindow::Open(void)
{
	if (TheNewUserWindow) TheInterfaceMgr->SetActiveWindow(TheNewUserWindow);
	else TheGame->AddWindow(new NewUserWindow);
}

void NewUserWindow::HandleElementTrigger(Element *element)
{
	if (element == enterButton)
	{
		if(AddUser())
			delete this;
	}
}

bool NewUserWindow::AddUser(void)
{
	SAConnection con; //connection object
	SACommand cmd(&con); //command object

	con.Connect("SENTINEL\\SQLEXPRESS@ADF", "username", "password", SA_SQLServer_Client);
	SAString userName = userEditableText->GetText(); // Get the text from the text box

	SAString connStr = "SELECT * FROM tbl_users WHERE '" + userName + "' = userName"; // Create a query using the text entered in the fields
	cmd.setCommandText(connStr);
	cmd.Execute();

	bool userAdded = false;
	

	if(cmd.isResultSet())	// If there are results...
	{
		if(cmd.FetchNext()) // If a row returns the userName is already In the Database.
		{
			errorText->SetText("User Name Taken");
			userAdded =  false;
		}
		else
		{
			if(Text::CompareText(password ,passwordConfirm))
			{
				connStr = "INSERT INTO tbl_users(userName, userPassword) VALUES (:1, :2)"; // Insert values from text boxes into the DB
				cmd.setCommandText(connStr);

				SAString password = passwordEditableText->GetText();

				cmd.Param(1).setAsString() = userName;
				cmd.Param(2).setAsString() = passwordConfirm;

				cmd.Execute();

				userAdded = true;
				errorText->SetText("User Successfully Added");
			}
			else
			{
				passwordEditableText->SetText("");
				passwordConfirmEditableText->SetText("");
				password = "";
				passwordConfirm = "";
				errorText->SetText("Password and Password Confirm mismatch!");
				userAdded =  false;
			}
		}
	}
	con.Disconnect();

	return userAdded;
}

bool NewUserWindow::HandleKeyboardEvent(EventType eventType, long key, unsigned long modifiers)
{

	if (eventType == kEventKeyDown)
	{
		Element *element = GetFocusElement();

		if(key == 13) //Enter key
		{
			enterButton->TriggerElement(); //Trigger the OK button when enter is pressed
			return true;
		}
		else if(element == passwordEditableText && key != 8)// If the element is the password element and its not backspace
		{	
			char ch = key; 
			password += ch;	//Save the entered text to a string.
			key = 63; // set the key to a question mark.
		}
		else if(key == 8) //If its the backspace delete the last element in the string
		{
			short index = password.Length() - 1;
			password[index] = nullptr;
		}
		else if(element == passwordConfirmEditableText && key != 8)// If the element is the password element and its not backspace
		{	
			char ch = key; 
			passwordConfirm += ch;	//Save the entered text to a string.
			key = 63; // set the key to a question mark.
		}
		else if(key == 8) //If its the backspace delete the last element in the string
		{
			short index = passwordConfirm.Length() - 1;
			passwordConfirm[index] = nullptr;
		}
	}

	return (GameWindow::HandleKeyboardEvent(eventType, key, modifiers));
}

Connecting C4 to an SQL database with SQLAPI

From C4 Engine Wiki

Contents

Overview

Important user Information

First things first

SQL Server Management Studio Express

Modifying the Source code

Views

Personal tools

Navigation

Search

Toolbox