BlaisePascalMagazine 44 UK

D E L P H I, L A Z A R U S, O X Y G E N E, S M A R T M O B I L E, A N D P A S C A L R E L A T E D L A N G U A G E S A N D R O I D, I O S, M A C, W I N D O W S & L I N U X

BLAISE BLAISE PASCAL PASCAL MAGAZINE MAGAZINE 44

OVER VIEW OF DELP HI TO DELP HI HISTOR Y EUCLIDES AN AGE PUZZ LE BY DAVID DIRKS E

ARDU INO: THE VISUI NO PROJECT - PART 4 INTE RNET OF THIN GS - WIT H ARDU INO AND DELP HI USE ETHERN ET SHIELD, PROGRA M IT WITH VISUINO , CONN ECT FROM A DELPH I APPL ICATION OVER THE LOCAL NETWORK OR INTERNET BY BOIAN MITOV

DATA BASE WORKBE NCH 5 THE SWISS ARMY KNIFE FOR DATABASES BY PET ER VAN DER SMAN

TIPS AND TRI CKS WITH KBMMEMTAB LE BY KIM MADS EN

PRIN TED ISSUE PRICE € 15.00 DOW NLOA D ISS UE PRI CE

€ 7.50

BLAISE BLAISE PASCAL PASCAL MAGAZINE MAGAZINE 44 44 D E L P H I, L A Z A R U S, S M A R T M A N D P A S C A L R E L A T E D F O R A N D R O I D, I O S, M A C, W I N

O B I L L A N D O W S

E S T U D G U A G & L I N U X

I E

O, S

CONTENTS Articles OVER VIEW OF DELPH I TO DELPHI HISTO RY EUCLIDES AN AGE PUZZ LE

PAGE 6 PAGE 7 PAG E 8

BY DAVID DIRKS E

ARDU INO: THE VISUI NO PROJECT - PART 4 INT ERNET OF THINGS - WIT H ARDU INO AND DELP HI USE ETHERNE T SHIELD, PROGRAM IT WITH VISUI NO, CONN ECT FROM A DELP HI APPLICATION OVER THE LOCAL NETWORK OR INTERNET

PAGE 12

BY BOIAN MITOV

DATA BASE WORKBE NCH 5 THE SWISS ARMY KNIFE FOR DATABASES PAGE 28

BY PET ER VAN DER SMAN

TIP S AND TRICKS WITH KBMMEMTA BLE BY KIM MADSE N

PAG E 39

Advertisers BETTER OFFICE COMPONENTS 4 DEVELOPERS 48 COMPUTER MATH MATH & GAMES 4/5 DANIEL TETI DELPHI COOKBOOK 11 EVERS 36 NEW BLAISE LIBRARY 38 PASCON DELPHI 37 UPSCENE 35 VISUINO MITOV 27

2

Publisher: Foundation for Supporting the Pascal Programming Language in collaboration with the Dutch Pascal User Group (Pascal Gebruikers Groep) © Stichting Ondersteuning Programmeertaal Pascal

Issue Nr 6 2015 BLAISE PASCAL MAGAZINE

Stephen Ball http://delphiaball.co.uk @DelphiABall Marco Cantù www.marcocantu.com marco.cantu marco.can tu @ gmail.com gmail.com

Peter Bijlsma -Editor peter @ blaisepascal.eu

Michaël Van Canneyt, michael @ freepasca freepascal.org l.org

David Dirkse www.davdata.nl E-mail: David @ davdata.nl

Benno Evers b.evers @everscustomtechnology.nl Cary Jensen www.jensendatasystems.com http://caryjensen.blogspot.nl

Bruno Fierens Primož Gabrijelčič www.tmssoftware.com www. gabrijelcic.org c.org m primoz @ gabrijelci m bruno.fierens bruno.fie rens @ tmssoftware. tmssoftware.co co John Kuiper [email protected]

Wagner R. Landgraf wagner @ tmssoftware.com

Kim Madsen www.component4developers

Peter van der Sman [email protected]

Jeremy North

Detlef Overbeek - Editor in Chief www.blaisepascal.eu editor @ blaisepascal.eu

Howard Page Clark E-mail: hdpc @ talktalk.net

Andrea Raimondi

Wim Van Ingen Schenau -Editor wisone @ xs4all.nl

Rik Smit rik @ blaisepascal.eu

Bob Swart www.eBob42.com Bob @ eBob42.com

Max Kleiner www.softwareschule.ch [email protected] max@kleine r.com

jeremy.north @ gmail.com

Daniele Teti www.danieleteti.it [email protected]

Please note: extra space characters have been deliberately added around the @ symbol in these email addresses, which need to be removed if you use them.

editor @ blaisepascal.e blaisepascal.eu u Authors - Christian name in alphabethical order A B C D F G H J

Andrea Raimondi , Stephen Ball, Peter Bijlsma, Dmitry Boyarintsev Michaël Van Canneyt, Marco Cantù, David Dirkse, Daniele Teti Bruno Fierens Primož Gabrijelčič, Mattias Gaertner Fikret Hasovic Cary Jensen

L K M N O P S Z

Wagner R. Landgraf, Sergey Lyubeznyy Max Kleiner Kim Madsen, Felipe Monteiro de Cavalho Jeremy North, Inoussa Ouedraogo Howard Page-Cla Page-Clark, rk, Rik Smit, Bob Swart, Siegfried Zuhr

Editor - in - chief Detlef D. Overbeek, Netherlands Tel.: +31 (0)30 890.66.44 / Mobile: +31 (0)6 21.23.62.68 News and Press Releases email only to [email protected] Editors Peter Bijlsma, W. (Wim) van Ingen Schenau, Rik Smit, Correctors Howard Page-Clark, James D. Duff Trademarks All trademarks used are acknowledged as the property of their respective owners. Caveat Whilst we endeavour to ensure that what is published in the magazine is correct, we cannot accept responsibility for any errors or omissions. If you notice something which may be incorrect, please contact the Editor and we will publish a correction where relevant. Subscriptions ( 2013 prices ) 1: Printed version: version: subscription subscription € 65.-- Incl. VAT VAT 6 % (including (including code, programs programs and printed magazine, 10 issues per year excluding postage). 2: Electronic - non printed printed subscription subscription € 45.-- Incl. VAT VAT 21% (including code, code, programs programs and download magazine) magazine) Subscriptions can be taken out online at www.blaisepascal.eu or by written order, or by sending an email to [email protected] Subscriptions can start at any date. All issues published in the calendar year of the subscription will be sent as well. Subscriptions run 365 days. Subscriptions will not be prolonged without notice. Receipt Receipt of payment will be sent by email. Subscriptions can be paid by sending the payment to: ABN AMRO Bank Account no. 44 19 60 863 or by credit card: Paypal Name: Pro Pascal Foundation-Foundation Foundation-Foundation for Supporting the Pascal Programming Programming Language (Stichting Ondersteuning Programeertaal Programeertaal Pascal) IBAN: NL82 ABNA 0441960863 BIC ABNANL2A VAT no.: 81 42 54 147 (Stichting Programmeertaal Pascal) Subscription department Edelstenenbaan 21 / 3402 XA IJsselstein, The Netherlands / Tel.: + 31 (0) 30 890.66.44 / Mobile: + 31 (0) 6 21.23.62.68 [email protected]

Copyright notice All material published in Blaise Pascal is copyright © SOPP Stichting Ondersteuning Programeertaal Programeertaal Pascal unless otherwise noted and may not be copied, distributed or republished without written permission. Authors agree that code associated with their articles will be made available to subscribers after publication by placing it on the website of the PGG for download, and that articles and code will be placed on distributable data storage media. Use of program listings by subscribers for research and study purposes is allowed, but not for commercial purposes. Commercial use of program listings and code is prohibited without the written permission of the author.


3

DAVID DIRKSE presales at www.blaisepascal.eu/DavidDirkse/ComputerMath_Games.html

procedure ; procedure ; var begin for i := 1 to 9 do begin end ; end ;

BLAISE PASCAL MAGAZINE is proud to announce the first edition of David Dirkse’ Dirkse’s s book:

COMPUTER MATH & GAMES IN PASCAL

www.blaisepascal.eu/DavidDirkse/ComputerMath_Games.html

DAVID DIRKSE’s

COMPUTER MATH & GAMES IN PASCAL A book printed in full color, col or, sewn back bound with a hard cover. Quality first. A fully indexed PDF file is included. The book contains 87 chapters , 53 projects with source code and compiled programs (exe). All of these projects you can download at our special website www.blaisepascal.eu The book is highly educational and suitabl e for beginner beginners s as well as for professionals. Play board games, solve puzzles, operate a vintage mechanical calculator, Produce 3-dimensional computer art, generate lists of prime numbers, expl ore and draw any mathematical function. Solve systems of equations, equatio ns, calculate calcula te the area of complex polygons. Draw lines, circles and ellipses. Resize, rotate, compress digital images. Design your own font, generate and reduce Truth Tables from Boolean algebra. And more important: understand underst and how it all works! For the games, winning strategies are explained. For puzzles the search algorithm. For all projects: the math behind is thoroughly discussed. The Delphi 3 – 7 (or later) source code is avai lable together with full explanation. Most of the projects can be done with FPC Lazarus as well. Pascal is the most educative, educative , easy to learn and only language available for several operating systems like Windows, Linux, Mac and Android. It is a great programming language…

OVERVIEW OF DELPHI TO DELPHI HISTORY

DELPHI XE8

On February 8, 2006 Borland announced Borland announced that it was looking for a buyer for its IDE and database line of products, including Delphi, to concentrate on its ALM line. On November 14, 2006 Borland transferred the development tools group to an independent subsidiary company named CodeGear CodeGear,, instead of selling it. Borland sold CodeGear to Embarcadero Technologies in 2008. Embarcadero retained the CodeGear division created by Borland to identify its tool and database offerings, but identified its own database tools under the DatabaseGear name.

