Ïðîäîëæàåòñÿ ïîäïèñêà íà íàøè èçäàíèÿ! Âû íå çàáûëè ïîäïèñàòüñÿ?

ascDB - cursors in multitier applications

ascDB is a set of ActiveX components that can simplify and accelerate the work with DB in the multi-tier environment. It allows to use any Windows (NT, 9x, W2k) application as an application server. Such an application can also work as a service under Windows NT/2000. But the most convenient way is the usage of Microsoft Transaction Server (MTS) under Windows NT or Windows 9x and Component Services (COM+) under Windows 2000. ascDB is a DCOM-based technology, so it can use all the advantages of Automation marshaling for the network data transfer or between OS processes.

What are the advantages of ascDB? Let’s start from some general points.

First, ascDB allows you to use ANY (for the intellectual majority I must repeat ANY!) development tool, available for Wintel platform. The only term is the COM supporting. It’s not so easy, isn’t it? Everyone who tried to create components that can work equally well with Microsoft or Borland products (let alone Sybase or Oracle), can evaluate this moment. Really the ascDB library allows to write in C/C++, VB, VJ++, Delphi, Cobol or any other Automation compatible language and development tool. Each of this languages can be used for creating both server and client part of your application. Interesting point — you can use script languages (JavaScript or VBScript, and VBA). So, ascDB can be called from browsers, or used in Internet applications, or just in Microsoft Office or Visio documents.

ascDB provides network interaction or, rather, this network interaction is provided by DCOMresources, but through ascDB components. Thus you can get rid of the drivers from certain supplier. You must just have the registered ascDB components on the client machines — only several DLL’s. The registration is quite simple and can be done manually or with the help of a small installer that can be downloaded from this site.

With ascDB an ordinary ISAM DB (like Dbase, Paradox, Access) can become a multifunctional client-server DB. Sure, one can hate or like ascDB for this. You’ll hate ascDB if you are selling a cumbersome and very expensive SQL-server. And you’ll like this product if you are the end-user solution provider, and if you need smooth scaling of your applications from Access or MSDE to MS SQLServer or Oracle's Enterprise Edition.

You cannot arbitrarily scale the application using two tier architecture in principle. The problems of compatibility will appear on the way of scaling inevitably. For example you cannot transfer the MS SQLServer application to Oracle 8 without losses of functionality or productivity. The same statement will be correct for another servers — call any pair you want. For such a transfer you must rewrite the whole application - just to get all the advantages of Oracle.

ascDB can incapsulate in the server component all details of the interaction with specific server , and often you can ignore such moments because ascDB components, which have the direct interaction with DBMS, were already optimized for specific servers.

In the two tier architecture any application that pretend to be universal has to use the minimal set of SQL rigidly corresponding to the standard (not SQL'1999 but SQL92).That results in a decreased productivity due to increased amount of network calls. ascDB can simplify the problem. Server component and DB server can reside at the same machine, or the server component can be connected using more wide channel. Smaller latency of the communication channel decreases the criticality of numerous calls. The illustration of this statement is a comparison of working via Internet channel and with local DB.

There are a lot of religious disputes pertinent to the advantages of different languages — which dialect is better PL-SQL, Transact-SQL or something else. We want to offer you to use the primitive object oriented languages (like ?, ?++, Object Pascal, Visual Basic, Java etc.) instead of incredible and somewhere even object oriented script languages. Using any of this language you can get the whole functionality of ascDB library and visual tools that included.

What does the optimization of ascDB for multitier environment mean? Competent separation of the components between server and client and their adaptable interaction will provide maximizated productivity in the multi tier network environment. Using of ascDB will preserve from extreme situations — from permanent "twitching" of the network or from transferring excessive amounts of information in single call via the network. The information will be divided into blocks on the server side and joined on the client side transparently for the user. Client will apprehend the information like a scroll cursor.

The cursor can be passed without any overhead charges through several network computers and, nevertheless, end user will work with the server directly, omitting intermediate stages.

ascDB is oriented for handling extreme amount of data. ascDB fully optimizes the work with large resultsets and allows the programmer who using ascDB not to keep in mindall low-level details of interaction with server. For example, if you want to build the cursor based on query that returns a big amount of data (several thousands rows or more) using ascDB, you can build forward-only cursor that allows to instantly get the first data chunk and later get next data chunk if necessary. ascDB allows to cache data on client side and to organize free navigation through downloaded data. To browse data in the data grid ADO requires the keyset cursor. In interaction with the most of SQL servers such approach will cause the serious decrease of performance if resultset is too cumbersome. ascDB not imposes to use only this way, and adaptable technology of caching will hide all details of interaction with them.

ascDB uses OLE DB for the access to the DB. Thus you can use any DBMS if exists OLE DB provider or ODBC drivers. In general the debugging was performed in MS SQL Server 7.0/7.5 beta 1, but in the nearest future will be available support and corresponding optimization for MS Access and for popular SQL servers. Earliest of them will be, probably, IBM DB2, Oracle, Informix and InterBase. Drivers for specific DBMSs must reside only on the server side. This is very convenient for the deployment of systems with numerous workplaces because you don’t need to take trouble over drivers for each of them. This is especially topical if the application must cooperate with servers from different vendors. For the debugging purposes would be more convenient to supply the developer’s computer with both client and server sides of ascDB, including the DBMS drivers. It will allow you to debug both sides of application, transparently go on from client application to the server objects and vice versa.

And in the end what’s the necessity of ascDB?

The principal concept that we’ll use below is the cursor. Cursor is a loose concept. On the one hand cursor is a data structure that allows the data publishing. On the other hand this is an object used for feedback to server. As a matter of fact, let’s gain an understanding of our conception of cursor step by step.

Client/Server and Client vs. Consumer

Let’s make a digression to grasp the terms. Server is an application, running on the server side, which aims at publishing data through ascDB cursors. Such application is also called ascDB provider. Server is a simple term while the client can result in mishmash. In most cases the term "client" means the program running on a workstation, workstation itself or a human who works with workstation. In our case it’s wrong or, rather,not completely correct. On the one hand cursors’ are designed for use in applications running in workstation. On the other hand cursors can be used for passing data to another servers on a network, to another server applications that are working on the same computer or for the DB access inside the server. Thus be careful please, because in the most cases the term Client will mean a Consumer. The real localization of a client (Consumer) is not too important because DCOM marshaling eliminates the distinction between processes on same computer and what is more makes remote object call methods transparent (indistinguishable from local).

Accessing to data using cursor

Practically ascDB doesn't contain any resources (means?) for passing the cursor to the client. Thus you have to create COM object so that client will get a cursor through the parameters of its methods . Another state of affairs impossible since there is no point in using application server, and the architecture turns into two tier one. As it was mentioned above the ascDB purpose is a simplification of work with DB in the multi tier client/server architecture.

Object may be an EXE-server or it can be located in DLL. If the object is located in EXE-module, it can be called without any additional means. If an object is located in DLL, you must use MTS or COM+. As a matter of fact, DLL object can be launched directly, like in-process server, but such an approach is usable for debugging purposes or while creating single user version of multiuser application. To make the component launch not as in-proccess server but in application server, you must just register it properly.

General principles

Suppose, you would like to create an object which method must return the cursor. What's necessary to do? Just add VARIANT type parameter to the wanted method and assign attribute [in, out] or [out]. In VB it will look like ByRef ParamName As Variant. VB programmer can ignore the description of this parameter because by default VB uses just this description.

The components of ascDB library can be divided into five groups.

  1. Server components. Their task is to execute an SQL-requests both returning the cursor or not, to handle the recording of changes in DB, to manage transactions and customize the data source parameters. This components can be found in ascRemoteDB library.

  2. Non-visual client components (further — local components). They are providing a simple navigational access to cursor data, the data caching, the block receiving of data from server components, the passing modified data to server components to recording them in DB, the customizing the properties of cursor and its columns, the event notification. This components are located in ascLocalDB library.

  3. Non-visual ActiveX controls are providing the loading of remote components, calling the methods of those components that are returning cursors, connection of the visual controls and the most amount of features, realized by 2-nd group of components. All the enumerated features can be customized in program mode (from the code in run-time) or using visual customizing at the design time. This components are located in ascLocalDB library.

  4. Visual ActiveX controls providing visualization and editing of data, the adaptable mechanism of the functionality enhancement, event notifications (most of which redefines the handling of events of 2-nd and 3-rd groups). Mainly, this components are located in ascDbControls library.

  5. Service components. They are providing simplification and acceleration of some design and administrative functions. Often the service components are created by pair: one component provides the main functionality and the second one is a visual ActiveX control which provides graphical user interface (GUI).This components are located in ascLocalBaseControls and ascRemoteBaseControls libraries.

Let’s look at the interaction of described components.

The client creates component on the server using CoCreateInstance (?/C++) or CreateObject (VB). Please don’t forget that the loaded component isn’t included in ascDB. That's your component!. It will be more simple to use special non-visual ActiveX control - ascLoader - to load the component if the cursor must be used for visual navigation. Using ascLoader you can visually select the needed user component in the server at the design time. The choosed component will be automatically created at the moment of creation of a form in which ascLoader was placed after selection ’True’ for the property "ascActive". Otherwise it will be necessary to select the value of ascActive property at runtime.

Client calls the method of remote object and receives the cursor in the Variant variable. Then the client creates the caching cursor ascCachedCursor and attaches to it the cursor received from the server. Essentially, that’s all if the cursor is used only from the code. If the cursor must be used in some visual ActiveX controls, it would be better to use ascVisualCursor instead of ascCachedCursor. You don’t need to create ascVisualCursor dinamically. More preferable is to create it visually by placing on the form at the design time and. ascVisualCursor allows you to call the method of remote object (the object itself must be loaded by the ascLoader object). As with ascLoader, ascVisualCursor can be visually customized at the design time, and after this you can select using ascActive property whether it is necessary to call method automatically on the form loading or not. When customizing you can attach ascVisualCursor to the ascLoader, customize cursor parameters, set defaults for called method parameters, design cursor’s columns. Also you can add user columns.

The next stage is a visualization (not a visual handling) and/or data editing. As in described situations enumerated operations can be executed in the code or data can be directed to some special visual components. At this moment there are three visual ActiveX controls that can be attached to the ascVisualCursor. The first of them is ascGrid - the table like DataGrid from VB. Second is ascDataField - a field. Third of them, ascComboBox - is a pulldown list. All components are supporting socalled pluggable editor and pluggable painter. Pluggable editor is a visual ActiveX control that pops up in an ascGrid cell or in a field of ascDataField component. These components are responsible for interaction with user. Painters are responsible for visualization of cell when it is visualized in ascGrid, ascDataField, or ascComboBox (if the cell is on the foreground). Painter is a simple COM object that realizes a special COM interface. You can use included editors and painters, in most cases needed objects will be connected automatically because of column type information. Or, if you aren’t satisfied by the functionality of included editors and painters, you can create them yourself. For this task you can use any development tools that can create COM object and visual ActiveX control. You can attach editors and painters to the specific column selecting them directly in the column properties or by assigning the user-defined datatype to the column (previously you must select wanted editor and/or painter in the special service component). You can make almost total customizing both for ascCachedCursor, ascVisualCursor and ascGrid locally and directly for the cursor on the server side. User-defined datatype, editor or even painter aren’t an exclusion. As an addition to the standard properties you can select any quantity of extended (user defined) properties of Variant type. They can be transferred via network consisting of the cursor.

You can make data modification programmatically using ascCachedCursor methods, or interactive in ascGrid, ascDataField and ascComboBox. Modified data will be automatically stored in the buffer and then send to the server to be written in DB. Such sending can be performed automatically by leaving of the cell or row, or by calling Save method of ascCachedCursor or ascVisualCursor components. The event on which the recording will be initiated can be defined in the rsUpdateType property of these components. you can interfere in the writing process on client through the connection to the events of ascCachedCursor and ascVisualCursor components or from the server (Look for details in "Editable cursor"). You can use ascCursorChanger component on server to easethe process of writing.

The client-server cursor passing scheme

The client application indirectly calls the method of server component designed by user using ascVisualCursor component.

The instance of user server component performs SQL query using server ascDB components, creates cursor and returns it to the client through the Cursor parameter.

ascVisualCursor attaches the cursor , switches to the active status and notifies all connected visual ActiveX components.

After this the ActiveX control ascGrid receives the confirmation about activation, visualizes data and allows to perform a navigation and data editing.

Error handling

ascDB uses a standard COM method of error handling. In case of an error its code, message and some additional information will be assigned to the thread where the error occurred. It is the responsibility of SetErrorInfo API function. Such an approach allows to transfer the information about the error via the network freely (if a calling program runs on another computer). VB uses the same technique. If the call was made not from the code but from the visual component, after receiving of error code it displays the message window and terminates the current operation. You can design a handler of special event and perform your own error handling. If the call is generated from the program you must manually handle the error. For instance, in VB - by using the On Error operator.

Creating a user component that returns cursor

In our example we’ll use MS SQLServer 7.? with installed Northwind DB as a server.

Let’s create a new VB project. Select ActiveX.DLL in the project creation dialog.

Figure 1. Creation of a new project

Open project properties and change parameters as shown in fig. 2

The Single Threaded model selected to avoid the VB assistance. Those assistance results in the creation of distinct copies of global variables for every created object, In MTS/COM+ you can use "shared properties". To close the project select OK.

Figure 2. Project properties.

Rename created default class Class1 to Customers. After this set the MTSTransactionMode property of this class to NoTransactions (fig. 3).

Figure 3. Object Customers properties

Add the method that must return the cursor:

Public Sub GetCustomersList(ByRef Cursor As Variant)
    CurSes.Execute Cursor, "select * from customers"
End Sub

Save your project (File\Save Project) and compile it (File\Make NorthWind.dll). Open project properties again and in the Component property page select Binary Compatibility option. This will save you from components re-registration in MTS or COM+ after each compilation. Select OK and try to compile the project again. If everything was correct, VB would suggest you to replace existing NorthWind.dll. Select Yes. File must be saved without any additional messages. And if your feeling is not agreed with described reality, look for wrong steps that maybe you committed ;-).

