C++,introducion to SQL and ADO

[Flying Panda]

Hey guys.So that previous tut were just basics,this is introduction to SQL and ADO techniques.Also,I am in no way creator of this.Managing database operations using ADO and C++, Part 1: Introduction to SQL
So here are simple SQL tables and procedures. This is a brief encapsulation of SQL and more advanced concepts are beyond the scope. Transact SQL (T-SQL) will be used in the syntax of the procedures to keep the operation as generic as possible. For a complete reference to T-SQL, see the MSDN library.In describing the T-SQL scripting the format used is not the standard one used in the T-SQL documentation. For the purposes of this article it is much more compact and direct to use in FlyingPanda's examples. The syntax this crazy awesome panda is using here does not take into account any constraints or foreign key relationships, for the purposes of this article I will not need to document the syntax. For ease of creating these tables and procedures it is recommended to do this within a management piece of software such as Microsoft Management Studio for example.
Tables

To create a table in SQL, the following syntax is used: CREATE TABLE <schema>.<table name> (
<columnname identity value> INT IDENTITY(seed, increment) NOT NULL,
<columnname 1> <type> [size] NULL,
<columnname 2> <type> [size] NULL,
.
.
<columnname n> <type> [size] NULL)
Stored Procedures

To create a stored procedure in SQL the following syntax is used: CREATE <schema>.<procedure name> [Parameter1 name] [type] [size] [ = default value], [Parameter2 name] [type] [size] ] [ = default value], . . [Parameter(n) name] [type] [size] ] [ = default value] AS BEGIN [DECLARE <local variable name> <type> <size>] <procedure logic> END Parameters

In many stored procedures parameters are used which are similar to the parameters used in a C++ function. They have a type, size and a variable name.

The parameter list contains a set of variables that are passed into the procedure from the caller. The format of this list is very similar to the table definition in that it contains a name, variable type and size in bytes if applicable. However, each variable must be proceeded by an "at" symbol (@). For MS-SQL an @ symbol precedes all variable names whether they are parameters or local variables.

Return parameters

In a stored procedure there is an option for what is called a return parameter. The return parameter is a specialized procedure parameter and is set upon exit of the procedure. You can set ADO up to allow the retrieval of this return parameter upon executing a stored procedure. In the stored procedure simply put the desired return parameter after the "return" keyword and at the application level read this value from the returned status after execution of the procedure.

Example of a stored procedure: CREATE PROCEDURE dbo.People_Select @idRecord int = 0 AS BEGIN SET NOCOUNT ON; if(@idRecord>0) begin SELECT PersonName, PersonAge from People where idRecord = @idRecord; return; end else SELECT PersonName, PersonAge from People; END Basic SQL operations

The following descriptions of SQL syntax are not meant to be comprehensive; rather they are being described as basic operations. These statements can get rather advanced depending on the needs of the database schema but for the purposes of designing our ADO management class they will be kept simple.

Delete Syntax DELETE <table name> [WHERE [column = condition] [AND, OR] [column = condition] ] Insert Syntax INSERT INTO <table name> VALUES (@value1,@value2, . @valuen) Select Syntax SELECT [*, column names] FROM <table name> [WHERE [column = condition] [AND, OR] [column = condition] ] Update Syntax UPDATE <table name> SET [Column1] = @value1, [Column2] = @value 2, . . . [Columnn] = @value n [WHERE [column = condition]]; Here are some examples of using these operations. Please refer to the previous table example for the names of the tables and columns in this example. DELETE People where idRecord = @idRecord; INSERT INTO People VALUES (.John Smith.,23); SELECT * FROM People where PersonAge = 23; UPDATE People SET PersonAge = 40 WHERE idRecord = @idRecord; Naming convention for database class manager design

In order to look forward and design our ADO management class correctly it would be a good thing to consider a standard naming convention for the tables and stored procedures in the database. Generally the design will require that all operations on the database take place in a stored procedure. However, it is rather tedious to remember every single procedure name for every type of operation and to manually configure the ADO manager class to call all these different procedures.

It can generally be stated that the operations of DELETE, INSERT, SELECT and UPDATE are considered to be the four basic operations that take place on tables in a database schema. Therefore it makes sense to modularize these operations as procedures.

Given a table name of TestTable, the four basic procedures against it could be written as

• TestTable_Delete
• TestTable_Insert
• TestTable_Select
• TestTable_Update

With this naming scheme in place it will be very easy to design the ADO manager class to operate against this and all other tables in the database schema. Database Schema

This is a very simple database schema as an example to illustrate the concepts of managing database operations with C++. We will be using this later when we build our database ADO manager class class. Table: People

Column Name Type Size (bytes) Description idRecord int 4 Identity column of the table, auto increments as new records are added PersonName varchar 50 Full Name of the person PersonAge int 4 Age of the person
List of procedures:
People_Delete
People_Insert
People_Select
People_Update