Embarcadero Technologies in 2008. Codegear Delphi 2007. DELPHI 7 released in August 2002

DELPHI released February 14, 1995

The roots of Turbo Pascal v1.0 started in Denmark. Denmark. The first step, step, in 1981, was the Blue Label Software Pascal Compiler - BLS Pascal Compiler v1.2, copyright 1981 by Poly-Data microcenter ApS, Strandboulvarden Strandboulv arden 63, DK 2100 Copenhagen - written by Anders for the NASCOM kit computer. Hejlsberg for Hejlsberg

Turbo Pascal Developer(s)) Anders Hejlsberg while Developer(s working at Borland Operating system CP/M, CP/M-86, DOS, Windows 3.x, Macintosh Platform 8080/Z80, 8085, x86

Lisa - Pascal

was a Pascal implementation for the Apple Lisa workstation. It was an extension of the earlier Apple Pascal for Apple II machines, but generated object code for 68000 processors that had to be linked against the required libraries in the Lisa OS workshop. Lisa Pascal laid the foundation for the development of Clascal and Mac Pascal the first implementations of Object Pascal.

Windows

Charles Babbage - mathematician

Niklaus Wirth

conceived of the first programmable computer in the 1830s

born February 15, 1934 He is a Swiss computer scientist, best known for designing several programming languages, including Pascal, and for pioneering several classic topics in software engineering.

Babbage never built his Difference Engine - a mechanical calculator with thousands of parts because of cost overruns and political disagreements, but the inventor passed on plans for plans for its completion, and in 1991, the Science Museum in London actually built it (the printing component was finished in 2000). As suspected, it actually works. Blaise Pascal (19 June 1623 – 19 August 1662) was a French mathematician, physicist, inventor, writer and Christian philosopher. philosopher. creates the first calculators Blaise Pascal starts to gamble - result first s tatistics

Discovery of America by Columbus Columbus led his three ships - the Nina, the Pinta and the Santa Maria out of the Spanish port of Palos on August 3, 1492. Discovery of Amerca by the Vikings 990 - 1050 Building of Spain 912 and Portugal 800 Building of France 58–52 BC

Decay of the Roman Empire 500 Building of Europe

Roman Empire 700 BC

Archimedes Mathematician Archimedes of Syracuse was an Ancient Greek mathematician, physicist, engineer engineer,, inventor, and astronomer. He is regarded as one of the leading scientists in classical antiquity. Wikipedia Born: 287 - 212 BC, Syracuse, Italy Plato in Classical Attic; 428/427 or 424/423 – 348/347 BC) was a philosopher, as well as mathematician, in Classical Greece. Euclid Mathematician Born Mid-4th century BC - 3rd century BC Residence Alexandria, Hellenistic Egypt Fields Mathematics Known for Euclidean geometry / Euclid's Elements Euclidean algorithm

IN THIS ISSUE (43)

Pythagoras Philosopher Pythagoras of Samos was an Ionian Greek philosopher philosopher,, mathematician, and founder of the religious movement called Pythagoreanism. Born: 571 - 495 BC,

Thales Philosopher Thales Philosopher Thales of Miletus was a pre-Socratic Greek philosopher from Miletus in Asia Minor and one of the Seven Sages of Greece. Many, most notably Aristotle, regard him as the first philosopher in the Greek tradition. Born: 624 BC - 546 DELPHI The cult of Apollo at Delphi probably dates back to the 700s B . C .,

Difference Engine No. 1, portion,1832

ISSUE 40

Page 9 WATER CLOCK - CHINA - BEGINNING OF TIME (BC 4000) Some authors claim that water clocks appeared in China as early as 4000 BC

Babbage Difference Engine No. 2

EUCLIDES Euclid (300 BC), sometimes called Euclid of Alexandria to distinguish him from Euclid of Megara, was a Greek mathematician, often referred to as the "Father of Geometry". He was active in Alexandria during the reign of Ptolemy I (323–283 BC). His Elements is one of the most influential works in the history of mathematics, serving as the main textbook for teaching mathematics (especially geometry) from the time of its publication until the late 19th or early 20th century. In the Elements, Euclid deduced the principles of what is now called Euclidean geometry from a small set of axioms. Euclid also wrote works on perspective, conic sections, spherical geometry, number theory and rigor. rigor.

Very few original references to Euclid survive, so little is known about his life. The date , place and circumstances of both his birth and death are unknown and may only be estimated roughly relative to other figures mentioned alongside him. He is rarely mentioned by name by other Greek mathematicians from Archimedes onward, who usually call him "the author of Elements".The few historical references to Euclid were written centuries after he lived, by Proclus c. 450 AD and Pappus of Alexandria c. 320 AD Proclus introduces Euclid only briefly in his Commentary on the Elements. According to Proclus, Euclid belonged to Plato's "persuasion" and brought together the Elements, drawing on prior work by several pupils of Plato. Proclus believes that Euclid is not much younger than these, and that he must have lived during the time of Ptolemy I because he was mentioned by Archimedes (287–212 BC). Although the apparent citation of Euclid by Archimedes has been judg ed to be an a n interpol inte rpol atio n by later lat er editors edi tors o f his works, it is still believed that Euclid wrote his works before those of Archimedes. Proclus later retells a story that, when Ptolemy I asked if there was a shorter path to learning geometry than Euclid's Elements, "Euclid replied there is no royal road to geometry. In the only other key reference to Euclid, Pappus briefly mentioned in the fourth century that Apollonius "spent a very long time with the pupils of Euclid at Alexandria 247–222 BC.


Because the lack of biographical information is unusual for the period (extensive biographies are available for most significant Greek mathematicians for several centuries before and after Euclid), some researchers have proposed that Euclid was not, in fact, a historical character and that his works were written by a team of mathematicians who took the name Euclid from the historical character Euclid of Megara.

Euclidean geometry is a mathematical system attributed to the Alexandrian Greek mathematician Euclid, which he described in his textbook on geometry: the Elements. Euclid's method consists in assuming a small set of intuitively appealing axioms, and deducing many other propositions (theorems) from these. Although many of Euclid's results had been stated by earlier mathematicians, Euclid was the first to show how these propositions could fit into a comprehensive deductive and logical system. The Elements begins with plane geometry, still taught in secondary school as the first axiomatic system and the first examples of formal proof. It goes on to the solid geometry of three dimensions. Much of the Elements states results of what are now called algebra and number theory, explained in geometrical language. For more than two thousand years, the adjective "Euclidean" was unnecessary because no other sort of geometry had been conceived. Euclid's axioms seemed so intuitively obvious (with the possible exception of the parallel postulate) that any theorem proved from them was deemed true in an absolute, often metaphysical, sense. Today, however, many other self-consistent non-Euclidean geometries are known, the first ones having been discovered in the early 19th century. An implication of Albert Einstein's theory of general relativity is that physical space itself is not Euclidean, and Euclidean space is a good approximation for it only where the gravitational field is weak. Euclidean geometry is an example of synthetic geometry, in that it proceeds logically from axioms to propositions without the use of coordinates. This is in contrast to analytic geometry, which uses coordinates.

7

AN AGE PUZZLE PAGE 1/4 BY DAVID DIRKSE

The solutions are displayed in a paintbox, see picture. We notice columns for the solution number, the year of birth, the age and the answer of the question “anniversary passed?”. Pressing the search button starts the search process. Also pressing the return key after the current year starts the search. The text on the form is placed in Tlabel components.

On new year of the year 1997 mr. Black, a math teacher,, meets his form er student White. W hite teacher remembers Black’s fascination for number s and greets him with: “my age is equal to the sum o f the digits of my year of birth”. Black thinks for a while and then a nswers: “co ngr ngratu atu lat ion s on yo ur birth bi rth day ”.

QUESTIONS:

1. 2.

How Black may know that it is White's birthday? What is White's age?

On the bottom of the form at full width there is a statictext comp component onent for messages. The picture below shows : “4 solutions found”.

SOLUTION

If Peter was born in 2000 and we live in the year 2015 there are two possibilities: Peter is 14 years old and his birthda birthday y still has to come or Peter 15 years old and his birthday is passed. The solution may be found by checking all possible years and calculating the sum of the birthyear digits. Test for digitsum digits um = current year – birth year ...(1 if anniversary still has to come) This means a lot of work, so better we write a program to do the job.

×

THE PROGRAM

There may be more solutions. We choose to calculate them all and store them in a list. Thereafter the solutions are displayed on the screen. For a solution we define the data type: Tsolution = record birt bi rthy hyea ear r : wo word rd ; age : byte ; birth bi rthda daypa ypasse ssed d : boo boolea lean n;

; end

And these global variables: : word ; solutions : array[1..maxsolution ] of Tsolution ; solu so luti tion onNr Nr : byte byte ;

var thisyear

Thisyear comes from a TEdit component Thisyear comes (“this year”) where the current year was typed. is an array[1..maxsolution] array[1..maxsolution] of type Solutions is Solutions TSolution.

SolutionNr is the number of stored solutions. SolutionNr is This value is 0 if no solution exists. Ma xs ol ut io n is a constant set at 20.

8

There are three reasons to make a function or a procedure. First is the case of common code that is needed at multiple places of the main program. This reduces the total code. The second reason is clearity. We place specific code apart for readability. For this reason we made a function to sum the birthyear digits.


AN AGE PUZZLE PAGE 2/4 MaxSolution is a constant set to 20, the space in

Third is to concentr concentrate ate specif specific ic operation operationss for maintainability. If the calculation of an area is performed by one function, in case of a change only one place in the program needs to be changed.

the solutions array. Which saves typing. Otherwise we had to add solutions[solutionNr]. before birthyear, age. Now this is done by Delphi.

umd dig igi its(ye yea ar : wo word rd ) : byte ; function sum var s : string; i : byte ;