Naturally, it’s not a complete version, but even now we can try to load our component using ascLoader.

For immediate debugging in MTS/COM+ you must create package/application in MTS Explorer/Component Services on the server and register your DLL and ascRemoteBaseControls.dll in it. ascRemoteBaseControls.dll contains ascRemoteLoader component that is used by ascLoader to get the list of registered components from the server.

Our component must be properly registered for ascLoader to see it. Open ascDB.msc (Microsoft Management Console file supplied with ascDB) and select "Remote Objects Manager". Select our component (NorthWind.Customers) and after this press "<". The name of our component must move to the left list. It means that the component is registered now and that it will be shown in the list of remote components on ascLoader property page.

After registration you can create the second component. It will be a project of simple EXE-application with GUI. Rename it to VNorthWind and after this save it.

Add reference to the ascLocalDB.dll as it shown in figure 4.

Fig. 4. Junction components from ascLocalDB.dll

Create ascLoader component in the form (it is named ascLoader1 by default). Select the ascObjectName property in its properties. The button of drop-down list will appear. Select it and from the list select the name of our component. True value for ascActive property will cause a loading of our component. "Just do it." If the property does not change, it means that ascDB components were not installed properly or that you made a mistake somewhere.

ascLoader can load ascRemoteLoader from any computer of the network. Type the server name in the "Server" field of its property page and press Enter (or select the server from list using the "…" button near the corresponding field).

Now the time to fill the methods of our server object with some content has come. Save VNorthWind and open our source project (NorthWind).

To get an access to the DB you must create ascServer object. ascServer is a singleton object. Singleton objects are theobjects that are loaded in memory only once when creating. Anybody trying to create it will receive a pointer to the same instance. This object gives you an access to the list of data sources. The data source describes a connection to the DB, but basically it can describe a connection to another data sources represented by OLE DB provider. Each data source can have any quantity of its own (unique) parameters, but usually some subset of parameters for DB-based sources is used: "Provider" — OLE DB provider name, "Data Source —server name, "Initial Catalog" — DB name, "User ID" — user name and "Password"— a password. Every provider can contain any number of additional parameters. For instance, virtually all the SQL servers support "Connect Timeout" parameter - the period of waiting for the server's answer until the failure of the connection attempt. This parameter is supported only by SQL servers, it has no sense for ISAM DB like MS Access. The specification of unsupported parameters usually makes the data source inoperable. The data source is represented in ascDB by the IascDataSource object.

We separated the processes of creation and tuning of data sources - just for the simplicity purposes. The data source can be tuned from ascDB MMC snap-in (the Data Source Manager branch, fig.5). The information about tuned data sources is placed into server's registry. The tuning itself can be performed from any computer on the network.

When the ascServer is asked about the data source, it goes to the registry, reads the information and creates the requested IascDataSource object. To define it more exactly, the data reading is performed only once, on the first request,the second and all further requests would receive the previously read data source information. If the field "Initial amount of sessions" is filled while IascDataSource creation, the specified amount of sessions would be created immediately. If the next request exceeds this amount, an additional session would be created. "Maximum amount of sessions", how it follows from the name, limits an amount of sessions. At the achievement of the limiting number of sessions the report on the mistake will be issued. If this undesirable, just leave this field blank.

Figure 5. Data Source Manager

The ascServer object affords a list of data sources (IascDataSource objects). It's possible to get a pointer to the concrete IascDataSource by calling the ascServer.Item(...) method and assigning as an index a line, which contents the data source name. Naturally, source with such a name must be beforehand described, as on this was spoken above. Method "Item" is a method by default, so VB programmers can use more simple syntax: ascServer("DataSourceName"). Fission of "duties" allows to simplify and accelerate a work with data sources. It's possible to guarantee the protection from malicious penetrators by setting the protection attributes for the registry branches with information on data sources.

As you understand, the data source is not valuable by itself, we need it for the connection creation to DB, described by the data source. The session is presented in ascDB by the IascSession object, which personifies a connection, allows to control transactions, execute SQL-queuries etc. Strictly from IascDataSource we need only a reference to IascSession. It's necessary to call IascDataSource.GetSession method for this. We do not need IascDataSource as itself, in VB it's possible to simply execute something like: rv.Item("NorthWind").GetSession(ailReadommitted). In this instance NorthWind - a name of the data source, and ailReadCommitted - a level of insulating of a transactions for the required session.

We need the derivable session only for the creation of cursors by execution of SQL-queries like SELECT. Cursor can live as long as it want and, indeed, after transferring it to the client we have no way to influence on its lifetime. So we have to provide a separate transaction for every cursor - because it's defined for a session. Another way is to create all the cursors in one session.

But this way can cause several problems

  1. Some servers on some insulation levels are destroying the cursor at the commit moment

  2. Low insulation level (Read Uncommitted) causes the phantoms (dirt).

  3. High insulation levels, such as ailRepeatableRead and ailSerializable, are leading to the invisibility of other sessions (transactions) data even for new cursors - nobody can commit the transaction in our session/

  4. DB cannot be modified in this session, because some wrong SELECT operator can cause the rollback of the whole transaction.

  5. Naturally, several parallel cycles of data modification couldn't be performed in this session - as it can bring to the unpredictable results.

It's, however, possible to create some separate session for every human and filter the calls by the user name, but who can guarantee that one user name wasn't used on two or more workstations, or that one user didn't launch two copies of application? In general such an approach is very difficult in realization. But all those requirements could be met by setting the transaction insulation level for this session to Read Committed. You are to avoid the data modifying SQL queries in this session also. Taking into consideration that multi-tier architecture doesn't allow to use OLE DB tools for writing the changes in the cursor, this approach looks rather pretty. (Note: Don't worry, we realized the automated recording of changes in ascDB, but we'll talk about it a little later)

So, to use a session repeatedly we'd place it in a global module. We shall create it on the DLL loagding in the Main procedure. Don't forget to set the initial object to "Sub Main" in the project properties (fig.2). Let's call it "GlobalModule".Here is the code to be placed there:

Global srv As ascServer
Global CurSes As IascSession
Public Sub Main()
   Set srv = New ascServer
   Set CurSes = srv("NorthWind").GetSession(ailReadCommitted)
End Sub

From this code you can see, that ascServer we called "srv", and IascSession is named "CurSes". Now the time had come to make our object create cursor and return it to the calling application. Let's go to the Customers class module (Customers.cls) and change it's code the next way:

Public Sub GetCustomersList(ByRef Cursor As Variant)
  CurSes.Execute Cursor, "select * from customers"
End Sub

You, surely, understood that the "Execute" method executes an SQL query and returns a cursor if everything is going correctly. This method has plenty of parameters, but only two first of them must be specified. The other are optional. Later we'd look through those parameters, but now it's much more interesting to compile our object and try to use it in the test application.

So, just compike it and open VNorthWind.

Open the form and drop there the ascVisualCursor component. Select ascLoader1 on the "Main" page of the component properties in the list of visual loaders and turn the "Activate" switch on. All the controls except the swich are to become unavailable. That means, that the call of the object's metod was succesful. Now you can deactivate the switch and try to make out other parameter on this property page. They are describing the called method. Only the "used parameter" drop-down list requires some explanation. There the parameters of the called method, potentially capable to be a cursor ([in, out] VARIANT)., are enlisted. If you have several parameters in te list, select the necessary one (ascVisualCursor can use only one cursor). Don't forget to turn on the switch before closing the property page, otherwise the following step beside you will not be got

Now you will link up the visual components, and the first application would be almost ready. The visual components are stored in the ascDbControls.dll library. The complete name of the library in VB is "ascDbControls 1.0 Type Library". Link it to the project. For this right-click on the VB Toolbox and select Components from the context menu. Select the ascGrid from the components list, place it on the form, set the appropriate size and find "ascSourceName" in its properties. Select it, and choose a value "ascVisualCursor1" from the list. After that the data from DB must appear in the table (grid) (see fig.6). If this did not happen, ensure that the ascActive properties of the ascLoader and ascVisualCursor components were set to True.

By the way, did you mention that data becomes available in design time? From the table's context menu you can select the Adjustments point. These will allow you to walk through the data and set the suitable column sizes. If the cursor was editable, you could even make some DB modifications.

It is, of course, a very simple example. A very simple query is used, no parameters, cursor doesn't support recording and no properties are changed. But it can clearly demonstrate the principles of work with the ascDB library. The main advantage of use of the multitier architecture is the ability to set business rules flexibly while processing and to provide an additional control on data. In this case we could strict the access to the returned data by defining the protection attributes for the whole object (in MTS/COM+ Explorer) or on the single method level programmatically. For instance, we could define in the MTS/COM+ Explorer the role of some person, and in the component code return the complete or restricted list of customers according to the person's role. For the instructions look the MTS/COM+ programming reference.

Fig. 6. Here is the form of our first application

Further, to demonstrate the ways of work with ascDB components we shall add a method to our component. One of parameters of this method will set a search template. We shall also make our cursor editable and discuss the client component abilities on data formatting and advanced data presentation. But beforehand let's pay some more attention to the used components.

The main task of ascServer object is presentation of the list of data sources and execution of some service functions. For example, ascServer.SetCursorUserData(Cursor As Variant, UserData As Variant) allows to associate your data with the created cursor. You can specify any kind of data, supported by the variant type, including an array. Now we shall speak about IascSession, because the detailed examination of ascServer has no sence - for now.

IascSession represents the connection to DB. The transactions could be controlled only through the session. All executed requests, both creating cursors and no, are created within the framework of some session. There is a special system session ("Default"). It always exists, and it is possible to call it through the methods of ascServer object. This session is used for system necessities. You may use it for internal necessities, but better require own session. The way to do it was shown in our example. In general, it is necessary to use sessions sensibly, on the one hand, not allowing to create excessively big amount of them, but on the other, correctly manipulating with already created. You must not allow the mixing of actions, belonging to different transactions. As was it already spoken, for the cursor creation the correctly adjusted, "long living" sessions could be used, but for the modification of DB - dynamically required copies, released after the transaction termination. A pool of sessions could be organized - in order not to slow a server functioning by the dynamic request of sessions. The parameters of pool are adjusted in Data Source-designer of MMC console.

Let's look through the IascSession object methods.

The first method of this object is intended for the creation of commands. The commands (objects IascCommand) allow to create cursors on the base of the parametrized requests. The talk about commands will go later. This method description looks as follows:

HRESULT CreateCommand(
   [in, defaultvalue(NULL)] IDispatch *Changer, 
   [in, defaultvalue(0)] long CursorID, 
   [out, retval] IascCommand **ppascCommand
);

or:

Function CreateCommand([Changer As Object], [CursorID As Long]) As IascCommand

The first parameter of this method - Changer - allows to assign an object, which will produce a record of changes made in the cursor (refer to below). The second parameter - CursorID - allows to assign an unique identifier, which will allow you to separate one cursor from other in the process of recording of the changes.

The following method, Execute, is already familiar. As you already have understood, it executes SQL and returns a cursor. The SQL query, executed by this method, can not contain external parameters (constants only). If it is necessary to execute a parameterized query, it is necessary to use IascCommand object. This method description looks as follows:

HRESULT Execute(
   [in, out] ASC_CURSOR *Cursor, 
   [in] BSTR SQL, 
   [in, defaultvalue(NULL)] IDispatch *Changer, 
   [in, defaultvalue(0)] long CursorID, 
   [in, defaultvalue(ascCUR_DEFAULT)] ASC_DB_CURSOR_TYPE DBCursorType, 
   [in, defaultvalue(0)] long FetchBufferSize, 
   [in, optional] VARIANT UserData 
);

or

Sub Execute(
   Cursor,
   SQL As String,
   [Changer As Object],
   [CursorID As Long], 
   [DBCursorType As ASC_DB_CURSOR_TYPE = ascCUR_DEFAULT],
   [FetchBufferSize As Long],
   [UserData]
)