The database installation script that creates the above database schema is included in this article. In viewing the script notice that in the People_Insert procedure, a term @@IDENTITY is used as a return parameter. This can be useful to return the last known identity of a table after an insert is performed in order to do further processing on the record that was just inserted. It can also be used to reference this record such as for a node on a tree list control for example.

The script was generated from MS-SQL management studio from an existing database. It will wipe out any existing database called ADOTest and replace it with a new one. If there are any connections or "locks" to the database when this script is generated, i.e. you have a window open in the management studio or an ADO connection to the database, the script will fail because it will be unable to drop the existing database until all connections are severedManaging database operations using ADO and C++: Part 2: Introduction to ADO

This is a brief synopsis of the various objects that will be used in the C++ ADO manager class and is not an exhaustive description of what each object is.

ADO consists of a series of objects that are used to manage interfacing to an SQL server. In C++ these objects need to be instantiated using a smart pointer and then set up correctly in order to perform database operations. Generally ADO is abstracted from the particular syntax issues of various SQL servers. It operates via a driver supplied by a specific SQL server provider. Each provider must supply the driver necessary for ADO to interface to the server properly and handle the various interfaces that ADO operates on. For instance, the MS-SQL server uses a driver called 'SQL Native Client'. In this way, with only a few exceptions, only one piece of code needs to be written in order to interface properly with various SQL servers.
Connection

The ADO connection object handles the actual persistent connection to the SQL server. The main thing to keep in mind is to properly set up the connection string. The connection string consists of a series of key value pairs. Generally the connection string tells ADO the name of the SQL server computer to connect to, the driver to use as a client to connect to the server, the database to use and any authentication information and any other various miscellaneous values that are necessary to properly connect to the SQL server. Command

This ADO object is used to generate a specific command to send to the SQL server. For example, creating a command object, one can either call a SQL statement directly or could call a specific procedure. This object is very important because properly configuring this object is what makes it possible to perform a specific ADO operation. Parameter

The parameter object is used in passing parameters to either a stored procedure or an SQL command. It is important to generate this list of parameters correctly and in order before calling a procedure or running query. Each parameter that is defined to ADO must have a type, a size and to indicate whether or not the parameter is an input or an output parameter. Recordset

Recordset is the heart of ADO in that it is the primary mechanism for retrieving and storing data into a database. The recordset object can be used to scroll through a set of records in any direction, from any point. It can also be used to sort the recordset before traversing it using a filter property. Overview of the ADO API

To get an overview of the available interfaces and the class members ADO consists of, open up Visual Studio and open up the "C:\Program Files\Common Files\System\ado\msado21.tbl" file. Visual Studio will open this up as an object browser and open up the ADODB [2.1] tree as follows:



As you can see, you can go ahead and navigate all the objects that exist in ADO. If you select the 'Command' class for example you can browse through the properties and the methods available. Doing this should give one a rough idea of what is available in ADO by simply clicking through the object browser. C++ code necessary to access ADO API

To set up ADO for use in the compiler, the importing of a typelib that contains the ADO manager class calls is necessary. ADO is a series of COM interfaces and in C++ it could get rather complex to access all these various interfaces using the COM API. Below is a method that helps streamline some of this.

Somewhere in one of your main modules you must have the following line: #import "c:\program files\common files\system\ado\msado15.dll" rename ("EOF","adoEOF") no_namespace
To access the ADO objects in C++, probably the easiest way is to use what are called 'smart pointers'. With COM smart pointers the compiler handles all the things like interface instantiation, memory management etc all the programmer has to do is create an instance to the proper interface and start using the pointer as if it were any other instantiated class. The programmer does not need to actually delete the object, when it falls out of scope that is handled by the program. Below is a piece of code that is typical of Microsoft and how to instantiate a smart pointer: HRESULT _hr =sp.CreateInstance( __uuidof( riid ) );
if (FAILED(_hr))
{
_com_issue_error(_hr);
}
The riid is a GUID to a specific interface and sp is the instantiation of the smart pointer. The hr variable is the result of the call to the CreateInstance API and should be checked to see if there are any errors in creating the interface pointer. Typical errors would include things like the interface does not exist, memory allocation failure, etc. _com_issue_error is a Microsoft call that throws an exception to the system with the error that is passed to it. You don't necessarily need to throw an exception for things like this, but in production code it is a good idea.

