Pascal Newsletter #25
The full source code examples of this issue are available for download.
 |
 |
Pascal Newsletter #25 - 18-AUG-2001 INDEX 1. A FEW WORDS FROM THE EDITOR 2. IBADMIN 3.2 - COMPLETE INTERBASE SQL TOOL - What is IBAdmin? - Editions - Features - Prices and ordering - Downloads - More information 3. DIVING INTO DELPHI (I) - A necessary theoretical introduction - A little bit of history - Paradigms and abstraction - Some final words - Bibliographical references - Glossary 4. DOWNLOADS FROM BORLAND'S WEB SITE 5. EMBEDDING FILES AS RESOURCES IN A DELPHI EXECUTABLE 6. TRICKS - Moving rows and columns of a StringGrid by code - Preventing the user from selecting text in a Memo control - Deactivating the context menu - Sorting a TListView by the column clicked by the user - Updating a table with data from another table using Local SQL - Looking for text in any part of a field - Painting Rows in a Delphi 4 DBGrid 7. DELPHI ON THE NET ________________________________________________________________________ 1. A FEW WORDS FROM THE EDITOR In this issue I'm glad to introduce IBAdmin, one of the best Interbase administation tools available, as well as the first part of the article "Inside Delphi" by MSc. Víctor Lorenzo Prado. Regards, Ernesto De Spirito eds2004 @ latiumsoftware.com __________________ English-language editor: Matthew J. Brock <mjb@delphi-programmer.com> ________________________________________________________________________ JfControls Library. Multi-language. Multi-appearance. Skins. Privileges. More than 40 integrated and customizable components. Impressive GUI. Centralized resources administration. Multiple programming problems solved. For Delphi 3-7 and C++ Builder 3-6. http://www.jfactivesoft.com/ ________________________________________________________________________ 2. IBADMIN 3.2 - COMPLETE INTERBASE SQL TOOL What is IBAdmin? ================ IBAdmin is a DBA/Development tool for managing Interbase servers and databases. IBAdmin provides many capabilities to help with your DB design and management. You can use the "Database Designer" to visually design database structure, the "Grant Manager" to manage users, or the "SQL Debugger" which can be used to debug stored procedures and triggers. Comfortable code editing is possible with Code-Insight and Code Completion. Other features include "Database Comparer", where you can easily upgrade deployed databases and "Dependencies Explorer" which will show you all dependencies between database objects. Editions ======== IBAdmin 3 is available in three different versions: * IBAdmin Professional - full-featured version. * IBAdmin Standard - this is the same as Professional without the Database Designer (useful when you don't need the built-in CASE tool). * IBAdmin Lite - includes only basic DB management ability, Database Comparer, Grant Manager and Interbase 5.x to 6.0 migration wizard (useful for client deployment and small amount of Database coding). Features ======== * Easy and comfortable metadata editors. * Data View with data sorting and export capability. * Description for all database objects (except generators) * Any object can be duplicated. * Using the SQL Editor you can: - run any SQL or DDL statement - get full execution statistics - run parametrized queries - use Visual Query Builder to visually build queries - run query in the background - save your queries in the "Favorites" list - save and load session query history - export query results to the Excel files * Extract database metadata: - extract only selected objects - extract table data as INSERT (...) INTO XXX VALUES(...) - print metadata * Search metadata [S] * Stored procedure debugger with breakpoints [S] * Database Designer [P] - CASE tool for Interbase databases * Object Grouping [S] * Dependencies Explorer [S] * SQL Scripts execution * Database Comparer * Grant Manager * Test Data Generator [S] * Interbase 5.x to 6.0 migration wizard * SQL Code templates * Keywords list setup * Interbase 6.0 support includes: - Backup/restore - User Manager - Database Shutdown and Restart - Server Log - Extended database properties (dialect, sweep interval, read-only flag, forced writes) Legend: without sign - included in all versions. [S] - included in Standard and Professional versions. [P] - included in Professional version only. Prices and ordering =================== Up to three licenses, the costs per license are shown below: - IBAdmin 3.2 Professional............$ 229 (USD) - IBAdmin 3.2 Standard................$ 159 (USD) - IBAdmin 3.2 Lite....................$ 79 (USD) If you buy four or more licenses, the costs per license are: - IBAdmin 3.2 Professional............$ 199 (USD) - IBAdmin 3.2 Standard................$ 129 (USD) - IBAdmin 3.2 Lite....................$ 69 (USD) In the case of IBAdmin 3.2 Lite, for eight or more licenses, the cost per license is $ 59 (USD). The are many choices of payment thru ShareIt! For more information about prices, discounts, and ordering, visit: http://www.sqlly.com/ordering.htm Downloads ========= There's a fully-functional shareware version you can download to try the software for 30 days: http://www.sqlly.com/files/ibadmin3.zip An additional tool for IBAdmin 3 is available. IBAdmin Model Viewer can be used for viewing and printing the database models created with Database Designer. IBAdmin Model Viewer can be downloaded from: http://www.sqlly.com/files/dbm_viewer.zip More information ================ You can see some screenshots of IBAdmin at: http://www.sqlly.com/ibadmin_screenshots.htm For more information, you can visit the web site of SQLLY Development, the creators of IBAdmin: http://www.sqlly.com/ibadmin2.htm --------------------- Portions of this article were taken from SQLLY Development's web site and are Copyright (c) 1999-2001 SQLLY Develpoment Inc. - All Rights Reserved. They have been reproduced and modified here with the author's permission. ________________________________________________________________________ 3. DIVING INTO DELPHI (I) By Víctor Lorenzo Prado <vlorz@yahoo.es> A necessary theoretical introduction ==================================== In the theory of programming we usually find our teachers putting a great deal of effort in making sure we follow the set of rules and paradigms that, like a magic wand, will turn us into "Real Programmers". But, "unfortunately", sometimes we are forced to stray a little bit from their teachings in order to get the best out of our compiler and the code it generates. This first part will introduce some basic concepts on the necessity of making abstractions in order to obtain quality software. The subsequent parts will show us how the opposite, not abstracting completely from the internals of the code implementation, could be very helpful for a better understanding of the reasons and purposes of things, and the strengths and weaknesses of the compiler we use. A little bit of history ======================= Some years ago, computer programming was considered an Art. I remember books entitled "The Art of Computer Programming", "Programming as an Art" and others; and somehow it was. Think a little of the scene during those times: the most common computers were the PC-XT and compatibles, a "poor", "tiny" and "defenseless" computer if we think in the limited possibilities of its Intel 8088 central processor (16 bits internal and 8 bits external, 4.77 MHz CLK), with RAM memory of up to 640 Kbytes (although it could be expanded to over 1 MByte using memory expansion cards), together with its "primitive" operating system MS-DOS. With this type of computer each byte of data, each byte of code counts, and therefore the programmer cannot abstract from the kind of computer that he uses when he generates his program code. At present times, the scenario is very different, so much so that the potential of today's personal computers are some years ahead (depending on technology and according to the criteria of specialists) from the average potential required by the software that is being produced nowadays. If years ago the art of computer programming lay in obtaining programs as small and fast as possible in order to fully exploit a computer with such limited potential, today the art of computer programming lies in how to attain an abstraction level that allows the creation of reliable and robust models for the behaviour of objects and entities from the real world. The availability of RAM memory or hard disk space is no longer a major problem. Now, let's see some concepts that will be useful to us as programmers. Paradigms and abstraction ========================= First, let's take a look at some of the most important paradigms in programming theory. Surely we will agree that every software developer always wishes to produce quality code that is: - PORTABLE: Who would be interested in buying a software that cannot be used in his PC? - EXTENSIBLE and MANTAINABLE: Who would be interested in buying obsolete software which is not being constantly upgraded and incorporating the latest technologies? - RE-USABLE: Would a programmer be interested in buying or producing a component library that is not useful for anything? - RELIABLE: Does anybody like to be deceived or see a whole day of work destroyed in a matter of seconds? If we analyze what is discussed [1] about object oriented design and [2] about the design of software for data acquisition systems (and in related papers by other authors), we can see the extraordinary importance that ABSTRACTION has on the software quality - quality that is measured in part by the criteria listed above. Let's see the following example: Example 1. Let us suppose we have a program that is an instructive game where images and sounds are generated. Here is a small/big problem: accessing the video hardware (video controller card) and the generation of sounds (sound card). Let's focus on the access to the sound card and see the various levels of abstraction: 1. Not to consider any abstraction at all and perform direct access to the hardware using "simple" read/write operations to the card ports. But... Do all sound cards have the same INTERFACE? The answer is NO and this generates a tremendous headache for us. Not making abstractions regarding the physical implementation of the hardware forces software to "know" all the possible hardware configurations it will have to work with, and therefore, all the data formats the hardware will accept or return. Can you name software packages that are in this situation? Sure you can, there are some that have been very popular for both entertainment and professional applications. *** BY DEVELOPING SOFTWARE WITHOUT CONSIDERING ABSTRACTIONS, WE ARE GREATLY LIMITING ITS ABILITY TO ADAPT TO NEW OR CHANGING WORK CONDITIONS *** 2. To include a set of APIs as part of the operating system (extensions) or the software that will allow developers to obtain the necessary functionality of the hardware with relative independence over its implementation. Ah..., this sounds better. Now it would be enough with, "solely", making a set of calls to some API functions using well documented data formats. How could the software be updated? By updating the APIs. How could it be made possible for the APIs to be selected at run time? Using APIs in the form of DLLs with explicit binding. But... Again a "but"? Yes. When using the functions from an API, the programmer is totally RESPONSIBLE for the INTEGRITY OF THE DATA. If a function in charge of making some initialization work gives as a result a block of data that will be used by another function (and that, in this case, could precisely be the one that does what needs to be done), it is only the programmer and nobody else who is responsible for maintaining the data safety. And there are many cases where those blocks of data are no more than intermediate data without another utility. An additional problem is added to this: an API, after all, is simply a set of functions grouped in a DLL, and like all functions, they need their parameters given using a specific syntax. If abstractions without respect for the data are made then using syntax with strong type specifications for the input parameters limits the development process of an API. If it is required that a newer version maintains compatibility with previous ones, the syntax for passing parameters must remain the same. An error in the initial design of the API interface, for the sake of compatibility, could be dragged on for life. Can you name examples? Of course you can, do you remember those Windows API functions that end with Ex()? Surely for all of them you'll find previous versions without the Ex() termination. *** DEVELOPING SOFTWARE WITHOUT CONSIDERING ABSTRACTIONS OF THE DATA TYPES AND THE INTERFACES OF THE FUNCTIONS THAT WILL PROCESS THEM WILL ENORMOUSLY LIMIT ITS DEVELOPMENT PROCESS *** 3. To make a double level of abstraction, of data and hardware, using programming tools that allow us to encapsulate, in a same entity, the data and the methods of processing. Nothing more similar to Object Oriented Programming (OOP), right? Ah... The dream..., the great paradigm of programming - not to have to know in detail how things are done at a low level, but solely who must make them and, of course, how to tell him to. Has anyone tried to develop a Delphi application without using the VCL? How much does his productivity diminish? *** BY CREATING OF A SET OF CLASSES THAT ENCAPSULATE ALL THE NECESSARY FUNCTIONALITY WE WILL BE ABLE TO CREATE ROBUST AND RELIABLE SOFTWARE, EASILY EXTENSIBLE AND --MAINLY-- MAINTAINABLE *** Some final words ================ Theory is extremely useful when we want to do things in a professional and elegant way, but by itself it isn't everything. There are cases where "to abstract from abstractions" can be very useful in obtaining the maximum performance from the compiler. Some of these cases will be treated in next sections; some of them will be very useful for a better understanding of how Delphi works while some others are just simple tricks to obtain better performance from the generated code. Bibliographical references ========================== [1] - Grady Booch; Object Oriented Design; 1991, pp. 39-41, 49-53. [2] - Lorenzo Prado, Victor P.; "La Arquitectura del Software: Importancia en el Diseño de los Sistemas de Adquisición de Datos"; Revista Ingeniería Electrónica, Automática y Computación. ISPJAE(CUBA), Volumen XXII, No. 2, 2001, pp. 54-59. Glossary ======== API - Applications Programmer Interface. Set of function libraries (in DLL form) that give support to application development without having to know exactly the technical specifications of the hardware or the inner workings of the operating system. CLK - Clock signal (timing) of the processor. It determines the frequency at which instructions are executed. The higher this frequency, the greater the microprocessor's execution speed will be. When it is said "this processor works at THIS MANY MHz", it doesn't necessarily mean that the entire computer will be using that clock frequency. Generally, only the central processing unit core works at high frequencies (mainly because of physical limitations that cause signal delays and radiation in the form of electromagnetic waves) while most of the computer works at lower frequencies already standardized. DLL - Dynamic Link Library. Functions library that is linked (explicitly or implicitly) with the application at runtime. KByte - Kilo Byte. 1 KByte = 1024 Bytes. MByte - Mega Byte. 1 MByte = 1024 KBytes = 1048576 Bytes. RAM - Random-Access Memory. Memory to which the processor can have access for reading and writing. It is where the data and programs are stored when they are executed. ------------------ Copyright (c) 2001 Víctor Lorenzo Prado ________________________________________________________________________ 4. DOWNLOADS FROM BORLAND'S WEB SITE Kylix Open Edition is ready for free download from Borland's site: * Kylix Open Edition http://www.borland.com/kylix/openedition/ As I mentioned before, the license of this edition only allows the creation of applications distributable exclusively under the terms of the GNU GPL. The one that in some way would be the equivalent of Kylix Open Edition in the Windows environment is Delphi 6 Personal. Its license precludes its commercial use (neither direct nor indirect), and it is also available for free download: * Delphi 6 Personal http://www.borland.com/delphi/personal/index.html Also available for download are 30-day evaluation versions of Delphi 6 Enterprise and Kylix Server Developer Edition: * Delphi 6 Enterprise - Trial Edition http://www.borland.com/delphi/trial6/index.html * Kylix Server Developer Edition - Trial Edition http://www.borland.com/kylix/trial/ In the third step of the download process don't forget to click the link "Send me a serial number and activation key via email" to receive by email the serial number and activation key that is required to be able to install the product. ________________________________________________________________________ 5. EMBEDDING FILES AS RESOURCES IN A DELPHI EXECUTABLE It is possible to embed any kind of file in an executable using resource files (*.RES). Certain kinds of resources are recognized by the API and can be used directly. Others are simply taken as binary data and its up to you to use them. In this article we will see examples of both kinds. To create the resource file we start with the source file (*.RC), for example named RESOURCES.RC, which is a simple text file that contains the resource entries (name, class and file): sample_bmp BITMAP sample.bmp sample_ico ICON sample.ico sample_cur CURSOR sample.cur sample_ani ANICURSOR sample.ani sample_jpg JPEG sample.jpg sample_wav WAVE sample.wav sample_txt TEXT sample.txt The names of the resources (sample_bmp, sample_ico, etc.) are arbitrary. The kind of resource may be one recognized by the APIs (BITMAP, ICON, CURSOR) or arbitrary (JPEG, WAVE, TEXT). The file names specify the files that will be included in the .RES file (and later in the .EXE). Now we have to compile the .RC file to produce the .RES file. For that we can use the Borland Resource Compiler (brcc32.exe) that you can probably find in Delphi's BIN folder. It's a simple command-line utility that expects the name of the source file as parameter: C:\DELPHI\P0025>brcc32 resources Borland Resource Compiler Version 5.40 Copyright (c) 1990, 1999 Inprise Corporation. All rights reserved. C:\DELPHI\P0025>_ To instruct the linker to embed the resource file in the executable, we use the resource file directive ($R or $RESOURCE) in our Pascal source code: {$R resources.res} Loading the resources in your application is easy for the "recongnized" resources like BITMAP, ICON and CURSOR since the Windows API provides functions (LoadBitmap, LoadIcon and LoadCursor respectively) to get handles for these elements, that for example we can assign to the Handle property of the corresponding object: Image1.Picture.Bitmap.Handle := LoadBitmap(hInstance, 'sample_bmp'); Icon.Handle := LoadIcon(hInstance, 'sample_ico'); Screen.Cursors[1] := LoadCursor(hInstance, 'sample_cur'); For more alternatives when loading image resources, see the API LoadImage. Other resources are little bit more difficult to manage. Let's start with JPEG images. The following function uses TResourceStream to load the resource as a stream that will be loaded into a TJPEGImage object: function GetResourceAsJpeg(const resname: string): TJPEGImage; var Stream: TResourceStream; begin Stream := TResourceStream.Create(hInstance, ResName, 'JPEG'); try Result := TJPEGImage.Create; Result.LoadFromStream(Stream); finally Stream.Free; end; end; Example: var Jpg: TJPEGImage; begin // ... Jpg := GetResourceAsJpeg('sample_jpg'); Image2.Picture.Bitmap.Assign(Jpg); Jpg.Free; // ... end; For WAV files we need a pointer to the resource loaded in memory, and for a text file we need to load a resource in a string. We can do it using TResourceStream, but let's see an example using the API: function GetResourceAsPointer(ResName: pchar; ResType: pchar; out Size: longword): pointer; var InfoBlock: HRSRC; GlobalMemoryBlock: HGLOBAL; begin InfoBlock := FindResource(hInstance, resname, restype); if InfoBlock = 0 then raise Exception.Create(SysErrorMessage(GetLastError)); size := SizeofResource(hInstance, InfoBlock); if size = 0 then raise Exception.Create(SysErrorMessage(GetLastError)); GlobalMemoryBlock := LoadResource(hInstance, InfoBlock); if GlobalMemoryBlock = 0 then raise Exception.Create(SysErrorMessage(GetLastError)); Result := LockResource(GlobalMemoryBlock); if Result = nil then raise Exception.Create(SysErrorMessage(GetLastError)); end; function GetResourceAsString(ResName: pchar; ResType: pchar): string; var ResData: PChar; ResSize: Longword; begin ResData := GetResourceAsPointer(resname, restype, ResSize); SetString(Result, ResData, ResSize); end; Sample calls: var sample_wav: pointer; procedure TForm1.FormCreate(Sender: TObject); var size: longword; begin ... sample_wav := GetResourceAsPointer('sample_wav', 'wave', size); Memo1.Lines.Text := GetResourceAsString('sample_txt', 'text'); end; Once we have the wave resource loaded into memory we can play it as many times as we want by using the API sndPlaySound declared in the MMSystem unit: procedure TForm1.Button1Click(Sender: TObject); begin sndPlaySound(sample_wav, SND_MEMORY or SND_NODEFAULT or SND_ASYNC); end; There are some resources (like fonts and animated cursors) that can't be used from memory. We necessarily have to save these resources to a temporary disk file and load them from there. The following function saves a resource to a file: procedure SaveResourceAsFile(const ResName: string; ResType: pchar; const FileName: string); begin with TResourceStream.Create(hInstance, ResName, ResType) do try SaveToFile(FileName); finally Free; end; end; The following function makes use of the previous one to save a resource in a temporary file: function SaveResourceAsTempFile(const ResName: string; ResType: pchar): string; begin Result := CreateTempFile; SaveResourceAsFile(ResName, ResType, Result); end; The discussion of the function CreateTempFile falls beyond the scope of this article and its implementation can be seen in the example attached to the newsletter. The following function makes use of SaveResourceAsTempFile to save an animated-cursor resource to a temporary file, then it loads the cursor from the file with LoadImage and finally deletes the temporary file. The function returns the handle returned by LoadImage: function GetResourceAsAniCursor(const ResName: string): HCursor; var CursorFile: string; begin CursorFile := SaveResourceAsTempFile(ResName, 'ANICURSOR'); Result := LoadImage(0, PChar(CursorFile), IMAGE_CURSOR, 0, 0, LR_DEFAULTSIZE or LR_LOADFROMFILE); DeleteFile(CursorFile); if Result = 0 then raise Exception.Create(SysErrorMessage(GetLastError)); end; Sample call: Screen.Cursors[1] := GetResourceAsAniCursor('sample_ani'); Form1.Cursor := 1; Well, that's it. I hope you find it useful. You can find more information about resource files in the MSDN Library: http://msdn.microsoft.com/library/en-us/winui/hh/winui/rc_6cs3.asp ________________________________________________________________________ 6. TRICKS Moving rows and columns of a StringGrid by code =============================================== The user can move rows and columns of a StringGrid with the mouse. Can it also be done by code? In the help for TCustomGrid you can see the methods MoveColumn and MoveRow, but they are hidden in TStringGrid. We can make them accessible again by subclassing TStringGrid and declaring these methods as public: type TStringGridX = class(TStringGrid) public procedure MoveColumn(FromIndex, ToIndex: Longint); procedure MoveRow(FromIndex, ToIndex: Longint); end; The implementation of these methods simply consists of invoking the corresponding method of the ancestor: procedure TStringGridX.MoveColumn(FromIndex, ToIndex: Integer); begin inherited; end; procedure TStringGridX.MoveRow(FromIndex, ToIndex: Integer); begin inherited; end; You don't have to register this component in the Components Palette. Use a TStringGrid or any TCustomGrid descendant, and when you need to call these methods simply cast the object to the new class. For example: procedure TForm1.Button1Click(Sender: TObject); begin TStringGridX(StringGrid1).MoveColumn(1, 3); end; Preventing the user from selecting text in a Memo control ========================================================= The easiest way would be to set the Enabled property of the Memo (or Edit) control to False so that the control cannot receive events. This drawback to this method is the user won't be able to scroll the text and the disabled text looks bad. In order to prevent the user from writing in the memo, we set its ReadOnly property to True. To prevent the user from selecting text with the mouse, we generate the handler of the MouseMove event of the control and write the following code: procedure TForm1.Memo1MouseMove(Sender: TObject; Shift: TShiftState; X, Y: Integer); begin if ssLeft in Shift then Memo1.SelLength := 0; end; In order to prevent the user from performing a selection using the keyboard, we generate the handlers of the KeyDown and KeyUp events, assigning the OnKeyDown and OnKeyUp properties to the same procedure: procedure TForm1.Memo1KeyDownUp(Sender: TObject; var Key: Word; Shift: TShiftState); begin if (ssShift in Shift) and (Key in [VK_LEFT, VK_RIGHT, VK_UP, VK_DOWN, VK_PRIOR, VK_NEXT, VK_HOME, VK_END]) then Key := 0; end; Deactivating the context menu ============================= When we right-click on an Edit component (or other components that allow editing such as MaskEdit, Memo, DbEdit, etc.), by default the system's context menu pops up with options to undo, copy, paste, etc. If for any reason we don't want this menu to be displayed, one way is to simply put a TPopupMenu component on the form and assign it to the PopupMenu property of the components whose context menu we want to disable. Sorting a TListView by the column clicked by the user ===================================================== We want the following behaviour for a ListView: * When the user clicks on a column header, the ListView should be sorted by that column * The initial sort order should be ascending. If the user clicks on the same column again, the sort order should be toggled. If the user clicks on another column, the sort order of the new column should be the same as the last sorted column. For the implementation we need two variables to hold the last column clicked by the user and the current sort order: var LastSortedColumn: integer; Ascending: boolean; We can initialize them when the form is created: procedure TForm1.FormCreate(Sender: TObject); begin LastSortedColumn := -1; Ascending := True; end; In the ColumnClick event of the ListView we determine the sort order and perform the sort: procedure TForm1.ListView1ColumnClick(Sender: TObject; Column: TListColumn); begin if Column.Index = LastSortedColumn then Ascending := not Ascending else LastSortedColumn := Column.Index; TListView(Sender).CustomSort(@SortByColumn, Column.Index); end; SortByColumn is a function that should be previously declared and is the function used by CustomSort to compare two items. The value passed to the Data parameter of CustomSort will be passed as the Data parameter to SortByColumn and we use it for the sort column: function SortByColumn(Item1, Item2: TListItem; Data: integer): integer; stdcall; begin if Data = 0 then Result := AnsiCompareText(Item1.Caption, Item2.Caption) else Result := AnsiCompareText(Item1.SubItems[Data-1], Item2.SubItems[Data-1]); if not Ascending then Result := -Result; end; Making a form "always visible" ============================== To make a form always visible above other forms either belonging to the same application as well as other applications, we can change the FormStyle property to fsStayOnTop. Later we can switch back to normal by setting FormStyle to fsNormal. However, changing the FormStyle property at runtime isn't recommended. Instead, we should use the Windows API function SetWindowPos, passing it HWND_TOPMOST as the second parameter to activate the effect: procedure TForm1.Button1Click(Sender: TObject); begin SetWindowPos(Handle, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE); end; To deactivate this effect, we call SetWindowPos again, this time passing it WND_NOTOPMOST as the second parameter: procedure TForm1.Button2Click(Sender: TObject); begin SetWindowPos(Handle, WND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE); end; Updating a table with data from another table with Local SQL ============================================================ +-------------+ +-------------+ | Orders.db | | Customer.db | +-------------+ +-------------+ | CustNo | <<---------> | CustNo | | ShipToAddr1 | | Addr1 | | ShipToAddr2 | | Addr2 | +-------------+ +-------------+ Assuming that we wanted to update the fields ShipToAddr1 and ShipToAddr2 of the Orders.db table with the values of the fields Addr1 and Addr2 respectively from the table Customer.db, for those records of Orders that have both fields blank, and joining the tables by the field CustNo present in both tables, perhaps we would be temped to write: UPDATE Orders INNER JOIN Customer ON Customer.CustNo = Orders.CustNo SET ShipToAddr1 = Addr1, ShipToAddr2 = Addr2 WHERE ShipToAddr1 = "" AND ShipToAddr2 = "" However, in Local SQL (the one used by the BDE), joins are not supported in the UPDATE statement, and we have to use subqueries to achieve the expected result: UPDATE Orders SET ShipToAddr1 = (SELECT Addr1 FROM Customer WHERE Customer.CustNo = Orders.CustNo), ShipToAddr2 = (SELECT Addr2 FROM customer WHERE Customer.CustNo = Orders.CustNo) WHERE ShipToAddr1 = "" AND ShipToAddr2 = "" In the "UPDATE statement" topic of the Local SQL Guide you can find an example of a 1-to-many relationship that uses grouping the subqueries. Looking for text in any part of a field ======================================= The following function searches for text in any part of a field of any dataset (it can be for example a TTable, TQuery, TADOTable, TADOQuery, TIBTable, TIBQuery, etc.) type TLocateStrOption = (loCaseSensitive, loContinue); TLocateStrOptions = set of TLocateStrOption; function LocateStr(Dataset: TDataset; Field: TField; Str: String; LocateOptions: TLocateStrOptions): boolean; // Searches text in any part of a dataset field. The search can be // case sensitive (option loCaseSensitive) and can start from the // beginning or from the current record (option loContinue). // // Returns True if the string was found (the dataset is positioned // in that record) and False otherwise (the dataset is left in EOF) var ControlsDisabled: boolean; begin ControlsDisabled := Dataset.ControlsDisabled; if not ControlsDisabled then Dataset.DisableControls; try if loContinue in LocateOptions then begin if not Dataset.Eof then Dataset.Next; end else Dataset.First; // Start from the beginning if not (loCaseSensitive in LocateOptions) then Str := UpperCase(Str); while not Dataset.Eof do begin if loCaseSensitive in LocateOptions then begin if Pos(Str, Field.AsString) <> 0 then break; end else begin if Pos(Str, UpperCase(Field.AsString)) <> 0 then break; end; Dataset.Next; end; Result := Dataset.Eof; finally if not ControlsDisabled then Dataset.EnableControls; end; end; Painting rows in a Delphi 4 DBGrid ================================== In a past issue we presented an example that painted the rows of a DBGrid with different colors, but that code was meant for Delphi 5. The following code uses the OnDrawDataCell event and the DefaultDrawDataCell method, and will hopefully work in Delphi 4: procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect; Field: TField; State: TGridDrawState); begin with TDBGrid(Sender) do begin if SelectedRows.IndexOf(DataSource.Dataset.Bookmark) >= 0 then Canvas.Brush.Color := clPurple else if gdSelected in State then Canvas.Brush.Color := clHighlight else if (DataSource.Dataset.RecNo and 1) <> 0 then Canvas.Brush.Color := $00DDEEFF else Canvas.Brush.Color := $00DDFFFF; DefaultDrawDataCell(Rect, Field, State); end; end; ________________________________________________________________________ 7. DELPHI ON THE NET Articles ======== * Delphi Database Programming Course - By Zarko Gajic Free online database programming course for beginner Delphi developers focused on ADO techniques. Two new chapters have been added in the last month (Chapter 12 "Master detail relationships" and Chapter 13 "New...Access Database from Delphi"). http://delphi.about.com/compute/delphi/library/weekly/aa010101a.htm * Building a stand-alone Web service with Indy - By Dave Nottage This article shows how to build a Web service using Indy and Delphi 6. http://community.borland.com/article/0,1410,27513,00.html * An introduction to programming strategy games - By Armando de la Torre This article describes the creation of a game known as Pente or Go, a simple strategy game which has similar rules to tic-tac-toe. http://community.borland.com/article/0,1410,27512,00.html * WebSnap is now in session - By Nick Hodges This article discusses how to maintain session information in a WebSnap application. http://community.borland.com/article/0,1410,27521,00.html * Loggin' in ain't hard to do - By Nick Hodges This article illustrates how easy it is to add log-in capability to your WebSnap applications http://community.borland.com/article/0,1410,27485,00.html * Managing sessions with Delphi 6 Web services (updated) - By Daniel Polistchuck Building an e-business application? Then you'll need techniques like these to manage session states and authentication. http://community.borland.com/article/0,1410,27575,00.html * Groping in the dark - By John Flaxman A first look into the Open Tools API to implement an extension to the Alt-[ key mapping http://community.borland.com/article/0,1410,26389,00.html * Simulating the Windows API's FindWindow function with Kylix - By Matthias Thoma This article descripes a very handy FindWindow function. http://community.borland.com/article/0,1410,27395,00.html * Maximizing Performance of Delphi/C++Builder/InterBase Applictions - By Robert Schieck This paper will present some tips to help you with getting better performance from your Delphi/C++Builder/InterBase system. http://community.borland.com/article/0,1410,27534,00.html * Migrating Your Delphi 5 Projects to Kylix - By Bob Swart This paper provides an overview and detailed description of migrating applications from Borland Delphi 5 for Windows to Kylix for Linux http://community.borland.com/article/0,1410,27534,00.html * Community TV: Delphi 6 WebSnap with Jim Tierney John K interviews Jim Tierney, architect of WebSnap, for the details on this new Delphi 6 feature. Now with text transcript and MP3 audio. http://community.borland.com/article/0,1410,27303,00.html * Sip From The Firehose: July 9, 2001 - Everybody's Talking About Web Services... - by David Intersimone In this installment of my column, we'll take a look at the hot topic of Web Services. Publishing your web application interfaces over the Internet is the next great evolution in the developent of distributed objects and applications. http://community.borland.com/article/0,1410,27501,00.html * Aspect ratio lab report - By Earl F. Glynn This lab report demonstrates how to display a rectangular TBitmap in a rectangular TImage of any size while preserving the original aspect ratio. http://homepages.borland.com/efg2lab/ImageProcessing/AspectRatio.htm * Cursor overlay lab report - By Earl F. Glynn This lab report demonstrates how to convert a cursor to a bitmap and then overlay the cursor's bitmap on another bitmap. http://homepages.borland.com/efg2lab/Graphics/CursorOverlay.htm * Windows Shell Extensions – Info Tip - By Larry J. Rutledge Creating the InfoTip Shell Extension, which lets us, control the information that appears in Explorer when the mouse hovers over a file. Creating a Delphi Infotip that will display the FileName, the project type (Program or Library), the Project Name (from the source file), and the size of the file in bytes. http://delphi.about.com/library/bluc/text/uc071701a.htm * A more powerful Delphi Form - By Zarko Gajic Messing with the creation process of a form object, or how to change the default style of a window when it gets created to suit your particular needs. Transparent forms, no caption forms, realy StayOnTop forms, ... http://delphi.about.com/library/weekly/aa073101a.htm * Component writing, part 1 - By Peter Morris This first part demonstrates some of the best approaches to building components, and at the same time provides tips on deciding on the best base class to inherit from, using virtual declarations, the process of overriding, and so on. http://www.howtodothings.com/showarticle.asp?article=310 * Real-time 2D particle systems (with gravitation!) - By Curtis W. Socha This tutorial is going to discuss a library of Delphi routines that will help you create your own particle systems. The term system is defined to mean "A group of interacting, interrelated, or interdependent elements forming a complex whole". You can use the particle to represent a cannonball being shot out of a cannon in a trajectory game. http://delphi.about.com/library/bluc/text/uc071501a.htm Other links =========== * Freeware ADO dataset components http://www.deer-soft.com/ http://www.alohaoi.com/Software/Products/aoado/default.htm http://www.agric.za/freeway/ADOds.htm * The Delphi Inspiration Freeware Delphi Components: rjExContainers Library (Vector, List, Tree and Hash Containers), rjHtmlParser (Parser for Html files), rjMime (Encode and Decode), rjPCRE (Perl Regular Expressions for Delphi), rjPasDoc (Generate Html Help from Pascal Sources). http://www.zeitungsjunge.de/delphi/ ________________________________________________________________________ YOU CAN HELP US We need your help to keep this newsletter going and growing. Contribute! =========== If you have developed an interesting routine, found a solution to a problem, etc., please write us so that we can evaluate the inclusion of your material in the newsletter. PLEASE don't send us compiled items, and PLEASE don't send us material copyrighted by others. If you found something interesting in the Internet, please limit to sending us the link. Divulge! ======== The easiest way you can help us is by voting for us in any or all of these rankings to help give more visibility to our web site and thus increase the number of subscriptions to this newsletter: http://www.sandbrooksoftware.com/cgi-bin/TopSite2/rankem.cgi?id=latium http://news.optimax.com/delphi/links/links.exe/click?id=70C517ECAE6E http://www.programmingpages.com/?r=latiumsoftwarecomenpascal http://www.top219.org/cgi-bin/vote.cgi?delphi&83 http://top100borland.com/in.php?who=20 http://top200.jazarsoft.com/delphi/rank.php3?id=latium http://213.65.224.200/cgi-bin/toplist.cgi/hits?Id=80 It's just a few seconds for you that REALLY mean a lot to us. ________________________________________________________________________ If you haven't received the full source code examples for this issue, you can get them from http://www.latiumsoftware.com/download/p0025.zip ________________________________________________________________________ This newsletter is provided "AS IS" without warranty of any kind. Its use implies the acceptance of our licensing terms and disclaimer of warranty you can read at http://www.latiumsoftware.com/en/legal.php where you will also find a note about legal trademarks. Articles are copyright of their respective authors and they are reproduced here with their permission. You can redistribute this newsletter as long as you do it in full (including copyright notices), without changes, and gratis. ________________________________________________________________________ Main page: http://www.latiumsoftware.com/en/pascal/delphi-newsletter.php Group home page: http://groups.yahoo.com/group/pascal-newsletter/ Subscribe/join: pascal-newsletter-subscribe@yahoogroups.com Unsubscribe/leave: pascal-newsletter-unsubscribe@yahoogroups.com Problems with your subscription? eds2004 @ latiumsoftware.com ________________________________________________________________________ Latium Software http://www.latiumsoftware.com/en/index.php Copyright (c) 2001 by Ernesto De Spirito. All rights reserved. ________________________________________________________________________ |
The full source code examples of this issue are available for download.
 |
Errors? Omissions? Comments? Please contact us!
