Work with FireBird from 1C. Collection of proven recipes
Hello.
This material is intended to summarize and show the main techniques and techniques for working with InterBase / FireBird databases from programs running on the 1C platform. Almost everything that is stated here is somehow found in other Internet sources. However, in this article, the material is quite complete. It is possible that something from the stated topic is missed, but all the same, the material presented (in my understanding) is enough to solve most of the tasks (by the way, something did not work out - someone can tell the solution, and it will be possible to add material). In addition, a demo, attached to the article, can be used by those who wish as a framework for developing their own similar programs. Despite the fact that most of the tasks for access from 1C to third-party databases are limited by the need to load any data from an adjacent information system, we will show here more diverse options for access to databases, including various types of recording information in the database.
This post will tell you about:
- how to connect to the server (database) and disconnect when shutting down;
- how to execute SQL queries (to receive and transmit data, including those that cannot be placed directly in the query text (long lines, BLOB fields));
- how to perform data conversion, incl. text encodings when transferring data to and from the database;
- how to access the server database logic (call stored procedures);
- how to protect your program from automatically breaking a TCP connection by the database server;
- other interesting tricks related to programming in general (how to do all the above actions is convenient, reliable and portable).
So let's get started ...
Introduction.
The demo turned out to be quite large. Nevertheless, the structure of the program is quite clear - so it’s quite realistic to understand.
In the program (this is external processing), a special technique of structured programming, conventionally called "OOP in 1C" (described in more detail here ), is actively used. In fact, almost the entire general processing module (a part of the program that is not directly related to the user interface) consists of such structures.
Also used such an interface "adapt", as the "list of informational messages" (described in more detail here ).
The program was tested on versions of FireBird 1.5 and FireBird 2.1 . Most likely, it will work for any other version of the database. This database access mechanism uses ADO technology. Accordingly, the “ Firebird_ODBC ” driver and the “ gds32.dll ” client library must be installed on the computer where the 1C client is running.
')
I will not give full source codes of the program (even source codes of separate classes will be given with abbreviations). To view the source code, it is better to open a test sample directly in 1C - the quality of viewing will be much higher than what is available by means of HTML publishing.
Problem statement (the essence of a demo).
We will consider as an example a simple information system, conventionally called the “Notebook”. It will be somewhat similar to a directory that can be created in the platform. We will have a directory of contact information. The directory will have a tree of groups (directories) - you can create an arbitrary tree of any nesting. The elements of such a directory will be the so-called "Cards", i.e. actually records with contact information. In addition, in this information system there will be users (accounts) with their own access rights. No search (only data entry and navigation through the group tree). There is also no binding of users to the data that they entered (like the Primary Responsible in the 1C standard solutions documents) fields, and there is also no logging (logging) of user actions. To demonstrate the work with the base of this functionality is enough, and even more complicated the implementation of the user interface just did not want.
Here is how this application will look like (show on a series of slide-screenshots):
Main program window:
At the top is the main menu and toolbar with commands.
In the middle are the main elements of the contact directory display: in the left column is the directory group tree, and in the right column are the contents (groups and elements) of the current (selected in the left column) group.
At the bottom there is a field for displaying messages that occur during the program operation (in this case, these are error messages).
Further we will consider that:
- “ Current group ” is the group selected in the group tree (in the left column);
- “ Contents of the current group ” is a list of groups and elements displayed in the right column;
- “The current object of the current group ” is the group or element selected in the right column;
All commands (add, edit, delete, move) refer to the contents of the right column (either to the current group or to the current element of the current group) - just like in the standard interface for working with 1C directories.
The main menu and toolbar contain the following commands:
| Fig. | Team name | Description |
|---|---|---|
| “Base” → “Connection” | Connects to the database (closes the previous connection, if it was) | |
 | “Base” → “Exit” | Exit from the program |
 | “Actions” → “Add” | Create a new item (card) in the current directory group. |
 | “Actions” → “New Group” | Create a new group in the current directory group. |
 | “Actions” → “Copy” | Create a new object in the current directory group based on the data of the currently selected object. What exactly is created (group or card) depends on which object was selected in the current group. |
 | “Actions” → “Change” | Open for editing the current object of the current group (group or card). |
 | “Actions” → “Delete” | Delete the current object of the current group (group or card). |
 | “Actions” → “Move to group” | Move to another group the current object of the current group (group or card). |
 | “Actions” → “Refresh” | Reload all data (both the group tree and the contents of the current group). |