begin s := int ntt tos ost tr (yea ear r ); result := 0; ] )-ord ('0'); for i := 1 to length (s ) do result := result +ord (s [i ])

; end

The procedure ShowSolutions:

procedure showSolutions ;

//display solutions in paintbox1

Ord('0') is 48, the ASCII code of digit 0. So ord('8') – ord('0') = 8. Without -ord('0 -ord('0') ') we would get 56.

var i,n : byte ; s : string ;

THE SEARCH PROCESS

begin

x,y : word ;

In procedure searchBrtClick, called by the search clearpaintbox ; with form1.PaintBox1 .Canvas do button, we notice following local variables: begin

var va r

brush brus h .st styl yle e := bs bsCl Clea ear r; font fo nt.Co Colo lor r := fo font ntco colo lor r; font fo nt.Na Name me := fo font ntna name me ; font fo nt.He Heig ight ht := fo font nthe heig ight ht ; for n := 1 to solutionNr do solu luti tion onsp sper erdi disp spla lay y then if n <= so

d ig ig it it su su m : b yt yt e ; birth bi rthye year ar : wor word d;

variabele birthyear runs from startyear...thisyear . startyear is a constant set to 1900.

begin y := (n-1)*fontheight + ymarge ; for i := 0 to 3 do

begin

Digitsum is the sum of the digits of birthyear.

Before the search starts two checks are made: 1. the current year must be defined 2. the current year must be equal or bigger than startyear. Then solutionNr is set to zero. All values of birthyear are checked. If the test yields true, age (=digitsum) and anniversary passed is added to the solutions array. This program loop looks like: birt rthy hyea ear r := st star arty tyea ear r to th this isye year ar do for bi begin

x := columns [i ]; case i of 0 : s := inttostr (n ); intt ttos ostr tr(so solu luti tion ons s [n ].bi birt rthy hyea ear r ); 1 : s := in : : = ( [ ] . ) ; s intt in ttos ostr tr so solu luti tion ons s n ag age e 2 birthdaypassed assed 3 : if solutions[n ].birthdayp then s := 'yes' else s := 'no';

; //case end

textout(x ,y ,s );

; end ; //for..if end ; //width end ; end

digits dig itsum um := sum sumdig digits its (bir birthy thyea ear r ); digi gits tsum um = th this isye year ar - bi birt rthy hyea ear r then if di savesolution savesolut ion (digitsum ,birthyear ,true ); digi gits tsum um = th this isye year ar - bi birt rthy hyea ear r - 1 then if di savesolution savesolut ion (digitsum ,birthyear ,false ); ; //for end

The procedure SaveSolution: SaveSolu Solution tion(ag ,by :word word ; bp :boolea boolean n ); procedure Save

//ag:age; by:birthyear; bp:birthday passed begin inc(solutionNr ); solu luti tion onNr Nr <= ma maxs xsol olut utio ion n then if so with solutions [solutionNr ] do

begin

n adresses all solutions. Fontcolor,

fontname, fontheight are

preset constants. Y is the vertical position of the text on paintbox1. X is the horizontal position. Per solution, so per line of text, a variable i counts 0..3 for the column s . Column s[i] holds the x where the text is to start, also a preset constant. Per case of i the text s is prepared. Textout(...) prints s.

birthyear := by ; age := ag ; birt bi rthd hday aypa pass ssed ed := bp ;

; end ; end


9

AN AGE PUZZLE PAGE 3/4 Why all this preset constants? Why not using the values directly? Sure we could. But using constants is smarter because they make future changes much more easy. Say we want to change the startyear. Without the constant definition we would have to change all places where the constant (1900) was used. But defined as a constant we only need to change the constant value once to be effective in all places in the program. PROCEDURE BEFORE CALLED CLEARPAINTBOX.

We notice that the width occupies left ... right-1 and the height is from top to bottom -1. Width = right – left. Height = bottom – top. SETTINGS In the TEdit component YearEdit we enter the current year. Property maxlength of YearEdit is set to 4. This prevents entering

more than four digits. The event YearEditKeyPress points to a procedure of this name. Reason is to allow only the digits 0..9 together with backspace for

