Recent

Author Topic: Lazarus SQL database video tutorials  (Read 27674 times)

tkramer3

  • New Member
  • *
  • Posts: 16
Lazarus SQL database video tutorials
« on: August 14, 2010, 04:19:44 pm »
Hello everyone,

I have recorded a few simple video tutorials on connecting Lazarus to SQL databases. I uploaded them to Youtube.

The first one shows connecting to an SQL database with the components on the SQLDB tab.
The second one shows how you can use the results of a select query in a program and how you can program inserts, deletes and updates. The third one shows the usage of a datamodule and using Lazreport objects to make a simple report.

You can find the tutorials by searching in Youtube for "Lazarus SQL database", and here are the links to the videos:

http://www.youtube.com/watch?v=pq2oCiJePHo
http://www.youtube.com/watch?v=ewNjqFXqDDc
http://www.youtube.com/watch?v=urEdbyV_AbE

The resolution is 1380 x 768

have fun..

eny

  • Hero Member
  • *****
  • Posts: 1617
Re: Lazarus SQL database video tutorials
« Reply #1 on: August 14, 2010, 05:52:29 pm »
I love the fact that the grandfather clock was moved ahead by 3 minutes between video 2 and 3  :D
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

SteveF

  • Jr. Member
  • **
  • Posts: 92
Re: Lazarus SQL database video tutorials
« Reply #2 on: August 30, 2010, 11:36:54 pm »
Hello everyone,

The first one shows connecting to an SQL database with the components on the SQLDB tab.
The second one shows how you can use the results of a select query in a program and how you can program inserts, deletes and updates. The third one shows the usage of a datamodule and using Lazreport objects to make a simple report.


Would you please be so kind as to make your code available for download as well?  It would be of great help so see examples.

Thanks,
Steve

univeda

  • New member
  • *
  • Posts: 5
Re: Lazarus SQL database video tutorials
« Reply #3 on: August 31, 2010, 10:27:14 pm »
Thanks, nice tutorials.

Michael

sieward

  • Newbie
  • Posts: 2
Re: Lazarus SQL database video tutorials
« Reply #4 on: September 04, 2010, 12:06:28 pm »
Hi this are nice video's, to get started in Lazarus. I'm delphi user, but still I got problems:

I use TODBCconnection (TSQLDBlaz 1.0.1) linked by setting de ODBC DNS into the field databasename. This allows me to get a connection.
then I combined linked the database with TTransaction (caNone) and TQuery,

I put into the field SLQ a very easy query to get started. But befor I enter the Query editor I get the message "The metadata is not availible for this type of database" and being inside the editor I get the message "Could not retrieve primary key metadata for table..."

Operating system, windows 7, tried differnt versions of MS Accessdatabases, lazarus 0.9.28.2 beta.

Should I use other lazerus or ODBC versions? Who can help me?

tkramer3

  • New Member
  • *
  • Posts: 16
Re: Lazarus SQL database video tutorials
« Reply #5 on: September 14, 2010, 07:10:52 pm »
Hello Sieward, sorry for my late reaction

I think MSACCESS support is somewhat better in Delphi, so I think, if you have to use ACCESS, you better stick to Delphi.

This tutorial is intended for SQL databases, MSACCESS is merely a local database. Sometimes it is stretched to about 5 users, but above that you already get in trouble. This is caused by the simple locking mechanism, which is not intended for multiuser projects.

SQL databases are a much better match for Lazarus, because with them you can extend your project to huge numbers of users if that suddenly appears to be necessary. But you can still use them as a local database. The footprint is never a problem for modern machines.

And SQL databases can add some extra functions to your projects by using things like generators, triggers and database procedures. With Lazarus you can combine the power of an SQL database with the high speed and versatility of Free Pascal and make the result look good with the options the IDE provides to create a good looking GUI.

asdf

  • Sr. Member
  • ****
  • Posts: 310
Re: Lazarus SQL database video tutorials
« Reply #6 on: October 19, 2010, 12:39:08 pm »
Dear MRTKRAMER3

I'm one of your fans of those video tutorials.
I'm new and just migrated from VB6+MS Access.

Before that I used:

sql1 = "select Eid,Ename,ppfSub,Basic From EmpData Order By Eid"
Dim cndb As New ADODB.Connection
Dim rsdb As New ADODB.Recordset
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=EMP.mdb;Persist Security Info=False"
cn.Open
rs.Open sql1, cn, adOpenStatic, adLockPessimistic
Text1.text=rs.Fields(0)
etc....

And how could I write in Lazarus+Firebird without using lots of controls such as TIBConnection, TSQLTransaction, TSQLQuery,TDatasource and TDBGrid ?

In my code I have to connect many times.

Regards,
Lazarus 1.2.4 / Win 32 / THAILAND

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Lazarus SQL database video tutorials
« Reply #7 on: October 19, 2010, 01:50:37 pm »
sieward? and asdf?
Quote
Should I use other lazerus or ODBC versions? Who can help me?

steve?
Quote
Would you please be so kind as to make your code available for download as well?  It would be of great help so see examples.

try to visit this post;
http://forum.lazarus.freepascal.org/index.php/topic,10811.0.html
 :D
you might explore some ideas with the project source codes I contributed.


nice video, I'm watching right now.


Quote
tkramer3
Upload the source code project of your work and let others enjoy your work, just like I did. :D

they are waiting.....
« Last Edit: October 19, 2010, 02:22:35 pm by xenablaise »

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Lazarus SQL database video tutorials
« Reply #8 on: October 19, 2010, 06:54:18 pm »
asdf?
That's what you want. :D

Quote
rsdb := 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;


hard coding huh 8-)
« Last Edit: October 22, 2010, 01:37:13 am by xenablaise »