| “Settings” → “Change Password” | Change current user password | |
| “Settings” → “User List” | Editing the list of users of the database. | |
| “Settings” → “Connection Profiles” | Editing database connection profiles |
Program window before connecting to the database:
Dialogue of the connection:
(Team: “Base” → “Connection”)
Program window after connecting to the database:
Dialog for setting database connection profiles:
(Team: “Settings” → “Connection Profiles”)
Dialog change password:
(Team: “Settings” → “Change Password”)
User list settings:
(Team: “Settings” → “List of Users”)
Editing contact directory group:
(Commands: “Actions” → “New Group”, “Actions” → “Copy”, “Actions” → “Edit”)
Editing a contact directory item (card):
(Commands: “Actions” → “Add”, “Actions” → “Copy”, “Actions” → “Edit”)
Group selection dialog in the group tree:
It is used to specify the parent group in the group and card editing dialogs, as well as to indicate the group to values ​​when the current object of the current group (an object selected in the right list of the main program window) is moved to a new group.
Database structure
The main entities of the system:
1. Reference system users (accounts). Consists only of elements (accounts). Accordingly, the database is represented by a single table.
2. Actually the very targeted directory of contact information. Consists of groups (directory tree) and elements (cards). In the database, the directory will be represented by a separate table of groups (the directory level tree will be stored in it) and a separate table of elements (cards with contacts will be stored in it).
In the database, create the following tables:
- the table “GROUPS” - storage of the group tree;
| Field name | Data type | Description |
|---|---|---|
| GRID | INTEGER | Table entry identifier - tree node identifier (all values> 0). |
| Pgraid | INTEGER | Parent node ID. Thus, a connection is established between nodes in a single tree. A value of 0 means that this node is included in the "root" of the tree. The root itself is not stored in the database (it is always in the program - it cannot be changed). |
| GRNAME | VARCHAR (255) | The name of the tree node. The uniqueness of these values ​​(within the table) is not tracked, as in the 1C references. |
- “CARDS” table - storage of reference items - cards with contact data;
| Field name | Data type | Description |
|---|---|---|
| CDID | INTEGER | The entry identifier in the table is the card identifier (all values> 0). |
| GRID | INTEGER | Identifier of the parent tree node (link to GROUPS.GRID). Thus, the connection of the card with the tree is established. A value of 0 means that this card is included in the "root" of the tree (although there is no entry in the GROUPS table with GRID = 0). |
| GRNAME | VARCHAR (255) | The name of the card. The uniqueness of these values ​​(within the table) is not tracked, as in the 1C references. |
| CDDESCRIPT | VARCHAR (255) | Description field. |
| CDCONTACTS | BLOB (TEXT - 1) | Contact Information. In fact, a text field in the form of the MEMO type is a large text of variable length, stored separately from the main table. All rules of automatic work with encodings (as in CHAR and VARCHAR) are applied to it. |
| CDNOTE | BLOB (BINARY - 0) | Notes. In fact, the text field, but described as a binary blob. This should not be done, but since the format of the database is often not chosen by us, this option is quite possible. In this example, we will show how to work with text (encodings) in case the database does not know that it is text. |
| CDIMAGE | BLOB (BINARY - 0) | Here is the image attached to the card. In fact, it contains the same as the corresponding graphic file. |
- table "USERS" - the credentials of the information system;
| Field name | Data type | Description |
|---|---|---|
| UID | INTEGER | Record ID in the table. |
| UNAME | VARCHAR (255) | The name of the user account (login). The program controls the uniqueness (within the table) of this field (case-insensitive — that is, if you have the user “admin”, you cannot create an account “AdMiN”). |
| UPWD | VARCHAR (255) | User password (stored in clear text, because the md5 function is absent in the younger versions of FireBird, and the implementation of this algorithm in 1C is also voluminous - I didn’t want to “bother”). |
| URIGHTS | VARCHAR (255) | User authority - a string of characters "0" and "1", which, being in the corresponding position, indicate the absence ("0") or the presence ("1") of the corresponding authority of the user. This version uses the following user rights: -Position 1 - the ability to view data; -Position 2 - the ability to edit data; -Position 3 - the ability to edit the list of users (administration) |
Also in the database will be placed the following stored procedures:
| Procedure name | Parameter name | Parameter type | Description |
|---|---|---|---|
| SAVE_GROUP | The procedure saves (adding or updating) a group to the database. | ||
| Input parameters: | |||
| GRID | INTEGER | ID of the group to be saved (0 if the group was not saved before) | |
| Pgraid | INTEGER | Parent Group ID | |
| GRNAME | VARCHAR (255) | Group name | |
| Output Parameters: | |||
| RESULT | INTEGER | The identifier of the saved group, if it was possible to save it; 0 - if the group was not saved | |
| ERRMSG | VARCHAR (255) | Error message text if an error occurred (RESULT = 0) | |
| DELETE_GROUP | The procedure removes the group from the database. | ||
| Input parameters: | |||
| GRID | INTEGER | ID of the group to be deleted | |
| Output Parameters: | |||
| RESULT | INTEGER | ID of the deleted group, if it was deleted; 0 - if the group has not been deleted | |
| ERRMSG | VARCHAR (255) | Error message text if an error occurred (RESULT = 0) | |
| MOVE_GROUP_TO_GROUP | The procedure transfers the specified group and all of its contents (subgroups and embedded cards) to another specified group. | ||
| Input parameters: | |||
| SRC_GRID | INTEGER | ID of the group to migrate | |
| DST_GRID | INTEGER | ID of the group to which the source group is to be transferred | |
| Output Parameters: | |||
| RESULT | INTEGER | Group ID (SRC_GRID), if it has been migrated; 0 - if the group transfer did not take place (it was not transferred) | |
| ERRMSG | VARCHAR (255) | Error message text if an error occurred (RESULT = 0) | |
| MOVE_GROUP_CONTENTS_TO_GROUP | The procedure transfers the contents of the specified group (subgroups and embedded cards) to another specified group. At the same time, the original group itself remains in its place. | ||
| Input parameters: | |||
| SRC_GRID | INTEGER | ID of the group whose contents you want to transfer | |
| DST_GRID | INTEGER | ID of the group to which the group contents should be transferred | |
| Output Parameters: | |||
| RESULT | INTEGER | Group identifier (SRC_GRID), if its content has been transferred; 0 - if the group content transfer did not take place. | |
| ERRMSG | VARCHAR (255) | Error message text if an error occurred (RESULT = 0) | |
| MOVE_CARD_TO_GROUP | The procedure performs the transfer of the card to the specified group. | ||
| Input parameters: | |||
| SRC_CDID | INTEGER | ID of the card you want to transfer | |
| DST_GRID | INTEGER | ID of the group to which the card is to be transferred | |
| Output Parameters: | |||
| RESULT | INTEGER | Card ID (SRC_CDID), if it was transferred; 0 - if the transfer of the card did not take place (it was not transferred) | |
| ERRMSG | VARCHAR (255) | Error message text if an error occurred (RESULT = 0) | |
| TEST_GROUP_PARENT_HIERARCHY | The procedure checks whether the PGRID group is hierarchical (possibly through several intermediate nodes) the parent of the GRID group or not. This procedure is not called from the program, but is used in other procedures. | ||
| Input parameters: | |||
| GRID | INTEGER | Identifier of the group whose entry into the PGRID group should be checked | |
| Pgraid | INTEGER | ID of the group for which the GRID group is to be checked | |
| Output Parameters: | |||
| RESULT | SMALLINT | Result of checking: 1 - if the PGRID group is the hierarchical parent of the GRID group; 0 - if the PGRID group is not the parent of the GRID group | |
There will be no procedures for working with cards (SAVE_CARD, DELETE_CARD) and users (SAVE_USER, DELETE_USER). We implement their functionality directly in 1C to show the call from 1C not only stored procedures, but also various queries.
Representation of information system entities in the program.
In our system there are the following informational entities:
- contact reference group (a set of groups forming a tree (subtree) of groups - full or partial presentation of the GROUPS table data);
- an element (card) of the contact directory (a set of cards - a full or partial presentation of the data in the CARDS table);
- system account (set of accounts - full or partial presentation of USERS table data)
The model is presented in 1C code by the following classes:
- BaseObj (Base Entity) - class - an abstract parent for all other classes from this group. Contains common fields and methods.
- ObjGroup (EntityGroup) - a descendant from BaseObj , is an implementation of the “ contact directory group ” entity.
- ObjCard (Entity Card) - a descendant from BaseObj , is an implementation of the entity " contact directory entry ".
- ObjUser (EntityUser) - a descendant from BaseObj , is an implementation of the “ system user ” entity.
- ObjSet (Set of Objects) is also a descendant from BaseObj , but is not an entity, but a universal collection of entities, which can represent a set of objects of any type (ObjGroup, ObjCard, ObjUser). Naturally, you can not mix in the same set of entities of different types.
The corresponding implementation of these classes in 1C will look like this:
Class code
/////////////////////////////////////////////////////////////////////////////////////////////////// // // // /////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// // (BaseObj) // . // // : // . // : // // BaseObj_() BaseObj = ; // BaseObj.("ClassID", 0); // (INT) BaseObj.("ChangeInfo", );// BaseObj.("ErrMsg", ""); // (STR) BaseObj.ClassID = .ClassIDs.CLN_UNKNOWN; // BaseObj; // . // // : // ClassID - , . , // - // : // () - // BaseObj_(ClassID=0) BaseObj = ; ClassID = .ClassIDs.CLN_GROUP // ObjGroup BaseObj = ObjGroup_() ClassID = .ClassIDs.CLN_CARD // ObjCard BaseObj = ObjCard_() ClassID = .ClassIDs.CLN_USER // ObjUser BaseObj = ObjUser_() ; BaseObj = // BaseObj = BaseObj_(); ; BaseObj; // - . // // : // BaseObj - , // BaseObj_(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_GROUP // ObjGroup ObjGroup_(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_CARD // ObjCard ObjCard_(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_USER // ObjUser ObjUser_(BaseObj) // . // // : // BaseObj - // BaseObj_SetDefAttr(BaseObj) BaseObj.ErrMsg = ""; BaseObj.ClassID = .ClassIDs.CLN_GROUP // ObjGroup ObjGroup_SetDefAttr(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_CARD // ObjCard ObjCard_SetDefAttr(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_USER // ObjUser ObjUser_SetDefAttr(BaseObj) // BaseObj2 BaseObj1. // BaseObj1 . // // : // BaseObj1 - // BaseObj2 - // BaseObj_Assign(BaseObj1, BaseObj2) (BaseObj1 <> ) (BaseObj2 <> ) (BaseObj1.ClassID = BaseObj2.ClassID) (BaseObj_IsEqual(BaseObj1, BaseObj2) = ) BaseObj1.ClassID = .ClassIDs.CLN_GROUP // ObjGroup ObjGroup_Assign(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_CARD // ObjCard ObjCard_Assign(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_USER // ObjUser ObjUser_Assign(BaseObj1, BaseObj2) // . // // : // BaseObj1 - // BaseObj2 - // // , BaseObj2 , // BaseObj1 - // BaseObj_IsEqual(BaseObj1, BaseObj2) = ; (BaseObj1 <> ) (BaseObj2 <> ) (BaseObj1.ClassID = BaseObj2.ClassID) BaseObj1.ClassID = .ClassIDs.CLN_GROUP // ObjGroup = ObjGroup_IsEqual(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_CARD // ObjCard = ObjCard_IsEqual(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_USER // ObjUser = ObjUser_IsEqual(BaseObj1, BaseObj2) ; // . // // : // BaseObj1 - // BaseObj2 - // // , BaseObj2 , // BaseObj1 - // BaseObj_IsEqualKeys(BaseObj1, BaseObj2) = ; (BaseObj1 <> ) (BaseObj2 <> ) (BaseObj1.ClassID = BaseObj2.ClassID) BaseObj1.ClassID = .ClassIDs.CLN_GROUP // ObjGroup = ObjGroup_IsEqualKeys(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_CARD // ObjCard = ObjCard_IsEqualKeys(BaseObj1, BaseObj2) BaseObj1.ClassID = .ClassIDs.CLN_USER // ObjUser = ObjUser_IsEqualKeys(BaseObj1, BaseObj2) ; // , (), // .. , . // // : // BaseObj - // // , BaseObj - // BaseObj_IsKeyEmpty(BaseObj) = ; BaseObj <> BaseObj.ClassID = .ClassIDs.CLN_GROUP // ObjGroup = ObjGroup_IsKeyEmpty(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_CARD // ObjCard = ObjCard_IsKeyEmpty(BaseObj) BaseObj.ClassID = .ClassIDs.CLN_USER // ObjUser = ObjUser_IsKeyEmpty(BaseObj) ; // . // // : // BaseObj - // BaseObj_GetErrorMsg(BaseObj) BaseObj.ErrMsg // . // // : // BaseObj - // // // BaseObj_GetChangeInfo(BaseObj) BaseObj.ChangeInfo // // . // // : // BaseObj - // ChangeInfo - // BaseObj_SetChangeInfo(BaseObj, ChangeInfo) BaseObj <> BaseObj.ClassID = .ClassIDs.CLN_GROUP // ObjGroup ObjGroup_SetChangeInfo(BaseObj, ChangeInfo) BaseObj.ClassID = .ClassIDs.CLN_CARD // ObjCard ObjCard_SetChangeInfo(BaseObj, ChangeInfo) BaseObj.ClassID = .ClassIDs.CLN_USER // ObjUser ObjUser_SetChangeInfo(BaseObj, ChangeInfo) // DS. // // : // BaseObj - // DS - ADORecordSet // : // , , // BaseObj_LoadFromDataSet(BaseObj, DS) = ; // BaseObj.ErrMsg = ""; // , BaseObj.ClassID = .ClassIDs.CLN_GROUP // ObjGroup = ObjGroup_LoadFromDataSet(BaseObj, DS) BaseObj.ClassID = .ClassIDs.CLN_CARD // ObjCard = ObjCard_LoadFromDataSet(BaseObj, DS) BaseObj.ClassID = .ClassIDs.CLN_USER // ObjUser = ObjUser_LoadFromDataSet(BaseObj, DS) ; //////////////////////////////////////////////////////////////////////////////// // (ObjSet) - BaseObj // ( - ClassID , // ) // . // // : // . // : // // ObjSet_() ObjSet = BaseObj_(); // ObjSet.("ItemsList", ); // ObjSet; // . // // : // ClassID - , . // : // () - // ObjSet_(ClassID=0) ObjSet = ObjSet_(); ObjSet.ClassID = ClassID; // ObjSet_SetDefAttr(ObjSet); ObjSet; // - . // // : // ObjSet - // ObjSet_(ObjSet) ObjSet_Clear(ObjSet) // . // // : // ObjSet - // ObjSet_SetDefAttr(ObjSet) ObjSet.ErrMsg = "" // Obj ObjSet. // // : // ObjSet - // Obj - , // : // , Obj ObjSet - // ObjSet_TestItemForAdd(ObjSet, Obj) = ; // ObjSet.ErrMsg = ""; Obj <> ObjSet.ClassID = Obj.ClassID // = ; ObjSet.ItemsList BaseObj_IsEqualKeys(, Obj) = ; ; = ObjSet.ErrMsg = " " ObjSet.ErrMsg = " " ObjSet.ErrMsg = " " ; // - . // // : // ObjSet - // : // // ObjSet_GetCount(ObjSet) ObjSet.ItemsList.() // Index ( 0). // // : // ObjSet - // Index - // : // ( Index) , // // ObjSet_GetItemByIndex(ObjSet, Index) = ; ObjSet.ErrMsg = ""; (Index >= 0) (Index < ObjSet.ItemsList.()) = ObjSet.ItemsList[Index] ObjSet.ErrMsg = " "; ; // Index ( 0), // . // // : // ObjSet - // Index - // Obj - // : // , , // // ObjSet_SetItemToIndex(ObjSet, Index, Obj) = ; ObjSet.ErrMsg = ""; Obj <> ObjSet.ClassID = Obj.ClassID (Index >= 0) (Index < ObjSet.ItemsList.()) // , // BaseObj_IsKeyEmpty(Obj) // ( ) - = BaseObj_IsEqualKeys(ObjSet.ItemsList[Index], Obj) // , - = // = ; ObjSet.ItemsList BaseObj_IsEqualKeys(, Obj) = ; ; = ObjSet.ErrMsg = " " ; // // - BaseObj_Assign(ObjSet.ItemsList[Index], Obj) ObjSet.ErrMsg = " " ObjSet.ErrMsg = " " ObjSet.ErrMsg = " " ; // Obj . // // : // ObjSet - // Obj - // : // , - // ObjSet_Add(ObjSet, Obj) = ; ObjSet.ErrMsg = ""; Obj <> // ObjSet_TestItemForAdd(ObjSet, Obj) // ObjSet.ItemsList.(Obj); ObjSet.ChangeInfo = ; = // ObjSet.ErrMsg = " " ; // . // // : // ObjSet - // Index - ( 0) // : // , - // ObjSet_Delete(ObjSet, Index) = ; ObjSet.ErrMsg = ""; (Index >= 0) (Index < ObjSet.ItemsList.()) BaseObj_(ObjSet.ItemsList[Index]); ObjSet.ItemsList.(Index); = ObjSet.ErrMsg = " "; ; // . // // : // ObjSet - // : // ObjSet_Clear(ObjSet) ObjSet.ItemsList.() > 0 Item ObjSet.ItemsList BaseObj_(Item); ; ObjSet.ItemsList.(); ObjSet.ChangeInfo = // // // // : // ObjSet - // : // // ObjSet_GetChangeInfo(ObjSet) ObjSet.ChangeInfo // , Item ObjSet.ItemsList BaseObj_GetChangeInfo(Item) ObjSet.ChangeInfo = ; ; ObjSet.ChangeInfo // // ChangeInfo // // : // ObjSet - // ChangeInfo - // ObjSet_SetChangeInfo(ObjSet, ChangeInfo) ObjSet.ChangeInfo = ChangeInfo; ChangeInfo Item ObjSet.ItemsList BaseObj_SetChangeInfo(Item, ChangeInfo) // // // // : // ObjSet - // : // // ObjSet_GetErrorMsg(ObjSet) ObjSet.ErrMsg = "" // , Item ObjSet.ItemsList Item.ErrMsg <> "" ObjSet.ErrMsg = Item.ErrMsg; ; ObjSet.ErrMsg // . // // : // ObjSet - // : // , - // ObjSet_TestSet(ObjSet) = ; // ObjSet.ErrMsg = ""; // (ObjSet.ClassID = .ClassIDs.CLN_GROUP) (ObjSet.ClassID = .ClassIDs.CLN_CARD) (ObjSet.ClassID = .ClassIDs.CLN_USER) // 1=0 ObjSet.ItemsList.()-1 1 = ObjSet.ItemsList[1]; ObjSet.ClassID = 1.ClassID = ; 2=1+1 ObjSet.ItemsList.()-1 2 = ObjSet.ItemsList[2]; BaseObj_IsEqualKeys(1, 2) = ; ; ObjSet.ErrMsg = " "; // ObjSet.ErrMsg = " " // ObjSet.ErrMsg = " " ; // ObjSet.ErrMsg = "" = ; // DS. . // // ( ) // . // : // ObjSet - // DS - ADORecordSet // RowCount - , // ( RowCount=-1, // ) // : // // ObjSet_LoadFromDataSet(ObjSet, DS, RowCount=-1) = 0; ObjSet.ErrMsg = ""; ObjSet_Clear(ObjSet); ObjSet.ErrMsg = "" = ObjSet_AddFromDataSet(ObjSet, DS, RowCount) ; // DS, . // // ( ) // . // : // ObjSet - // DS - ADORecordSet // RowCount - , // ( RowCount=-1, // ) // : // // ObjSet_AddFromDataSet(ObjSet, DS, RowCount=-1) = 0; ObjSet.ErrMsg = ""; RowCount <> 0 (DS.EOF = 0) ((RowCount < 0) ( < RowCount)) // Obj = BaseObj_(ObjSet.ClassID); Obj <> // BaseObj_LoadFromDataSet(Obj, DS) // - ObjSet_Add(ObjSet, Obj) // = + 1 // // - ObjSet.ErrMsg = Obj.ErrMsg; BaseObj_(Obj); // ObjSet.ErrMsg = " " ; // DS.MoveNext(); ; //////////////////////////////////////////////////////////////////////////////// // (ObjGroup) // . // // : // . // : // // ObjGroup_() ObjGroup = BaseObj_(); ObjGroup.ClassID = .ClassIDs.CLN_GROUP; // ObjGroup // ObjGroup.("GRID", ); // (INT) ObjGroup.("PGRID", ); // (INT) ObjGroup.("GRName", ); // (STR) ObjGroup; // . // // : // . // : // () - // ObjGroup_() ObjGroup = ObjGroup_(); // ObjGroup_SetDefAttr(ObjGroup); ObjGroup; // - . // // : // ObjGroup - // ObjGroup_(ObjGroup) // . // // : // ObjGroup - // ObjGroup_SetDefAttr(ObjGroup) ObjGroup.GRID = 0; ObjGroup.PGRID = 0; ObjGroup.GRName = ""; // ObjGroup2 ObjGroup1. // ObjGroup1 . // // : // ObjGroup1 - // ObjGroup2 - // ObjGroup_Assign(ObjGroup1, ObjGroup2) (ObjGroup1 <> ) (ObjGroup2 <> ) (ObjGroup_IsEqual(ObjGroup1, ObjGroup2) = ) ObjGroup1.GRID = ObjGroup2.GRID; ObjGroup1.PGRID = ObjGroup2.PGRID; ObjGroup1.GRName = ObjGroup2.GRName; ObjGroup1.ChangeInfo = // , ObjGroup2 , // ObjGroup1 - // // : // ObjGroup1 - // ObjGroup2 - // : // , - // ObjGroup_IsEqual(ObjGroup1, ObjGroup2) IsEqual = ; (ObjGroup1 <> ) (ObjGroup2 <> ) (ObjGroup2.GRID = ObjGroup1.GRID) (ObjGroup2.PGRID = ObjGroup1.PGRID) (ObjGroup2.GRName = ObjGroup1.GRName) IsEqual = // ; IsEqual // . // // : // ObjGroup1 - // ObjGroup2 - // // , ObjGroup2 , // ObjGroup1 - // ObjGroup_IsEqualKeys(ObjGroup1, ObjGroup2) IsEqual = ; (ObjGroup1 <> ) (ObjGroup2 <> ) ((ObjGroup2.GRID = ObjGroup1.GRID) (ObjGroup1.GRID > 0)) IsEqual = // ; IsEqual // , (), // .. , . // // : // ObjGroup - // // , ObjGroup - // ObjGroup_IsKeyEmpty(ObjGroup) = ; IsEmpty = ; ObjGroup <> ObjGroup.GRID <= 0 IsEmpty = ; IsEmpty // . // // : // ObjGroup - // GRID - // PGRID - // GRName - // ObjGroup_SetAttr(ObjGroup, GRID, PGRID, GRName) // , , // TmpObjGroup = ObjGroup_(); TmpObjGroup.GRID = GRID; TmpObjGroup.PGRID = PGRID; TmpObjGroup.GRName = GRName; ObjGroup_Assign(ObjGroup, TmpObjGroup); ObjGroup_(TmpObjGroup); // // . // // : // ObjGroup - // ChangeInfo - // ObjGroup_SetChangeInfo(ObjGroup, ChangeInfo) ObjGroup.ChangeInfo = ChangeInfo // DS. // // : // ObjGroup - // DS - ADORecordSet // : // , , // ObjGroup_LoadFromDataSet(ObjGroup, DS) = ; // ObjGroup.ErrMsg = ""; ObjGroup.GRID = DS.Fields("GRID").Value; ObjGroup.PGRID = DS.Fields("PGRID").Value; ObjGroup.GRName = DS.Fields("GRNAME").Value; ObjGroup_SetChangeInfo(ObjGroup, ); // , .. // = ; // // ObjGroup.ErrMsg = ().; ; //////////////////////////////////////////////////////////////////////////////// // (ObjCard) // . // // : // . // : // // ObjCard_() ObjCard = BaseObj_(); ObjCard.ClassID = .ClassIDs.CLN_CARD; // ObjCard // ObjCard.("CDID", ); // (INT) ObjCard.("GRID", ); // (INT) ObjCard.("CDName", ); // (STR) ObjCard.("CDDescript", ); // (STR) ObjCard.("CDContacts", ); // (STR) ObjCard.("CDNote", ); // (STR) ObjCard.("CDImage", );// () // , BLOB- ObjCard.("ChangeCDContacts", );// CDContacts ObjCard.("ChangeCDNote", ); // CDNote ObjCard.("ChangeCDImage", ); // CDImage ObjCard; // . // // : // . // : // () - // ObjCard_() ObjCard = ObjCard_(); // ObjCard_SetDefAttr(ObjCard); ObjCard; // - . // // : // ObjCard - // ObjCard_(ObjCard) // . // // : // ObjCard - // ObjCard_SetDefAttr(ObjCard) ObjCard.CDID = 0; ObjCard.GRID = 0; ObjCard.CDName = ""; ObjCard.CDDescript = ; ObjCard.CDContacts = ; ObjCard.CDNote = ; ObjCard.CDImage = ; ObjCard.ChangeCDContacts = ; ObjCard.ChangeCDNote = ; ObjCard.ChangeCDImage = ; // ObjCard2 ObjCard1. // ObjCard1 . // // : // ObjCard1 - // ObjCard2 - // ObjCard_Assign(ObjCard1, ObjCard2) (ObjCard1 <> ) (ObjCard2 <> ) (ObjCard_IsEqual(ObjCard1, ObjCard2) = ) ObjCard1.CDID = ObjCard2.CDID; ObjCard1.GRID = ObjCard2.GRID; ObjCard1.CDName = ObjCard2.CDName; ObjCard1.CDDescript = ObjCard2.CDDescript; ObjCard1.CDContacts <> ObjCard2.CDContacts ObjCard1.CDContacts = ObjCard2.CDContacts; ObjCard1.ChangeCDContacts = ; ObjCard1.CDNote <> ObjCard2.CDNote ObjCard1.CDNote = ObjCard2.CDNote; ObjCard1.ChangeCDNote = ; (ObjCard1.CDImage, ObjCard2.CDImage) ObjCard1.CDImage= ObjCard2.CDImage; ObjCard1.ChangeCDImage = ; ObjCard1.ChangeInfo = // , ObjCard2 , // ObjCard1 - // // : // ObjCard1 - // ObjCard2 - // : // , - // ObjCard_IsEqual(ObjCard1, ObjCard2) IsEqual = ; (ObjCard1 <> ) (ObjCard2 <> ) (ObjCard2.CDID = ObjCard1.CDID) (ObjCard2.GRID = ObjCard1.GRID) (ObjCard2.CDName = ObjCard1.CDName) (ObjCard2.CDDescript = ObjCard1.CDDescript) (ObjCard2.CDContacts = ObjCard1.CDContacts) (ObjCard2.CDNote = ObjCard1.CDNote) (ObjCard2.CDImage, ObjCard1.CDImage) IsEqual = // ; IsEqual // . // // : // ObjCard1 - // ObjCard2 - // // , ObjCard2 , // ObjCard1 - // ObjCard_IsEqualKeys(ObjCard1, ObjCard2) IsEqual = ; (ObjCard1 <> ) (ObjCard2 <> ) ((ObjCard2.CDID = ObjCard1.CDID) (ObjCard1.CDID > 0)) IsEqual = // ; IsEqual // , (), // .. , . // // : // ObjCard - // // , ObjCard - // ObjCard_IsKeyEmpty(ObjCard) = ; IsEmpty = ; ObjCard <> ObjCard.CDID <= 0 IsEmpty = ; IsEmpty // . // // : // ObjCard - // CDID - // GRID - // GRName - // CDDescript - // CDContacts - // CDNote - // CDImage - // ObjCard_SetAttr(ObjCard, CDID, GRID, CDName, CDDescript = , CDContacts = , CDNote = , CDImage = ) // , , // TmpObjCard = ObjCard_(); TmpObjCard.CDID = CDID; TmpObjCard.GRID = GRID; TmpObjCard.CDName = CDName; CDDescript <> TmpObjCard.CDDescript = CDDescript; ; CDContacts <> TmpObjCard.CDContacts = CDContacts; ; CDNote <> TmpObjCard.CDNote = CDNote ; CDImage <> TmpObjCard.CDImage = CDImage; ; ObjCard_Assign(ObjCard, TmpObjCard); ObjCard_(TmpObjCard); // // . // // : // ObjCard - // ChangeInfo - // ObjCard_SetChangeInfo(ObjCard, ChangeInfo) ObjCard.ChangeInfo = ChangeInfo; ChangeInfo = ObjCard.ChangeCDContacts = ; ObjCard.ChangeCDNote = ; ObjCard.ChangeCDImage = // DS. // // : // ObjCard - // DS - ADORecordSet // : // , , // ObjCard_LoadFromDataSet(ObjCard, DS) = ; // ObjCard.ErrMsg = ""; ObjCard.CDID = DS.Fields("CDID").Value; ; ObjCard.GRID = DS.Fields("GRID").Value; ; ObjCard.CDName = DS.Fields("CDNAME").Value; ; ObjCard.CDDescript = DS.Fields("CDDESCRIPT").Value; ; ObjCard.CDContacts = DS.Fields("CDCONTACTS").Value; ; CDNoteArray = DS.Fields("CDNOTE").Value; ObjCard.CDNote = COMSafeArrayWIN1251_UTF8(CDNoteArray); ; CDImageArray = DS.Fields("CDIMAGE").Value; ObjCard.CDImage = COMSafeArray_(CDImageArray) ; ObjCard_SetChangeInfo(ObjCard, ); // , .. // = ; // // ObjCard.ErrMsg = ().; ; //////////////////////////////////////////////////////////////////////////////// // (ObjUser) // . // // : // . // : // // ObjUser_() ObjUser = BaseObj_(); ObjUser.ClassID = .ClassIDs.CLN_USER; // ObjUser // ObjUser.("UID", ); // (INT) ObjUser.("UName", ); // (STR) ObjUser.("UPwd", ); // (STR) ObjUser.("URights", ); // (STR) ObjUser; // . // // : // . // : // () - // ObjUser_() ObjUser = ObjUser_(); // ObjUser_SetDefAttr(ObjUser); ObjUser; // - . // // : // ObjUser - // ObjUser_(ObjUser) // . // // : // ObjUser - // ObjUser_SetDefAttr(ObjUser) ObjUser.UID = 0; ObjUser.UName = ""; ObjUser.UPwd = ; ObjUser.URights = ""; // ObjUser2 ObjUser1. // ObjUser1 . // // : // ObjUser1 - // ObjUser2 - // ObjUser_Assign(ObjUser1, ObjUser2) (ObjUser1 <> ) (ObjUser2 <> ) (ObjUser_IsEqual(ObjUser1, ObjUser2) = ) ObjUser1.UID = ObjUser2.UID; ObjUser1.UName = ObjUser2.UName; ObjUser1.UPwd = ObjUser2.UPwd; ObjUser1.URights = ObjUser2.URights; ObjUser1.ChangeInfo = // , ObjUser2 , // ObjUser1 - // // : // ObjUser1 - // ObjUser2 - // : // , - // ObjUser_IsEqual(ObjUser1, ObjUser2) IsEqual = ; (ObjUser1 <> ) (ObjUser2 <> ) (ObjUser2.UID = ObjUser1.UID) (ObjUser2.UName = ObjUser1.UName) (ObjUser2.UPwd = ObjUser1.UPwd) IsEqualUserRights(ObjUser2.URights, ObjUser1.URights) IsEqual = // ; IsEqual // . // // : // ObjUser1 - // ObjUser2 - // // , ObjUser2 , // ObjUser1 - // ObjUser_IsEqualKeys(ObjUser1, ObjUser2) IsEqual = ; (ObjUser1 <> ) (ObjUser2 <> ) ((ObjUser2.UID = ObjUser1.UID) (ObjUser1.UID > 0)) ((ObjUser2.UName) = (ObjUser1.UName)) IsEqual = // ; IsEqual // , (), // .. , . // // : // ObjUser - // // , ObjUser - // ObjUser_IsKeyEmpty(ObjUser) = ; IsEmpty = ; ObjUser <> (ObjUser.UID <= 0) ((ObjUser) = "") IsEmpty = ; IsEmpty // . // // : // ObjUser - // UID - // UName - // UPwd - // URights - // ObjUser_SetAttr(ObjUser, UID, UName, UPwd, URights) // , , // TmpObjUser = ObjUser_(); TmpObjUser.UID = UID; TmpObjUser.UName = UName; TmpObjUser.UPwd = UPwd; TmpObjUser.URights = URights; ObjUser_Assign(ObjUser, TmpObjUser); ObjUser_(TmpObjUser); // // . // // : // ObjUser - // ChangeInfo - // ObjUser_SetChangeInfo(ObjUser, ChangeInfo) ObjUser.ChangeInfo = ChangeInfo // DS. // // : // ObjUser - // DS - ADORecordSet // : // , , // ObjUser_LoadFromDataSet(ObjUser, DS) = ; // ObjUser.ErrMsg = ""; ObjUser.UID = DS.Fields("UID").Value; ObjUser.UName = DS.Fields("UNAME").Value; //ObjUser.UPwd = DS.Fields("UPWD").Value; // ObjUser.UPwd = ; ObjUser.URights = DS.Fields("URIGHTS").Value; ObjUser_SetChangeInfo(ObjUser, ); // , .. // = ; // // ObjUser.ErrMsg = ().; ; Work with the database. ADO technology.
To access the database, we use the technology that is most well described on the Internet in relation to 1C - “ Microsoft ADO ”. And this is of course reasonable, because This technology is the most stable available on Windows computers. This not only concerns 1C, but also other development tools, for example, “ Visual Studio ”. These are Delphi programmers accustomed to a variety of database technologies. For other developers, the choice of tools is more limited. I will not describe ADO as such, as there are a lot of excellent articles on the Internet that have accumulated over the past 10-15 years. We will look at the interfaces and data types that we need for the implementation described in this article (see the MSDN documentation for details).
| Interface name | Description | Used properties and methods |
|---|---|---|
| Connection | Provides a connection to the database. | State (property) - shows the current connection status (established or not); Open (method) - allows you to connect to the database; Close (method) - performs disconnection from the database |
| Command | It provides the execution of commands (we are only interested in SQL queries) within the specified connection (Connection). | ActiveConnection (property) - indicates the connection with which the command execution object is connected; CommandType (property) - indicates the type of command (we are interested in the type - "SQL query"); CommandText (property) - command text (in our case - request text); Parameters (property) - a collection of command parameters (query) for transmitting data to the database; Execute (method) - executes the command (request). Returns the result dataset; CreateParameter (method) - allows you to create a new command parameter (query) |
| Recordset | The data set (table) resulting from the query. Provides data transfer from the database to the client application. | State (property) - shows the status of the data set (available or not); EOF (property) - a sign of the end of the data set (indicates whether the internal pointer is on the last row of the set or not); Fields (property) - collection of fields - access to the columns of the current row of the set; MoveNext (method) - shifts the internal set pointer by one line towards the end of the set; Close (method) - closes the dataset |
| Fields | A collection of fields that provides access to the fields of the current row in the dataset. | Fields (FieldInd) , Fields (FieldName) - access to a specific field Field by its index (sequence number) or by name (string value of the field name) |
| Field | A field that provides access to the column in the current row of the dataset. | Value (property) - field value (gives access to the value of the corresponding column in the current row of the associated Recordset) |
| Parameters | A collection of values ​​representing query parameters — for transferring data from a client application to the database. | Count (property) - shows the number of parameters in the collection; Append (method) - adds a parameter to the end of the collection; Delete (method) - removes the specified parameter from the collection |
A full description of the ADO data types used in this program is given below:
Implementing enumerated types
// , COM- ADO // // : // . // // : // - ADO // ADO() ADO = ; // Connection, Record Stream ConnectModeEnum = ; ConnectModeEnum.("adModeRead", 1); // " " ConnectModeEnum.("adModeReadWrite", 3); // " " ConnectModeEnum.("adModeRecursive", 4194304); // ConnectModeEnum.("adModeShareDenyNone", 16); // " " ConnectModeEnum.("adModeShareDenyRead", 4); // " " ConnectModeEnum.("adModeShareDenyWrite", 8); // " " ConnectModeEnum.("adModeShareExclusive", 12); // " - " ConnectModeEnum.("adModeUnknown", 0); // ConnectModeEnum.("adModeWrite", 2 ); // " " ADO.("ConnectModeEnum", ConnectModeEnum); // Command (, ) CommandTypeEnum = ; CommandTypeEnum.("adCmdUnspecified", -1); // CommandTypeEnum.("adCmdText", 1); // CommandText // SQL-, // CommandTypeEnum.("adCmdTable", 2); // CommandText // , // SQL- CommandTypeEnum.("adCmdStoredProc", 4); // CommandText // , // CommandTypeEnum.("adCmdUnknown", 8); // // (adCmdText, adCmdTable, adCmdStoredProc) // CommandText CommandTypeEnum.("adCmdFile", 256); // CommandText // , // Recordset CommandTypeEnum.("adCmdTableDirect", 512);// CommandText // , // , SQL- ADO.("CommandTypeEnum", CommandTypeEnum); // , : Field, Parameter Property DataTypeEnum = ; DataTypeEnum.("AdArray", 8192); // , , ( "" ) DataTypeEnum.("adBigInt", 20); // 64- DataTypeEnum.("adBinary", 128); // DataTypeEnum.("adBoolean", 11); // DataTypeEnum.("adBSTR", 8); // , Null ( Unicode) DataTypeEnum.("adChapter", 136); // DataTypeEnum.("adChar", 129); // DataTypeEnum.("adCurrency", 6); // . 8- , 10000 DataTypeEnum.("adDate", 7); // . , - , 12/30/1899 DataTypeEnum.("adDBDate", 133); // DataTypeEnum.("adDBTime", 134); // DataTypeEnum.("adDBTimeStamp", 135); // DataTypeEnum.("adDecimal", 14); // DataTypeEnum.("adDouble", 5); // DataTypeEnum.("adEmpty", 0); // ( ) DataTypeEnum.("adError", 10); // 32- DataTypeEnum.("adFileTime", 64); // 64- , - 100, 01/01/1601 DataTypeEnum.("adGUID", 72); // GUID DataTypeEnum.("adIDispatch", 9); // COM- IDispatch. - . DataTypeEnum.("adInteger", 3); // 32- DataTypeEnum.("adIUnknown", 13); // COM- IUnknown. - . DataTypeEnum.("adLongVarBinary", 205); // ( Parameter) DataTypeEnum.("adLongVarChar", 201); // ( Parameter) DataTypeEnum.("adLongVarWChar", 203); // , Null - Unicode ( Parameter) DataTypeEnum.("adNumeric", 131); // DataTypeEnum.("adPropVariant", 138); // DataTypeEnum.("adSingle", 4); // DataTypeEnum.("adSmallInt", 2); // 16- DataTypeEnum.("adTinyInt", 16); // 8- DataTypeEnum.("adUnsignedBigInt", 21); // 64- DataTypeEnum.("adUnsignedInt", 19); // 32- DataTypeEnum.("adUnsignedSmallInt", 18); // 16- DataTypeEnum.("adUnsignedTinyInt", 17); // 8- DataTypeEnum.("adUserDefined", 132); // DataTypeEnum.("adVarBinary", 204); // ( Parameter) DataTypeEnum.("adVarChar", 200); // ( Parameter) DataTypeEnum.("adVariant", 12); // . - . DataTypeEnum.("adVarNumeric", 139); // ( Parameter) DataTypeEnum.("adVarWChar", 202); // Unicode, Null ( Parameter) DataTypeEnum.("adWChar", 130); // Unicode, Null ADO.("DataTypeEnum", DataTypeEnum); // ParameterDirectionEnum = ; ParameterDirectionEnum.("adParamInput", 1); // ParameterDirectionEnum.("adParamInputOutput", 3);// ParameterDirectionEnum.("adParamOutput", 2); // ParameterDirectionEnum.("adParamReturnValue", 4);// - ParameterDirectionEnum.("adParamUnknown", 0); // ADO.("ParameterDirectionEnum", ParameterDirectionEnum); // ( ) // , ObjectStateEnum = ; ObjectStateEnum.("adStateClosed", 0); // , ObjectStateEnum.("adStateOpen", 1); // , ObjectStateEnum.("adStateConnecting", 2); // , ObjectStateEnum.("adStateExecuting", 4); // , ObjectStateEnum.("adStateFetching", 8); // , ADO.("ObjectStateEnum", ObjectStateEnum); // Stream StreamTypeEnum = ; StreamTypeEnum.("adTypeBinary", 1); // StreamTypeEnum.("adTypeText", 2); // , // , Stream.Charset ADO.("StreamTypeEnum", StreamTypeEnum); // Stream SaveOptionsEnum = ; SaveOptionsEnum.("adSaveCreateNotExist", 1); // , SaveOptionsEnum.("adSaveCreateOverWrite",2); // , // ( , ) ADO.("SaveOptionsEnum", SaveOptionsEnum); ADO Work with the database. Subprograms on 1C.
For the convenience of users with several databases, “ Connection Profiles ” are provided, i.e. groups of parameters associated with the database, united under one name. Most often these are the parameters for connecting to the DBMS (the address of the database server, the name of the database, the login and password for connecting to the database at the DBMS level), but it is possible to add some other parameters
For working with profiles, there are classes: “ Connection Profile KBase ” and “ Connection Profiles KDB ” in the main processing module, as well as the form “ Setting Connection Profiles ”, which implements the dialog for editing profiles. All this can be seen in the demo. The scope of this functionality is a program to access any external database on any DBMS.
The main class for working with the database is the ConnectionCBase class. It allows you to connect to disconnect to the database and execute queries.
Also a convenient aid is the " Set Parameters Request " class. This set allows you to conveniently embed various values ​​into the text of SQL queries. We prepare a request template, add named values ​​(parameters) to the set, and then process the request text template, which contains instructions on where to insert a particular request value. The set processes the request template, inserts specific values ​​into it, and thus receives the final text of the request. In the query template, the place where the parameter value is substituted is indicated as : parameter_name (a colon, and then the name of the parameter — this is done in Delphi).
Here is how it looks in an example:
= _(); ... // // QSelectGroupTmpl = "SELECT grid, pgid, grname FROM groups WHERE grid = :grid;"; // _(.); // grid 10 _(, "grid", 10); // – // grid 20. - 1 _(, "grid", 20); // – – // – // : "SELECT grid, pgid, grname FROM groups WHERE grid = 20;" = _(, QSelectGroupTmpl); In more detail, the use of this mechanism (query templates) can be viewed in a demo example in subcategories of the “ Sub-Base ” class, starting with the “ Sub-Base_SelectGroup ” function.
The set of parameters analyzes the type of value (it understands "Undefined", "Number", "String", "Date", "Boolean") and substitutes the values ​​in the query in the syntax correct for this DBMS.
The source code of the class " SetParameter SetInquiry ":
Source text
//////////////////////////////////////////////////////////////////////////////// // () // . // // : // . // : // // _() = ; .("", ); // ; // . // // : // . // : // () - // _() = _(); ; // - . // // : // - // _() _() // . , // // : // - // - ( ) // - // _(, , ) ( <> ) (() = ("")) (() <> "") = (); // . (.) = () // - . = ; ; // = ; .("", ); .("", ); ..() // // // : // - // : // // _() ..() // - // // : // - // _() ..() // // (? - , * - - ) // SQL- Like ( _ %) // // : // - : ? * // : // : _ % // _Like( ) = ""; ( <> ) (() = ("")) ( <> "") = ; = (, "_", ""); = (, "%", ""); = (, "?", "_"); = (, "*", "%"); ; // , // SQL- FireBird // - // - 0 1 // - dd.mm.yyyy hh:mm:ss, // - // - Null NULL // // : // - // : // , // _( ) = ""; ( = ) ( = Null) = "NULL" () = ("") // = (, "=0; ='.'; =''") () = ("") // - = "'" + (, "='dd.MM.yyyy HH:mm:ss'") + "'" () = ("") // = (, "=0; =1") () = ("") // = (, "'", "''"); // = "'" + + "'" ; // , . // // : // - // - , ":_" // : // , // _(, ) = ""; ( <> ) (() = ("")) ( <> "") = ; . = (, ":" + ., _(.)) ; So, let us consider, finally, the main class of working with the database - the class “Connecting BaseBase”.
The class has the following subroutines:
| Subprogram name | Description | Note |
|---|---|---|
| General purpose subprogram group. Here, the object constructor, subroutines for connecting to the database and disconnecting from the database, as well as subprograms that return the current values ​​of the general properties of the object. | ||
| SubCBase_CreateObject () function | Creating an object structure | |
| Sub BaseB_Constructor function () | Object constructor | |
| _() | ||
| _GetDBAddr() | ||
| _GetDBUserName() | , | |
| _GetConnect() | ||
| _ConnectToDB(, FBDBAddr, FBUserName, FBUserPass) | ||
| _Disconnect() | ||
| _GetUserRegister() | ||
| _RegisterUser (, UserName, UserPass) | ||
| _UnregisterUser() | ||
| _GetRegLogin() | , | |
| _GetUserID() | ||
| _GetUserName() | ||
| _GetUserRights() | ||
| _TestUserRight(, RightIndex) | , | |
| _GetErrorMsg() | . | |
| , ADO – - . ( ). | ||
| _ClearCommandParameters() | ADODB.Command | |
| _ExecSQL(, ) | SQL-, | |
| _GetRecordSet(, ) | SQL-, | |
| _CheckRestConnect(, =) | . | |
| , . . | ||
| _CheckRights(, RightsMask="") | . | |
| _SelectDataSet(, , DstDataSet, RightsMask="") | ||
| _SelectSingleVal(, , OutVal, OutValName, RightsMask="") | ||
| _SelectPair(, , OutVal1, OutVal2, OutValName1, OutValName2, RightsMask="") | ||
| _SelectList(, , List, RightsMask="") | ||
| _SelectListPair(, , List, RightsMask="") | ||
| _SelectObject(, , Obj, RightsMask="") | ||
| _SelectObjects(, , ClassID, ObjectsSet, RightsMask="") | ||
| _WriteQuery(, , RightsMask="") | ||
| , . | ||
| _SelectGroup(, GRID, ObjGroup) | ||
| _SelectGroups(, PGRID, ObjSetGroups) | , | |
| _SelectCard(, CDID, ObjCard) | ||
| _SelectCardsNames(, GRID, ObjSetCards) | , | (BLOB- ) |
| _SelectUser(, UID, ObjUser) | ||
| _SelectUsers(, ObjSetUsers) | — | |
| _SaveGroup(, ObjGroup) | — INSERT UPDATE | |
| _DeleteGroup(, GRID) | ( ) | |
| _SaveCard(, ObjCard) | — INSERT UPDATE . | |
| _DeleteCard(, CDID) | ||
| _MoveGroupToGroup(, SrcGRID, DstGRID) | . . | |
| _MoveGroupContentsToGroup(, SrcGRID, DstGRID) | . . | |
| _MoveCardToGroup(, SrcCDID, DstGRID) | . | |
| _UpdatePassword(, OldPassword, NewPassword) | ( ) . | |
| _SaveUsers(, ObjSetUsers, DelUsersIDs) | — INSERT UPDATE . — DELETE. | |
, ADO:
— ( , ) – «_ConnectToDB», «_RegisterUser» .
( , ):
— « ADODB.Connection »;
— « ADODB.Command » . – SQL- (CommandType = CommandTypeEnum.adCmdText). (, ..) ;
— , ( ).
. , .
:
// // // : // - // FBDBAddr - ( ) // FBUserName - // FBUserPass - // : // , - // _ConnectToDB(, FBDBAddr, FBUserName, FBUserPass) // QGetTab = "SELECT RDB$RELATION_NAME AS tabname FROM RDB$RELATIONS " + "WHERE ((RDB$SYSTEM_FLAG = 0)AND(RDB$VIEW_SOURCE IS NULL)) " + "ORDER BY RDB$RELATION_NAME;"; = ; .FBDBAddr = FBDBAddr; // .FBUserName = FBUserName; // .FBUserPass = FBUserPass; // .FBSrvConn = ; // .FBCommand = ; // .IsDBConnect = ; // .LastTestTime = 0; // _UnregisterUser(); // .ErrMsg = ""; // // FireBird _Driver = "driver={" + "Firebird/InterBase(r) driver" + "}"; _UID = "uid=" + FBUserName; _PWD = "pwd=" + FBUserPass; _DataBase = "database=" + FBDBAddr; = _Driver + ";" + _UID + ";" + _PWD + ";" + _DataBase; // .FBSrvConn = COM("ADODB.Connection"); // .FBSrvConn.open(); .IsDBConnect = ; // .FBCommand = COMObject("ADODB.Command"); .FBCommand.ActiveConnection = .FBSrvConn; .FBCommand.NamedParameters = True; .FBCommand.CommandType = ADO.CommandTypeEnum.adCmdText; // // , = QGetTab; .FBCommand.CommandText = ; = .FBCommand.Execute(); = ; <> .State = ADO.ObjectStateEnum.adStateOpen = ; // .EOF = 0 .((.Fields("TABNAME").Value)); // .MoveNext(); ; // TabName .QTabsLst .(TabName) = // .ErrMsg = " " + TabName + " "; ; // .ErrMsg = " " // .ErrMsg = " "; ; // .Close(); = // .ErrMsg = " " ; // .ErrMsg = " FireBird: " + () ; .ErrMsg = "" // - = // - _Disconnect() ; ; // - // UserName UserPass // . // // : // - // UserName - // UserPass - // : // , // ( ) - // _RegisterUser(, UserName, UserPass) // QGetUsr = "SELECT * FROM USERS WHERE " + "((UPPER(UNAME COLLATE PXW_CYRL) = :upname)OR(UNAME = :name)) AND (UPWD = :pass);"; = ; .ErrMsg = ""; // .IsDBConnect // - _UnregisterUser(); // // // _(.); _(., "upname", (UserName)); _(., "name", UserName); _(., "pass", UserPass); = _(., QGetUsr); .FBCommand.CommandText = ; = .FBCommand.Execute(); = ; <> (.State = ADO.ObjectStateEnum.adStateOpen) (.EOF = 0) .UID = .Fields("UID").Value; .UName = .Fields("UNAME").Value; .Rights = .Fields("URIGHTS").Value; ; .IsRegister = ; // .Close(); = ; .IsRegister // .RegLogin = UserName; .RegPass = UserPass; = // .UID = 0; .UName = ""; .Rights = ""; .ErrMsg = "" .ErrMsg = " " // .ErrMsg = " " ; — ( , ) – «_Disconnect», «_UnregisterUser» .
« »
« ADODB.Command », « ADODB.Connection » ( TCP) ;
:
// // // : // - // _Disconnect() // _UnregisterUser(); // .IsDBConnect = ; .LastTestTime = 0; .FBCommand = ; .FBSrvConn <> .FBSrvConn.State = ADO.ObjectStateEnum.adStateOpen .FBSrvConn.Close() ; .FBSrvConn = ; // // // : // - // _UnregisterUser() .IsRegister = ; .RegLogin = ""; .RegPass = ""; .UID = 0; .UName = ""; .Rights = ""; — – «_ExecSQL», «_GetRecordSet», «_CheckRestConnect» .
« ADODB.Command », – « Execute ». « ADODB.Recordset » .
. : (15-20 .), ( TCP-) . , – ( ), , . . , .
. «», – . , () , . , ( localhost, .. , , ).
The communication protocol between the database server and its client does not provide for any additional confirmation requests (sometimes called NOP requests) of the physical TCP connection health, which would periodically run in the background (for example, from the server to the client, the request for a connection, and back answer). In general, Firebird provides so-called. DUMMY-packages (in principle, this should be exactly what I described above), but they are disabled by default in the server configuration. Moreover, there is a postscript in the comments of the “config” that it is impossible to turn it on, otherwise Windows may crash. In confirmation, there is a link to an article from the Microsoft website that talks about such problems when using sockets too heavily (why sockets are used so much by DUMMY packages ???), and, moreover, for Windows 2000 only,and even then only before installing the appropriate KB-update. However, a comment about the inadmissibility of enabling DUMMY-mode passes through all versions of FireBird (maybe it is not only in the newest ones).
, FireBird , ( ) TCP- ( ASK- TCP), SO_KEEPALIVE. , -, «KEEP ALIVE» ( Windows ) 2- ( 15-20., ). -, , , , .. ASK- . , .
But it is clear how to solve this problem. When executing each request, we will evaluate the existence of the connection, and if it is broken, we will try to restore it (reopen) imperceptibly (in the background) from the query execution routine itself. Let's call a subroutine that monitors the presence of the connection (and attempt to restore it) " Function SubCB_CheckRestConnect ". It will return “ True ” if the connection exists (or it was possible to reopen it while the function is running) and “ False ” if the connection cannot be restored at all (in this case the request will not be completed and the user interface of the program should handle the situation loss of connection). How does CheckRestConnect » , , «ADODB.Command»? – , InterBase/FireBird, «SELECT 1 FROM RDB$DATABASE». , . « CheckRestConnect » . « CheckRestConnect » , , 2 ( + 1 ) . « CheckRestConnect », – 5. , , . , « _ExecSQL » « _GetRecordSet », « CheckRestConnect » 2- ( 1- , 2- ). , ( 5.) - , « CheckRestConnect » , . , « CheckRestConnect » .
:
Source text
// SQL- // ( , ). // , - // // : // - // - // : // , - // _ExecSQL(, ) .ErrMsg = ""; // = ; _CheckRestConnect() .FBCommand.CommandText = ; .FBCommand.Execute(); // = ; // .ErrMsg = " : " + (); // , - _CheckRestConnect(, ) .FBCommand.CommandText = ; .FBCommand.Execute(); // = ; // .ErrMsg = " : " + (); ; // SQL- // ( , // ). // // : // - // - // : // , // _GetRecordSet(, ) .ErrMsg = ""; // = ; _CheckRestConnect() .FBCommand.CommandText = ; = .FBCommand.Execute(); // .ErrMsg = " : " + (); // , - _CheckRestConnect(, ) .FBCommand.CommandText = ; = .FBCommand.Execute(); // .ErrMsg = " : " + (); ; // FireBird // ( ). , . // , - // // : // - // - (), , // // (5 .) // : // , - // _CheckRestConnect(, =) // QTestConnect = "SELECT 1 FROM RDB$DATABASE;"; .ErrMsg = ""; // = ; (.FBSrvConn <> ) .IsDBConnect // = 300000; // ( ), = (); (.LastTestTime = ) (.LastTestTime = 0) ( - .LastTestTime >= ) // .FBCommand.CommandText = QTestConnect; .FBCommand.Execute(); // .LastTestTime = ; = // - // FBDBAddr = .FBDBAddr; // FBUserName = .FBUserName; // FBUserPass = .FBUserPass; // IsRegister = .IsRegister; // UserName = .RegLogin; // UserPass = .RegPass; // _Disconnect(); // _ConnectToDB(, FBDBAddr, FBUserName, FBUserPass) // - IsRegister _RegisterUser(, UserName, UserPass) // - .LastTestTime = ; = // - _Disconnect(); // - .LastTestTime = ; = // = // .ErrMsg = " "; ; Auxiliary routines for working with BLOB-parameters.
In InterBase / FireBird, there are 2 types of blob fields: TEXT and BINARY.
TEXT is essentially the same as MEMO in other DBMSs. This is an indeterminate text that is stored separately from the main table. You can work with him through ADO as well as with CHAR / VARCHAR - as with a text string (all transcoding is performed automatically). At the SQL level, you can make comparisons with other text strings (comparison operators and LIKE). There is one such field in our database - this is “CARDS.CDCONTACTS”.
BINARY – BLOB, . , . 1 («ADO.Recordset», «ADO.Parameters») COMSafeArray ( ).
, . – «CARDS.CDNOTE». , – . WIN-1251, 1 «» – UTF-8. :
WIN1251_UTF8 UTF8_WIN1251 COMSafeArrayWIN1251_UTF8 UTF8_COMSafeArrayWIN1251 // ( Windows-1251) // 1C ( UTF-8) // // : // WIN1251 - Windows-1251 // : // "", // WIN1251_UTF8(WIN1251) = ""; (WIN1251) = ("") WIN1251 () = ("") // Windows-1251 UTF-8 ( >= 192) ( <= 223) // - = + 848 ( >= 224) ( <= 239) // - = + 848 ( >= 240) ( <= 255) // - = + 848 ( = 184) // = 1105 ( = 168) // = 1025 ( = 185) // № = 8470 ; // = + () ; // 1 ( UTF-8) // ( Windows-1251) // // : // UTF8 - UTF-8 // : // Windows-1251 // UTF8_WIN1251(UTF8) = ; (UTF8) = ("") =1 (UTF8) = ((UTF8, , 1)); // UTF-8 Windows-1251 < 192 = ( >= 192+848) ( <= 223+848) // - = - 848 ( >= 224+848) ( <= 239+848) // - = - 848 ( >= 240+848) ( <= 255+848) // - = - 848 ( = 1105) // = 184 ( = 1025) // = 168 ( = 8470) // № = 185 // = -1 ; // >= 0 .() ; // COMSafeArray ( Windows-1251) // 1C ( UTF-8) // // : // COMSafeArray - COMSafeArray // ( 1) // - // Windows-1251 // : // "", // COMSafeArrayWIN1251_UTF8(COMSafeArray) = ""; (COMSafeArray) = ("COMSafeArray") (COMSafeArray.GetDimensions() = 1) (COMSafeArray.GetLength(0) > 0) = COMSafeArray.(); = WIN1251_UTF8() ; // 1C ( UTF-8) // ( Windows-1251) COMSafeArray // // : // UTF8 - UTF-8 // : // COMSafeArray Windows-1251 // , // UTF8_COMSafeArrayWIN1251(UTF8) = ; (UTF8) = ("") = UTF8_WIN1251(UTF8); = COMSafeArray(, "VT_UI1", .()) ; BLOB(BINARY) () — «CARDS.CDIMAGE». , « » « COMSafeArray ». « COMSafeArray » « ». « » , «», « BASE64 » « Base64 » « Base64 ». ( ) : « COMSafeArray »« »« »« ». « Base64 » « Base64 ».
, :
Base64 Base64 COMSafeArray_ _COMSafeArray // BASE64. // // : // - ( 0..255), // ; // - BASE64, // ( ). // - 72 . 0, // ( ); // Base64 - - BASE64; // - , // ( 0), // ( -1, // ) // - , // . // : // , , ( // ) // Base64(, =72, Base64, , ) Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"; = ; = -1; = ""; () = ("") (() = ("")) ( >= 0) = .(); Base64 = ""; = 0; // Base64 // , . = 0; // (0..2) = 0;// , 3- =0 // = []; ( >= 0) ( <= 255) // = 256* + ; = + 1; ( > 2) ( = ) // // ( ) - // BASE64 // , // = 2 = 256* ; // BASE64 = ""; // BASE64 // ( ), // "=" =+1 3 = % 64; = "=" + ; = ( - ) / 64 ; // BASE64 =0 = % 64; = (Base64, +1, 1) + ; = ( - ) / 64 ; // = 0 // Base64 = Base64 + // // >= Base64 = Base64 + (13) + (10); = 0 ; // = (); // 4 + <= Base64 = Base64 + ; = + ; =1 >= Base64 = Base64 + (13) + (10); = 0 ; Base64 = Base64 + (, , 1); = + 1 ; = 0; = 0 // : 0.255 = ; = " (0..255)"; // : = " " // : = " " ; // , = "" = ; // BASE64 . // // : // Base64 - BASE64, ; // - ( 0..255), // ( Base64) // - , // Base64 ( 1), // ( 0, // ) // - , // . // : // , , ( // ) // Base64(Base64, , , ) Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"; = ; = 0; = ""; (Base64) = ("") Base64 = (Base64); = ; = 0; // , 3- 4- = 0; // (1..4) // ( "=") = 0; // - ( "=") =1 Base64 = (Base64, , 1); ( = " ") ( = .) ( = .) ( = .) // - = "=" // - 2- (=Base64-1) (=Base64) // "=" // = 64* + 0; = + 1; // : = ; = " "; // , BASE64 Base64 = (Base64, )-1; (Base64 >= 0) (Base64 <= 63) // // = 64* + Base64; = + 1; = + 1 // : = ; = " "; ; // - >= 4 // , , // = % 256; 2 = ; = ( - ) / 256; = % 256; 1 = ; = ( - ) / 256; = % 256; 0 = ; >= 2 .(0); >= 3 .(1); >= 4 .(2) ; // = 0; = 0; = 0; ; // , > 0 = "" // : = " " // : = " " ; // , = "" = ; // COMSafeArray (BLOB- // ) // // : // COMSafeArray - COMSafeArray // ( 1) // - // // : // , // COMSafeArray_(COMSafeArray) = ; (COMSafeArray) = ("COMSafeArray") (COMSafeArray.GetDimensions() = 1) (COMSafeArray.GetLength(0) > 0) = COMSafeArray.(); Base64 = ""; = 0; = ""; Base64(, 0, Base64, , ) = Base64(Base64); = (); = ; // // COMSafeArray // // : // - // : // COMSafeArray // , // _COMSafeArray() = ; (() = ("")) (. <> .) = .(); Base64 = Base64(); = ; = 0; = ""; Base64(Base64, , , ) = COMSafeArray(, "VT_UI1", .()) ; Nothing came of this idea, since the functions “ MassiveByteVStringBase64 ” and “ StringBase64VA MassivByte ” turned out to be too slow, and the conversion of images of tens of kilobytes in size took tens of seconds. I never got to the bottom of the reason - was it possible to improve my algorithm or did everything rest on the platform limitations (execution through the interpreter + the need to work with strings through the “ Medias ” function (since there is no index access to string characters, since data type " Symbol " is also not), as well as the function " Find "). In general, in the end I decided to do the “like all” data exchange “ COMSafeArray ” “ File ” “ Picture". I also used the “ADO.Stream” COM object for saving to a file and downloading it from a file as recommended from the Internet. As a result, the conversion is performed almost instantly. Here is the code:
COMSafeArray_ _COMSafeArray Source text
// COMSafeArray (BLOB- // ) // // : // COMSafeArray - COMSafeArray // ( 1) // - // // : // , // COMSafeArray_(COMSafeArray) = ; (COMSafeArray) = ("COMSafeArray") (COMSafeArray.GetDimensions() = 1) (COMSafeArray.GetLength(0) > 0) = (); // BLOB StreamOut = COM("ADODB.Stream"); StreamOut.Type = ADO.StreamTypeEnum.adTypeBinary; // StreamOut.Mode = ADO.ConnectModeEnum.adModeReadWrite; // - StreamOut.Open(); StreamOut.Write(COMSafeArray); StreamOut.SaveToFile(, ADO.SaveOptionsEnum.adSaveCreateOverWrite); StreamOut.Close(); // = (); = ; // () ; // // COMSafeArray // // : // - // : // COMSafeArray // , // _COMSafeArray() = ; (() = ("")) (. <> .) = (); // .(); // BLOB StreamIn = COM("ADODB.Stream"); StreamIn.Type = ADO.StreamTypeEnum.adTypeBinary; // StreamIn.Mode = ADO.ConnectModeEnum.adModeReadWrite; // - StreamIn.Open(); StreamIn.LoadFromFile(); = StreamIn.Read(); StreamIn.Close(); // () ; Note on the program interface.
It is important to ensure the consistency of the state of the data model and its display interface. Those. - , , . , , . 1 , . ( ), . , , «» . , ( ), . , ( ).
In this test example, the subroutines that allow linking the data model and the display interface for the “ group tree ” and the “ group content list ” are separated into a separate block, i.e. isolated and portable. In principle, the code is quite typical and universal - for application in its programs, only minor modifications are required, performed “on the machine”, without thinking.
These subroutines look like this:
Source text
/////////////////////////////////////////////////////////////////////////////////////////////////// // // // /////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////// // () // ( ). // . // // : // - , ; // _() <> // , = (10, 0); = (); = ; .(); .(("")); = (); // "" .(); .(("")); = (, , ,); // "" .(); .(("")); = (); // "" .(); .(("")); = (, , ); // "" // ..(); ..("", ); ..("", ); ..("", ); ..("", ) // . // // : // - , // - , ; // : // , , // // _(, ) = ; <> (. <> ) (..GRID = ) = . (. <> ) (..GRID = ) = ; = _(, ); <> ; // . // // : // - , ; // - , ; // - , // , ( , // ); // : // , , // // _(, , =) = ; <> // = _(, ) ; = // . (. <> ) (..GRID = ) = = _(, ) ; <> ; // , . // // : // - , ; // - ObjGroup; // - , // , ( , // ); // : // , , // // _(, , =) = ; <> _(, .GRID, ) ; // , // ( , // ) . . // // : // - , ; // - ; // - ; // : // , , // // _(, , ) = ; ( > 0) ( >= 0) ( <> ) = 0 // = // = _(, ); <> // , // = .; <> ..GRID = // , = ; ; = . ; // . // , . // , . ( // ), . // // : // - , ; // - ObjGroup, ; // - , // , ( , // ); // : // , // , // _(, , =) = ; // , ( <> ) ( <> ) = _(, .GRID, ); = // - = _(, .PGRID, ); <> = ..(); . = ; . = 0; . = ; . = .GRName; = // = .; (( = ) (.PGRID < 0)) (( <> ) (..GRID = .PGRID)) // . = ; . = .GRName; = // <> ..() ; = _(, .PGRID); <> = ..(); . = ; . = 0; . = ; . = .GRName; = ; // , // ( ). // // : // - , ; // - ObjGroup, ; // - , // , ( , // ); // : // , // , // _(, , =) = ; ( <> ) ( <> ) = _(, .GRID, ); <> = .; <> = ..(); ..(); = ..(); > 0 < = .[] = .[-1] = ; // . // // : // - ; // - ; // - , ; // - , ; // - , // - // ( , // ); // - - , , // ( =0, - // ). // _(, , , , =, =0) ( <> ) ( >= 0) ( >= 0) // , = ; ( <> ) (..GRID = ) = ; = = _(, , ) ; <> // . = ; ..(); // = ObjSet_(.ClassIDs.CLN_GROUP); _SelectGroups(, , ) // // , // =0 ObjSet_GetCount()-1 = ObjSet_GetItemByIndex(, ); = ..(); . = ; . = 0; . = ; . = .GRName; ; . = ; // <> 1 . // = 0 _(, , , ..GRID, , ) > 1 _(, , , ..GRID, , -1) // _(, ._1, " ", ._, _GetErrorMsg()) ; ObjSet_() // , . // . // // : // - ; // - ; // - , ; // - - , , // ( =0, - // ). // : // , // // _(, , , =0) = ; ( <> ) ( >= 0) // ..(); // = ObjGroup_(); ObjGroup_SetAttr(, 0, -1, ""); = ..(); . = ; . = 0; . = ; . = .GRName; // , ( 2- ) _(, , , .GRID, , ); = ; //////////////////////////////////////////////////////////////////////////////// // () // - // ( ). . // // : // - , ; // _() <> // , = (10, 0); = (); = ; .(); .(("")); = (); // "" .(); .(("")); = (, , ,); // "" .(); .(("")); = (); // "" .(); .(("")); = (, , ); // "" // ..(); ..("", ); ..("", ); ..("", ); ..("", ) // . // // : // - ObjGroup ObjCard, ; // - , ; // : // , - // // _(, ) = ; ( <> ) ( >= 0) = ((.ClassID = .ClassIDs.CLN_GROUP) (.PGRID = )) ((.ClassID = .ClassIDs.CLN_CARD) (.GRID = )) ; // , . // // : // - ObjGroup ObjCard, ; // - , ; // : // , - // // _(, ) = ; ( <> ) ( <> ) = ((.ClassID = .ClassIDs.CLN_GROUP) (. = ) (.GRID = ..GRID)) ((.ClassID = .ClassIDs.CLN_CARD) (. = ) (.CDID = ..CDID)) ; // , . // // : // - , ; // - ObjGroup ObjCard, ; // - , // ( , // ); // : // , , // // _(, , ) = ; ( <> ) ( <> ) ( <> ) _(, ) // , - = // _(, ) = ; ; // ( ) // . // // : // - ; // - ; // - , ; // - , ; // _(, , , ) <> // .(); >= 0 // = ObjSet_(.ClassIDs.CLN_GROUP); _SelectGroups(, , ) // =0 ObjSet_GetCount()-1 = ObjSet_GetItemByIndex(, ); = .(); . = ; . = 0; . = ; . = .GRName; // _(, ._1, " ", ._, _GetErrorMsg()) ; ObjSet_(); // = ObjSet_(.ClassIDs.CLN_CARD); _SelectCardsNames(, , ) // =0 ObjSet_GetCount()-1 = ObjSet_GetItemByIndex(, ); = .(); . = ; . = 1; . = ; . = .CDName; // _(, ._1, " ", ._, _GetErrorMsg()) ; ObjSet_() // . // , . // , . ( // ), . // // : // - , ; // - , ; // - ObjGroup, ; // - , // , ( , // ); // : // , ( ) // , // _(, , , =) = ; ( <> ) ( >= 0) ( <> ) = _(, , ); = // - _(, ) // - .ClassID = .ClassIDs.CLN_GROUP // 1- // = 0; < .() = []; . = + 1 ; // = .(); . = ; . = 0; . = ; . = .GRName; = .ClassID = .ClassIDs.CLN_CARD // = .(); . = ; . = 1; . = ; . = .CDName; = // _(, ) // - . = ; . . = .GRName . = .CDName // ( ) = _(, , ) ; // , . // // : // - , ; // - ObjGroup ObjCard, ; // - , // , ( , // ); // : // , // , // _(, , =) = ; ( <> ) ( <> ) = _(, , ); <> = .(); .(); = .(); > 0 < = [] = [-1] ; How specifically these subroutines are used can be seen in the code of the modules of the corresponding forms (for example, the module of the main form) from the demo.
. ( ), , 2 (1- , 2- , , 1- – ). , , , . «». , , «» , «» . , .
FireBird.
, FireBird :
1. , , :
— «ADO.Connection» «ADO.Command» ;
— SQL- ( )
// FireBird _Driver = "driver={" + "Firebird/InterBase(r) driver" + "}"; _UID = "uid=" + FBUserName; _PWD = "pwd=" + FBUserPass; _DataBase = "database=" + FBDBAddr; = _Driver + ";" + _UID + ";" + _PWD + ";" + _DataBase; // .FBSrvConn = COM("ADODB.Connection"); // .FBSrvConn.open(); .IsDBConnect = ; // .FBCommand = COMObject("ADODB.Command"); .FBCommand.ActiveConnection = .FBSrvConn; .FBCommand.CommandType = ADO.CommandTypeEnum.adCmdText; — – , ,
// .IsDBConnect = ; .LastTestTime = 0; .FBCommand = ; .FBSrvConn <> .FBSrvConn.State = ADO.ObjectStateEnum.adStateOpen .FBSrvConn.Close() ; .FBSrvConn = ; 2. .
. SQL-, . , , . , ( – ), , ( ) . , – 5. «_CheckRestConnect», .
3. :
— , , «ADO.Recordset», ;
— – (, /, ) (, 65535 ), BLOB- «ADO.Parameter»;
— «SELECT» «EXECUTE PROCEDURE» ( SQL-);
4. :
- when working with data declared in the database as text (CHAR, VARCHAR, BLOB (TEXT)) - do not worry about the conversion of encodings;
- when placing the text in the data that is not intended for this (BLOB (BINARY)) - perform the transcoding itself (1C lines are stored in UTF-8). In principle, transcoding can not be done at all - for a DBMS this is not text, but simply a set of bytes. But of course, it is more correct to store these data in the database in the same encoding as the rest of the text fields.
5. Storage of images in the database (in BLOB):
— ( BLOB(BINARY)-) ( ) COMSafeArray (, BLOB(TEXT) ). «» «COMSafeArray» ( ), . «» . «COMSafeArray», COM- «ADO.Stream».
6. BLOB- :
— , , « Parameters » « ADO.Command ». , , « :prm_name » ( ). ADO FireBird ( …). – , . – ADO FireBird . , « ? » ( ). , , « Parameters ». ( ) — « ».
That's all. Good luck to all. .
PS
( 1 8.2 + ) .FireBird 1.5 2.1. : FireBird, « script.sql » ( ).
Source: https://habr.com/ru/post/278583/
All Articles