Parameter Cursor is, certainly, a cursor. SQL means a line, containing an SQL-expression. The 3-rd and the 4-th parameters correspond accordingly to the first and second parameters of the preceding method. DBCursorType - defines a type of the cursor (refer to Table 1). FetchBufferSize - defines a size of block of record sent in one network call. UserData - the data which could be associated with the created cursor (they will not be sent on the network, but it's possible with their help to keep the necessary information in each cursor). Apart is necessary to speak of the type of the cursor (parameter DBCursorType). How it follows from the method description, this parameter by default has the value ascCUR_DEFAULT ascCUR_DEFAULT means that while the cursor creation a definition of real type will be taken from the information, received in Cursor parameter. This information fits there on the client side by means of ascVisualCursor object or ascCachedCursor. ascVisualCursor does this automatically, and the cursor type itself can be adjusted visually through its properties. How to realize an adjusting of the cursor type by means of ascCachedCursor, will be shown later. Now, since we did not assign strictly the DBCursorType, you may experiment with the settings of ascVisualCursor. All said on DBCursorType is correct for FetchBufferSize also, but "0" is used as a value for the instruction that information is necessary to take from the client,.

If the information on the type of the cursor and size of buffer did not come from the client, the values DBCursorType = ascCUR_FORWARD and FetchBufferSize = 100 would be used. If DBCursorType parameter or FetchBufferSize has non-default value, the clients adjustments are ignored.

The following method of the IascSession object is called ExecuteImmediate. It is intended for performing of non-creating cursors SQL-queries,. For instance, requests, executing the data changes in DB (Insert, Update, Delete), or calling the stored procedures, in the same way not returning cursors and not having output (out) parameters:

HRESULT ExecuteImmediate(
   [in] BSTR SQL,
   [out, retval] long *RowsAffected
); 

or

Function ExecuteImmediate(SQL As String) As Long

Table 1

ASC_DB_CURSOR_TYPE enumeration description

Value

Description

ascCUR_DEFAULT

The value is defined by the called line. If neither calling no server sides defined the definite value, the cursor type would be set in ascCUR_FORWARD.

ascCUR_INSENSITIVE

After the query execution the cursor doesn't see the data modifications made by other transactions.

ascCUR_KEYSET

After the query execution the cursor see the data modifications made by other transactions, but only on the next buffer reading (see the note). The cursor doesn't see the lines addition and deletion.

ascCUR_DYNAMIC

After the query execution the cursor see all the data modifications, but only on the next buffer reading (see the note). The cursors of this type do not support the absolute positioning.

ascCUR_FORWARD

The cursor of this type supports the forward-only scrolling. But within one buffer the other scrolling methods are also available, and in case of the cache turned on it's possible to move to any cashed record. For the majority of SQL servers ascCUR_FORWARD is the fastest mode not demanding a lot of resourses.

Note:  Regardless of the type of the cursor the data caching of cursor on the client side is used in ascDB. So ascCUR_KEYSET- and ascCUR_DYNAMIC-type cursors are reading changes only in first fetch. Moreover, since physically fetching is produced in blocks, it is impossible to be assured that the updated data from the server rather then kept in the cache were read,. But caching allows to realize the high-performance scrolling cursor with any principle of access.

SQL is a line, containing an SQL query. RowsAffected is an amount of processed lines, returned by SQL server. For instance, it can be an amount of deleted lines for the Delete operator.

Hereinafter the methods are going, which are providing the support of transactions. The first of them is TranBegin. How it follows from its name, it creates (opens) a new transactions. Here is its description:

HRESULT TranBegin([out,retval] long *TransactionLevel);

or

Function TranBegin() As Long

This method has no input parameters. It returns an actual insulation level (TransactionLevel) of new transaction.

TranCommit and TranRollback methods accordingly commit or rollback a transactions. These methods do not return any values, but can (C/C++ programmers must not forget that interpretation in this article goes in VB terms, and "initiate exceptions" means that method returns a mistake (negative return code), at that an additional information could be received through GetErrorInfo function.). Here is their description:

HRESULT TranCommit();
HRESULT TranRollback();

or

Sub TranCommit()
Sub TranRollback()

The last method of IascSession object is TranJoin. It allows to connect to the distributed transaction, organized by means of Microsoft Distributed Transaction Coordinator (MSDTC). Its description looks as follows:

HRESULT TranJoin([in] IUnknown * TransactionCoord);

or

Sub TranJoin(TransactionCoord As Unknown)

The execution of parameterized queries

Parameterized queries executing

The methods of IascSession object allow to execute SQL-queries, but do not allow to assign parameters for these queries. Certainly, some simple input parameters could be assigned by concatenation of their values to SQL-expressions. But if the matter concerns the parameters returned by stored procedures, or simply parameters of complex types of data, it is impossible to manage only with IascSession methods. To call the parameterized queries, it is necessary to use IascCommand object. This object, either as IascSesion, cannot be created by means of "new" operator (CoCreateInstance in C/C++). It's possible to get it calling the CreateCommand method of IascSession.

As was it already spoken earlier, all the parameters of this method are optional. They are necessary when making the editable queries. Presently we shall not discuss these parameters, but go over to the IascCommand object description procedure.

So, it is necessary to describe the parameters before using them in the IascCommand object. It is necessary to use a "Params" property for this. Its description looks as follows:

[propget, id(DISPID_COMMAND_PARAMS)] 
   HRESULT Params([out, retval] IascParams **pVal);
[propput, id(DISPID_COMMAND_PARAMS)] 
   HRESULT Params([in] IascParams * newVal); 

or

Property Params As ascParams

Params is the command parameters collection.

To add a parameter, it is necessary to call the IascCommand.Params.Add... method. Indeed, method Add belongs the ascParams object, but it is possible in VB to omit this detail. Method Description Add looks as follows:

HRESULT Add(
   [in,defaultvalue("")] BSTR Name, 
   [in,defaultvalue(apfIn)] ASC_PARAM_FLAG Flag, 
   [in,defaultvalue(ASC_CDT_STRING)] ascColumnDataType Type, 
   [in,defaultvalue(256)] long Size, 
   [in,optional] VARIANT Value, 
   [out,retval] IascParameter **ppParameter
);

or

Function Add(
   [Name As String], 
   [Flag As ASC_PARAM_FLAG = apfIn],
   [Type As ascColumnDataType = ASC_CDT_STRING],
   [Size As Long = 256],
   [Value]
) As IascParameter

Name here is a name of asserted parameter. If the name is not given, parameter will get a name "ParamX", where X - a serial number of parameter. Flag - a flag, describing, is a parameter incoming (apfIn), returned back (apfOut) or both of them (apfInOut). Value - allows to assign a value of parameter by default. Size - a maximum size for parameters with variable-length types. Oddly to say, for the string input parameters (type apfIn) the specification of length is optional. So in the real work a size must be specified only for a limited number of data types.

If this method was successfully called, it returns IascParameter object. It is possible to make some additional adjustments of an added parameter by means of IascParameter. But usually there is no need in this, since all parameters could be specified while calling an Add method.

Params keeps a method Move (except standard methods of a list management (Item, Count, Add, Remove, Clear and _NewEnum)), which allows to change a serial number of parameter (move it), and CanModify property, which allows to get the information on the possibility of modification of a parameters structure at the given time. If CanModify is set to False, only the values of parameters could be changed, but not their amount or properties.

Params is a default property, so it is possible in VB to address to parameters without the evident specification of name of this characteristic. For instance, it's possible to write:

cmd.Params("CompanyName") = "F%"

or

cmd("CompanyName") = "F%"

or even:

cmd!CompanyName = "F%"

It's not enough to describe a parameter in order to use it. It must be also marked in the SQL query. This is done by entering a "?" character in place of the substituted parameter.

Let's add a new method in our server component, returning the same list, but filtered by the "CompanyName" field.

Here is the required code:

Dim cmd As IascCommand
Public Sub GetCustomersListByCompanyNameMask(ByRef Cursor As Variant, ByVal CompanyNameMask As String)
    ' Create a command only once...
    If cmd Is Nothing Then
        Set cmd = CurSes.CreateCommand(Changer, 1)
        ' Add parameter description
        cmd.Params.Add "CompanyName"
    End If
    cmd("CompanyName") = CompanyNameMask
    cmd.Execute Cursor, "select * from customers where CompanyName Like ? order by CompanyName"
End Sub 

Now we shall recompile the NorthWind.dll and open VNorthWind project. Now we need to change a method called in ascVisualCursor1. Let's open the "Main" property page and deactivate ascVisualCursor1 by the "Activate" switch. Now we shall choose our new function from the drop-down list "List of functions". We'll choose "CompanyNameMask" and field "value of parameter" in the parameter list, and enter a mask. For instance, "%" (show all records). Press "Apply" and turn the "Activate" switch on. Close a dialogue of property pages. Place a TextBox on form. We shall enter a mask in it. Create beside this TextBox an event handler Change. Add in handler a code so that handler looked as follows:

Private Sub Text1_Change()
    ascVisualCursor1.rsFuncParameter(1) = Text1
    ascVisualCursor1.Refresh
End Sub 

rsFuncParameter property allows to set or read a value of the parameter of the called functions by its serial number. Parameters must be described beforehand (we have done this in the page of properties.).

Press F5 and test the resulting application. If you have done everything correctly, after the mask entering the table must show the records, satisfying this mask. To test a multiuser mode, you may compile an EXE- file and start it several times on one or several machines.

Right before we created an example working with parameterized queries. As it was spoken above, such a functionality we are reciving from IascCommand object.

Let's in brief analyse its properties and methods.

Properties Changer, CursorID, SQL and methods Execute, ExecuteImmediate are similar to the same name methods of the IascSession object, so we shall not pay them a lot of attention. The only that is necessary to say - properties Changer, CursorID, SQL duplicate similar parameters of some methods, allowing to assign corresponding values only once instead of doing it every time (as in IascSession).

Method Prepare allows to prepare a query. Herewith occurs an analysis (parsing) of SQL-expressions, verification of the information on parameters, preparing of a query etc. Hereon query is executed faster and consumes less system resources. However some servers are optimizing the queries without using Prepare, and the possibility exists that general performance would increase unessentially or even fall for the using of this method. For instance, Prepare with MS SQLServer 7.0 gives a small deterioration of performance in contrast with usage of not prepared, but parameterized SQL queries. Additionally, the Prepare using (with MS SQLServer 7.0) leads to some problems with parallel using of a command. Don't forget that using of Prepare with other servers can give an essential performance advantage. But you are to be careful with Prepare using. Here is its description:

HRESULT Prepare(
   [in, defaultvalue("")] BSTR SQL,
   [in, defaultvalue(FALSE)] VARIANT_BOOL GetParams
); 
or
Sub Prepare([SQL As String], [GetParams As Boolean = False])

SQL parameter can be specified both while preparing, and beforehand, through SQL property. The Second parameter speaks, whether to try to read a parameter description. This is possible only if such a functionality is supported by OLE DB-provider.

No properties of the command could be changed after the query preparation. The only exclusion are the values of parameters. The other properties of a command could be changed only after command's translation to the unprepared state by the Unprepare method. It has no parameters and can only initiate an exception if and when the command was not prepared. The condition of a command (prepared or not) can be found out from the IsPrepared property. It has a Boolean type.

Table 2

ASC_SUPPORT_MULTIPLE_RESULTS enumeration description

Value

Description

smrNotSupported

Several cursors can not be returned

smrSupported

Several cursors can be returned, but the previous cursor must be closed before the opening of the next.

smrConcurrent

Several cursors can be returned. The cursors could be used simultaneously.(At the end of this millenium no SQL servers supported such an ability.)

Some, but far from all, servers support a return of several cursors for one call of the Execute method. It is possible to define, whether a server support returning of multiple cursors or not, through the value of SupportMultipleResults property. It has a type ASC_SUPPORT_MULTIPLE_RESULTS (refer to Table 2) and it is a read-only one.

To get several cursors, beforehand is necessary translate a command in the respective mode. To Do this by means of characteristics MultipleResults possible, it has a type Boolean and can be as installed, so and scanned.

The first cursor becomes available at once after the Execute method execution. To receive the following cursor, it is necessary to call a method GetNextResult. Remember, that if the provider supports only smrSupported type, you are to close the previous cursor before the GetNextResult call. Tha result of such a limitation is that only the last cursor in the list can be returned to the client. Yuo can use other cursors safely only on the server.

Here is the description of a GetNextResult method:

HRESULT GetNextResult(
   [in,out] ASC_CURSOR *Cursor,
   [out,retval] ASC_GET_NEXT_RESULT_CODE *pCode
);

or

Function GetNextResult(Cursor As Variant) As ASC_GET_NEXT_RESULT_CODE

It returns the following values: gnrcNoResult - there are no more results, gnrcNoCursor - the results are present, but the cursor was not created and gnrcCursor - the cursor is obtained.

Editable cursor

So, we have created a cursor on the basis of parameterized query, but our cursor is not editable! In the following step we shall make our cursor editable.

By default the cursors are not editable. For the transformation of a non-editable cursor in an editable one it is necessary to take advantage of the auxiliary ascCursorChanger object. Changing the properties of this object, the developer can thinly adjust the process of record.

Save changes concept

The ascDB concept assumes, that between the application server (where the ascRemoteDB library) and the DB server (or the information storage subsystem, further a data server - DS), the interaction occurs on a faster channel, rather than with the client (where the ascLocalDB library is loaded). AscCursorChanger is responsible for conversion of the changes, sent from the client, in a sequence of data modifying operations. Thus, the maximum productivity is achieved, the network traffic decreases, and the possibilities of modification of data extend due to the use of unique possibilities of the recording object - ascCursorChanger.

At record the compactly packed changed data are transmitted to the application server at record in one network call, and the calls to the data server can be multiple and as complex as necessary, in a word, designed for more fast and reliable connection. A good example is MS Access. It works very fast and stable on the local machine. The separate data modification calls are executed immediately, that cannot be told about it's work in a network mode. If we used the components from the ascDB library for the access to MS Access, the common result practically will be similar on a reliability and productivity to results, which can be received at use of the brand-name SQL-server.

You can interfere with the process of record, both on the client and server side. Such approach allows to encapsulate the data recording business logic in server objects, and also to organize preliminary checks of data on the client side, that, in turn, avoids the network overloading. Moreover, in the first buffer of a cursor some information allowing to make many checks in an automatic mode, , is transferred

The technology of information interchange stipulated by the ascDB library, assumes the transmission of a minimum quantity of the information through the network. For the recording of changes in the DB, the changes, without excesses are transferred only.

In the concept of the changes saving the idea of automatic generation of SQL queries by the ascCursorChanger object is incorporated. Thus the queries adding (INSERT), changing (UPDATE) and deleting (DELETE) data in the tables are generated. In practice other methods of data changing (for example, navigating way for ISAM DB) are used also. The ascCursorChanger customization does not depend on the concrete mechanism of modification of data.

The modification of data is based on:

The array containing changed or appeared data is sent directly at modification of the DB tables after the changes recording on the customer side. For example, at addition of records in the table containing column with auto increment (IDENTITY-column in MS SQL Server). It is necessary to the client to mark appropriate columns with special tag previously for the data sending.

The array of changes

The array of changes made by the client, is formed and is transmitted on the server side at call of an ascCached [Visual] Cursor. Save ()method. Structure of this array is of a little interest for us, therefore we shall consider, that it is simply a BLOB (binary information). The transfer on the server occurs automatically. Thus there is a call of a Save method of the latent ascCursor object, placed on the server, the pointer on which is included into structure of a cursor (see fig. 7). In this array besides changes are the type of operation (INSERT, UPDATE or DELETE) and value of so-called key fields (we shall speak a little later about them).

Fig. 7. Path of the information at the changes recording

The structure of DB tables and columns

That ascCursorChanger could perform a recording, the developer should in necessary scope describe the tables to modify. Many DB servers allow to study the structure of the database by various ways (tables, properties of columns), but at each creation of the new ascCursorChanger object the obtaining of such information could occupy a plenty of time. Therefore in the ascDB library other way is offered. The developer can with his own hand describe the structure of the DB tables, either manually (inside the methods of the server side user object), or with the help of the visual interface allowing to adjust parameters of the ascCursorChanger object interactively, to save customizations under the defined name in special storage and, subsequently to load such named objects (look "The visual designer of commands ").

Customization of the ascCursorChanger object

AscCursorChanger supports recording in several tables. For this purpose it is necessary to adjust property (Tables). This property and the properties of objects, included in it, define the correspondence of columns of a cursor and columns of the physical DB tables.

Fig. 8. The scheme of the changes recording.

The property Tables is a collection, which each unit contains the description of one table. The description of each table contains subsections responsible for addition, deleting and change of data. Each of these subsections is represented by the list containing the description of columns of the table. Each description, in turn, represents the description of a column (name, data type, maximum size, etc.) in DB and additional information (number or name of a column of a cursor, the value from which should be written down in this field, tag, talking that the value of this column should be returned to the client after execution of recording). The access to such description of a field is made as follows:

Tables("The name of table").Insert("The name or index of column").XXX

For example, the following line allows to set "a source column " for a column "CompanyName" of the "customers" table in the Northwind DB:

cmd.Tables("customers").Insert("CompanyName").SourceColumn = "CompanyName"

This line speaks to the ascCursorChanger object, that at a data insertion in the "customers" table the data for a field "CompanyName" should be taken from the same column of a cursor. Instead of the name of a column it is possible to set a serial number of a column of a cursor.

In a fig. 9 the block diagram of the description of the ascCursorChanger object tables is represented

This figure shows that the description of columns is various for different operations. It is caused by that different operations need the different information. So, for operation of deleting (Delete) it is necessary only to specify key fields (field, on which value the deleted line will be uniquely defined). For the insertion operation (Insert) it is necessary to specify correspondence of columns of the table and cursor. Besides, it is necessary also to specify the key fields, by marking them, in case of necessity, as returnable - piotOutAlways, as a field with auto increment - avspFromIdentity, as a field, for which ascCursorChanger should generate new unique value - avspFromNewID etc. For update, as well as in the previous case it is necessary to specify key fields and to specify correspondence of columns of the table to columns of a cursor.

There is a possibility to set SQL-expressions executed before some stage of recording or after it for the extension of possibilities of the data recording process. The expressions executed before any stage, are set through PreSQL properties, and executed after any stage - through PostSQL. These SQL-expressions can open and complete transactions, write data in a log of changes, and so on.

The recording of changes

Let's create the object of the ascCursorChanger class with the Changer name. One ascCursorChanger object can be used for any amount of cursors. Main, that the cursor, whose information is written, corresponded(met) to the description stored in the ascCursorChanger. Proceeding from this, we shall declare Changer as a global variable.

Global Changer As New ascCursorChanger

It is convenient to customize it in the "Main" procedure. In view of changes GlobalModule should look as follows:

Global srv As ascServer
Global CurSes As IascSession
Global Changer As New ascCursorChanger
Public Sub Main()
    Set srv = New ascServer
    Set CurSes = srv("NorthWind").GetSession(ailReadCommitted)
    'Addition of the table, in which the changes will be made.
    Changer.Tables.Add "customers"
    'The customization of the added table
    With Changer.Tables("customers")
        'Customization of the operation of insertion (Insert)
        .InsertSQLParamAdd "CustomerID"
        .InsertSQLParamAdd "CompanyName"
        .InsertSQLParamAdd "ContactName"
        .InsertSQLParamAdd "ContactTitle"
        .InsertSQLParamAdd "Address"
        .InsertSQLParamAdd "City"
        .InsertSQLParamAdd "Region"
        .InsertSQLParamAdd "PostalCode"
        .InsertSQLParamAdd "Country"
        .InsertSQLParamAdd "Phone"
        .InsertSQLParamAdd "Fax"
        
        'Customization of the operation of updating (Update)
        .UpdateSQLParamAdd "CustomerID", Flag:=spfKeyNotUpdatable
        .UpdateSQLParamAdd "CompanyName"
        .UpdateSQLParamAdd "ContactName"
        .UpdateSQLParamAdd "ContactTitle"
        .UpdateSQLParamAdd "Address"
        .UpdateSQLParamAdd "City"
        .UpdateSQLParamAdd "Region"
        .UpdateSQLParamAdd "PostalCode"
        .UpdateSQLParamAdd "Country"
        .UpdateSQLParamAdd "Phone"
        .UpdateSQLParamAdd "Fax"
        
        'Customization of the operation of deletion (Delete)
        .DeleteSQLParamAdd "CustomerID"
    End With
End Sub

Fig. 9. Structure of the description of the tables in the ascCursorChanger object

Table 3

The description of ASC_VALUE_SQL_PARAM enumeration

Value

Description

avspFromChanges (used by default)

The value is taken from the changes array received from the client

avspFromDefault

The value is taken from the DefaultValue parameter

avspFromIdentity

Orders to read the value of an autoincremental field

avspFromNewID

Orders to generate a new integer value

avspFromString

The value is taken from the DefaultValue parameter but must be substituted as a string. This allows, for instance, to specify expression or function

avspFromUserData

The value is taken from the UserData attribute, stored in the cursor on server side. The UserData attribute can be specified on the Execute method call of IascCommand, IascSession and ascServer objects. It can be also set for the created cursor directly with the help of SetCursorUserData method of the ascServer object.

The methods xxxSQLParamAdd allow to set all attributes of one column at once. These methods have a lot of parameters, but almost all of them are optional. Main at call of these methods - to specify correspondence between a column from DB and column from a cursor (first and second parameter accordingly). In our case the name of columns of a cursor correspond to the names of columns of the DB table. It means, that it is not necessary to fill in the second parameter (name of a column of a cursor - SourceColumn). The ascCursorChanger substitutes this parameter by a name of a column from a cursor by default. Hardly it is more difficult with key columns. At operations Update and Delete, ascCursorChanger object must simply know the list of key columns (other way ascCursorChanger can not unambiguous identify changed or deleted record). It is not necessary basically to know at the Insert operation, what are the key columns. But it is frequently necessary to generate unique values for key columns, the key column can be of an auto incremental type, the value of key fields can be formed by the trigger (in two last cases the values, generated on the server, should be read out from the server and returned to the client after an insertion). The ValueSource parameter answers how the value in a described column is formed. It has the type ASC_VALUE_SQL_PARAM, the possible values of this parameter are described in tab. ? 3. Pay attention, if in this parameter there will be a value which is distinct from avspFromChanges, the value, coming from the client, will be always ignored. In our example the key field is filled by user, i.e. it is necessary to specify value avspFromChanges in this parameter, but it is a not standard case. The values avspFromIdentity or avspFromNewID are usually applied, and others are applied in such exotic cases, that do not deserve the special analysis (at least, in this article). We shall leave by a default the avspFromChanges value (the majority of non-key fields as a rule use this value, therefore we have no necessity to fill in this parameter).

So, we have described already three parameters of a method InsertSQLParamAdd, and it would be logical to continue this description, but before it is necessary to give the complete description of this method. Here it is:

HRESULT InsertSQLParamAdd(
   [in] BSTR Name,
   [in, optional] VARIANT SourceColumn,
   [in, defaultvalue(avspFromChanges)] ASC_VALUE_SQL_PARAM ValueSource,
   [in, defaultvalue(adspOptional)] ASC_DEFAULT_SQL_PARAM DefaultValueSource,
   [in, optional] VARIANT DefaultValue,
   [in, defaultvalue(piotInOnly)] ASC_PARAM_IN_OUT_TYPE InOutType,
   [in, defaultvalue(sptFromSource)] ASC_SQL_PARAM_TYPE Type,
   [out, retval] IascInsertParameter ** pIascInsertParameter
); 

or

Function InsertSQLParamAdd(
   Name As String, 
   [SourceColumn],
   [ValueSource As ASC_VALUE_SQL_PARAM = avspFromChanges], 
   [DefaultValueSource As ASC_DEFAULT_SQL_PARAM = adspOptional], 
   [DefaultValue],
   [InOutType As ASC_PARAM_IN_OUT_TYPE = piotInOnly], 
   [Type As ASC_SQL_PARAM_TYPE = sptFromSource]
) As IascInsertParameter

The following parameter - DefaultValueSource, defines a source of data in a case, when for a described column the main source (ValueSource) is given as avspFromChanges and the data were not brought in by the client. The type of the DefaultValueSource parameter - ASC_DEFAULT_SQL_PARAM. Its values are described in tab. ? 4.

The DefaultValue parameter is used, if in the ValueSource parameter avspFromDefault or avspFromString is indicated. If avspFromString is indicated, the value of the DefaultValue parameter is transformed to a string, and already string is substituted in SQL (avspFromString is inapplicable, if not generation of SQL queries, but the direct modification on the basis of ISAM-methods is used for the data recording).

The DefaultValue parameter is used also, if in the DefaultValueSource parameter adspFromDefault or adspFromString is indicated. The remarks and conditions described for a case with ValueSource = avspFromString are fair for this case also.

The InOutType parameter of a ASC_PARAM_IN_OUT_TYPE type is used for the indication, whether it is necessary to return a value of a described field to the client. The value of a field can vary during the processing of other parameters of the same field or because of the DB trigger operations. The description of this parameter possible values is given in tab. ? 5.

Table ? 4

The description for ASC_DEFAULT_SQL_PARAM enumeration

Value

Description

adspOptional used by default

The field is optional. If the value of a field is not specified in the array of changes, this field is not included in a data modifying SQL-operator (Insert, Update).

adspFromDefault

If the value of a field is not specified in the array of changes, the value would be taken from the DefaultValue parameter.

adspRequired

An obligatory value. If the field value was not set, the exception is initiated.

adspFromKey

If the value of a field is not specified in the array of changes, the value would be taken from the key fields.

adspFromString

If the value of a field is not specified in the array of changes, the value would be taken from the string, located in the DefaultValue parameter.

adspFromUserData

If the value of a field is not specified in the array of changes, the value would be taken from the UserData property of the cursor. (look in tab.3 - avspFromUserData)

adspFromNewID

If the value of a field is not specified in the array of changes, the new unique integer value would be generated.

Table 5

The description for the ASC_PARAM_IN_OUT_TYPE enumeration

Value

Description

piotInOnly

The field value is never returned to the client.

piotOutAlways

The field value is always returned to the client.

piotOutIfNoValue

The field value is returned to the client only if the value is not received from the client in the array of changes.

The last parameter of a InsertSQLParamAdd method - Type. It has a data type ASC_SQL_PARAM_TYPE. With the help of this parameter it is possible to set a data type of a described field. Except for supported data types, there is a value sptFromSource in the ASC _ SQL _ PARAM _ TYPE list. This is a default value. It is interesting not by itself, but as it allows to read a data type from a cursor field specified in the SourceColumn parameter. it is necessary to specify manually a data type of a ValueSource field in the Type parameter, if not avspFromChanges, avspFromIdentity or avspFromNewID is specified for this field. If ValueSource is set to avspFromString, the Type parameter loses any sense.

This method returns the IascInsertParameter object (descriptor of a field).

The second method we used for the description of the table - UpdateSQLParamAdd. As follows from its name, it is used for the description of fields in the table of DB for the Update operation. It is possible to tell, that parameters of this method are practically the same, as of the InsertSQLParamAdd method. The exception makes the Flag parameter with the ASC_SQL_PARAM_FLAG type (see tab. ? 6).

There is a difference in the interpretation of some parameters of methods UpdateSQLParamAdd and InsertSQLParamAdd.

Table 6

ASC_SQL_PARAM_FLAG enumeration description

Value

Description

spfNormal

The usial field

spfKey

The key field. The field is used for the row identification while modification or deletion

spfKeyReadOnly

The key field, non-editable on the client. The field is used for the raw identification while modification or deletion.

The third method - DeleteSQLParamAdd - is intended for the description of fields of the table at the Delete operation. Here is the description of this method:

HRESULT DeleteSQLParamAdd(
   [in] BSTR KeyColumnName,
   [in, optional] VARIANT SourceColumn,
   [in, defaultvalue(sptFromSource)] ASC_SQL_PARAM_TYPE Type,
   [out,retval] IascDeleteParameter ** pIascDeleteParameter
); 

or

Function DeleteSQLParamAdd(
   KeyColumnName As String, 
   [SourceColumn], 
   [Type As ASC_SQL_PARAM_TYPE = sptFromSource]
) As IascDeleteParameter 

The KeyColumnName parameter is intended for definition of key fields. Other parameters are similar to the parameters of InsertSQLParamAdd.

The methods InsertSQLParamAdd, UpdateSQLParamAdd and DeleteSQLParamAdd are intended for the reduction of the description of fields of appropriate operations. For customizations of separate attributes of the description of fields it is possible to take advantage of lists Insert, Update and Delete.

the ascCursorChanger object must be connected to a cursor after customization. It can be made in an Execute method of ascServer, IascSession or IascCommand objects, and it is also possible to connect ascCursorChanger to the IascCommand object at its creation. The IascCommand object with the connected ascCursorChanger automatically connects it to each cursor, created through it, if, certainly, in an Execute method another ascCursorChanger will not be specified. This last way we also shall use to connect ascCursorChanger and to make our cursor editable.

Bring in change in line:

Set cmd = CurSes.CreateCommand()

of the GetCustomersListByCompanyNameMask method of the Customers class, so that it would look like:

Set cmd = CurSes.CreateCommand(Changer, 1) 

So, that's all... Just compile NorthWind. DLL and, to check up editing, open and start the execution of the project VNorthWind. You can save the changes by selecting Save from the context menu. Yes, if at attempt of compilation you'd receive a message:

just open MTS Explorer/Component Services Manager and say "shut down" to the NorthWind application

The extended mechanism of the recording of changes

If you want to intervent into the saving process you can connect any object, implementing method ascSaveCursorData to the IascCommand command object. The description is given below:

Function ascSaveCursorData( 
    ByRef UserData As Variant, 
    ByRef CursorID As Long, 
    ByRef BlobChanges As Variant
) As Variant

For example, if you want to connect your object instead of ascCursorChanger, you must not only implement the above described method, but change the line:

Set cmd = CurSes.CreateCommand(Changer, 1)

to

Set cmd = CurSes.CreateCommand(Me, 1)

Note for C/C++ programmers: Me is a pointer on the object, available in the methods of this object (analogue of "this" in C ++).

After that your object's method ascSaveCursorData will be called at the moment of recording, and one of its parameters will transfer the array of changes to you. It will be not possible to use this array directly. You can connect it to ascCursorChanger instead, and receive an access to the array of changes through it methods.

To connect the array of changes to ascCursorChanger, it is necessary in ascSaveCursorData to call a method AttachChanges of the ascCursorChanger object and to transfer the array of changes to it as the parameter.

AscCursorChanger represents the array of changes as the two-dimensional array. On the first axis the changed records are placed, and on the second - column are enumerated.

Each record has a type of change (Insert, Update, Delete). The changed fields contain these changes, and not changed - the Empty value (VT _ EMPTY).

Feeling tired from ascCursorChanger, you can perform the recording in DB manually (by separate SQL-calls) or to call a method ascSave of the ascCursorChanger object.

The code of a method ascSaveCursorData is given below:

'A Method of the user object changes recording
Function ascSaveCursorData(UserData, CursorID As Long, BlobChanges)
   Dim Changer As ascCursorChanger
   'Connection of the array of changes to the ascCursorChanger object.
   Changer.AttachChanges UserData, BlobChanges
   'Access to the list of changes (change of the first record's first cell).
   Changer.Changes(1)(1) = "Some Data"
   'Recording the modified changes in DB.
   Changer.ascSave
