Copyright: Copyright Aldea Interactive 1996-1997. All rights reserved.


AGSQL Extension
AGSQL Introduction

AGSQL (Active Gateway SQL) is an Active Gateway Server Extension that allows any Active Gateway Client to query a SQL database. You are not supposed to know anything about SQL in order to use this extension.

The purpose of this manual is to teach you:

•	how to setup the extension

•	how to send requests to the extension

Code sample

The Active Gateway SDK contains a example called AGClientSQL. This example contains sample code for all AGSQL requests. Please, refer to this example for a better understanding of AGSQL requests.


AGSQL : Installation
Installation

To install this extension, use the Active Gateway SQL setup program.

The setup program:

•	copies AGSQL dynamic-link library,

•	copies help files,

•	and makes the necessary changes in the Windows Registry.

Warning

You must install an Active Gateway Server before running AGSQL setup program. You cannot install any Active Gateway Server Extension if there is no server installed.

We recommend you to install the extension dynamic-link library in the Active Gateway Server directory or in the system directory.

ODBC requirements

The machine on which the Active Gateway Server and AGSQL will run must have ODBC components installed.

When a client requests AGSQL to query a database, it specifies the name of an ODBC data source, a login name and a password. If you use a database server (like SQL Server), ask you administrator for an account. The restriction that apply to this account apply to AGSQL queries. Thus, you must create at least one ODBC data source to be able to use this extension.

For security reasons, you may want not to send login names and passwords over a networks. The topic "AGSQL_ODBC frame" explains how to set up the Windows Registry to create default accounts for data sources.


AGSQL : Request import
AGSQL Requests

AGSQL allows you to perform seven different requests:

•	AGSQL_OPEN_DATASOURCE: to open an ODBC data source

•	AGSQL_CLOSE_DATASOURCE: to close an ODBC data source

•	AGSQL_EXECSQL: to execute directly SQL statements

•	AGSQL_INSERT: to add new records to a table

•	AGSQL_UPDATE: to modify records in a table

•	AGSQL_DELETE: to remove records from a table

•	AGSQL_SELECT: to retrieve records from a table

Performing a request:

1.	You create a frame containing some predefined slots (for example a slot called "EXTENSION_REQUEST" that contains the name of the request you want to perform).

2.	You connect to the extension.

3.	You send the frame.

4.	The extension analyses the contents of the frame and queries the selected database.

5.	You receive the response frame sent by the extension.

6.	You analyze the response frame which contains a return value and if the query was successful the query results.


AGSQL_OPEN_DATASOURCE

This requests allows you to open an ODBC data source. The source stays open until the connection is closed or the AGSQL_CLOSE_DATASOURCE is performed.

Use this request when you have several request to perform on the same data source. If you work with an Microsoft Access database, you should use this request to minimize subsequent opening and closing of the database.

Warning

If a data source is open (with AGSQL_OPEN_DATASOURCE), any subsequent request will be performed on this data source even if another data source is specified in the AGSQL_ODBC slot.

Required Slots:

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_OPEN_DATASOURCE)));

See also

AGSQL_CLOSE_DATASOURCE


AGSQL_CLOSE_DATASOURCE

This requests allows you to close an open ODBC data source.

Required Slots:

None

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_CLOSE_DATASOURCE)));

See also

AGSQL_OPEN_DATASOURCE


AGSQL_EXECSQL

This is the most powerful request. To use this request you have to know the basics of SQL. You should also understand what you are doing because performing SQL statements on a database might be hazardous (you can delete database tables containing valuable data).

Required Slots:

AGSQL_EXECSQL_DATA	A string containing the SQL statements.

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_EXECSQL)));
m_oAGCtrl.SetSlotValue(AGSQL_EXECSQL_DATA,	COleVariant(CString("DROP TABLE Employees")));


AGSQL_INSERT

This request allows you to add new records (rows) to a table of a selected ODBC data source.

Required Slots:

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

AGSQL_TABLE_NAME	A string containing the name of the table where the records are to be inserted.

AGSQL_INSERT_DATA	An array of frame.
Each frame contains a record to insert. The names of the slots must match the names of columns of the table. The values of the slots must be of the same type than the values of the columns.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

The following code inserts one record in the "Employees" table. Only the "Name" and "Age" columns are filled. If the table contains more columns, they are filled with null values.

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_INSERT)));
m_oAGCtrl.SetSlotValue(AGSQL_TABLE_NAME,	COleVariant(CString("Employees")));
m_oAGCtrl.SetSlotValue(AGSQL_INSERT_DATA ".0.Name",	COleVariant(CString("Smith")));
m_oAGCtrl.SetSlotValue(AGSQL_INSERT_DATA ".0.Age",	COleVariant(35L));


AGSQL_UPDATE

This request allows you to update records in a table of a selected ODBC data source.

Required Slots:

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

AGSQL_TABLE_NAME	A string containing the name of the table where the records are to be updated.

AGSQL_UPDATE_DATA	A frame containing the column values to update in the subset of records defined by AGSQL_WHERE.

Optional Slots:

AGSQL_WHERE	A frame which represents the filter that determines what subset of records are selected from the table. If this slot is not supplied, all the records of the table are updated.
See AGSQL_WHERE frame.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_UPDATE)));
m_oAGCtrl.SetSlotValue(AGSQL_TABLE_NAME,	COleVariant(CString("Employees")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_FILTER,	COleVariant(CString("Name = 'Doe'")));
m_oAGCtrl.SetSlotValue(AGSQL_UPDATE_DATA ".Age",	COleVariant(34L));


AGSQL_DELETE

This request allows you to delete records from a table of a selected ODBC data source.

Required Slots:

AGSQL_EXECSQL_DATA	A string containing the SQL statements.

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

Optional Slots:

AGSQL_WHERE	A frame which represents the filter that determines what subset of records are to be deleted from the table. If this slot is not supplied, all the records of the table are deleted.
See AGSQL_WHERE frame.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST,	COleVariant(CString(AGSQL_DELETE)));
m_oAGCtrl.SetSlotValue(AGSQL_TABLE_NAME,	COleVariant(CString("Employees")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYNAME,	COleVariant(CString("Name")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYVALUE,	COleVariant(CString("Smith")));


AGSQL_SELECT

This request allows you to select records from a table of a selected ODBC data source.

Required Slots:

AGSQL_SELECT_DATA	A array containing the name of the columns to be retrieved.

AGSQL_ODBC	A frame containing the ODBC data source definition.
See AGSQL_ODBC frame.

Optional Slots:

AGSQL_WHERE	A frame which represents the filter that determines what subset of records are to be selected from the table. If this slot is not supplied, all the records of the table are selected.
See AGSQL_WHERE frame.

Result slots:

EXTENSION_RESULT	A frame which contains the result value of the request.
See EXTENSION_RESULT frame.

AGSQL_SELECT_RESULTS	An array of frame. Each frame contains the selected values of the selected records.
If no record matches the query, the array is empty.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));
m_oAGCtrl.SetSlotValue(EXTENSION_REQUEST	COleVariant(CString(AGSQL_SELECT)));
m_oAGCtrl.SetSlotValue(AGSQL_TABLE_NAME,	COleVariant(CString("Employees")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYNAME,	COleVariant(CString("Name")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYVALUE,	COleVariant(CString("Doe")));


AGSQL_ODBC frame

This frame allows you to define an ODBC data source

Required Slots:

AGSQL_ODBC_SOURCE	A string containing the name of the data source.

Optional Slots:

AGSQL_ODBC_LOGIN	A string containing the login name used to access the data source.

AGSQL_ODBC_PASSWORD	A string containing the password corresponding to the login name.

If you do not want to transmit a login name and a password over a network, you can set up a login name and a password in the Windows Registry for each data source.

Setting up a login name and a password for a data source

Warning

Modifying the Windows Registry may be hazardous. If you manually modify the Registry, take all the necessary measures for saving it before to avoid damaging it. If you damage your Registry you might not be able to restart your system.

To setup a login name and a password, select the key

HKEY_LOCAL_MACHINE\SOFTWARE\Aldea Interactive\Active Gateway\AGSQL

and create a new subkey. This subkey must have the same name than the data source name (cases are relevant). Selected the new subkey and add two values of type REG_SZ named "LoginName" and "Password".

In the following screen shot, a login name ("MyLoginName") and a password ("MyPassword") are set for the data source called "AG_SqlSrv".

Example:

m_oAGCtrl.SetSlotValue(AGSQL_ODBC_SOURCE,	COleVariant(CString(_T("MySource"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_LOGIN,	COleVariant(CString(_T("MyLogin"))));
m_oAGCtrl.SetSlotValue(AGSQL_ODBC_PASSWORD,	COleVariant(CString(_T("MyPassword"))));

See also

AGSQL_CLOSE_DATASOURCE, AGSQL_OPEN_DATASOURCE


AGSQL_WHERE frame

This frame allows you to define a SQL filter either directly using SQL (slot AGSQL_ODBC_AGSQL_WHERE_FILTER) or letting the extension constructs it (slots AGSQL_WHERE_KEYNAME and AGSQL_WHERE_KEYVALUE).

Slots:

AGSQL_ODBC_AGSQL_WHERE_FILTER	A SQL string containing the filter.

AGSQL_WHERE_KEYNAME	A string containing the name of a column of the queried table.

AGSQL_WHERE_KEYVALUE	The value of the AGSQL_WHERE_KEYNAME column.
Required if AGSQL_WHERE_KEYNAME is supplied.

Example:

m_oAGCtrl.SetSlotValue(AGSQL_WHERE_FILTER,	COleVariant(CString("Name = 'Doe'")));

or

m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYNAME,	COleVariant(CString("Name")));
m_oAGCtrl.SetSlotValue(AGSQL_WHERE_KEYVALUE,	COleVariant(CString("Doe")));

See also

AGSQL_UPDATE, AGSQL_DELETE, AGSQL_SELECT


EXTENSION_RESULT frame

This frame is returned by the extension in the EXTENSION_RESULT slot of the response frame.

Slots:

EXTENSION_RESULT_CODE	The result value of the request. A long integer (type asLong).

EXTENSION_RESULT_MESSAGE	A string containing the error message.

EXTENSION_RESULT_REASON	A string containing the error reason. This slot is not always present. Especially if the request was successful, EXTENSION_RESULT_CODE is equal to 0 (= aeNoError).

See also

Return values


AGSQL : Return values
Return values

aeNoError	The query was successful
aeExtensionUnknownRequest	The request is unknown. You have to specify one of the requests handled by AGSQL.
AeSqlDatabaseError	AGSQL failed to open the database. Check the name of the data source, the login name or the password.
AeSqlMissingParam	A parameter is missing. The error reason contains the name of this parameter. For example, you did not add the AGSQL_TABLE_NAME slot in the request frame.
AeSqlInvalidParam	One of the parameter is not valid. It usually happens when the type of a slot you supplied does not match with the type of the corresponding column in the table.
AeSqlServerError	The database server returned an exception. The error reason contains the message of this exception.
AeSqlQueryError	The query failed. Your select statement cannot be performed on the database server.
See also

EXTENSION_RESULT frame