Unit? Datamodule? Whats the right way? lazarus / 16-Databases ---------------------------------------------------------------------- Codelizard · 2014-04-08 22:57 Ok, I've been reading the wikis and searching the forums, but I still have many questions.  My major roadblock right now is keeping me from writing any code.  LEt me explain what I am doing right now in my VB apps, and why I do it that way, and then lay out my questions. As I said before in another post, my first Lazarus/FPC app will be a rewrite of something I've already written in VB6.  In my current app, I dont mess with any data bound components at all.  ALL of my database connection and updating is done via source code.  This way, it is not form dependant.  When my app starts, the first thing it does is call a function in a module, that then does the connection and puts it into a GLOBAL connection object.  Then, it creates a recordset object for each of my tables and ties it to the connection. So now, I have a db connection sitting in a global connection object, and i can use recordsets or just execute queries against the connection object.  This means any form, any function, any subroutine, etc has access to use the database at any time. Now, if I am correct, from what I have read I cant do this in Lazarus.  It wants me to have a connection object on a form, so i can create the connection.  Is this required?  Is there some way to emulate what I do above?  Im trying to use SQLDB so i can utilize both embedded and remote firebird, and i want to to be solid so i can do it on MAC OSX as well. Thats part 1. In my existing code, its all ad-hoc.  whenever i need to create a row, i write a sql statement.  so i have code all over the place.  This time, from design time, I want to do it smarter.  I want to have a class for each table.  So, if im on an employee screen and click the update button, i dont need to write a long as query, i can just call the update method of my class. datamodules?  Is that how i do that?  or do I write my own class and do it myself?  And then, how do I get the global db connection (from above) to work with it?  As i said, I'd like all db work to be formless if possible. ---------------------------------------------------------------------- Mike.Cornflake · 2014-04-08 23:26 QuoteNow, if I am correct, from what I have read I cant do this in Lazarus. Why not, it's what I do... Code: [Select]Constructor TNexusConnection.Create; Begin   Inherited Create;   ...   FConnection := TODBCConnection.Create(Application.MainForm);   FTransaction := TSQLTransaction.Create(Application.MainForm);   FConnection.Params.Add(';ROWSET SIZE = -1');   FConnection.Transaction := FTransaction;     ... End; Function TNexusConnection.Open: Boolean; Var   oDlg: TdlgNexusConnection; Begin   If FConnection.Connected Then     FConnection.Connected := False;   oDlg := TdlgNexusConnection.Create(Application.MainForm);   Try     oDlg.Repository := FRepository;     oDlg.Database := FDatabaseName;     oDlg.Server := FServer;     oDlg.ShowModal;     If (oDlg.ModalResult = mrOk) Then     Begin       FRepository := oDlg.Repository;       FDatabaseName := oDlg.Database;       FServer := oDlg.Server;       FConnection.Params.Add('DRIVER=SQL Server');       FConnection.Params.Add('Trusted_Connection=Yes');       FConnection.Params.Add('DATABASE=' + FDatabaseName);       FConnection.Params.Add('SERVER=' + FServer);       MainForm.Busy := True;       MainForm.Status := 'Connecting to MS SQL';       Try         Try           FConnection.Connected := True;         Except           On E: Exception Do             ShowMessage(E.Message);         End;       Finally         MainForm.Busy := False;       End;     End;   Finally     oDlg.Free;   End;   Result := FConnection.Connected; End; Function TNexusConnection.CreateQuery: TSQLQuery; Begin   Result := TSQLQuery.Create(MainForm);   Result.ParseSQL := False;   Result.PacketRecords := -1;   Result.Database := FConnection;   Result.ReadOnly := True; End;    Lots of application specific code there, so you can't just compile it, but you'll see the overall theme.  My Main Form creates and holds an instance of TNexusConnection, which is my global connection.  All my other routines request TSQLQuery objects from the TNexusConnection instance. Personally I'm against DataModules.  Used them twice in commercial apps and they were a nightmare to maintain.   Very easy and quick to change settings for datasource/dataset/queries but trying to track a bug down and you know that one of the dozens of controls on that module is the problem...   I'm sure there are ways to correctly manage DataModules to prevent the unnecessary complication, but I couldn't deduce it - so dropped back to what I know - code :-) Quote This time, from design time, I want to do it smarter.  I want to have a class for each table. Then you have an opportunity to play with some smart tools :-)  Look up tiOPF, or other ORM helpers.  Never played with them myself, looks like a steep learning curve and I haven't had the time.  However, from what you say, could be what you're after... UPDATE:  :-)  I've been using the above code for four years now, and never noticed the inconsistent use of Application.MainForm and MainForm until now :-)   MainForm is a simple helper function I wrote... ---------------------------------------------------------------------- Codelizard · 2014-04-09 01:19 So you add the connection object to your main form?  Is the below code part of your main forum or in a seperate unit?  Sorry, Im coming from VB and am still stumbling my way through FPC.  I'll have it soon enough... ---------------------------------------------------------------------- Mike.Cornflake · 2014-04-09 01:39 TNexusConnection descends from TClientConnection (another class of mine).  MainForm holds a FClient:TClientConnection object (declared in Private, Public has a readonly Client property. I should add, my app connects to various database backends (and "Nexus" is one of those), none of which share a common schema - another reason why I don't use DataModules. Don't focus on my Client/Nexus terminology.  Just the overall idea that a connection can be global :-) UPDATE:  I should point out that just declaring an object in your main form doesn't make it global, and even if you work your code so that it is, doesn't mean it's your only option. There's no real global in Pascal.  Declare a variable in the Interface section of a Unit, then wherever you want to use that variable, just add the name of that Unit to your Uses clause of you current unit.  To avoid compiler failures, it's best to only add the Unit name to your Implementation Uses clause, not your Interface Uses clause. Sigh, this is escalating in my mind and I'm at work (shhh!).  Let me know if the above makes sense, if it doesn't I'll post a more code based example tonight... ---------------------------------------------------------------------- Codelizard · 2014-04-09 04:41 I'll bonest, I thought I was a smart dude.  But I'm just not getting the picture straight in my head how you have this put together.  The Client/Nexus stuff doesnt bother me.  I planned on using SQLDb myself, since it should be compatible with firebird on both WIN and OSX. As for those cool classes, yeah, they were cool but yes steep learning curve.  PLUS, I plan on adding a third layer of communication over TCP (so firebird embedded, firebird network, and TCP client/server). This will be invisible to the app, and handled all within my classes for each.  So custom classes it is, for me.  I did mark that project though, as it may come handy in another project down the road. So, back to the deal at hand.  So where exactly does your global connection live?  Is that the piece Im missing?  If I have a global connection that I can reference from anywhere, isnt that the key to making all of this work? ---------------------------------------------------------------------- Mike.Cornflake · 2014-04-09 05:05 Code: [Select]So where exactly does your global connection live? Mine lives in FormMain.pas.  Here's a simplification (typed directly here, not copy/pasted) Up in the top part of the unit Code: [Select]Unit MainForm Interface   ...   Protected     ...     FClient : TClientConnection;     ...   Public     ....     Property Client: TClientConnection Read FClient;     ....   End; Var   frmMainForm : TMainForm; // <--- As this VAR is declared in the interface, it can be considered GLOBAL. Now, lets say I have unit - lets call it HelperUnit. Code: [Select]Unit HelperUnit; Interface    ....   Procedure HelperProcedure;   ... Implementation Uses    MainForm;  // <-- By Using MainForm here, I can access all Variables declared in MainForms interface                       // By using MainForm here, and not in the Interface Uses I avoid cyclic compile errors if MainForm needs to use HelperUnit.   Forget that for now, not critical for current discussion. Procedure HelperProcedure; Var   oQuery: TSQLQuery; begin   oQuery := frmMainForm.Client.CreateQuery;   Try     ... // do stuff   Finally     oQuery.Free   End; end; Actual form class type, variable name made up on the spot :-) Now, you can use HelperProcedure in any other Unit just by added HelperUnit to that Unit's Uses clause.  In this way HelperProcedure just became a "Global" procedure.  Note:  It's not a true global, you can control it's scope by including or not including HelperUnit in other units Uses clause. Sorry if I've over-complicated this discussion :-) ---------------------------------------------------------------------- mangakissa · 2014-04-09 08:30 The MAIN reason to use datamodule is to separate your data communication from your form. Example 1: You put a connection, some datacomponents, a dbgrid and some DBEdits on your form. Before you save your record to the database, you want to make some calculations or changes. All these things are on your form. Always easy to get it together. Now you have a whole new form or you want to develop with another framework, all the code must be copied. You're not transparent. If all your calculations and datachanges were on a datamodule, you only have to create a new form, connect the datamodule to your form and you're done. Example 2: Create a datamodule for your connection to a database. Each datamodule has these variable declared. Code: [Select]var  Datamodule1: TDatamodule1;    This means that your datamodule can be reached globally if you put the unit (datamodule) in your uses clause Code: [Select]uses   Classes, SysUtils, Forms, FileUtil, dialogs, datamodule1;   In your form you can access your connection as datamodule.connection.open. Example 3: Create your datamodule as example 2. Create a new datamodule for your tables/queries. Put the datamodule of your connection in the uses of your new datamodule. This datamodule also has a variable declared can be reached globally if you put the datamodule to the uses of your form. The form doesn't have to know where the connection is made. The datacomponments knows it and you can access your data in your form. There's a fourth way to create your form as follows: form <--> unit <--> datamodule. In this way the only thing the form wants to know is the datalink from your data to GUI-DBComponents. In the early das of my development (Delphi 1), I also put all my components on a form and sometimes lost the survey. tiOPF or other ORM helpers has another approach to your database, but also separate your data from your form. I've created an example for another person, who also put all the non- DBComponenten on his form. Sometimes he was lost the way to communicate with the database. The main problem was putting several forms in the uses clause to get his data. I don't say you have to do it as the most developers do. It's just a way of thinking of using datamodules. Have you read this? I'll put a link of a demo from me on wetransfer. It's has the source, database and some dll of Embedded firebird. ---------------------------------------------------------------------- hinst · 2014-04-09 09:25 If you don't need visual stuff, then just forget about forms & visual stuff; just write the code, don't create forms, don't use designer. You can do everything in code. If your question is "Do I absolutely have to use visual components in Lazarus", then the answer is No. In FPC u have units. Forms and datamodules are units too. I personally do not use datamodules because I don't see any reason to do so; ---------------------------------------------------------------------- mangakissa · 2014-04-09 09:52 OOP is about sharing your code on a convenient way. Off course you can create all your components in runtime. Even create a unit for communications severally. It's only about using the unit to get your data. ---------------------------------------------------------------------- taazz · 2014-04-09 16:29 Quote from: mangakissa on April 09, 2014, 11:52:24 amOOP is about sharing your code on a convenient way. OOP is for minimizing code rewrites and maximizing code reuse. Code sharing was never a goal for OOP. ---------------------------------------------------------------------- Codelizard · 2014-04-09 23:04 Yes,  OOP is for encapsulation of business logic into a logical entity, to make it easier to use and to share among programs and projects. That being said, I'm still struggling just a little but with the DB business.  I think I have it narrowed down to just one question: How do I create a variable that has (or emulates) global scope throughout the program?  This is the part I'm not understanding, I think. Thanks so much all of you who have stuck with me on this.  I've learned so much already ---------------------------------------------------------------------- taazz · 2014-04-09 23:15 Quote from: Codelizard on April 10, 2014, 01:04:48 amI think I have it narrowed down to just one question: How do I create a variable that has (or emulates) global scope throughout the program?  This is the part I'm not understanding, I think. A variable is considered global if it is declared at the interface part of a unit, using this unit in any other unit will give you access to the variable's data so by placing it in a unit globals and using this unit everywhere you solve the problem. Of course the use of global variables is discourage what is encouraged is the use of singletons aka objects that can be created once and only once in the life time of an application and usually they are destroyed when the application closes. Searching this forums for singleton should give you a couple of interesting posts. ---------------------------------------------------------------------- Codelizard · 2014-04-09 23:55 Thanks, I'll look up singletons. Just so we're clear, I'm looking to have 1 DB connection for the app.  When new forms open, and new units are loaded, I want to use the same DB connection for them.  I also have several settings that will be used globally.  These are in the DB, but why read them every single time i want to use them? ---------------------------------------------------------------------- Codelizard · 2014-04-10 00:22 Ok, everything I just read about Singletons here: http://wiki.freepascal.org/Singleton_Pattern Says NOT to use Singletons, since they cannot be platform independent. ---------------------------------------------------------------------- taazz · 2014-04-10 00:43 Quote from: Codelizard on April 10, 2014, 01:55:57 amThanks, I'll look up singletons. Just so we're clear, I'm looking to have 1 DB connection for the app.  That is considered a good case for a singleton use, so go ahead and try it ee the Application in forms could be implemented as a singleton too. Quote from: Codelizard on April 10, 2014, 02:22:48 amOk, everything I just read about Singletons here: http://wiki.freepascal.org/Singleton_Pattern Says NOT to use Singletons, since they cannot be platform independent. That is not accurate if not down right false, I do not see why a singleton should be tight to a platform in any case here is a small but very good forum thread for singletons http://forum.lazarus.freepascal.org/index.php/topic,19761.0.html. Try looking at the code for the implementation, it is cross platform and one of the best implementations for freepascal/lazarus. In your case I would take a good look on circular's implementation created once at start up used in all the application and destroyed at exit seems a good pattern for TConnection. Now that I feel that my position is defended for some stupid reason I feel better let me tell you this at this point, forget singleton for db connection just add a datamodule, drop the dbconnection in, set it up and use it from every where in your application. This is as global as one can get. ---------------------------------------------------------------------- Mike.Cornflake · 2014-04-10 01:42 @CodeLizard And there you have it :-)   Half of us are recommending DataModules, and half are recommending a more code based solution.  We're all making our calls based on our experience and training.  We got our experience by making mistakes, both in planning and in implementation.  I think you're trying to get the perfect solution before you even start coding.  Aint going to happen, no matter what language. :-) I've changed my advice.   I used to recommend a code based solution.  Now I'm recommending *anything*.   It's time for you to start :-)  Sure you'll end up having to refactor, but that will add to your Pascal experience, no bad thing.   You get stuck on specific code, then post away :-) ---------------------------------------------------------------------- Codelizard · 2014-04-10 01:58 @Mike Yeah, all kinds of ideas flying in across the board.  I myself come from a background of the code solution, so this is the way I am inclined to do it, at least to start.  My issue, as I stated earlier, is in your example I don't see how to make the connection global, or act like its global.  Thats the piece i dont understand. If I create code at app startup that loads a unit, then everywhere else i use that unit, does it share the same variables from the interface section?  Is that the key? Thats what I am grasping at in the code solution...  Once I get that, I can start coding without having to learn simpletons and datamodules right now. ---------------------------------------------------------------------- Mike.Cornflake · 2014-04-10 02:08 QuoteIf I create code at app startup that loads a unit, then everywhere else i use that unit, does it share the same variables from the interface section? Yes :-)  But you'd have confirmed that by writing perhaps 10 lines of code and testing.  All the information you need has been presented here.  Time to start experimenting :-) ---------------------------------------------------------------------- taazz · 2014-04-10 02:09 there is no such thing as "not recommended to use" component technique or pattern only "not recommented to be used like that or for that purpose" datamodules where introduced for a purpose to be able to write db and domain/bussines specific code in a single place and not have it all over the place. For this they are doing their job well. Look at them as the frames of the data applications. a single datamodule can be eg customers can have properties and methods eg dmCustomers.beginTransaction; dmCustomers.Comit; dmCustomers.Update; etc. and usually can be set to be a black box to the outside world too. So use them it is for your own good that they are there. If you do not plan to use dbaware control in the visual/form designer then I guess it has no big difference between datamodule and hand coded units but if you do plan to use dbaware controls then datamodules there is no second choice to be even ranked. Quote from: Codelizard on April 10, 2014, 03:58:33 am@Mike Yeah, all kinds of ideas flying in across the board.  I myself come from a background of the code solution, so this is the way I am inclined to do it, at least to start.  My issue, as I stated earlier, is in your example I don't see how to make the connection global, or act like its global.  Thats the piece i dont understand. If I create code at app startup that loads a unit, then everywhere else i use that unit, does it share the same variables from the interface section?  Is that the key? correct once a variable is declared in a units interface section all other units that access it see and change the same data. Quote from: Codelizard on April 10, 2014, 03:58:33 am Thats what I am grasping at in the code solution...  Once I get that, I can start coding without having to learn simpletons and datamodules right now. well there you have it then. ---------------------------------------------------------------------- Codelizard · 2014-04-10 02:19 Thanks guys for everything... You're right, if I started playing, I probably would have figured some of this out by now.   I've been spending the last few days finishing up a few other projects and getting them off my plate so I have time now to jump in.  I was just taking some of the time I have trying to get a better perspective so I can hit the ground running. As far as the datamodules, I do not discount their value at all.  But for this particular project and the different methods I'm going to be approaching for the database i/o, I'm going to go with custom DB classes.  Besides, what better way to get a handle on the language As I said, between what I've read and what you guys have offered up, I've learned alot.  Can't wait to use it! Thanks ---------------------------------------------------------------------- Codelizard · 2014-04-12 09:14 So, I thought I'd follow up and let you know that I've got this working in my testing. On my main form, I've got a bunch of SQLDB controls (i couldnt make this work without having these controls present).  I then add a couple of properties to the form.  These properties represent the TSQLConnection and TSQLQuery objects. I then have a unit called dbMain.  DBMain has procedures for connectDB, closeDB, etc.  It uses mainForm (the reference to my main form) in the implementation, and then actually opens the connection and assisgns it to the mainform property. I have also created a class for one of my tables, which i create right after the DB is opened.  The class reference also belongs to the mainForm. So now, i have the connection, the sqlquery, and my class all open and connected, and belonging to the mainform.  In my tests, I then just include mainform in the implementation and use anything I need to.  ALL of the data I/O happens inside the classes.  So if I need to add a new record for time, I just: frmMain.dbTime.clear frmMain.dbTime.DateIn := xxxxxx; frmMain.dbTime.TimeIn := xxxxxx; frmMain.dbTime.Status := xxxxxx; frmMain.dbTime.add; And the class handles all of the additional logic that I would normally have to do when creating such a record.  I'll admit, figuring out the proper way to get the transactions working was a little of a pain.  So much documentation, but so much of it is very focused.  Tells me everything about the Transaction object, but not the right sequence to use it when referencing the DB and the query, and when to commit.  When you're doing this code by hand and not through the controls, the documentation comes up a little short.  I had to hunt and peck, trying different things, until it worked. So, I'm sure there may be some flaws in what I'm doing, but I'm sure I'll work them out in time.  I'm only a small step along the way getting things done, but very excited at the pieces I've already begin to create. Thanks for the input and help from everyone who took the time out to read and comment. ---------------------------------------------------------------------- mangakissa · 2014-04-12 12:38 QuoteOn my main form, I've got a bunch of SQLDBDo you also have other components on your mainform? QuoteAnd the class handles all of the additional logic that I would normally have to do when creating such a record. And that's logical?. You can also put all your connections and queries in this class, so all your handles are on the same class.  QuoteI'll admit, figuring out the proper way to get the transactions working was a little of a pain.  So much documentation, but so much of it is very focused.  Tells me everything about the Transaction object, but not the right sequence to use it when referencing the DB and the query, and when to commit.  When you're doing this code by hand and not through the controls, the documentation comes up a little short.You read to mutch. Just try the SQLdb tutorials practically. It isn't so hard to do it programmatically. Code: [Select]procedure TSQLdbClass.InsertRecord(aConn : TSQLConnection; const aSQLtext : string); var MyQuery : TSQLQuery;       TransAction : TSQLTransAction; begin    MyQuery := TSQLQuery.create(nil);    TransAction := TSQLTransAction.create(nil);    try      try         Transaction.database := aConn;         MyQuery.database := aConn;         MyQuery.transaction := Transaction;         MyQuery.SQL.Text := aSQLText;         Transaction.StartTransaction;         MyQuery.ExecSQL;         MyQuery.applyupdates;         Transaction.commit;      except         Transaction.rollback;      end;   finally     MyQuery.free;     Transaction.free;   end; end; In Dutch we say: "Je maakt van een mug een olifant".