tkramer3

  • New Member
  • *
  • Posts: 16
Re: Lazarus SQL database video tutorials
« Reply #9 on: October 21, 2010, 08:12:48 pm »
Thank you for your great answer xenablaise.

About the code in the tutorials: In the first tutorial, the code was just the automatically generated code from Lazarus. In the third the only extra code is a few lines to launch the report in runtime.

In the second tutorial a lot of the code is used to have the queries run for 10 seconds and display the result and is not so relevant for a database tutorial. The only database code is:

1. The statements to run through the resulting records of the select query in the FormKeyPress procedure: (the query is not active at the start)

    // first activate the query, so it executes on the server
    SQLQuery2.Active := true;
    SQLQuery2.First;
    // run through the records
    for IntI := 1 to (ord(Key) -48) * 1000 do
        SQLQuery2.Next;
    // check if we didn't run out of the table
    If SqlQuery2.EOF
       then
        // Show a warning
        showmessage('Sorry, we hit the bottom of the table...')
      else
        // Show the values of the textfield
        showmessage(SQLQuery2.FieldByName('TESTTEXT').AsString);
    // deactivate the query again
    SQLQuery2.Active := false;

The code displays the value of the field with fieldname TESTTEXT in the current record after running through a number of records depending on the number pressed on the keyboard (1000 records times the number pressed).

2. Launchquery:

procedure LaunchQuery(QueryObj:TSqlQuery;TransActionObj:TSqlTransaction;StrSqlIn:string);
  begin
    TransActionObj.StartTransaction;
    QueryObj.SQL.Clear;
    QueryObj.SQL.Add(StrSqlIn);
    QueryObj.ExecSQL;
    TransActionObj.Commit;
  end;

This procedure launches the query in StrSqlIn on QueryObj using transaction TransActionObj  and directly after launching commits it on TransActionObj. So in this test for every query a transaction is started and committed.

3. Statements for deleting records in the tables:

This is used to delete the records from the Mysql and Firebird tables with the following statements:

  //Delete all records from the Firebird table
  StrSql:=  'delete from testtab;';
  LaunchQuery(SQLQuery1, Sqltransaction1, StrSql);
  //Delete all records from the Mysql table (same StrSql)
  LaunchQuery(SQLQuery2, Sqltransaction2, StrSql);

4. Statements for inserting and updating:

In the loops records are inserted and updated with:
(SqlQuote is a single quote ( ' ) : const SqlQuote = #39;)

       //Insert in the Mysql table
        StrSql:=  'insert into testtab values(' + IntToStr(IntM) + ',' +
                  SqlQuote + 'a little test ' + IntToStr(IntM) +  SqlQuote + ');';
        LaunchQuery(SQLQuery2, Sqltransaction2, StrSql);

          //Update the Mysql table
          StrSql:=  'update testtab set testtext = ' + SqlQuote +
                    'another little test ' + IntToStr(IntM) +  SqlQuote +
                    ' where testnum = ' + IntToStr(IntM - 1);
          LaunchQuery(SQLQuery2, Sqltransaction2, StrSql);

The same queries are used for the Firebird table, using  SQLQuery1 and Sqltransaction1.

I Think this is more useful than just cutting and pasting the entire program,

Theo  :-\

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Lazarus SQL database video tutorials
« Reply #10 on: October 22, 2010, 01:55:26 am »
Drag and drop for Delphi and lazarus which makes them the fastest in building native applications.
Drag and drop using built-in components.  It's easy,  but less interpace with the database design that you really want too.

But using native codes like;
function opendb:boolean;
var
qr:tsqlquery;
tr:tsqltransaction;
cn:txxxconnection
begin
qr:=tsqlguerry.create(nil);
tr:=tsqltransaction.create(nil);
cn:=txxxconnection.create(nil);

cn.databasename := 'xxx';
qr.database := cn;
tr.database := cn;
..
...
..
...
try
...open..
result := true;
except
result := false;
end;
end;

I think that is the best way to improve our programming skills.
Using drap and drop db components are boring. :D

But the video is very very helpful, I like it.
Anyway where not born robots to memorise each line we've experience.  We forgot, we age, we get bored. ;D

r_1gm

  • New Member
  • *
  • Posts: 26
Re: Lazarus SQL database video tutorials
« Reply #11 on: October 22, 2010, 03:21:46 am »
hi all,

is the created object doesn't need to be free ?

Best Regards,
Rody

emadera

  • Newbie
  • Posts: 1
Re: Lazarus SQL database video tutorials
« Reply #12 on: October 22, 2010, 04:34:47 am »
The database code is...
...
...
I Think this is more useful than just cutting and pasting the entire program,

Theo  :-\


Agreed.  Thanks for taking time to make the videos and for posting relevant code with comments on how it was used in the video.  Very helpful information and greatly appreciated.

---
Que le vaya bien...Steve

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Lazarus SQL database video tutorials
« Reply #13 on: October 23, 2010, 11:46:08 am »
Quote
is the created object doesn't need to be free ?

it depends on your database design
but if you think it adds more load to the memory, free it.
:D

SteveF

  • Jr. Member
  • **
  • Posts: 92
Re: Lazarus SQL database video tutorials
« Reply #14 on: October 25, 2010, 03:41:47 pm »
Xenablaise,

Is there some source where the various methods, actions, etc. for the database components are described in detail?  My searches have so far been in vain. 

I've been coding MS Access applications for over 10 years, using only Access' VB code and SQL, so I know what I want to achieve.  It's translating that over to "the Lazarus way" that's frustrating me.  I'm looking for some source that tells me: "If you want to do this, use this other component and method."

Steve

 

TinyPortal © 2005-2018