ADO uses COM as its framework for interfacing to the application. You must instantiate COM before performing any ADO operations. Below is an example of how to get COM going and instantiate the four main ADO interface objects in the application. Notice the use of the underscore and the letters Ptr after the name of the ADO object when declaring the object variable. This is the syntax of a smart pointer that COM uses in order to generate the underlying code that actually creates and initializes the interface object and de-allocates when the object is out of scope to the smart pointer in question. #import "c:\program files\common files\system\ado\msado15.dll" rename ("EOF","adoEOF") no_namespace
int main(int argc, char *argv[])
{
_ConnectionPtr pConnection;
_CommandPtr pCommand;
_ParameterPtr pParameter;
_RecordsetPtr pRecordset;
int iErrorCode;
HRESULT hr;

// Initialize COM
if(FAILED(hr = CoInitialize(NULL)))
{
goto done_err;
}

// Intialize the ADO Connection object
if(FAILED(hr = pConnection.CreateInstance(__uuidof(Connection))))
{
goto done_err;
}

// Intialize the ADO Command object
if(FAILED(hr = pCommand.CreateInstance(__uuidof(Command))))
{
goto done_err;
}

// Intialize the ADO Parameter object
if(FAILED(hr = pParameter.CreateInstance(__uuidof(Parameter))))
{
goto done_err;
}

// Intialize the ADO RecordSet object
if(FAILED(hr = pRecordset.CreateInstance(__uuidof(Recordset))))
{
goto done_err;
}

// Uninitialize COM
CoUninitialize();

// Everything worked out, report an OK
iErrorCode = 0;

done:
return iErrorCode;
done_err:
// TODO: Cleanup
iErrorCode = (int)hr;
goto done;
}
Interfacing ADO to various database platforms

In this article, the MS-SQL server is used in the examples. However, as Panda mentioned before ADO is not limited to just one type of SQL server. If the correct driver is installed on your computer, which a provider has made to properly use ADO, ADO can be used to interface to this platform.

The main difference in platforms is the connection string that is used in the Connection object. ADO has five standard keys that it looks at in processing a connection string and all other key value pairs are passed directly to the provider driver. This information on the Connection String Property of the Connection object can be found in the documentation for ADO in the MSDN library.

Providers can use their own syntax in this connection string so it is up to the user of this platform to research and figure out what the connection string should look like. Once this syntax is figured out, the rest of the code is for the most part the same. With the right connection string one can even access XML or CSV files as if they were a database. Being able to do this requires that ActiveX Data Objects library be installed.

In order to view the various connection strings, go to your favorite search engine and type "ADO connection strings" to get a list of web pages on how connection strings to other database platforms are structured.

The following string should theoretically work if you have installed SQLExpress on your system locally and have installed the ADOTest database installation script. If you have a different database you need to attach to or if your server is on a remote machine, you will obviously need to change those values. DRIVER=SQL Native Client;SERVER=.\SQLExpress;Trusted_Connection=Yes; Initial Catalog=ADOTest;
Building a connection string

There is a way to 'build' your own connection string if you have your SQL server running and you can get to it locally or remotely.
On your desktop, create a text file and rename it to 'connect.udl'.
Double click on it and a 'Data Link Properties' dialog will appear.
Click on the radio button 'Use Connection Strings.'
At this point if you already have your connection string, just put it here and click on 'Test Connection' button and test the string. Otherwise continue with instructions.
Click on the 'Build' button, a 'Select Data Source' dialog will appear.
Click on 'New' next to DNS Name. A 'Create New Data Source' dialog will appear.
Select the driver that interfaces to your SQL server from this list. If your running SQLExpress server, this choice will be 'SQL Native Client'.
Next click on the 'Browse' button and go to your database file and select it. If running SQLExpress this will be in \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Change the dropdown filter to 'All Files' After selecting your database file, click save.
Click Next.
Click Finish. A new dialog will appear that is specific to your database server. Fill in the appropriate details.
Once your finished with this configuration, you can opt to test your connection by clicking on the 'Test Data Source' button on the finishing dialog, or just click OK.
After saving you should now have a string in the main dialog you can copy and paste.

[Serverman]

I've had 3 hours of sleep, but it looks great!
, since i need to spread rep..

[Flying Panda]

Thanks

[Bat]

Cprogramming.com Tutorial: Managing Database Operations using ADO and C++ Part 1, Introduction to SQL

Pretty much copy/pasted from there, again.

Hey guys.So that previous tut were just basics,this is introduction to SQL and ADO techniques.If you guys want,I can make an advanced tutorial of anything related to C++,just ask

There's a certain difference of MAKING a tutorial, and copy pasting it.

[Flying Panda]

Yeah,I made it cuz i edited stuff and remade it abit.Also,I even said i am in no way creator of this >.<

[Bat]

You've edited it out, that's why.

Also,I am in no way creator of this

Wasn't there when i posted.

But, this was.

Hey guys.So that previous tut were just basics,this is introduction to SQL and ADO techniques.If you guys want,I can make an advanced tutorial of anything related to C++,just ask

Here you clearly state that you can MAKE a tutorial.

You edited at: Last edited by Flying Panda; Yesterday at 10:37 PM.
Posted at Yesterday, 10:39 PM
Which means that you edited just before you posted, shouldnt be hard to view the original thread.

[Flying Panda]

I actually posted it down,not there in the begining,so i edited it to up,so ppl would read it.Didnt give credits to creater cuz I forgot who it is,cuz these guides have been on my comp long time ago,so everything i know about it,I learned from that guide.Also,I just took them from my comp,not from any website,thats why I didnt give any credits.