Corrections to the wiki (Solved)

[dbannon]

I am aware of a bit of a code example on the wiki that is, IMHO, a bit buggy. I'd like people's reassurance before I edit it. Not sure of the etiquette around here.

I'm new here but, long, long ago, considered myself a reasonable Turbo Pascal and Delphi user. Recently needed a bit of code and remembered the FP/Lazaeus project. So, quickly found a good start on a bit of code called CreateTable on http://wiki.freepascal.org/SqlDBHowto but to my surprise, the code has some problems. I really like the idea of example code, something you can copy and paste, and one way or another, run. This code has no GUI, starts with a "program" declaration, ends with "end." and so looks like  standalone FP programme. But it does not compile because -

1. Is missing a function (noted in text).
2. Needs but has no "uses" clause.
3. Has a function returning a result via keyword 'result', OK in Lazarus but not FP.
4. Has badly constructed strings, using single inverted comma within the string, compiler thinks thats the end of the string.
5. Needs to free the Transaction before the Connection to avoid a run time error.

I have edited the example a bit and produced a working version in about the same number of lines.

Code: Pascal  [Select][+][-]

1. program CreateTable;
2.  
3. uses
4.   SysUtils, sqldb, db, sqlite3conn;
5.  
6. var
7.   AConnection : TSQLite3Connection;
8.   ATransaction : TSQLTransaction;
9.  
10. begin
11.   AConnection := TSQLite3Connection.Create(nil);
12.   AConnection.DatabaseName := 'test_dbase';
13.   // Thats all SQLite needs, other dbases might also want -
14.   // AConnection.Hostname := 'localhost';
15.   // AConnection.UserName := 'sysdba';
16.   // AConnection.Password := 'masterkey';
17.  
18.   ATransaction := TSQLTransaction.Create(AConnection);
19.   AConnection.Transaction := ATransaction;
20.   AConnection.Open;
21.   ATransaction.StartTransaction;
22.   AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));');
23.    ATransaction.Commit;
24.  
25.   ATransaction.StartTransaction;
26.   AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');');
27.   AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');');
28.   ATransaction.Commit;
29.   AConnection.Close;
30.   ATransaction.Free;   
31.   AConnection.Free;
32. end.

[Thaddy]

Some remarks if you want to improve on example code (good effort):
You actually leave some issues open.
ad 2: there is too much there
ad 3: that depends on the mode: it is not a real issue. Simply add the proper mode

And the code is not sufficiently safe.
Try:

Code: Pascal  [Select][+][-]

1. program CreateTable;
2. {$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
3. uses
4.   sqldb, sqlite3conn;
5.  
6. var
7.   AConnection : TSQLite3Connection;
8.   ATransaction : TSQLTransaction;
9.  
10. begin
11.   AConnection := TSQLite3Connection.Create(nil);
12.   try
13.     AConnection.DatabaseName := 'test_dbase';
14.     ATransaction := TSQLTransaction.Create(AConnection);
15.     try
16.       AConnection.Transaction := ATransaction;
17.       AConnection.Open;
18.       ATransaction.StartTransaction;
19.       AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));');
20.       ATransaction.Commit;
21.       ATransaction.StartTransaction;
22.       AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');');
23.       AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');');
24.       ATransaction.Commit;
25.       AConnection.Close;
26.     finally
27.       ATransaction.Free;
28.     end;
29.   finally  
30.     AConnection.Free;
31.   end;
32. end.

I would personally also add an if fileexists... And exception handling for database errors, but that is probably nitpicking in the context of an example

[wp]

@dbannon: Do you know that everybody can edit the wiki? Just register and you are ready to go. But, of course, it is a good idea to present the intended changes here for discussion. Thank you.

[dbannon]

Thanks WP, Thaddy. Yep, knew I could edit, just wanted to know if I should edit 

I have bit of a training background. I think that block of code needs to be minimal, only include whats essential to compile and run.  But, obviously agree with Thaddy about error checking. So, what I'll do is -

Replace existing code with my version (and include Thaddy's compiler directives).

Add another block of code further down, after the section on reads, that does include some sensible error checking. There are already comments there about that need. So, creating a table, writing to it, reading from it and checking errors is a nice summary at that stage.

[wp]

I could imagine presentation of code in the wiki in three ways:

• code fragment: just a few lines showing the essential ideas, will not necessarily compile. Not good for beginners because they may not know about the missing parts. But not everybody is a beginner... And the advantage is that the length of the article can be kept at a reasonable length.
• complete procedures: can be pasted into a program and will compile and run there. Necessary adjustments of "uses" and "requirements" will have to be made by the user. Still needs some experience.
• complete programs: should compile and run if pasted in to a separate file. Ideal for beginners, but will blow up the article length considerably.

The code in the wiki page that we are talking about begins with "program", i.e. claims to belong to group 3. However, it does not compile and, therefore, should be changed.

[dbannon]

OK, done.

[Thaddy]

OK, done.

Ok, Well Done!