Updated March_6 Source Code Project for database dev., using Lazarus with SQLdb lazarus / 16-Databases ---------------------------------------------------------------------- xenblaise · 2010-10-17 03:45 I'd like to contribute this code to all newbie of Lazarus Database development. Sample Source code project(.lpr) includes; (source code at the bottom of the page) SQLITE (updated2_with_add_delete_update) http://wiki.lazarus.freepascal.org/User:Rocarobin MSACCESS(updated2_with_add_delete_update) http://wiki.lazarus.freepascal.org/MS_Access MYSQL with (update delete add) using TMySQLConnection http://www.mediafire.com/file/o521t68uc1k6559/mysqltest1.zip This source code project files are working on the fly, you can put to any folder or directory you wanted too. Oh, sorry about the mysqltest, it can't fly, just create a database and table to test it, or just see the code on how does it flow. Working on it....(please comment or REQUEST,REQUEST,REQUEST, reply) SQLdb using Interbase/Firebird SQLdb using MSSQL Thanks ---------------------------------------------------------------------- xenblaise · 2010-10-17 07:24 http://wiki.lazarus.freepascal.org/User:Rocarobin You can find the source code attached at the bottom. Happy coding Lazarus Oh, with that small database application, I've earned 100box ---------------------------------------------------------------------- xenblaise · 2010-10-17 12:41 Tell everyone about Lazarus, like they did in Java(which is far). ---------------------------------------------------------------------- xenblaise · 2010-10-17 12:50 if you think the way I code is different and lack of professionalism or my code-way is bad, please do comment, I'm glad to hear it. And if you think it helped you guiding your way building a good database system, please let me know, so I can further continue to distribute another work for Lazarus. Thank you, Just a Lazarus newbie also. ---------------------------------------------------------------------- xenblaise · 2010-10-17 23:43 Please comment ---------------------------------------------------------------------- Vincent Snijders · 2010-10-18 05:07 Thansk for the information. ---------------------------------------------------------------------- BlueIcaro · 2010-10-18 07:09 Why don't you add this information to the wiki? /BlueICaro ---------------------------------------------------------------------- xenblaise · 2010-10-18 08:04 hi QuoteWhy don't you add this information to the wiki?About what? FILEDSN? or this one? http://wiki.lazarus.freepascal.org/User:Rocarobin Thank you ---------------------------------------------------------------------- BlueIcaro · 2010-10-18 08:15 Quote from: xenablaise on October 18, 2010, 10:04:16 amhi QuoteWhy don't you add this information to the wiki?About what? FILEDSN? or this one? http://wiki.lazarus.freepascal.org/User:Rocarobin OH! nice, I'll bookmark it, on my favorites /BLueIcaro Thank you ---------------------------------------------------------------------- xenblaise · 2010-10-18 08:29 A source code at the bottom of the page. Using SQLdbS with SQLITEConnection I'll make other SQL connection upon request. Maybe an hour I'll upload using TODBCConnection using FILEDSN which is safe on fly. And I don't know how to create a lazarus wiki, can you give me a hint. Do you mean about filedsn and the source code? I can include the ms_access code. Thanks. ---------------------------------------------------------------------- xenblaise · 2010-10-18 14:33 Accessing Ms_Access: http://wiki.lazarus.freepascal.org/MS_Access Working Source code sample project:(at the bottom of the page) Enjoy Lazarus ---------------------------------------------------------------------- xenblaise · 2010-10-19 12:18 Video tururials from Quotetkramer3 using Lazarus IDE with LazReport. http://www.youtube.com/watch?v=pq2oCiJePHo http://www.youtube.com/watch?v=ewNjqFXqDDc http://www.youtube.com/watch?v=urEdbyV_AbE Nice videos. GO and tell the others about Lazarus. tkramer3? Thanks for sharing. ---------------------------------------------------------------------- xenblaise · 2010-10-19 17:08 Anyone else wants to contribute? or anyone requesting another sample? or someone wants another database connection? project source code are update oct 21 2010 msaccess with add delete update print sqlite with add_delete_update_print_MASTER_and_Detail_DBGrids scroll at the top tell everyone about Lazarus ---------------------------------------------------------------------- xenblaise · 2010-10-20 23:57 Quoteif you dont want to use datamodule or the component try something like that; why use datamodules while you can directly call them just like in vb6 sdb := TSQLQuery.Create(nil); cndb := TODBCConnection.Create(nil); stran := TSQLTransaction.Create(nil); stran.DataBase := cndb; stran.Action := caCommit; stran.Active := True; cndb.Transaction := stran; cndb.Open; rsdb.DataBase := cndb; rsdb.SQL.Text := 'SELECT * FROM Table1'; rsdb.Open; Quotemy another sample   TDBClass = class   private     // a data set to mantain all templates of database     dsQuery: TSQLQuery;     // the connection object     mycon: TMYSQL40Connection;     // SQL Transaction     SQLTransact: TSQLTransaction;   public     function openDB(): boolean;     procedure closeDB();     procedure clearDB();   end; implementation ... ... ... // TdbClass Usage // Open connection function TDBClass.openDB(): boolean; begin   try         dsQuery := TSQLQuery.Create(nil);         mycon := TMySQL40Connection.Create(nil);         SQLTransact := TSQLTransaction.Create(nil);         mycon.UserName := 'root';         mycon.HostName := 'localhost';         mycon.port := 3306;         mycon.DatabaseName := 'XXX';         SQLTransact.DataBase := mycon;         SQLTransact.Action := caCommit;         SQLTransact.Active := True;         dsQuery.DataBase := mycon;         mycon.Transaction := SQLTransact;         mycon.Open;        openDB := true;   except         openDB := false;   end; end; ---------------------------------------------------------------------- thierrybo · 2010-10-25 18:16 I downloaded msaccesstest2.zip from the wiki and get : Transaction already active, press OK to ignore and risk data corruption. Then I get the main form with no data. Compiled with Lazarus 0.9.29 r27163 FPC 2.4.3 i386-win32-win32/win64 (Windows XP SP3) I get the same error with Windows 7. Thierry B. ---------------------------------------------------------------------- xenblaise · 2010-10-25 23:56 I am using lazarus-0.9.28.2-fpc-2.2.4-win32 (the stable version) And there is no problem runnin in windows xp or 7 (on my computer) However you have to understand that your using the unstable version, BUT the way I look at it?, Lazarus 0.9.29 has the correct procedure accessing ms_access. Because in lazarus-0.9.28.2 the handling of tsqlquery.open and tsqlquery.close is not stable, as you noticed I've put the QuoteThere's a bug here(commented). procedure TForm1.Button3Click(Sender: TObject); begin if SQLQuery1.RecordCount>0 then   begin   SQLQuery1.Delete;   SQLQuery1.ApplyUpdates;   SQLQuery1.Close; //ms_access bug, not needed with other connections   SQLQuery1.Open; //ms_access bug, not needed with other connections   Form1.caption := 'DELETED';   end; end; And ONLY ms_access uses the SQL extension like deleteSQL, updateSQL, insertSQL The other problem that you have, MAYBE you have set in the (object palette) TSQLtransaction properties to Active=true, WHILE in the code when it loads, it set the tsqltransaction.active to true. Try to reset your components properties, dont change anything, because it was already set when the form loads. ---------------------------------------------------------------------- thierrybo · 2010-10-26 09:23 For the moment I just opened the project and ran it without any change, so SQLTransaction is false at design time. I use Lazarus SVN version because some months ago I tried some tests with ODBC against SQL Server 2008 and ran with bugs with the current version (0.9.28.2) that were almost all solved in the development version. But I never tried to use it with access. I will try to play with this sample a little bit more. ---------------------------------------------------------------------- xenblaise · 2010-10-26 12:31 oh, the sample is very basic, it only views the basic sql transactions like add, delete, update using the component, but it can be done using the variable declaration. SQLTransaction1.Active := True; //not applied to ms access(false) QuoteSo, it must be false SQLTransaction1.StartTransaction; QuoteBUT we need to initialized SQLTransaction to the Connection in order to communicate to the whole database design. SQLTransaction was never used in Access Connection, as I noticed during a lot of testings. Anyway I build very basic sample for some newbies.  Just like me asking for samples of lazarus database during my first day, and NOW Im always online to assist whatever I can do to help newbies and Lazarus. We need a team work here, sharing information, codes and other tools to lift-up lazarus in this coming year. And the way I look at it, next year will be one of the top programming software tools. ---------------------------------------------------------------------- dkjMusic · 2011-01-14 17:05 Quote from: xenablaise on October 17, 2010, 05:45:57 amI'd like to contribute this code to all newbie of Lazarus Database development. ... http://wiki.lazarus.freepascal.org/MS_Access ... Are you still around to help with this? I am trying to get it running on Ubuntu 10.10 with Lazarus 0.9.28.2 and fpc 2.4.1. ---------------------------------------------------------------------- xenblaise · 2011-01-21 17:30 hi I don't know if ms_access is accessible in Linux, but ubuntu is more near to Bsd, and probably they're the same can't see ms_access. ---------------------------------------------------------------------- dkjMusic · 2011-01-22 01:37 Quote from: xenablaise on January 21, 2011, 06:30:58 pmhi I don't know if ms_access is accessible in Linux, but ubuntu is more near to Bsd, and probably they're the same can't see ms_access. MS Access must be accessible in Linux because MDB Tools and Kexi work. I got msaccesstest2 to run but I get an error when I Open the query which says "Duplicate fieldname: ''. Soooo close... Any ideas or just give up? ---------------------------------------------------------------------- xenblaise · 2011-01-22 20:18 QuoteDuplicate fieldname means,you,have,a,screwed,your,table. ---------------------------------------------------------------------- dkjMusic · 2011-01-26 00:50 Quote from: xenablaise on January 22, 2011, 09:18:07 pmQuoteDuplicate fieldname means,you,have,a,screwed,your,table. maybe,a,not. I put a Query.Close just before the Query.Open and it worked like a charm (MS Access bug). Of course I had to also change numerous other items to get the thing to work (no DNS file, etc.). ---------------------------------------------------------------------- xenblaise · 2011-01-26 14:28 Yes, that's the bug, you don't have to put that in some data_model, except for ms_access,  but when I test back the code?, I removed the .close and .open , it does work smoothly.  Something messy about using SQLdB ms_access. In your case, that's really a bug, that's only for ms_access. Try some other data_model like sqlite or firebird. ---------------------------------------------------------------------- ramon · 2011-03-04 18:24 Quoteanyone requesting another sample? [October 19, 2010] Could you share a sample of SQLQuery with Firebird? Thanks. ---------------------------------------------------------------------- xenblaise · 2011-03-06 09:18 Ok, We have SQLdB samples with; Sqlite Ms_Access MySQL and Firebird as reguested But Actually if you compare the codes from sqlite,msaccess,mysql?, they all look the same, the only difference is the sqldB connection. Must see it. ---------------------------------------------------------------------- JimBeam · 2011-03-10 13:05 Quote from: xenablaise on March 06, 2011, 10:18:37 amOk, We have SQLdB samples with; Sqlite Ms_Access MySQL and Firebird as reguested But Actually if you compare the codes from sqlite,msaccess,mysql?, they all look the same, the only difference is the sqldB connection. Must see it. XenaBlaise, (Haven't looked at the code) If the samples only differ a bit, couldn't you improve the samples by merging into one sample with the user's (either at compile or runtime) of database? That would neatly show cross-database programming, too! ---------------------------------------------------------------------- xenblaise · 2011-03-11 14:58 hi jimyX Yes the code's are nearly same, please look at the codes. Frankly, I tried it with FireBird, but seems I can't create a database, I've used flameRobin, but didn't work out.  I tried many, including, username with "SYSDBA" and password "masterkey". I don't know where the problem is, no possible reasons erupt to my mine, maybe you can help. Try to merge them including firebird.  Lazarus newbie users will be glad. The sample links are shown in the beginning of the post. ---------------------------------------------------------------------- JimBeam · 2011-03-13 18:11 Ok, I'll have a look... ---------------------------------------------------------------------- HowardH · 2011-07-28 02:16 Quote from: xenblaise on October 17, 2010, 05:45:57 amI'd like to contribute this code to all newbie of Lazarus Database development. Sample Source code project(.lpr) includes; (source code at the bottom of the page) SQLITE (updated2_with_add_delete_update) http://wiki.lazarus.freepascal.org/User:Rocarobin Thank you for the example. I do have some questions about SQLite with Lazarus if you are still around. I am doing a migration of a small (7500 records in main table) PostgreSQL database to SQLite (so I can have a zero-config program) and I am having some performance issues. If you are around, please contact me or reply to this post. ---------------------------------------------------------------------- xenblaise · 2011-08-02 13:39 If your on Linux, or maybe(windows), just a hint, closed your used table ONLY application is shutdown, reused it if possible. thank you ---------------------------------------------------------------------- BigChimp · 2011-08-02 15:58 You could use Microsoft Access, but you could also use Firebird embedded. This will let you migrate to Firebird client/server by changing a couple of lines of code. Please see sample code attached. Xenblaise, could you modify your first post to include this sample? Thanks. Due to space constraints, I couldn't upload the required dlls/exes. For now, and hopefully in future, you can download everything (source code, dlls) from http://dl.dropbox.com/u/20995033/FirebirdSampleFull.zip Thanks. Contents of readme: This is a Firebird embedded database sample. You will need to download the Firebird embedded engine (dlls) - see below. It is adapted from the Microsoft Access by Xenblaise and SQLite sample by RocaRobin. It demonstrates how to create an embedded database if it doesn't exist, how to connect, bind grids to the data and edit/update/delete data. For now, this sample works only on Windows. Advice on how to run it on Linux is welcome. 1. Getting the required files ================= Due to space constraints, the Firebird dll files are not included in this sample. 1.1 Getting the Firebird embedded dlls ======================== Download Firebird Embedded 2.5 from http://www.firebirdsql.org/en/firebird-2-5/ (You can use a newer version if available) Extract these files to your application directory: fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll Because we will need these dlls both while in design mode (in the IDE) and while running your application, make sure that you compile your application into the source directory, so no fancy playing around with crosscompiling to various subdirectories... ---------------------------------------------------------------------- xenblaise · 2011-08-07 09:29 Quotecould you modify your first post to include this sample? not now,  and Great, you have created a sample code for using firebird,  that would be helpful for some newbies happening to explore lazarus thanks ---------------------------------------------------------------------- xenblaise · 2011-10-14 15:12 We're glad you made it thanks ---------------------------------------------------------------------- almsoares · 2012-01-03 22:44 Hi I am newbie in Lazarus, although I had a good experince in database development with Delphi/VB/C#. So I am doing a study about options that I realy have in Lazarus. So I am comparing SQLdb and ZeosLib. With ZeosLib I am had some troubles, but works. With SQLdb I simply can't get a connect with MSSQL.  Can you help me? How I need to configure the property named ConnectorType to get a connection wiht MSSQLServer (2000/20005/2008). best regards Adriano Soares ---------------------------------------------------------------------- goldenfox · 2012-03-20 03:46 Very nice example. Thanks.  Quote from: BigChimp on August 02, 2011, 05:58:36 pmYou could use Microsoft Access, but you could also use Firebird embedded. This will let you migrate to Firebird client/server by changing a couple of lines of code. Please see sample code attached. Xenblaise, could you modify your first post to include this sample? Thanks. Due to space constraints, I couldn't upload the required dlls/exes. For now, and hopefully in future, you can download everything (source code, dlls) from http://dl.dropbox.com/u/20995033/FirebirdSampleFull.zip Thanks. Contents of readme: This is a Firebird embedded database sample. You will need to download the Firebird embedded engine (dlls) - see below. It is adapted from the Microsoft Access by Xenblaise and SQLite sample by RocaRobin. It demonstrates how to create an embedded database if it doesn't exist, how to connect, bind grids to the data and edit/update/delete data. For now, this sample works only on Windows. Advice on how to run it on Linux is welcome. 1. Getting the required files ================= Due to space constraints, the Firebird dll files are not included in this sample. 1.1 Getting the Firebird embedded dlls ======================== Download Firebird Embedded 2.5 from http://www.firebirdsql.org/en/firebird-2-5/ (You can use a newer version if available) Extract these files to your application directory: fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll Because we will need these dlls both while in design mode (in the IDE) and while running your application, make sure that you compile your application into the source directory, so no fancy playing around with crosscompiling to various subdirectories...