yeareditKeyPress yPress (Sender : TObject ; This procedure erases the paintbox by painting procedure TForm1.yeareditKe Key y: Ch Char ar); var Ke the canvas white. ( for this th is color col or we could cou ld have ha ve used use d a begin constant as well) well) if not (key in ['0'..'9',#08]) then key := #0;

procedure clearpaintbox ; begin with form1.PaintBox1 do with canvas do begin

brush brus h.St Styl yle e := bs bsSo Soli lid d; brush.color := $ffffff ; fill fi llre rect ct(re rect ct (0,0,wi widt dth h ,he heig ight ht ));

; end ; end

The paintbox has properties width, height and canvas. By not using with form1.paintbox1.canvas but the separation with form1.paintbox1 do with canvas do, we may use both the canvas and the paintbox properties without the name prefixes. Canvas property brush takes care of background coloring. Style is a property of the brush. If we write brush.style := bsClear the fillrect does nothing, the brush is turned off. Fillrect( r ) paints rectangle r of the paintbox in the color brush.color. r is of type Trect. Function rect (defined inside Delphi) makes a Trect from coordinates. See picture:

; end

8 is the ASCII code of the ba ck sp ac e key. ke y. Prefix # denotes a following character code. The data between […] is of the SE T datatype. We want to start the search as well by pressing the return key after the current year. The return code is catched before it reaches the Tedit. This is done by setting the Tform property KeyPreview to true and defining the KeyPreview to event FormKeyDown. FormKeyDown wn (Sender : TObject ; procedure TForm1.FormKeyDo Word d ;Sh Shift ift : TSh TShif iftSt tState ate ); var Key: Wor

// starts search begin key y = VK_R VK_RET ETUR URN N then if ke begin key := 0; searchBtnClick (self );

; end ; end

VK _R ET UR N is a predifined Delphi constant representing the return key. NOTE: ke y now is of the type word, not char. Procedure searchBtnClick is called with Form1 (self) as sender . For more details please refer to the source code. FINALLY

With the help of this program the reader may solve the puzzle without doing the arithemetic himself. Now it becomes clear why Black is sure today is White's birthday. Other, more difficult, questions would be birth years that have zero or multiple solutions. This would require some extens extensions ions of the program. Have fun!

10


€ 30,00 including VAT € 39 including the printed book, ebook and shipping

Quick answers to common problems

Delphi Cookbook 50 hands-on recipes to master the power of Delphi for cross-platform and mobile development on Windows, Mac OS X, Android, and iOS

Daniele Teti

See our special offer: if you take out a subscription for two years the book will cost you only € 10,00

http://www.blaisepascal.eu/daniele_teti_book/DanieleTeti.html

ARDUINO: THE VISUINO PROJECT - PART 4

PAGE - 1/15

INTERNET OF THINGS WITH ARDUINO AND DELPHI BY BOIAN MITOV In the previous articles, articles , you learned how to program Arduino using Visuino, and ho w to communicate with it usin g USB simulated serial port from your Delphi co de. This opens a lot of interesting possibilities for collecting an d processing live data, but the direct USB connection imposes some limitations. Wha t if you want to collect data from many sensors spread over large area? Or what if you want to communicate with remote sensors over Internet? The basic Arduino UNO does not have built in network adapter, but there is Ethernet shield available for it. In addition many of the m ore advanced Arduino boards and their clones co me with WiFi or wired Ethernet built in. There are also cheap and simple ESP82 66 WiFi modules that can be connected to the Ardu ino, so networking Arduinos is routinely done. In this article you will learn how to setu p Arduino to use Ethernet Shield, how to pro gram it with Visuino, and how to connect to it fr om a Delphi application over the local network or Internet. Before you start, you will need to in stall Ethernet Shield on the Arduin o. This is fairly ea sy sy.. Just snap it on top of the board as shown in the picture.

Add Ethernet Shield:

Next you need to specify the MAC address for the shield. You can use a MAC address generator, generator, or one of the MAC addresses from the Arduino tutorials. Here I use DE-AD-BE-EF-FE-ED:

You also will need to install CommunicationLab, PlotLab and InstrumentLab from Mitov Software. CommunicationLab is not officially released yet, but prerelease builds are available on request. You can also easily modify the examples in this article not to use PlotLab or InstrumentLab. First you will create a simple Arduino server. Start Visuino. Click on the Down arrow button in the top right corner of the Arduino component, and from the menu select “Add Shields...” :

12


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 2/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Set the “UseDHCP” to false, so Arduino will work with a fixed IP address:

And set the Enabled property of the IPAddress to True so the IP address will be used when Arduino starts:

Set the IP address for the Arduino as example 192.168.0.55: Once the Ethernet Shield is configured, yo u can add one or more TCP/IP Client, TCP/IP Server, or UDP sockets to it. Click on the “...” button after the Sockets to add a socket:

Add TCP/IP Server socket:


13

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 3/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Set the Socket Port property to 8080:

To generate some test data from Arduino, you can use a Sine Generator as shown here, or you can use any other source o f Analog data, or one of the Analog channels:

6 14


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 4/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Connect the data source to the Input Pin of the Server Socket:

Press F9 to generate the Arduino Sketch and open the Arduino IDE:


15

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 5/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Click on the Upload button to compile and upload the sketch:

The simplest way to see if the Arduino project is working is to open a web browser and enter the Arduino IP address, and socket number – 192.168.0 .55:8080 . You should see the data appearing in the bro wser wser,, in this case Chro me:

16


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 6/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Next it’s time to receive the data in Delphi. Start RAD Studio, create a VCL Form project, and drop a TCLClientSocket from CommunicationLab on the form:

Set the IP Address to the same used in the Visuino project 192.168.0.55:


Set the Port to 8080:

17

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 7/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Drop TCLTerminal on the form:

Switch to the “ OpenWire ” tab, and connect the Output Pin of the Socket to the Input Pin of the Terminal:

Compile and run the application. You will see the data arriving in the terminal:

18


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 8/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI If you need to access the data in your code, the Socket component has OnReceive event:

Now you can receive data from Ardu ino over the network, however the data ar rives from a single sensor, and in text form. This makes it difficult to work with, and limits the data cha nnels that we can get. As shown in the previous article, Visuino and CommunicationLa b have support for packet data. You can use it with sockets the same way you did with the serial port. Start a new Visuino project, add and con figure the shield and the socket as you did in the previous project.


19

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 9/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Next, add a Packet component:

Connect the Output Pin of the Packet component to the Input Pin of the Socket: Double click on the packet component to open the elements editor. In the editor add 2 Analog and 2 Digital elements:

20


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 10/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Connect the Input Pins of the An alog Channels to the Output Pins of “Analog Input Channel[ 0 ]” and “Analog Input Channel[ 1 ]”, and the Input Pins of the Digital Channels to t he Output Pins of Digital Channel 0 and 1 of th e Arduino Board component:

Expand the HeadMarker and for the Bytes click on the “...” button:


21

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 11/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI In the Bytes editor enter 55 55, then click OK:

This will enure that the packet has unique header and its starting point can be identified in the data stream. Visuino uses special algorit hm to ensure that the header mar ker will not appear in the packet itself. Press F9 to generate, then compile and uplo ad the sketch in the Arduino IDE as you did in the previous project. Now that the Ar duino is ready, lets switch to Delphi. Start a new VCL Form project, add the TCLClientSocket, and set the IP Address and Port as in the previous project:

22


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 12/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Next add a TCLUnpacket, TSLScope, TILAngularGauge and 2 TILLed components:

Switch to the OpenWire tab, and double click on the CLUnpacket1:


23

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 13/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Add 2 Float and 2 Boolean channels:

Expand the HeaderMarker and for the bytes click on the “...” (called ellipsis)

24


ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 14/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI In the Bytes editor enter 55 55, then click OK:

Connect the components as shown in the picture:


25

ARDUINO: THE VISUINO PROJECT - PART 4 PAGE - 15/15 INTERNET OF THINGS WITH ARDUINO AND DELPHI Compile and run the application. You will see the data arriving from Arduino over the 4 channels:

When you need to access the data from code, you can use the TSLGenericRealValue as example to receive the Floating poin t data and process it in the OnProcessData event, as shown in one of the previo us articles. There are also similar components for processing the Boolean data included in LogicLab. The communication to Arduino is equally easy. In order to send the data from Delphi, use TCLPacket component and in Visuino use Unpacket component.

CONCLUSION This article has given you enough information to start communicating with one or more Arduino devices over wired network, or Internet from your Delphi code. This is your first introducti on to the exciting world of Internet of Thin gs. In the following articles you will learn ho w to communicate with Arduino over WiFi, and how to make multiple Arduino boards to ta lk to each other.

BLAISE PASCAL MAGAZINE subscribers that visit our PASCON - Event will receive a DVD with lots ofprograms, information and as a VERY SPECIAL INCENTIVE you INCENTIVE you will get an ARDUINO-BOARD FOR FREE INCLUDING THE VISUINO SOFTWARE from SOFTWARE from Boian Mitov to be able to compose and create your own software for the board

26


What is Visuino? Visuino is the latest innovative software from Mitov Software. A visual programming environment allowing you to program your Arduino boards. Although it currently supports the official Arduino boards, it is not restricted to their support alone and requests to support new hardware are welcome. What is Arduino? The components found in the Visuino software represent their hardware components and you will easily be able to create and design your programs using drag and drop. No equipment or hardware is needed to run the software in design mode. Once you have completed the design, you can connect Arduino board upload and run it. For those people who are not strong on writing code then designing, compiling and creating Arduino programs has never been easier! Why waste time on creating code when we have done all the hard work for you already? You have your Arduino board, and great hardware design, see it running in minutes, not hours! Currently we are running a Beta program which you can be part of by joining our Google group. Join the group now to download and test the software or send an email to [email protected].

AVAILABLE ON THE NEXT PASCON 15 SEPTEMBER 2015

www.visuino.com

VISUINO IS THE LATEST INNOVATIVE PRODUCT FROM MITOV SOFTWARE.

DATABASE WORKBENCH 5

PAGE 1/8

THE SWISS ARMY KNIFE FOR DATABASES

BY PETER VAN DER SMAN

When you start working with databases sooner or later you'll come at a poin t where you start searching for a tool to maintain your database. After a bit of searching you'll en d up using FlameRobin when your databa se is a Firebird database, using other databases might resu lt in other programs. This is cumbersom e at the moment you can't avoid using different databases. For instance when you have two custom ers each using their own database. Database Workbench is database tool working independent from databases, that is to say it supports a lot of different database managers. Tim e to take a closer look at it.

EXAMINING A EXISTING DATABASE

As stated before, Database Workbench d oes support different databases, but is should be said the databases you can use is subject to the licence you have. Having said that we start using Firebird as a starting point as this seems to be the database propagated for using with the current versions of Delphi. So we start our freshly installed program and firstly we have to select our previously installed Firebird as Database server to use. Then we can start opening a existing database. As an example we choose the Employees.fdb which comes as a demo with your Firebird installation. It gives you directly a nice overview ( right column ) Then we can give the things we like a closer view. In the bottom part we find all kinds of information about the database itself. A nice one are the “Connections” under “Activity”. Opening this gives a screen showing which programs are currently connected to the database. Of course it will allways show at least one connection as the program itself is connected to it. After making another connection, for example in the Delphi ( in “Dat a explo rer ” , a second entry will show up (in this case “bd “bds.ex s.exe”). e”). Always good to know in case you're wondering who is blocking the database.

28

Figure 1. The content for Employees.fdb in the “Navigator”

Of course we can use the “Navigator” as above to study the content of all the tables in the database. But Database Workbench offers a much more handy option for this. After opening a “Diagram Editor” we can use the option “Reverse Engineer Database” to build a schematic overview for the database. We can even select to show only a part of the Database but, greedy as we are, we of course select for the whole database.



THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 2/8

Figure 2. Employees.fdb in the “Diagram Editor” (part)

Always difficult to get a nicely looking schema for all this linked tables. You should consider it as a starting point to make a schema as nice as you want to have it. You can replace all tables, smarten the links, whatever you like. The “Navigator” is a nice tool to show where you are in the total schema. Or to navigate quickly to another part. After some manual adjustments our schema could be like figure 3.


29


THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 3/8

Figure 3. Employees.fdb in the “Diagram Editor” (after manual adjustment)

Who would have thought this is all needed for a sample employee file! This schema is just of a size to overview it at a glance. In the real world your schema will easily by a lot larger. Luckily enough we have the possibility to make a “Subdiagram” so you can divide your schema in relevant parts.

In the schema we directly see which fie lds are part of the “primary key” (having a golden key) and which play a role in a reference (having a grey key). More information about the table we get with a double click on it. For example:

Figure 4. Columns for table EMPLOYEE

30



THE SWISS ARMY KNIFE FOR DAT DATABASES ABASES PAGE 4/8 More than enough information. You might notice the “Column Type” for “EMPNO” for the column “EMP_NO”. Looks a bit odd, this has to do with defining your own types in Firebird. In the schema (figure 1) you can find these definitions under “Domains”. For our database has no less than 15 types defined. Our “EMPNO”' turnes out to be of type “SmallInt”. The other tabs in the “Table Properties” form show more information about the table. The tab “DLL” gives you all the code needed to create the table. This design is used consequently. Double clicking in the “Navigator” (figure 1) on different items will result in similar screens to popup. No matter if it is about a “Domain”, a “View”, an “Index”, a ”Constraint”, a “Trigger”, an “Exception” or any other item, you'll get Tab with relevant detail, and a tab “DDL” giving you the code to implement in the database. In fact, you don't really need this code, as we'll see later on, but it can be a handy way to check up what you have done with an example implementation. Unless you're the type inventing all by yourself and never looking at example code? The temptation is strong to directly start making all kind of changes. The schema (figure 3) shows us an “emp_NO”, “proj_ID” en “job_CODE”. Of course there can be puristical reasons the make the distinction between “NO”, “ID” and “CODE”, but perhaps you would prefer to change this all to “ID”. The good news is we actually can easily make these changes in the screen as shown in figure 4. Just change “EMP_NO” to “EMP_ID”. The bad news is it won't be of any use. The diagram in the diagram editor is not connected to a physical database. The consequence is we cannot pass our changes made to the database. In fact this is a good thing. In a designing phase it could be a nice idea to make the proposed changes, making them in an existing database can be quite disastrous. There's every change that these fields play a part in a trigger, a view or a procedure. In fact, we can change fieldnames using Database Workbench: just open a table from the navigator and change the name of the field. In this screen there is this handy button “Create/Alter Table”. Nine times out of 10 this will result in a message, from Firebird, telling you the update was unsuccessful “due to data integrity”.


But changing the “AGED” field from the table “SALES” will work fine using this option. The other way around will work fine. The option “Update From Database” can be used to match your diagram with an existing database. The lay-out we made with so much effort (figure 3) will be maintained. DESIGNING A NEW DATABASE

The previous part showed Database Workbench 5 can be used to analyse the structure of a database we received from a third party. But is it of any good designing a new database? You understand this question is rhetorical. Of course we can. Let's just do it! CONCEPTUAL AND PHYSICAL DIAGRAMS

The best way to start a new design is designing it using the Diagram Editor. Starting with an empty Diagram we'll find under the “New” button two options: “New Conceptual Diagram” and “New Physical Diagram”. It is just like l ike is i s sounds: soun ds: you y ou can star s tartt making maki ng a more mor e sketchy diagram or directly start with a design tailored to the possibilities your database manager of choice provides. If this would not be clear at first sight: after selecting a “New Physical Diagram” you must point out for which database you're going to design. So we choose for a “Conceptual Diagram” and get an empty working area where we can drop new “tables”. Of course I could have chosen to redesign this inevitable databases like Employees or FishFacts, but let's design a brandnew database we can use to browse all articles ever published in “ Blaise. Our first go would be to have three tables: “blaise edition”, “writer” and “article”. So we Figure 5. The first conceptual drop three tables (in diagram this phase named “Ent itie ities” s” ) on our working area, double click on them to give them meaningful names and adding some fields (in this phase named Attributes”). After some rearrangement it could look like this

31


THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 5/8 Note I did'nt mind about the type definition for my fields. No reason to bother about it right now, we can just focus in getting the general structure right. Looking to our design we note the field “Language” appearing in two tables. So most likely we should consider adding a new table/entity “Languages”. And of course we must find a way to bind the “Articles” to the proper “Blaise” edition. The most flexible way to do it is using a new table making the connecting between the two tables. It gives the editor a way to republish an article . Speaking about this: most articles in effect will be republished in different editions, that is to say, in translation. So apart from the writer of the original version there can be a “translator” too. And we should be able to refer to the original article. So we can add a table “Translators”, but the easy way is to use the table “Writers” for this purpose as well. This directly solves the problem of persons who both write articles and translate articles as well. And besides, writing an article or translating, both are a kind of writing. And, in case you had noticed, we now have a reason to use “author” in the Articles” table referring to a table “writers”. And a fieldname “date” is a bit ambiguous, so let's change it to “DatePublished” right away. Let's make some connections as well. The icons as shown in figure 5 point out we can use three kinds of links: ”Identifying Relationship”, and “Do ”Non-Identifying ”Non-Identifyin g Relationship Relationship” ” and “Documen cumen tati on link” . The last one is meant to make connections just for f or clarifi cla rificati cations ons in the t he diagr di agram, am, not no t for implementing in the database. After some not too hard labour it could look like this. In the real world this would be the moment to put it aside and to look at it again at a later point of time and discussing it with a colleague. However, in the world of Blaise the next edition is coming soon, so we continue with our schema right away. We do a bluff and ask Database Workbench to convert this schema to a “Physical Diagram” for Firebird. The result is a pile of error messages: this is the time we should point out the datatypes we are going to use. Further on we did make connections between tables, but I didn't really bother working them out. The good news is we don't have to search ourselves for missing data, we just get a list of items to resolve. We can't blame the program it initially is a long list. The following schema shows the more adapted conceptual schema (figure 7).

32

Figure 6. The half worked out conceptual diagram

Figure 7. The “fully adapted” conceptual diagram



THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 6/8 Where needed I provided for a unique key fie ld in all tables. All fields are given a type. Just for showing the differences I used some different types. And the connections are filled in with relevant information. To get a nice picture I removed the connection between “OriginalVersion” and “ArticleID” from the Diagram. But it still exists. To show it a “Subdiagram” can be used:

We just take the standard options, including Firebird 3.0, and then click OK. That's all to get a diagram adjusted for the desired database. All types are translated to types provided by our database. databas e. To illustr illustrate ate this I made two conversions, one to Firebird and one to MySQL. The differences for the table “Articles” are shown in figure 9. In fact you'll see just one difference, the conversion of the type “Unicode text” in the “Abstract” field . For Firebird it is converted to BLOB(text), for MySQL the “LongText” is used.

Figure 7b. The “Article “Articles” s” sub-diagram

FROM CONCEPTUAL TO PHYSICAL DIAGRAM

Figure 9a. Table “Articles” for Firebird.

So now we are ready to convert to a physical schema. This is the point to decide on which database manager our database must be build. The next thing to select the option “Generate Physical Diagram” and select for Firebird resulting to the following screen.

Figure 9b. Table “Articles” for MySQL

After looking closely to the final schema again, it will probably result in some desired changes. We don't have to go back to the conceptual diagram, we just can make the changes in our conceptual diagram. The interface is nearly the same. So - if this is the moment - we realise the Dutch Edition 126 is a translated version of the English 44 edition and we should provide a way bring this into the database we can adjust the table “Blaise” (in (in the same way we did for Articles): Articles):

Figure 8. Conversion to Firebird


Figure 10. Adapted Table “Blaise”

33


THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 7/8 The reference from “OriginalVersion” back to the table “Blaise” is called a “Constrai “Constraint”. nt”. It is build using the following screen.

Figure 11a. Definition of a Constraint

And, as promised before, if we really would be interested we could find the code needed to add this “Constraint” to the database in the “DDL” tab:

34



THE SWISS ARMY KNIFE FOR DATABASES DATABASES PAGE 8/8 Of course now we have to doubel check the total schema again and adapt it where necessary. We could add a “Che “Check ck Constra Con straint int” ” , an “Ind ex” or or a where desired. “Trigger” “Trigge r” where After doing this we can use the schema to build a real database. We have two options available. The hard way is to use the option “Ext ract DDL D DL For ALL Objects” . It provides us a SQL script to be used for generating the database. The advantage might be you can recheck the script before running it and/or maybe add some very special code you couldn't add using the program. And the script can be useful for documentation purposes. But we also can use the easy way. Just take t ake the th e option opti on “Create Database from Diagram” and and follow the instructions in the Wizard. It will ask you the location of the database, username and password and so on. When we're done the database will be created and the script will run in order to create your structured tables. In anything does go wrong with the script it will pop-up in the script editor so you can make your adjustments. .


EPILOGUE

Database Workbench is a handy tool for working with databases. Either in case you want to examine an unknown database delivered to you or in case you want to design a brand new database, in both cases the program offers you a lot of support to do so. Of course you can find loads of other programs doing this. The nice thing about Database Workbench is it supports multiple databases. So in case you're working with different databases you only have to know about one single tool instead of learning about a tool for each different database

35

COMPONENTS DEVELOPERS

4

Specialist help and consultancy for kbmMW

Benno Evers is our specialist for questions about kbmMW. He can help you with basic questions regarding kbmMW as well as with turnkey Development and Consultancy. He’s a specialist in netwoks, internet and hardware.

[email protected] b.evers@eversct .nl

better office benelux | asterlaan 6 5582EH waalre | 040 – 222 26 43 [email protected]

36


http://www.blaisepascal.eu/DucthPascon/Pascon2015UK.html

L I B R A R Y CLICK TO OPEN ITEM (NUMBER)

1

2

3

4

2 0 1 5

5

CLICK TO OPEN THE TOTAL OVERVIEW OF ALL ITEMS IN ONE

10 12 6 9 7 8 11 13 18 19 16 17 14 15 27 20 25 26 21 22 23 24 28 34 35 36 33 29 30 31 32 40 41 38 37 42 39

CLICK TO OPEN A BROWSER WITH URL CLICK TO OPEN DELPHI URL CLICK TO OPEN ITEM (NUMBER)

CLICK TO OPEN THE TOTAL OVERVIEW OF ALL ITEMS IN ONE

WINDOWS 10

BLAISE PASCAL MAGAZINE AUT HOR S ALFAB ETI CAL

ALL ISSUES IN ONE FILE

www.blaisepascal.eu/subscribers/UK/UK_CD_DVD_ www.blaisepascal.eu/s ubscribers/UK/UK_CD_DVD_USB_Department.html USB_Department.html Issue Nr 6 2015 BLAISE PASCAL MAGAZINE

38

TIPS AND TRICKS WITH KBMMEMTABLE PAGE 1/9 BY KIM MADSEN starter

var

expert

fldID, fldName , fldAddress fldAddres s :TField ;

Delphi

kbmMemTable is an in memory row /column kbmMemTable oriented dat abase which have a vast number of featur es and which is very fast in both storing data and locating stor ed data. I will in this article focu s on some basic things and on some of the l ess known, but very useful, feature s of kbmMemTable. kbmMemTable.

The Codegear edition is for free and can do it all But it only supports specific versions... eg only new xe8 has supprt for latest kbmmemtable. Xe7 suppo su pports rts older ol der kbmmt kb mmt and an d edition edi tion xe2 x e2 even eve n older etc.

begin

// First get quick access to the fields. // This prevents having to search for a field each // and every time a record is to be inserted. fldID ID:= Field eldByN ByNam ame e ('ID '); fld :=mt .Fi fldName ame:= FieldByN dByName ame ('Name '); fldN :=mt.Fiel fldAddre ddress ss := FieldByN dByName ame ('Addr Address ess '); fldA :=mt .Fiel // Prevent update of attached controls on each insert. // This is a big performance factor when inserting huge // amounts of records. mt.DisableCo DisableControls ntrols ; // Prevent update of indexes on each insert. Professional // Edition is much faster in on the fly updates of indexes, // so it will perform extremely fast even without disabling // indexes while inserting. But the highest performance // is obtained by disabling them before the batch insert. EnableIn leIndexe dexes s :=fals false e; mt.Enab

CREATE A MEMORY TABLE WITH INDEXES IN CODE

// Insert a lot of records. for i:=1 to 1000000 do begin

var mt:TkbmMemTa TkbmMemTable ble ;

begin

// Define fields (you may already // have them defined at designtime). Fiel eldD dDef efs s .Ad Add d ('ID',ft ftIn Inte tege ger r ); mt.Fi mt.FieldDefs .Add ('Name',ftString ,80); mt.FieldDefs .Add ('Address' ,ftString ,80); // Define indexes (if you have a large amount of records in your mt // indexes will make searches faster, but inserts/edits slower). mt .IndexDefs .Add ('iI iID' D','ID ID' ',[ixPrimary ]); mt.IndexDefs .Add ('iName' , ‚Name',[ixCaseInsensitive ]); mt.CreateTab CreateTable le ; mt.Open ; ; end

When searching on for records using the Name field as search criteria, we have defined that it should search case insensitively, so for example uppercase A is the same as lowercase a. FAST INSERTION INTO THE DATABASE

mt.Append ; fldID fldI D .AsIn AsIntege teger r :=i ; fldNam fld Name e .As AsStr String ing :='Nam Name e '+in intto ttostr str (i ); fldAdd fld Addres ress s .As AsStr String ing :='Add Addre ress ss '+int inttos tostr tr (i ); mt.Post ;

; end // Enable and update indexes. EnableIn leIndexe dexes s :=true ; mt.Enab mt.UpdateInd UpdateIndexes exes ; // Enable updating attached controls (grids etc). Mt.EnableCon EnableControls trols ;

HOW TO SEARCH?

You either choose to switch to the index representing the column(s) you want to search on, or you simply just search and let kbmMemTable find a relevant index (if any) to use for the search. If you want the absolutely fastest result, then you should switch to the index first, to avoid that kbmMemTable have to make an additional search on your current index to sync with the found place in the search index. However the second index sync search is generally very fast. You can use Locate to search for a complete name for example. If it finds a record with the given name, that record will be the current record, and you can access other fields in it immediately.

kbmMemTable exists in two primary versions, Standard Edition and Professional Edition. Standard Edition can be purchased separately, while Professional Edition only is available as a bundle with kbmMW Professional or kbmMW Enterprise Edition. While kbmMemTable Professional is the absolutely fastest memory table in the world, kbmMemTable Standard Edition can almost reach its performance when used correctly. if mt.Locate ('Na 'Name me' ','J 'Jens ens Hans Hansen' en',[]) then If you are to insert a large number of records in MessageDlg ('Found record. kbmMemTable Standard Edition, then you will Address='+mt.FieldByName ('Address').AsString ); want to do like this:

39


4


TIPS AND TRICKS WITH KBMMEMTABLE You can use Lookup to search for a record containing the given value, and return the contents of any field as result. It will not move the current record.

PAGE 2/9 PAGE - 2/4

Filter records using a filter expression mt.Filter :='(ID>=10) AND (ID<=20)' ; mt.Filt Filtered ered:=true ;

This will give the same result as using the range above, but do not require any indexes. It however scans every record to find matching records, and will thus be slow on large datasets. The advantage with the expression filter is that you can make quite complex filters using AND/OR. When a field name contains spaces, you must enclose the field name in brackets. For example:

v:=mt.Lo Look okup up('N 'Nam ame' e','J 'Jen ens s Ha Hans nsen en' ','A 'Add ddre ress ss' ' ); if not VarIsNull(v ) then MessageDlg ('Found record.Address=' +v );

You can also search for partial values if mt.Locate ('N 'Nam ame' e','J 'Jen ens' s',[loPartialKey ]) then MessageDlg ('Found record. Address='+mt.FieldByName ('Address').AsString ); How to search on multiple fields?

if mt.Locate ('N 'Nam ame' e','J 'Jen ens' s',[loPartialKey ]) then MessageDlg ('Found record. Address='+mt .FieldByName ('Address' ).AsString );

Let kMemTable automatically create indexes for you to improve search performance

[Home State ] = 'CA' or [Home State ] = 'MA'

mt.Auto AutoAddI AddIndex ndexes es :=true ;

This may however result in creation of many indexes over time if you often search on new columns and having many indexes to maintain when inserting/editing/deleting records will always give a performance penalty, so use with care. HOW TO SORT?

Adding an index will always result in the data being sorted according to an index. So you can add an index and switch to it.

mt.AddIndex ('iM 'iMyI yInde ndex' x','Na 'Name' me',[ixDescending ]); mt.IndexName :='iMyIndex' ;

Or even easier: mt.SortOn ('Name' ,[mtcoDescending ]);

HOW TO ONLY SHOW CERTAIN RECORDS?

For this purpose, you can use a range or a filter. A range can limit the records displayed in for example a grid, to show only records within a range (eg. 10<=ID<=20, only show records where the ID is between 10 and 20). 20). The fields participating in the range must be part of an index. mt.IndexName :='iID'; mt.SetRange ([10],[20]);

This will use the index iID, and setup a range to only make records having ID in the range 10 to 20 available. (inclusive) available. (inclusive) Another variant exists of the SetRange method, which accepts a string which contains names of fields that is to be filtered on. A new range index will automatically be generated, and switched to. mt.SetRange ('ID',[10],[20]);

Cancel a range again by mt.CancelRang CancelRange e;

Delete all records within a range mt.DeleteRan DeleteRange ge ('ID',[10 ], ],[20 ]) ]);