End Function

It is possible to reassign Pre and PostSQL-expressions inside a method ascSaveCursorData, to permit or to prohibit recording of separate cells by setting the IascUpdateParameter.Flag columns properties and other properties influential in the DB changes recording process. There is a way to turn the changes recording possibility on or off. It can be made by implementing a ascCanSaveCursorData method in the connected object.

The note for C/C++ programmers: if you want to use the advantages of ascSaveCursorData and ascCanSaveCursorData methods, do not forget, that the interface, in which you realize them, should be dispinterface or dual-interface (to be inherited from IDispatch). You call is made by name, that is at first IDispatch::GetIDsOfNames is called, and then - IDispatch::Invoke, to which the DISPID obtained from GetIDsOfNames is transferred.

On a customer coast

If you have executed all the operations described up to this, you have two projects. The first implements a full-functional middle-tire component capable to return ascDB-cursors through its methods. The second contains a pair or two pairs of insignificant code lines and three ActiveX-components. Practically all operation on the remote component connection we have executed by visual tools. If we had a task to quickly create some middle-tire project working with DB, the simplifications on the customer side would be the large advantage... But we do not search an easy way! Our schedules include a good desire of detailed and all-round mockery above the readers. So we shall begin discussion of possibilities, provided by a customer part of the ascDB library with a stonely-sadistic expression on a face.

