Recent

Author Topic: Best Practice for an ERP/Accounting Database Application  (Read 7168 times)

vilgilsoft

  • Newbie
  • Posts: 4
Best Practice for an ERP/Accounting Database Application
« on: February 25, 2014, 06:28:51 pm »
Hi
I am newcomer to lazarus / FPC.
I have been working with .Net
I use Postgres as my backend database.

I would like to know what would be the best practise in implementing a accounting / erp type application in lazarus.
In .Net usually i write a Single Class for Database Managment. That Class manages connection, getting query results, executing insert queries. I Create a single global DB object for that class and use it everywhere.

Should I Create a similar single class in lazarus. If so how to create a globally accessible object instance of that class ? I have no clue of this.
Or For each form i should use the tConnection object and connect to the database each time a form is opened ?

If any one can provide a basic framework / skeleton code for the best practise it would be helpful to me.

Thanks
 

zeljko

  • Hero Member
  • *****
  • Posts: 1090
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Best Practice for an ERP/Accounting Database Application
« Reply #1 on: February 25, 2014, 07:47:14 pm »
I'm using lazarus-qt + postgresql + zeoslib (linux,win32,macosx) for our erp application. Yes, with zeos you can create single class for database management (if you think about sharing one db connection per app instance).

vilgilsoft

  • Newbie
  • Posts: 4
Re: Best Practice for an ERP/Accounting Database Application
« Reply #2 on: February 26, 2014, 08:49:59 am »
Hi
Thanks for your reply.
I have not tried Zeoslib. I will try it out.
So from your reply i understand that using ZEOS is a good method than the standard TConnection method.

Any other suggestions / recommendations.



exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: Best Practice for an ERP/Accounting Database Application
« Reply #3 on: February 26, 2014, 08:56:31 am »
I use SqlDb and Firebird or Postgresql. Also there is DataModule class, so useful...

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Best Practice for an ERP/Accounting Database Application
« Reply #4 on: February 26, 2014, 09:43:35 am »
Edit: clarification
Yes, you can just as well use SQLDB (the db connection layer supplied with FPC/Lazarus) in a separate class like zeljko indicated for Zeos.

You may be interested in object-relational frameworks like tiOPF etc; Please see the Lazarus wiki pages for details and links to the sites.
« Last Edit: February 26, 2014, 03:00:04 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Graeme

  • Hero Member
  • *****
  • Posts: 1430
    • Graeme on the web
Re: Best Practice for an ERP/Accounting Database Application
« Reply #5 on: February 26, 2014, 02:35:06 pm »
You may be interested in object-relational frameworks like tiOPF etc; Please see the Lazarus wiki pages for details and links to the sites.
+1

tiOPF already supports a lot of data access components (SqlDB, IBX, DOA, Zeos, FBLib etc) allowing you to access various database servers (Oracle, MS SQL Server, MySQL, Firebird, PostgreSQL etc). tiOPF completely hides that complexity from your application code, so to switch data access components or talking to a database server is as simple as recompile. tiOPF obviously does a lot of other useful things (clear separation of UI/BO/DB layers, Model-GUI-Mediator for seemless sync between business objects and UI components). tiOPF is open source and has been used in commercial environments since 1998 - thus very stable.

http://www.tiopf.com
http://wiki.freepascal.org/tiOPF

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Best Practice for an ERP/Accounting Database Application
« Reply #6 on: February 26, 2014, 03:01:45 pm »
DOA? Sounds like a scary data access class: http://en.wikipedia.org/wiki/Doa
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

vilgilsoft

  • Newbie
  • Posts: 4
Re: Best Practice for an ERP/Accounting Database Application
« Reply #7 on: February 26, 2014, 06:23:06 pm »
Hi

Should i create a singleton kind of unit and connect it to database when the application starts and use the same connection for all queries (if so any model code is available)

or

Should i connect to the database each time a form is opened or a query is executed.

Which is best method

Thanks


motaz

  • Hero Member
  • *****
  • Posts: 508
    • http://code.sd
Re: Best Practice for an ERP/Accounting Database Application
« Reply #8 on: February 26, 2014, 06:52:13 pm »
In my ERP system, at first I used  single DataModule to put all database access components, such as IBConnection, SQLQuery, and all the methods, after a while this unit becomes very fat and hard to maintain.
After that I have started to split every module/sub-module in a separate class that concentrate of specific functionality, for example separate class for Accounts, which contains inserting new account, reading accounts, modify, search by account code, and a class for cheques manipulation, and a class for clients, etc

Also I have a shared class called TDataAccess which contains shared functionalities, such as database connection, creating SQLQuery at run-time, store last error message, etc. Other classes such as TAccount, TCheque, inherits this class.

Now the system becomes easy to modify and has a reusable classes that can be used in other systems, such as Security class, that contains user login, password change, permissions, etc.

I pass connection object and transaction to each data access class while initialization/creation, so that I can use one connection for all data access classes, or I can use multiple connections, it depends on calling situation.

Graeme

  • Hero Member
  • *****
  • Posts: 1430
    • Graeme on the web
Re: Best Practice for an ERP/Accounting Database Application
« Reply #9 on: February 27, 2014, 02:46:59 pm »
DOA? Sounds like a scary data access class: http://en.wikipedia.org/wiki/Doa
:D  Direct Oracle Access components
--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

Graeme

  • Hero Member
  • *****
  • Posts: 1430
    • Graeme on the web
Re: Best Practice for an ERP/Accounting Database Application
« Reply #10 on: February 27, 2014, 02:48:27 pm »
Should i create a singleton kind of unit and connect it to database when the application starts and use the same connection for all queries (if so any model code is available)
Reusing a single connection will probably be a lot more efficient. tiOPF does all this for you already. ;-)
--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/