The FilterOptions property controls case sensitivity and filtering on partial comparisons. The Filter can be changed at runtime at wish. The following field types can be part of a filter: ftSmallInt, ftWord, ftInteger, ftAutoInc, ftFloat, ftCurrency, ftString, ftFixedChar, ftDate, ftTime, ftDateTime, ftBoolean, ftBCD, and any other field which can return a string or a numeric value. The following operators are available: Arithmetic:

+ Addition can be used on all numeric and string expressions. - Subtraction can be used on all numeric expressions. * Multiplication can be used on all numeric expressions. / Division can be used on on all numeric expressions. Conditions:

= Equal > Greater than < Less than >= Greathe Greatherr than or equal <= Less than or equal <> Not equal IS NULL True if expres expression sion is null IS NOT NULL True if expres expression sion is not null NOT Negates the boolean expression OR True if one of the two boolean expressions are true AND True if both the two boolean expressions are true IN ( ... ) True if the result of the left side expression is found in the list of values. LIKE '....' True if the left side string expression is matching the wildcards in the right side string.


4

40

TIPS AND TRICKS WITH KBMMEMTABLE The wildcards consists of:

* Matches any number of unknown characters. ? Matches exactly one unknown character. An example: Fld1 LIKE 'JOHN*' Functions:

UPPER( expression ) UPPER( expression Convert string expression to uppercase. expression ) LOWER( expression LOWER( Convert string expression to lowercase. SUBSTRING( expression SUBSTRING( expression , startpos , count ) Extract a substring from the expression string starting at startpos and with max length of count chars. The count parameter can be omitted, in which case is means the rest of the string. expression ) Trim string expression for all TRIM( expression TRIM( leading and trailing spaces. expression ) Trim string expression TRIMLEFT( expression TRIMLEFT( for all leading spaces. expression ) Trim string expression TRIMRIGHT( expression TRIMRIGHT( for all trailing spaces. GETDATE Returns a floating point date/time value for current time. YEAR( expression YEAR( expression ) Return the year of a floating point date/time value. expression ) Return the month of the year MO NT H( H( expression (1..12) of a floating point date/time value. expression ) Return the day of the month DAY( expression DAY( (1..28/29/30/31) of a floating point date/time value. expression ) Return the hour (0..23) of a HOUR( expression HOUR( floating point date/time value. MI NU TE ( expression ) Return the minute (0..59) of a floating point date/time value. expression ) Return the second (0..59) of SECOND( expression SECOND( a floating point date/time value. DATE( datestring DATE( datestring , formatstring ) Convert the datestring to a floating point date/time value according to the SysUtils.FormatDateTime format string . expression ) Return the date part of a DATE( expression DATE( floating point date/time value. timestring , formatstring ) TIME( timestring TIME( Convert the timestring to a floating point date/time value according to the SysUtils.FormatDateTimeformat SysUtils.FormatDateTimefo rmat string . TIME(( expression ) Return the time part of a TIME floating point date/time value.

PAGE 3/9

This creates a new index, ordered (and quickly searchable) by ID, and filtered by the given filter string. You can filter on any field or calculation of fields. It doesn't have to be fields that are in the key fields list. Every time you add or alters records, the index will be updated and records may this way disappear or appear in the filtered index depending on the values of the record. When you switch to a filtered index, and thus only show the records participating in that index, scrolling through or searching isjust as fast as if you had no filter defined. Inserts and updates have a small performance penalty, so if you have many records to insert, use the batch insert method outlined earlier in this article. “STANDARD” INDEXES?

With standard indexes, I refer to indexes which are created/defined by the memtable during its course of operation. When a memory table is opened, there always exist one single index, the row order index. It's the index that is updated to ensure that order of insertion of records can be established. It's also responsible for holding a reference to all records. The row order index is selected by setting the IndexName to an empty string. Internally you will find the roworder index to be named '__MT__ROWORDER_' . A string constant exists for that name: kbmRowOrderIndex. The row order index can't be deleted. Further a number of other indexes may be defined at various occations.

Filtering however is done on each record every time that record is accessed, thru scrolling or counting number of records etc. So it's fairly slow on large datasets to use a filter. A better way, is to create a filtered index. That is a real index which only contains references to records that live up to the filter expression. mt. AddFilteredIndex ('iFi Fil lte tere red dIn Ind dex ex' ','ID',[],'(ID ID>= >=1 10) AN AND (ID (ID<=2 =20 0)',[]);

41


4


TIPS AND TRICKS WITH KBMMEMTABLE • '__MT__DETAIL_' (kbmDetailIndex). This index is created (and automatically recreated) when the master record changes in a master/detail relationship. It ensures that browsing through detail records is very fast, and quick to search on. • '__MT__DEFSORT_' (kbmDefSortIndex). This index is created when you are using the Sort or SortOn methods. It is only recreated when you issue another Sort/SortOn method call. • '__MT__DEFAULT_' (kbmDefaultIndex). This index is automatically created when you set IndexFieldNames to a set of fields for which no index already exist. • '__MT__RANGE_' (kbmRangeIndex). This index is automatically created when you apply a range filter. This is • '__MT__AUTO_' (kbmAutoIndex). (kbmAutoIndex). This actually not only one index, but potentially many indexes, which are automatically created if you have the property AutoAddIndexes set to true, and you make a search locate/lookup) on fields for which there are no existing indexe s available. The actual indexname will be '__MT__AUTO_' + the semicolon separated field names. Be aware that this index is not automatically destroyed, so if you search on many weird combinations of fields, you may end up with many indexes, and may need to delete them yourself if needed. INDEX INHERITANCE

If you want to temporarily sort on a different field than the ones participating on the current selected index (it could for example be a filtered index), you simply do SortOn, as described earlier on, while having the filtered index selected. This will create a new sorted index, based on the filtered index, so only records that are acceptable in the base index, are candidate for inclusion in the new sort index. TRANSACTIONS AND VERSIONING

If you make modifications to a number of records in a table, it would, in some situations, be nice to be able to roll back those changes in one go. For example if you have an application where the user can make modifications to a number of entries (inventory for example), and at a later stage decide to save those changes or cancel them. Cancelling the changes would require a roll back, while saving means that the user commits to his changes. Issue Nr 6 2015 BLAISE PASCAL MAGAZINE

PAGE 4/9

kbmMemTable supports whats called

transaction management via the methods StartTransaction, Commit and Rollback. Further it also supports an Undo function on the record level. More about that in a moment. To use transaction management, one also need to understand versioning. kbmMemTable have two properties controlling versioning: EnableVersioning and VersioningMode. EnableVersioning is a Boolean, while VersioningMode can have the value of mtvm1SinceCheckPoint or mtvmAllSinceCheckPoint. Versioning allows kbmMemTable to store

multiple versions of the same record. Whenever a record is inserted into the memory table, one version of that record exists, the one that was inserted. Default EnableVersioning is false and thus the records you see in the memory table are the ones you have. No other versions are created. If EnableVersioning is set to true, then kbmMemTable will start to make multiple versions of a record, depending on the setting of VersioningMode. If VersioningMode is set to mtvm1SinceCheckPoint, then at most, an original version is kept, and the altered one. This is cool, if you need to update an external storage with the changes made in the memory table.

Then the original version identifies what you have to search for, and the new version identifies what to change to. A deltahandler is what is used for handling those scenarios, but now we will focus on transactions. So in this case, you can only undo back to the original version, and not intermediate changes.


4

42

TIPS AND TRICKS WITH KBMMEMTABLE

PAGE 5/9

If you compile and upload the Arduino code as described in the yousee insert/append records, those will be previous articles, and then connect to it with Visuino,When you will the mt.AppendRec AppendRecord ord (…); with usInserted as their sine wave deformed by the quadratic function plottedmarked in the scope: mt. StartTransaction ; UpdateStatus propery. That also happens when mt.Ed Edit it….mt .Po Post st ; you load the records from another dataset, because mt.Delete ; mt.Rollback ; technically they have been inserted into the memory table. You can however ask kbmMemTable In this example a record is inserted, a transaction is to consider these newly loaded records, as being started, then the record is edited and then deleted. the original ones and thus that they should have With VersioningMode set to the UpdateStatus of usUnmodified. mtvm1SinceCheckPoint, then you can only choose to roll back to where the record was mt.CheckPoin CheckPoint t; inserted. The version containing the edited record is The latest version of any record is now considered lost the moment the record is deleted. Only one the original version and marked as version in addition to the original record is kept. To allow for a multi level undo, then usUnmodified, and all intermediate versions VersioningMode should be set to have been removed. That also means that if records mtvmAllSinceCheckPoint. Then you can do were deleted, they are now permanently deleted this: and can't be recovered. To undo last change of the mt.AppendReco AppendRecord rd (…); current record, you can use: mt.StartTrans StartTransaction action ; // First level of transaction Example:

mt.Edi Edit t ….mt.Pos Post t; mt.StartTrans StartTransaction action ; // Second level of transaction mt.Delete ; // Back to the edited record again mt.Rollback ; mt.Rollback ; // Back to the original appended record

You see you can nest transactions this way. If you want to make a change, that is protected by a StartTransaction, stick, then you call mt.Commit. After the commit, the change can only be rolled back if there are multiple levels of nested transactions as in this example: mt.AppendRec AppendRecord ord (…); mt. StartTransaction ; // First level of transaction mt.Ed Edit it….mt .Po Post st ; mt. StartTransaction ; // Second level of transaction mt.Delete ; mt.Commit ; // We will keep the delete but not the

edited record. mt.Rollback ; // Ah no.. we will revert to the original

appended record

You can check the state of a record by checking the UpdateStatus property. It returns the current records state. Was it inserted (usInserted), deleted (usDeleted), modified (usModified) and unchanged (usUnmodified). To actually see usDeleted marked records, it require adding and switching to a special index that allows for showing deleted records… check the overloaded version of AddIndex which accepts providing a set of UpdateStatus flags, for which records to include in the index.

43


4

mt.Undo ;

Remember that the number of undo's you can make on the current record, depends on the setting of EnableVersioning and VersioningMode. THE DELTAHANDLER

A deltahandler is a class that t hat can scan through a memory table, and determine what has happened with each record, and allow the developer to do something depending on what happened. Was it inserted, deleted, modified or was nothing changed with it? It is essentially a great tool t ool for making changes in your dataset resolve back to from where the data originated from (typically a SQL database or files in a file system etc). This is a simple deltahandler: // An example on how to create a deltahandler. TMyD TM yDel elta taHa Hand ndle ler r = class(Tk Tkbm bmCu Cust stom omDe Delt ltaH aHan andl dler er ) protected Inse sert rtRe Reco cord rd(var Re Retr try y :bo bool olea ean n; procedure In var State :TUpdateStatus ); override; va Dele lete teRe Reco cord rd(var Re Retr try y :bo bool olea ean n; procedure De var State :TUpdateStatus ); override; va Modi dify fyRe Reco cord rd(var Re Retr try y :bo bool olea ean n; procedure Mo var State :TUpdateStatus ); override;

// procedure UnmodifiedRecord(var Retry:boolean; var State:TUpdateStatus); override; ; end



PAGE 6/9

TMyD yDel elta taHa Hand ndle ler r .In Inse sert rtRe Reco cord rd (var Re Retr try y :bo bool olea ean n ; var St Stat ate e :TU TUpd pdat ateS eSta tatu tus s ); procedure TM procedure var i:integer ; s ,sv :string; v :variant ; begin s:=''; for i:=0 to FieldCount -1 do begin

v:= Valu lues es[i ]; :=Va if (VarIsNull (v )) then sv :='' else if not (Fields [i ].DataType in kbmBinaryTypes ) then sv :=v

else sv:=''; s:= :=s+sv +' ';

; end

ShowMessage (Format ('Inserted record (%s)' ,[s ]));

; end TMyD yDel elta taHa Hand ndle ler r .De Dele lete teRe Reco cord rd (var Re Retr try y :bo bool olea ean n ; var St Stat ate e :TU TUpd pdat ateS eSta tatu tus s ); procedure TM var i:integer ; s ,sv :string; v :variant ; begin s:=''; for i:=0 to FieldCount -1 do begin

v:= Valu lues es[i ]; :=Va if (VarIsNull (v )) then sv :='' else if not (Fields [i ].DataType in kbmBinaryTypes ) then sv :=v else sv:=''; s:= :=s+sv +' ';

; end

ShowMessage (Format ('Deleted record (%s)',[s ]));

; end TMyD yDel elta taHa Hand ndle ler r .Mo Modi dify fyRe Reco cord rd (var Re Retr try y :bo bool olea ean n ; var St Stat ate e :TU TUpd pdat ateS eSta tatu tus s ); procedure TM var i:integer ; s1 ,s2 ,sv :string; v :variant ; begin s1:=''; s2:=''; for i:=0 to FieldCount -1 do begin

v:= Valu lues es[i ]; :=Va if (VarIsNull (v )) then sv :='' else if not (Fields [i ].DataType in kbmBinaryTypes ) then sv :=v else sv:=''; s1:= :=s1+sv+' ';

v:= OrigVa gValue lues s [i ]; :=Ori if (VarIsNull (v )) then sv :='' else if not (Fields [i ].DataType in kbmBinaryTypes ) then sv :=v else sv:=''; s2:= :=s2+sv+' ';

; end

ShowMessage (Format ('Modified record (%s) to (%s)' ,[s2 ,s1 ]));

; end //procedure TMyDeltaHandler.UnmodifiedRecord(var Retry:boolean; var State:TUpdateStatus); State:TUpdateStatus); //begin //end; And you start the deltahandler like this : var myDeltaHandler :TMyDeltaHandler ;

begin

myDeltaHandler myDeltaHa ndler := TMyDeltaHandler andler .Create (nil); :=TMyDeltaH

try

mt.DeltaHandl DeltaHandler er :=myDeltaHa myDeltaHandler ndler ; mt.Resolve ;

finally

mt.DeltaHandl DeltaHandler er :=nil; myDeltaHandler .Free ;

; end ; end



4

44


PAGE 7/9

LOADING DATA

That require that the indexes are updated on the fly for each record. Standard Edition will have a larger performance penalty than Professional Edition of kbmMemTabl e for this situation. 1. Inserting Inserti ng them one record at a time mt cp oI gn or eE rr or s: 2. Loading the data from another dataset Ignore any copy errors instead of stopping 3. Loading the data from a file copying. mt cp oL oo ku pA sD at a: As for 1, you can use the ordinary Insert/Post, or Copy over lookup fields a regular datafield, with AppendRecord methods. its matching data. mt cp oC al cu la te dA sD at a: Copy over calculated fields as a regular data field As for 2, you can use: mt.LoadFromDataset(an mt.LoadFro mDataset(anotherdatase otherdataset, t, with its calculated data. mt cp oS tr in gA sW id eS tr in g: [mtcpoStructure]); Assume that source ftString, ftFixedChar field s This will make your memory table have the same should be created as ftWideString in your local fields as the anotherdataset (due to the mtcpoStructure option), and load a copy of all data memory table. mt cp oW id eS tr in gU TF 8: from the dataset into the memory table. If string/character/memo fields in the source dataset do not match “wideness” of the same in A number of copy options exists: the destination dataset (your (your local memory table), table), mt cp oS tr uc tu re : then automatically encode or decode to/from Copies table structure (field definitions) from the UTF8. Eg. source. Any field definitions you had in your local If the source dataset has field 'str1' which is a memory table are removed. mt cp oO nl yA ct iv eF ie ld s: ftWideString field, while the local Only field definitions in the source that actually memorytable has the same field 'str1' defined as a are represented as a true field, are copied. ftString field, it will UTF8 encode the data mt cp oP ro pe rt ie s: coming from the source field before putting it into Copy over field properties like DisplayWidth, the destination field. Similarly if a source field is DisplayLabel, Required, ReadOnly, of type ftString, but the destination is of type Visible, DefaultExpression, ftWideString then an UTF8 decoding will take Alignment, ProviderFlags, Lookup, place before putting the value into the destination LookupCache, LookupDataset, field. The same takes place with LookupKeyFields, LookupResultField, ftMemo/ftWideMemo and KeyFields, DisplayFormat, ftFixedChar/ftWideFixedChar . EditFormat, MaxValue, MinValue, Data can be loaded into a memory table in multiple ways:

DisplayValues, TransLiterate, Precision, Currency and BlobType.

If you already have an existing field structure in your local memory table, and want to copy fields from a source dataset, where the field names do not match, you can take advantage of field name mapping.

mt cp oL oo ku p: Also copy lookup fields from the source dataset. mt cp oC al cu la te d: Also copy calculated fields from the source dataset. mt cp oA pp en d: Eg. Append records to the existing records in the mt . LoadFromDataset (anotherdataset , memory table. This cant be used with [ ] , 'str1=str_1;int2=int_2' ) ; mtcpoStructure or mtcpoProperties. Copy the index position of mt cp oF ie ld In de x: x: Copy This is telling kbmMemTable that the local field fields to ensure field order is the same as in the named str1 is called str_1 in the source table etc. original. As for option 3, loading from a file, you can use: mt cp oD on tD is ab le In de xe s: mt .L oa dF ro mF il eV ia Fo rm at ('somefilename', Default a batch insertion is made, where indexes someformatinstance); are only updated after all records have been copied over. However if you for example have an index with unique constraint on a field, then you might want to have your copy to stop early, if there is a duplicate of that field value.

45


4


TIPS AND TRICKS WITH KBMMEMTABLE someformatinstance is

an instance of a stream format component. kbmMemTable comes with two, TkbmMemBinaryStreamFormat and TkbmMemCSVStreamFormat.

PAGE 8/9

Its practical if the data of field fld1 is required to be quickly accessible in your string list. In this example, s will only contain the value of fld2. If you need the field value both as an object and as part of the string, you can include it twice in your field list. Eg. 'fld1;fld1;fld2'.

The binary stream format class stores data compactly and efficiently, and is giving the fastest performance. However the CSV stream format class allows you to read in (and ( and write out) out) A different extraction method is GetRows which comma separated formatted data, for easy returns requested fields as an array of an array of integration with other external systems. variant. kbmMW (our ( our middleware product) product) comes with additional stream formatters for XML and JSON. var v:variant ; Each stream format instance have a number of begin v:=mt.GetR GetRows ows (kbmG kbmGetRo etRowsRe wsRest st , kbmB kbmBookM ookMarkF arkFirst irst ,1); flags that can be set, to tell how it's supposed to ; end handle the reading or writing of data, but that is a story for another time. This will return an array of variant containing an sf:=TkbmMemCS TkbmMemCSVStreamFor VStreamFormat mat .Create (nil); array of variant of size 1, with the contents of the try mt.LoadFromFileViaFormat ('.\mydata.csv' ,sf ) field with FieldNo=1 . finally Eg. v[0,0] is the value of the first field of the first sf.Free ; record. ; end EXTRACTING DATA

A number of methods exist for easily extract data from a memory table. mt .E xt ra ct (' fl d1 ;f ld 2' ,s lD at a) ; This one will extract the fields fld1 and fld2 for all records to a TStrings instance (TStringList that you will have to create beforehand. typically) that typically) If the optional AFormat string is given, e ach line in TStrings will be formatted according to that format. If none is given, then all field values for a record will be separated by a space. The above example will thus put a space between the value of fld1 and fld2 for each record/line. mt.Extract('fld1;fld2' mt.Extract( 'fld1;fld2',slData,'%s ,slData,'%s=%s'); =%s');

Since we specified to ask for kbmGetRowsRest number of records, starting with kbmBookMarkFirst, then we are essentially asking for all records. If we wanted to start from another place in the record set, you should provide a TBookmark value for that place. That is created by navigating to the record, then use the Bookmark function to obtain a bookmark for exactly that place in the record set. And by providing a number instead of kbmGetRowsRest, you can limit the number of

records returned to that particular count. The last argument of GetRows, can either be a field number, a field name or an array of field numbers or an array of field names.

This will put an equal v:=mt.GetRows (kbmGetRow kbmGetRowsRest sRest , sign between the value of fld1 and fld2. It is also possible to specify that the first field (in the following case fld1) should be stored as an object for the text line in the strings list. mt.Extract('fld1;fld2',slData,'',true); var v:variant ; Then you can begin

kbmBookMarkFirst kbmBookMa rkFirst ,VarArrayO VarArrayOf f ('f 'fld ld1' 1','f 'fld ld2' 2'));

access that value by:

… TkbmVariantObject ntObject (slData .Objects [i ] v:= :=TkbmVaria ).Value ; slData ta .Stri Strings ngs [i ]; s:= :=slDa … ; end



4

46


PAGE 9/9

STATISTICS

kbmMemTable contains a number of functions to allow very fast grouping and calculation of statistical values for your data. Returning a sum of fld1 for all records visible in the current index.

Having the following values in mt

var v:variant ; begin

v:= Aggreg regate ate('fld1:SUM' ); :=mt.Agg

; end

v will contain the sum of fld1 for all records in the current index. var v:variant ;

fl d Co un try

f l dSa l es

US

10

GB

20

US

30

DE

15

DE

5

GB

10

US

12

begin

v:= Aggreg regate ate ('fld1:SUM;fld1:STDDEV;fld1:COUNT' ); :=mt.Agg

; end

Will result in the following values v[0] will contain the sum of fld1 for all records in in mtGrouped the current index. v[1] will contain the standard deviation for the records, and v[2] will contain the fldCountry fldSales_Sum fldSales_MAX fldSales_COUN fldSales_COUNT T count of records. The following functions can be DE 15 2 20 used: GB 30 20 2 MA X, MIN M IN , AVG, AV G, COU C OU NT , SUM, SU M, STD S TD DE V and USR1,USR2,USR3. US 52 30 3 The USRx functions are special functions, where the developer have to provide the calculation in the eventhandler OnUserAggregate. If you have data that you want to aggreg aggregate ate in groups, like how big sales in each country, then you can use the GroupBy methods. As grouping often will result in a number of records with aggregated data, then GroupBy require a destination memory table to put the result in.

There are loads of more features available in kbmMemTable and this article touches only a subset of them. But go exploring and you will find attached datasets, which is multiple datasets sharing the same data without holding a copy of it.. essentially an advanced version of having multiple cursors into the data, SQL support, complex math expression evaluation via the SQL manager, locale support etc.

TkbmMemTable le ; var mtGrouped:TkbmMemTab begin

Go have fun!

mtGrouped:= TkbmMemTable le .Create (nil); :=TkbmMemTab

try

Kim Madsen

mt.GroupBy (mtGrouped ,

'fldCountry' , 'fldCountry;fldSales:SUM;fldSales:MAX;fldSales:COUNT' );

… finally mtGrouped.Free ;

; end ; end

The mtGrouped table will be left open for you, with the fields named fldCountry, fldSales_SUM, fldSales_MAX and fldSales_COUNT defined in it.

47


4


- Native high performance 100% developer defined application server with support for loadbalancing and failover - Native high performance JSON and XML (DOM and SAX) for easy integration with external systems - Native support for RTTI assisted object marshalling to and from XML/JSON, now also with new fullfeatured XML schema (XSD) import - High speed, unified database access (35+ supported database APIs) with connection pooling, metadata and data caching on all tiers - Multi head access to the applicatio application n server, via AJAX, native binary, Publish/Subscribe, SOAP, XML, RTMP from web browsers, embedded devices, linked application servers, PCs, mobile devices, Java systems and many more clients - Full FastCGI hosting support. Host PHP/Ruby /Perl/Python applications in kbmMW! - KBMMW V. 4.80 AMQP support

Supports Delphi/C++Builder/RAD Studio 2009 to XE8 (32 bit, 64 bit and OSX where applicable). kbmMW for XE5 to XE8 includes full support for Android and IOS (client and server).! server). ! kbmMemTable is the fastest and most feature rich in memory table for Embarcadero products. -

Easily supports large datasets with millions of records Easy data streaming support Optional to use native SQL engine Supports nested transactions and undo Native and fast build in M/D, aggregation /grouping, range selection features Advanced indexing features for extreme performance

Warning!

kbmMemTable and kbmMW are highly addictive! Once used, and you are hooked for life!

( Advanced Message Queuing Protocol) -

Added AMQP 0.91 client side gateway support and sample. Updated StreamSec TLS transport plugin component (by StreamSec). Improved performan performance ce on Indy TCP/IP Client messaging transport for large number of inbound messages.


4

EESB, SOA,MoM, EAI TOOLS FOR INTELLIGENT SOLUTIONS. kbmMW IS THE PREMIERE N-TIER PRODUCT FOR DELPHI / C++BUILDER BDS DEVELOPMENT FRAMEWORK FOR WIN 32 / 64, .NET AND LINUX WITH WITH CLIENTS RESIDING RESIDING ON WIN32 / 64, .NET, LINUX, UNIX MAINFRAMES, MINI S, EMBEDDED DEVICES, SMART PHONES AND TABLETS.

BlaisePascalMagazine 44 UK

Recommend Documents