First that we shall consider - operation with ascDB-cursors from the code of the program. What it is necessary for? Well, it not a question. The manual sunset is a favourite occupation of the Russians. Though about Russian I, probably, went too far. This feature is simply inherent in all the people in Russia, it was so closely integrated with daily occurence, that has become a national tradition. If to select a group not on their nationality, but by profession, there undoubtedly will be programmers on the first place. And, most likely they will not stand idle, but fussy assemble a hypercybernetic bicycle. The reasons are banal, the manual processes are easier for inspecting, you see. Yes, it is completely clear, that I (programmer) can make this process better, more fast and with smaller expenses (of processor time, memory etc.).

So we shall gradually begin.

Object is simply created by the regular methods of language, on which you work. For VB it - CreateObject or operator new, for C ++ CoCreateInstance [Ex]. The method call doesn't differ from the call of any other method. Before the call it is only necessary to declare a Variant type variable, in which the cursor will be returned. Let's name it Cursor.

After a call the Cursor variable will contain the first block of data (in binary sort), the metainformation describing a cursor and its columns, and also the pointer on the latent remote object, with the help of which the swapping of the following data blocks and the data transfer for recording on the server will occur in further. All this sounds dreadful, but it is impossible to organize productive and convenient operation in the disrtibuted environment in other way. For the simplification of interaction with a cursor in a local part there is a special object in ascDB - ascCachedCursor (I already mentioned it). If our cursor was connected to such an object, it would be possible to interact with a cursor in a simply navigation style, as though the cursor's data were located on the local side. The navigation style means, that the cursor data are represented as the two-dimensional array, the access to which units is carried out line by line. That is, it is necessary to move on it previously with the help of one of methods of navigation (see tab. ? 7) for reading fields of some record. Remark, all the navigation methods have the prefix Fetch. Other methods of ascCachedCursor and ascVisualCursor objects have the prefixes too. With the help of these prefixes the methods are divided into several logical groups. Such splitting allows to find the necessary method fast with the help of "complete word" in VB, CV ++, Delphi etc.

