Lazarus SQL database video tutorials
lazarus / 16-Databases

----------------------------------------------------------------------
tkramer3 · 2010-08-14 14:19

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 · 2010-08-14 15:52

I love the fact that the grandfather clock was moved ahead by 3
minutes between video 2 and 3

----------------------------------------------------------------------
SteveF · 2010-08-30 21:36

Quote from: tkramer3 on August 14, 2010, 04:19:44 pmHello 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 · 2010-08-31 20:27

Thanks, nice tutorials.

Michael

----------------------------------------------------------------------
sieward · 2010-09-04 10:06

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 · 2010-09-14 17:10

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 · 2010-10-19 10:39

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,

----------------------------------------------------------------------
xenblaise · 2010-10-19 11:50

sieward? and asdf?
QuoteShould I use other lazerus or ODBC versions? Who can help me?
steve?
QuoteWould 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

you might explore some ideas with the project source codes I
contributed.


nice video, I'm watching right now.


Quotetkramer3Upload the source code project of your work and let
others enjoy your work, just like I did.

they are waiting.....

----------------------------------------------------------------------
xenblaise · 2010-10-19 16:54

asdf?
That's what you want.

Quotersdb := 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

----------------------------------------------------------------------
tkramer3 · 2010-10-21 18:12

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:TSqlTransactio
n;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 · 2010-10-21 23:55

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.

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.

----------------------------------------------------------------------
r_1gm · 2010-10-22 01:21

hi all,

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

Best Regards,
Rody

----------------------------------------------------------------------
emadera · 2010-10-22 02:34

Quote from: tkramer3 on October 21, 2010, 08:12:48 pmThe 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 · 2010-10-23 09:46

Quoteis 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.

----------------------------------------------------------------------
SteveF · 2010-10-25 13:41

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

----------------------------------------------------------------------
xenblaise · 2010-10-25 16:11

try to visit this post;
http://forum.lazarus.freepascal.org/index.php/topic,10811.0.html


you might explore some ideas with the project source codes I
contributed.

----------------------------------------------------------------------
xenblaise · 2010-10-26 00:12

And Sad to say this, THAT of all the sqlconnections?, ms_access has
the weekest connection using tsqlquery in lazarus, BUT you can try
ZEOS maybe they have better procedure for ms_access.

Actually my doubt about ms_access connection has a bug.
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

Try to change you sqlconnection TO sqlite, if you like a standalone
database like ms_access.

Sqlite is a standalone data file, like ms_access.
Sqlite offers good result in databases accessing than ms_access.
Change your flavor since your now on lazarus.
http://wiki.lazarus.freepascal.org/User:Rocarobin

My calculation? Next year, Lazarus will be on the top 5 list of the
best programming software tools.

It will burts like java, however users of java these days are not
continuing to develop, as they noticed truly that java has a very slow
engine and it cant be altered.

Anyway thats only my sight and software analysis.

----------------------------------------------------------------------
SteveF · 2010-10-26 18:23

Xenablaise,

Thanks for the links -- I had already noted them, and have downloaded
and will be studying them.

Please rest assured that anything I build using a Lazarus front-end
will not be using MS Access for data storage.  I agree that the
Lazarus/SQLite combination makes a good open-source alternative to MS
Access, with similar multi-user capabilities and limitations.  If I
need a more robust back-end, my choice will most likely be Firebird
unless there's a specific reason to pick something else.

On a somewhat different topic, are you aware of any tutorials or
examples that deal with importing or exporting SQL data to or from a
different format, such as delimited-text, or DBF?  This is something I
have a need for all the time, and it's easy to accomplish within the
MS Access environment.  I want to be able to have the user click on a
button in my Lazarus application, browse to a specific file, and
transfer the data in the file into an SQL table.  Conversely, click a
button, choose a folder to drop the file, and transfer the data in an
SQL table or the results of a query to that file.

TIA,
Steve

----------------------------------------------------------------------
jgazquez · 2010-12-12 02:12

Quote from: tkramer3 on September 14, 2010, 07:10:52 pmHello 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.

Does Lazarus have direct Access support or just ODBC? By the way,
AFAIK accessing an Access database via ODBC you can't use VB functions
in SQL.

----------------------------------------------------------------------
xenblaise · 2010-12-16 01:57

hi
The only week part that I've encountered Lazarus+SQLdB+Ms_Access is
the deletion part, as you noticed it; it closes the dataset then
reopen it again.

I think you should try on Lazarus+Zeos+Ms_Access.