To read data from a definite field, it is necessary to select a necessary column and to read value. It is possible to select a column with the help of Columns property. This property represents a collection. The entry of this collection s the description of a column. It is possible to use the column's name or serial number as the identifier of a column. One of the column's properties column (Value) allows to receive from a column data for the current string. Speaking more strictly, the property Value returns not a value, but an object allowing to manipulate by the value of a column, but, as this object by default has a property returning Variant - "AsVariant", it is possible to tell at a stretch, that the property Value returns value. What for the Value property returns some object, instead of the packed in Variant value? Such a solution allows to receive not some abstract Variant, but the value coerced to the necessary type., it is possible to take advantage of one of AsXXX properties to receive the value coerced to some type. XXX is a name of a necessary data type. The type conversion by a cashing cursor itself occurs much faster, than converting in variant and back. It is possible in VB for simplification of the code writing not to specify at all the Value property, as the column has a latent property _ Value, used by default.

Table ? 7

The description of ascCachedCursor and ascVisualCursor objects navigation

Value

Description

FetchFirst

Fetch the first row.

FetchLast

Fetch the last row.

FetchNext

Fetch the next row.

FetchPrev

Fetch the previous row.

FetchRandom

Fetch the arbitrary row, the number of which is specified in the RowNum parameter.

FetchRelative

Fetch the row, the relative number of which is specified in the RowNum parameter. For example, if the current row has #5, the FetchRelative(-3) call would move the cursor to the row #2, and FetchRelative(2) - to the row #7.

FetchFreezePos

All the navigation functions result in the cursor position changes. Sometimes it is necessary to read the data, but not to change a cursor position. For example, ascGrid uses this method, when outputs rows on the screen. This method has the Freeze parameter of a Boolean type, which allows "to freeze" or to defreeze a cursor position. In the frozen state no Scroll event is fired, as results in an impression of a cursor position invariance.

Let's create an example receiving data from a cursor and placing them in a text field. For this purpose add to our form the new button. Name it "pbFillList". After that add a new text field ("Text2") and set its properties MultiLine and ScrollBars in True and vbBoth accordingly. Create (by double click) a "... _ Click " event for the new button and insert the following code there:

Private Sub pbFillList_Click()
    Dim Cursor As Variant
    Dim CCursor As New ascCachedCursor
    RemObj.GetCustomers1 Cursor, "%"
    CCursor.Attach Cursor, True
    Dim col As IascColumn
    Dim sText As String
    Do
        Dim sLine As String
        sLine = Empty
        For Each col In CCursor.Columns
            If sLine <> Empty Then sLine = sLine + vbTab
                sLine = sLine & col.Value '.AsVariant
        Next
        sText = sText & sLine & vbCrLf
    Loop While CCursor.FetchNext() = rsrcOk
    Text2 = sText
End Sub

The result of your operations must look like shown in a fig. 10.

In general each of objects included in the ascDB library, is rather complex. Practically each of them is worthy the descriptions in separate article, but, due to the considered defaults of method parameters and properties even a beginning VB-programmer can use them without preparation. The same about the column object. On the one hand, it allows to receive or to set a column value easily. With another, it allows to receive (and to set) a plenty of the information about a column and data. You can even add your own column which has not been connected directly to the cursor data. Except for the description of a column it is possible to set /read a 0Caption, visual properties and so-called extended (user) properties. Visual properties?! But what visual properties can the non-visual cashing cursor columns have? Practically any, which can have an ascDataField and ascGrid type visual component field.

Fig. 10. A programmatically filled text field.

It is natural that it is possible to use them in the code only for reading and installation, but you can connect one of the listed visual components to a caching cursor, and they will display all customizations of visual properties automaticaly. The visual properties are available through the VisualProp property of ascCachedCursor and ascVisualCursor objects. The list of currently supported visual properties is shown in the table 8. The visual components use defaults if some property was not specified and if it was not indicated, that the value would be taken from the parent object. The defaults correspond to the values specified in Control Panel\Regional Settings and in Control Panel\Display\Appearance.

The possibility of modifications of visual properties not only in visual components, but also in ascCachedCursor and ascVisualCursor, allows to centralize the visual data representation processing. The centralization does not force to make all world combed under one comb. You can define any visual property not only in ascCachedCursor and ascVisualCursor, but also in any visualization component (ascGrid, ascDataField or ascComboBox).

But all that sounds uninteresting in comparison with the fact that you can set all these properties on the server side, just after the cursor creation and before it will be returned to the client, and secondly, all (!!!)the visual properties of columns without exception can be changed dynamically. For example, you have a field of a "many" type, and you want to display negative numbers by red colour, and number exceeding ten thousands, dark blue. For this purpose it is necessary to connect to the "AfterDataRead" event of any columns implementing object (ascCachedCursor, ascVisualCursor, ascGrid, ascDataField or ascComboBox) and to write the code of column visual property modification, based on the experimental field value. The AfterDataRead event is called after transition (Fetch) to any line. Any visualization component should proceed to the redrawed line before redrawing. By default any transition to other line causes events AfterScroll and BeforeScroll in a cursor (before and after the transition accordingly).

But the components of an ascGrid type swich at the moment of drawing to a so-called messages moving freezing mode (prohibits dispatch AfterScroll and BeforeScroll), before the transition on the drawed line, thus not disturbing the main program logic.

The AfterDataRead message is transmitted independently from the messages moving freezing. In common and whole, the dynamic modification of visual properties allows to simplify the creation of applications with friendly user interface, not resorting to such complex ways, as manual drawing of cells, and the centralized processing allows to simplify the creation of uniform style in your applications.

The second interesting moment is that it is possible to assign unlimited (more correct, limited only by common sense) amount of extended (user) properties for each column in a cursor. The user property is some property with the name and value of a Variant type. The extended properties are available through ExtProp property of the IascColumn object. ExtProp has a string index, which is the name of property. The attempt to receive the value of the non-existing property will return Empty., It is just enough to set the Empty value if you want to clear the earlier specified property. It is possible to set extended properties at any stage of a cursor life (on the server, in the cashing or visual cursor, in any visualization component ). If the property is specified on the server, it would be impossible to clear it. It will be possible only to correct its value in the derived components (ascCachedCursor, ascVisualCursor, ascGrid, ascDataField or ascComboBox). If the value was corrected at some level, the clearing of the value would result in receiving of the value from the previous level.

Table 8

Column visual properties

Value

Type

Description

Width

Long

The width of column in pixels.

EditControlID

String

The identifyer of visual ActiveX component, used for the field editing.

Align

Enum

The alignment type (left, right, center)

ForeColor

Color

The text colour.

BeckColor

Color

The background colour.

Font

IFontDisp

Font.

Format

Enum

The format type. Allows to set one of the predefined formats of screen output or specify that the format mask defined in the FormatString property will be used. The predefined formats are taken from the current user settings (Control Panel\Regional Settings).

FormatString

String

The string of the screen output format. Used if the «fmtCustom» value specified for the "Format" property.

DomainName

String

Name of the user data type specified for this field. This name is similar to the domain in a DBMS or typedef in C/C++. Is used for the definition of additional properties of a column. It is possible to set the domain properties through special service components.

Visible

Boolean

Allows not to show the column in the visual components like ascGrid.

PropFromParent

Boolean, with index

Allows to specify, that some properties are not defined and that their values are to be taken from the parent component columns. For example, the cashing cursor can have its own column list. The values of visual properties could be set directly, though it is possible to specify that their properties are to be taken from the corresponding properties of the parent columns (of the cursor).

PropCanRequest

Boolean, with index

This property allows to find out could be the values of the definite visual properties read. This property is read-only.

Table 9

"inf" properties of ascCachedCursor and ascVisualCursor objects

Value

Description

infCountRow

Returns the number of rows in cursor. The value is unavailable if the cursor was opened in a unidirectional mode and not all the rows were read yet. infCountRowsRead property can be used instead of this property.

infCountRowsRead

Returns the number of already read rows in cursor.

infCurrentRow

Returns the serial number of a selected row.

infReadOnly

Allows to find out, whether it is possible to edit a cursor. For example, this property is set in True, if the ascCursorChanger object was not connected to a cursor.

infRowChangeType

Allows to find out the type of the row modications (rctInserted, rctInserting, rctUpdated, rctDeleted) or reports that the row was not changed (rctNotChanged).

infRowError

Returns the object, describing an error occured at recording of DB changes for current string(line).

infSaveErrors

The list of all the errors occured at DB changes recording.

infState

Returns the state of cursor.

I have already told a lot about the extended properties, but have not told yet, what for they can be used. Each of you can find your own ways to use the extended properties. But one specialized application nevertheless is. It is connected to the editors and painters (about which the speech went earlier). Through the extended properties the editors and painters can receive the extended information. For example, we implement an editor, which allows to select a unit from the list and to place in a cell some value, associated with it. That is, speaking easier to set the list for the editor looking like drop-down list. If you are going to create your own editors, you can take an advantage of extended properties too.

Other properties of columns do not deserve the waste of a precious place of the journal publication. Therefore we shall let alone columns and we shall come back to the cashing and visual cursors.

If such parameters as the type of a cursor and size of the network buffer (amount of lines readable for one network call) were not specified hardly on the server at the cursor creation , it is possible to set them directly at call of a method returning a cursor. It cannot be made directly. It is necessary to set appropriate properties (rsDBCursorType and rsFetchBufferSize) of a cashing cursor. After that it is necessary to assign a value of AttachInfo property of a cashing cursor to a cursor variable (transmitted to the server object). Thus the cashing cursor will bring the necessary information in a cursor, and on the server side at the Execute method call the information will be read out and used at the cursor creation. In case of a visual cursor it is not necessary to perform all this, as it makes it automatically. The following interesting property of ascCachedCursor and ascVisualCursor objects is rsUpdateMode. It allows to define an event, on which a recording of changes (creation of the buffer and sending it on the server) will be done. Three values are supported: cumCell - record occurs at the cell editing termination, cumRow - the record occurs at leaving the line, cumSave - the record occurs only at manual call of a Save method. By selecting cumCell or cumRow, you can be saved of necessity to have an eye on DB changes saving, and by selecting cumSave it is possible to optimize the network traffic, transmitting all changes for one network call.

The properties with the "inf" prefix allow to receive the cursor information. Their list is given in tab. 9.

The following group of properties are the properties with "Is" prefix. They are listed in tab. 10.

Table 10

The "Is" properties of ascCachedCursor and ascVisualCursor objects

Value

Description

IsEmpty

Returns True if the cursor is empty (contains no records)

IsEOF

Returns True if the cursor is positioned behind the last row.

IsFirst

Returns True if the cursor is positioned on the first row.

IsLast

Returns True if the cursor is positioned on the last row.

It is possible to set an amount of errors through the rsSaveStopErrorsCount property. The recording of changes stops after this amount of errors and the control is transferred to the client. The zero means that any amount of errors is accepted.

Events of cashing and visual cursors

That the interaction with cashing or with a visual cursor could be interactive and simple, in them the set of events is defined. All events have one of three prefixes: Before (is called before some operation), After (is called after some operation) and On (is called directly in some operation execution time). All the Before-events have the parameter allowing to cancel current operation. The list of ascCachedCursor and ascVisualCursor objects events is given in tab. ? 11.

Table 11

The ascCachedCursor and ascVisualCursor objects events

Value

Description

AfterCancel

Is called after a cancellation of the entered changes

AfterChangeCell

Is called after the cell modification.

AfterClose

Is called after the cursor closing.

AfterDataRead

After jump to another record (before their visualization)

AfterDelete

Is called after the cell deletion.

AfterInsert

Is called after the cell insertion.

AfterInsertCancel

Is called after the cell insertion undoing.

AfterOpen

Is called after the cursor opening.

 

AfterRowsRead

Used together with monodirectional cursor. Is called after reading a new buffer from server. the information concerned to the number of rows in the cursor become accessible at this stage.

AfterSave

Is called after the data recording in DB.

AfterScroll

Is called after scrolling. Note: The scrolling can happen due to the user operations, software cursor position movement by FetchXXX methods or on the cursor opening. This message os not called if the FetchFreezePos method with the True parameter was called.

BeforeCancel

Is called before the cancellation of the entered modifications.

BeforeChangeCell

Is called before the cell modification.

BeforeClose

Is called before the cursor closing.

BeforeDelete

Is called before the row deletion.

BeforeEdit

Is called before the cell editing.

BeforeInsert

Is called before the row insertion.

BeforeOpen

Is called before the cursor closing.

BeforeSave

Is called before writing the data to the DB.

BeforeScroll

Is called before scrolling. (see note to AfterScroll event)

OnCalculate

Allows to calculate the necessary values in progressive total and write those values into the user columns. Tis event is available if the rscmBatch mode (rsCalcMode property) is on, rsDBCursorType property set in ascCUR_FORWARD and cashing is enabled.

OnCreateComplete

Is called after creation. At that moment the cursor is ready and the container (for instance, VB) can fire the events. This message is necessary because if any method of ascCachedCursor or ascVisualCursor was called on the Form_Load, the corresponding events wouldn't be called as a container was not ready to fire events yet.

OnError

Is called in the case of error. Allows to handle an occured error in yuor own way.

OnInitRow

Is called in the process of the new row insertion. Allows to set the default values for the cells of the inserted row. The modification of the inserted row made from this event is not considered as a modification.

You have a convenient and simple way to inspect both the cursor properties , and the cursor controlled data by means of cashing, and, the main, visual cursors event system.

Let's expand our test project a little. Let's add CheckBox with the "cbVCursorActive" name. It will reflect a state of a visual cursor and will allow us to change its state. By the second stage we shall make so that all odd lines had light yellow colour. In third step we shall add a user's column and we shall fill it in by a serial number of line on the OnCalculate message. We shall call the recalculation of values after the recording, that they did not lose the urgency because of shift at an insertion of new lines.

That all listed above has earned, add the event handlers:

Then enter the code so that they looked as follows:

' This variable is necessary for us for the count 
' of lines by the progressive total
Dim RowCnt As Long
Private Sub ascVisualCursor1_AfterDataRead()
   Dim col As IascColumn
   'If it is an odd string...
   If ascVisualCursor1.infCurrentRow Mod 2 = 0 Then
      'That the white background colour is installed at all columns...
      For Each col In ascVisualCursor1.Columns
         col.VisualProp.BackColor = vbWindowBackground
      Next
   Else
      ' Differently all columns have a yellow background colour 
      For Each col In ascVisualCursor1.Columns
         col.VisualProp.BackColor = &H80000018
      Next
   End If
End Sub
Private Sub ascVisualCursor1_AfterOpen()
   'We reset a calculated variable to a zero
   RowCnt = 0
   'On a cursor opening the cbVCursorActive switch is properly installed
    cbVCursorActive.Value = Abs(True)
    Dim col As IascColumn
   'and new column "MyCol" added
    With ascVisualCursor1.Columns
' By default no columns are present. That is they are, but are marked as Default. Thus the first columnsaddition will result in the Default columns clearing and our new column will remain the only one...
' In order to avoid such a situation it is necessary to set the Default columns property in False. It will transform columns in usual, as though they were added in the code of the program and after a new column addition they will not disappear.
      .Default = False
      'Now it is possible to add a new column safely...
      .Add Name:="MyCol", Caption:="My column", Type:=ASC_CDT_LONG
   End With
End Sub
Private Sub ascVisualCursor1_AfterClose()
    'On of cursor closing the switch is switched off...
    cbVCursorActive.Value = Abs(False)
    'Also are destroyed columns. For this purpose we'd translate them to the Default state
    ascVisualCursor1.Columns.Default = True
End Sub
Private Sub ascVisualCursor1_OnCalculate()
' making accounts...
' It would be possible to simply assign ascVisualCursor1.infCurrentRow to a field, but we do not search an easy way...
' The rsCalcMode = rscmBatch mode guarantees, that OnCalculate event will be called in series for all readable lines
   RowCnt = RowCnt + 1
   ascVisualCursor1.Columns("MyCol").Value.AsLong = RowCnt
End Sub
Private Sub ascVisualCursor1_AfterSave()
   'We are resetting a calculated variable to a zero
   RowCnt = 0
   'after save
   ascVisualCursor1.ReCalculate
End Sub
Private Sub cbVCursorActive_Click()
' At the cbVCursorActive switching a visual cursor is translated in an appropriate state
    ascVisualCursor1.ascActive = Abs(cbVCursorActive)
End Sub

After you change the code, it is necessary to change some settings of a cursor. First, translate a visual cursor in an inactive state. For this purpose change its ascActive property to False. Secondly, set rsCalcMode property to rscmBatch. It will turn a calculation mode on. That's all, you can press F5 and admire. Scroll the tabulared components to the right. The last column is the added on a cursor opening one. You must get something similar to the fig. 11.

Fig. 11. This way the form of our application after modification should look

it is also possible to add a column from the "columns" property page of the ascVisualCursor1 object.

Sets of service components

For simplification and acceleration of operation in ascDB there is a number of so-called service components. As a rule, they are divided into groups. Such a group consists of two components: a server (executing main operation) and a client (ActiveX-unit realizing GUI) one. Three groups are available today. These groups are listed in tab. ? 12.

Table 12

Service components group list

Component

Description

Edit Manager

The manager of editors, allowing to register new ActiveX controls and visually tune the correspondence between them and the DB domens.

Object Loader

The Object loader provides the functionality of the component loading on the remote computer. Also provides a list of the components registered on this server. The ascLoader component property page acts here as the user interface.

Command Designer

Allows to create visually the description of the command and of its changer. The adjusted command descriptions are saved in files organized in a special storage. These files can be shared with ascServer for loading the completely adjusted command on the name of the description. The graphic interface of the Command designer is available as an ActiveX control or in a MMC-console.

The visual designer of commands

The most interesting service component is Command Designer. It is intended for fast and correct customization of command (IascCommand), and also the recording object (ascCursorChanger). The graphic interface of the designer is implemented as a Wizard. It allows to pass all the stages of command creation and customization step by step. Besides, it allows to change once created and saved commands. High interactivity of the command designer sets you free from keeping in mind any DB information and aloows to be sure in serviceability of the command, since it can be tested at any stage of tuning. The second advantage of this tool is the safety, as all operations occur in a specially opened transaction, and you are deciding to record the changes.

So, as was spoken earlier, the creation of the new command is divided into several steps.

It is possible to test the command beginning with the second stage.

The appearance of the designer of commands is represented in a fig. 12.

Fig. 12. The designer of commands

To load the adjusted command on the server, one line of the code in the designer of commands suffices:

Set cmd = srv.LoadCommand("CommandName")

Now you can use all the possibilities of the command. You can change its parameters, and, of course, execute it and receive a full-scale editable cursor.

Conclusion

Îne article is too small not only to describe all the subtleties and principles of work with the ascDB library, but even to completely describe its possibilities. In the following publications we shall go in ascDB details. I wanted to mention only that the test version of the ascDB library is available from our site (www.k-press.ru) since February, 2000. You can also send there all the questions. If you want to contact me personally, send the letters to mag@rsdn.ru.

Vladislav Chistyakov


Your comments and suggestions you can send to: mag@rsdn.ru
Copyright © 1994-2016 ÎÎÎ "Ê-Ïðåññ"