* * *

Author Topic: Help needed with first attempt at Database addition to program  (Read 1014 times)

Rayvenhaus

  • Jr. Member
  • **
  • Posts: 80
I hope this is the correct area to ask this question. I have a program I am writing and I've received wonderful help here in these forums as my last time programming in Pascal was over 25 years ago.

Anyways, long story short, I am looking to add database functionality into my program.  I have the following procedure on a form called wyckerechoload. Its purpose is to allow the user to set the location of a file called "AREAS.BBS" and then parse that file. That part works perfectly. Now, I want to add a database to my application. I've decided on using SQLite (So far). On my Main Form I've added a SQLDBLibraryLoader componet, a SQLite3Connection component, a SQLQuery component and a SQLTransaction component. I believe that I am able to make a connection successfully. I also have code that checks for the exitance of the database and, if it is missing, recreates it and that works fine.

What I want to add to the code below is, around line 25ish, where I have two variables loaded and insert them into the database.

Code: Pascal  [Select]
  1. procedure Tfrm_wyckerechoload.Button1Click(Sender: TObject);
  2. begin
  3.   if FileNameEdit1.FileName <> '' then
  4.     begin
  5.       echoCount := 0;
  6.       Logger.Info(sImportEchos);
  7.       // Set the name of the file that will be read
  8.       AssignFile(tfIn, FileNameEdit1.FileName);
  9.       try
  10.         // Open the file for reading
  11.         Label3.Caption := sOpeningFIle;
  12.         reset(tfIn);
  13.         Application.ProcessMessages;
  14.         delay(25);
  15.         Label3.Caption := sReadingFile;
  16.         // Keep reading lines until the end of the file is reached
  17.         while not eof(tfIn) do
  18.           begin
  19.             readln(tfIn, s);
  20.             echoCount := echocount + 1;
  21.             echoTag := ExtractWord(2,s,[' ']);
  22.             echoSys := ExtractWord(3,s,[' ']);
  23.             Application.ProcessMessages;
  24.             delay(10);
  25.             Label5.Caption := echoTag;
  26.             Label7.Caption := echoSys;
  27.           end;
  28.         // Done so close the file
  29.         CloseFile(tfIn);
  30.         Logger.Info(sImportComplete);
  31.         Label5.Caption :=  sImported + IntToStr(echoCount) + sEchoes;
  32.         Label7.Caption := '';
  33.         Label3.Caption := sFileImportComplete;
  34.       except
  35.         on E: EInOutError do
  36.           Logger.Error(sImportError);
  37.       end;
  38.     end
  39.   else
  40.     begin
  41.       ShowMessage(sFileSelect);
  42.     end;
  43. end;  
  44.  

Can anyone help we get and understanding of how this works? I've read the tutorials for DB's and it helped a lot but I'm not quite grasping the subtleties of doing this.

Thanks in advance.

taazz

  • Hero Member
  • *****
  • Posts: 3818
Re: Help needed with first attempt at Database addition to program
« Reply #1 on: March 30, 2017, 06:42:26 pm »
the query is responsible for the data in your application. It has code to lock/unlock edit and delete the data.
The data are separated in to two main groups the rows which in terms of a database it is a record and the columns which are the fields in a database. The query component has a number of sql properties specifically has 4 in alphabetical order they are DeleteSQL, InsertSQL, SQL and updateSQL with SQL meaning the select sql. SQLQuery gives access to only one row at a time and lets you navigate through all the rows using the methods first,next,previous and last. In order to add a new row in your data you call the method append or insert and to edit an existing you the method edit, this "locks" the data buffer to the active row and allows you change the data. You change the data for each column. columns can be selected either by their position in the sql eg
Code: [Select]
sqlQuery1.fields[0].Value := echoTag; or by the field name eg
Code: [Select]
sqlQuery1.fieldbyname('Echo').Value := echoTag;
After you have finished setting the individual field values you call the post method which writes you new data in the internal memory or the cancel method which discards the new values, and "unlocks" it for farther browsing or editing of other rows. when you have finished with all your changes you call the methods ApplyUpdates which in turn creates a number of insert, update or delete sql commands and sends them to the database engine for processing. IF everything goes well you have received no error messages and your programhas send the data to the database for safe keeping. The database keeps those changes for you and waits to tell it if everything is ok to save on the disk or they are problematic and it should discard those. In this case calling the TRansaction's Commit method instruct the database engine that everything must be saved permanently or calling the rollback method informs the database that you do not want the resent changes and it should discard them. In any case the database sees it a an end of transaction and closes the active connection with the query.

There are ways to commit or rollback and keep the connection active if you want to divide the data processing in more managable quantities eg 10 or 100 rows at a time most of them involve calling method that tend to end in the word retaining eg commitretaining.

And that's a birds eye view of the process.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Rayvenhaus

  • Jr. Member
  • **
  • Posts: 80
Re: Help needed with first attempt at Database addition to program
« Reply #2 on: March 30, 2017, 08:03:23 pm »
@taazz, thanks.  Here's what I've done so far:

Code: Pascal  [Select]
  1. procedure Tfrm_wyckerechoload.Button1Click(Sender: TObject);
  2. begin
  3.   if FileNameEdit1.FileName <> '' then
  4.     begin
  5.       echoCount := 0;
  6.       Logger.Info(sImportEchos);
  7.       // Set the name of the file that will be read
  8.       AssignFile(tfIn, FileNameEdit1.FileName);
  9.       try
  10.         // Open the file for reading
  11.         Label3.Caption := sOpeningFIle;
  12.         reset(tfIn);
  13.         Application.ProcessMessages;
  14.         delay(25);
  15.         Label3.Caption := sReadingFile;
  16.         // Keep reading lines until the end of the file is reached
  17.         while not eof(tfIn) do
  18.           begin
  19.             readln(tfIn, s);
  20.             echoCount := echocount + 1;
  21.             echoTag := ExtractWord(2,s,[' ']);
  22.             echoSys := ExtractWord(3,s,[' ']);
  23.             Application.ProcessMessages;
  24.             delay(10);
  25.             Label5.Caption := echoTag;
  26.             frm_wyckermain.SQLQuery1.FieldByName('echotag').Value:=echoTag;
  27.             Label7.Caption := echoSys;
  28.             frm_wyckermain.SQLQuery1.FieldByName('hubaddr').Value:=echoSys;
  29.             frm_wyckermain.SQLQuery1.Post;
  30.           end;
  31.         // Done so close the file
  32.         CloseFile(tfIn);
  33.         frm_wyckermain.SQLQuery1.ApplyUpdates;
  34.         Logger.Info(sImportComplete);
  35.         Label5.Caption :=  sImported + IntToStr(echoCount) + sEchoes;
  36.         Label7.Caption := '';
  37.         Label3.Caption := sFileImportComplete;
  38.       except
  39.         on E: EInOutError do
  40.           Logger.Error(sImportError);
  41.       end;
  42.     end
  43.   else
  44.     begin
  45.       ShowMessage(sFileSelect);
  46.     end;
  47. end;
  48.  

I'm assuming that I am missing something as I get the following error when importing the file:

SQLQuery1 : Field not found: "echotag".

Press OK to ignore and risk data corruption.
Press Cancel to kill the program.

egsuh

  • New member
  • *
  • Posts: 14
Re: Help needed with first attempt at Database addition to program
« Reply #3 on: March 31, 2017, 03:04:58 am »
You cannot change data in Query results. In order to change data in the table, you have to either:

1) open database "Table",  and use 'Edit', 'Post', etc.
2) compose a query with SQL statements that modify data in the table, like 'update tablename set echotag="..."' etc. (The syntax may be different), and then "Execute" the query.


taazz

  • Hero Member
  • *****
  • Posts: 3818
Re: Help needed with first attempt at Database addition to program
« Reply #4 on: March 31, 2017, 04:29:57 am »
The errror message says that the component SQLQuery1 has no knowledge of a field named echotag. Do you have such a field in your database? have you queried the correct table with the query1 component? To be able to answer that I would have to see a copy of the database and the settings of the query1 component, which you can get as a text directly from the lfm file of the form that the component is placed. In this case I'm guessing frm_wyckerechoload.lfm. One last think avoid using the form's variable to access components from the form it self. Use self or nothing, from the forms methods (aka procedure/functions that start with "Tfrm_wyckerechoload." are methods of the form and have full access to the controls on them). As for the Application.processMessages please stay away from it. It is not to something be used.
Code: Pascal  [Select]
  1.     procedure Tfrm_wyckerechoload.Button1Click(Sender: TObject);
  2.     begin
  3.       if FileNameEdit1.FileName <> '' then
  4.         begin
  5.           echoCount := 0;
  6.           Logger.Info(sImportEchos);
  7.           // Set the name of the file that will be read
  8.           AssignFile(tfIn, FileNameEdit1.FileName);
  9.           try
  10.             // Open the file for reading
  11.             Label3.Caption := sOpeningFIle;
  12.             reset(tfIn);
  13.             Label3.Caption := sReadingFile;
  14.             // Keep reading lines until the end of the file is reached
  15.             while not eof(tfIn) do
  16.               begin
  17.                 readln(tfIn, s);
  18.                 echoCount := echocount + 1;
  19.                 echoTag := ExtractWord(2,s,[' ']);
  20.                 echoSys := ExtractWord(3,s,[' ']);
  21.                 Label5.Caption := echoTag;
  22.                 self.SQLQuery1.FieldByName('echotag').Value:=echoTag;
  23.                 Label7.Caption := echoSys;
  24.                 self.SQLQuery1.FieldByName('hubaddr').Value:=echoSys;
  25.                 self.SQLQuery1.Post;
  26.               end;
  27.             // Done so close the file
  28.             CloseFile(tfIn);
  29.             SQLQuery1.ApplyUpdates;
  30.             Logger.Info(sImportComplete);
  31.             Label5.Caption :=  sImported + IntToStr(echoCount) + sEchoes;
  32.             Label7.Caption := '';
  33.             Label3.Caption := sFileImportComplete;
  34.           except
  35.             on E: EInOutError do
  36.               Logger.Error(sImportError);
  37.           end;
  38.         end
  39.       else
  40.         begin
  41.           ShowMessage(sFileSelect);
  42.         end;
  43.     end;
  44.  
There that's better.
« Last Edit: March 31, 2017, 04:34:02 am by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

kapibara

  • Sr. Member
  • ****
  • Posts: 443
Re: Help needed with first attempt at Database addition to program
« Reply #5 on: March 31, 2017, 05:42:36 am »
Ahh, RemoteAccess, FrontDoor, GoldEd, Terminate, Telix. That was the times!

Attached an example, something to start with. Click to create the table first, otherwise you get that message about Field not found: "echotag". Then press load and a textfile will be read, and for each row a record will be written to the sqlite db.

You have to assign the right values to the two Params. (You used FieldByName, but you should use ParamByName and for that to work you must have a INSERT statement with params, like the one you see I added by code. Otherwise you have to create the Params manually. Like this it is automatic)

Code: Pascal  [Select]
  1. unit uMain;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  9.   sqlite3conn, sqldb;
  10.  
  11. type
  12.  
  13.   { Tfrm_wyckermain }
  14.  
  15.   Tfrm_wyckermain = class(TForm)
  16.     btnLoad: TButton;
  17.     Button1: TButton;
  18.     Button2: TButton;
  19.     Conn: TSQLite3Connection;
  20.     FileNameEdit1: TEdit;
  21.     Label1: TLabel;
  22.     Label2: TLabel;
  23.     Label3: TLabel;
  24.     Label4: TLabel;
  25.     Label5: TLabel;
  26.     Label6: TLabel;
  27.     Label7: TLabel;
  28.     lblFilename: TLabel;
  29.     OpenDialog1: TOpenDialog;
  30.     SQLQuery: TSQLQuery;
  31.     TX: TSQLTransaction;
  32.     procedure btnLoadClick(Sender: TObject);
  33.     procedure Button1Click(Sender: TObject);
  34.     procedure Button2Click(Sender: TObject);
  35.     procedure FormCreate(Sender: TObject);
  36.   private
  37.     { private declarations }
  38.   public
  39.     procedure wyckerechoload;
  40.   end;
  41.  
  42. var
  43.   frm_wyckermain: Tfrm_wyckermain;
  44.  
  45. implementation
  46.  
  47. {$R *.lfm}
  48.  
  49. { Tfrm_wyckermain }
  50.  
  51. procedure Tfrm_wyckermain.btnLoadClick(Sender: TObject);
  52. begin
  53.   wyckerechoload;
  54. end;
  55.  
  56. procedure Tfrm_wyckermain.Button1Click(Sender: TObject);
  57. begin
  58.   if OpenDialog1.Execute then
  59.     FileNameEdit1.Text:= OpenDialog1.FileName;
  60. end;
  61.  
  62. procedure Tfrm_wyckermain.Button2Click(Sender: TObject);
  63. begin
  64.   SQLQuery.SQL.Text:= 'CREATE TABLE IF NOT EXISTS mytable' +
  65.                       '(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,' +
  66.                       'echotag VARCHAR NOT NULL,'     +
  67.                       'hubaddr VARCHAR NOT NULL);';
  68.   SQLQuery.ExecSQL;
  69.   TX.Commit;
  70. end;
  71.  
  72. procedure Tfrm_wyckermain.FormCreate(Sender: TObject);
  73. begin
  74.   Conn.DatabaseName:=ProgramDirectory + 'mydb.db';
  75.   FileNameEdit1.Text:= ProgramDirectory + 'area.bbs';
  76. end;
  77.  
  78. procedure Tfrm_wyckermain.wyckerechoload;
  79. var
  80.   s, echoTag, echosys: string;
  81.   echoCount: integer;
  82.   tfIn: TextFile;
  83. begin
  84.   if FileNameEdit1.Text <> '' then
  85.   begin
  86.     if not FileExists(FileNameEdit1.Text) then
  87.     begin
  88.       ShowMessage(FileNameEdit1.Text + 'Doesn''t exist!');
  89.       Exit;
  90.     end;
  91.  
  92.     SQLQuery.SQL.Text:='INSERT INTO mytable(echotag, hubaddr) VALUES(:echotag, :hubaddr)';
  93.     echoCount := 0;
  94. //      Logger.Info(sImportEchos);
  95.     // Set the name of the file that will be read
  96.     AssignFile(tfIn, FileNameEdit1.Text);
  97.     try
  98.       // Open the file for reading
  99.       Label3.Caption := 'sOpeningFIle';
  100.       reset(tfIn);
  101.       Application.ProcessMessages;
  102.       Sleep(25);
  103.       Label3.Caption := 'sReadingFile';
  104.       // Keep reading lines until the end of the file is reached
  105.       while not eof(tfIn) do
  106.         begin
  107.           readln(tfIn, s);
  108.           echoCount := echocount + 1;
  109.           echoTag := 'echotag ' +IntToStr(echoCount);
  110.           echosys := 'hub address ' +IntToStr(echoCount);
  111.           Application.ProcessMessages;
  112.           Sleep(10);
  113.           Label5.Caption := echoTag;
  114.           SQLQuery.ParamByName('echotag').AsString:= echoTag;
  115.           Label7.Caption := echoSys;
  116.           SQLQuery.ParamByName('hubaddr').AsString:= echoSys;
  117.           SQLQuery.ExecSQL;
  118.         end;
  119.       CloseFile(tfIn);
  120. //      SQLQuery.ApplyUpdates;  // Not needed when ExecSQL is used
  121.       TX.Commit;  // <-- Needed to save to disk in db.
  122. //        Logger.Info(sImportComplete);
  123.       Label5.Caption :=  'sImported' + IntToStr(echoCount) + 'sEchoes';
  124.       Label7.Caption := '';
  125.       Label3.Caption := 'sFileImportComplete';
  126.     except
  127.       on E: EInOutError do
  128.         ;//Logger.Error(sImportError);
  129.     end;
  130.   end
  131.   else
  132.   begin
  133.     ShowMessage('sFileSelect');
  134.   end;
  135. end;
  136.  
  137. end.
  138.  
« Last Edit: March 31, 2017, 06:08:21 am by kapibara »
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

Rayvenhaus

  • Jr. Member
  • **
  • Posts: 80
Re: Help needed with first attempt at Database addition to program
« Reply #6 on: March 31, 2017, 12:30:38 pm »
The errror message says that the component SQLQuery1 has no knowledge of a field named echotag. Do you have such a field in your database? have you queried the correct table with the query1 component? To be able to answer that I would have to see a copy of the database and the settings of the query1 component, which you can get as a text directly from the lfm file of the form that the component is placed. In this case I'm guessing frm_wyckerechoload.lfm. One last think avoid using the form's variable to access components from the form it self. Use self or nothing, from the forms methods (aka procedure/functions that start with "Tfrm_wyckerechoload." are methods of the form and have full access to the controls on them). As for the Application.processMessages please stay away from it. It is not to something be used.

OK, maybe that's the crux of the issue. The Data controls do not exist in the form that I am using (wyckerload), they exist in the main form (wyckermain) and I'd assumed I could use them throughout my applications forms by referencing the main form name. The database exists and I have code, that works, that will create the database in the main form, on startup. Here's the structure of my database:

Code: Pascal  [Select]
  1.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "echos"('+
  2.                 ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  3.                 ' "echotag" Text NOT NULL,'+
  4.                 ' "hubid" Integer NOT NULL );');
  5.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "echos_id_idx" ON "echos"( "id" );');
  6.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "hubs"('+
  7.                 ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  8.                 ' "hubaddr" Integer NOT NULL );');
  9.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "hubs_id_idx" ON "hubs"( "id" );');
  10.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "stats"('+
  11.                 ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  12.                 ' "hubid" Integer NOT NULL,'+
  13.                 ' "datetime" DateTime NOT NULL,'+
  14.                 ' "echotagid" Integer NOT NULL,'+
  15.                 ' "msgcount" Iteger NOT NULL );');
  16.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "stats_id_idx" ON "stats"( "id" );');
  17.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "systems"('+
  18.                 ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  19.                 ' "sysname" Text NOT NULL );');
  20.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "systems_id_idx" ON "systems"( "id" );');
  21.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(1,''Synchronet'');');
  22.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(2,''Mystic'');');
  23.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(3,''Argus'');');
  24.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(4,''Radius'');');
  25.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(5,''Taurus'');');
  26.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(6,''InternetRex'');');
  27.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(7,''BinkD'');');
  28.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(8,''Allfix'');');
  29.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(9,''TinyTic'');');
  30.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(10,''DBridge'');');
  31.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(11,''Serenity'');');
  32.           SQLTransaction1.Commit;    
  33.  

Do I need to have data controls in every form of an application that is going to access the database?

Also, Application.ProcessMessages is there to allow the labelw to be upadted for a "progress" report.
« Last Edit: March 31, 2017, 12:34:36 pm by Rayvenhaus »

mangakissa

  • Hero Member
  • *****
  • Posts: 684
Re: Help needed with first attempt at Database addition to program
« Reply #7 on: March 31, 2017, 01:12:50 pm »
Quote
Do I need to have data controls in every form of an application that is going to access the database?
No, there's a form called datamodule where you can put several datacomponents. That unit can be placed in the uses of your form. After that you can call the components with datamodule1 (or another name you gave).

You do it al wrong. With executedirect() you write directly to the database. This is usefull for creating tables, but selecting / inserting / deleting / updating records it's better to use TSQLQuery component.

In your case I shall read this tutorials, beginning with this : http://wiki.freepascal.org/SQLdb_Tutorial1
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

Rayvenhaus

  • Jr. Member
  • **
  • Posts: 80
Re: Help needed with first attempt at Database addition to program
« Reply #8 on: March 31, 2017, 01:30:53 pm »
Quote
Do I need to have data controls in every form of an application that is going to access the database?
No, there's a form called datamodule where you can put several datacomponents. That unit can be placed in the uses of your form. After that you can call the components with datamodule1 (or another name you gave).

You do it al wrong. With executedirect() you write directly to the database. This is usefull for creating tables, but selecting / inserting / deleting / updating records it's better to use TSQLQuery component.

In your case I shall read this tutorials, beginning with this : http://wiki.freepascal.org/SQLdb_Tutorial1

Sorry, maybe I was unclear. I have a form called wyckermain that contains the data controls already. The code with the table creation part exists in wyckermain and it works, I can create the database if it is missing. My issue with with accessing the database from a second form called wyckerechoload. That's where I am getting the error.

molly

  • Hero Member
  • *****
  • Posts: 1610
Re: Help needed with first attempt at Database addition to program
« Reply #9 on: March 31, 2017, 02:05:21 pm »
Sorry, maybe I was unclear.
No you weren't. The actual/current situation might be unclear because not showing what you actually have in front of you...

Quote
I have a form called wyckermain that contains the data controls already. The code with the table creation part exists in wyckermain and it works, I can create the database if it is missing.
And mangakissa was clear on that.

When you need to 'access' the database access controls (thereby not referring to field edit controls but actual data access components, as explained in tutor) from other forms then havoc usually starts to happen for most people.

Either they end up with circular reference, accessing the database while it is not initialized properly yet (or closed already)  or otherwise.

That is why you need to pay close attention to part 4 of the tutorial, here, besides reading and understanding the basics.

Quote
My issue with with accessing the database from a second form called wyckerechoload. That's where I am getting the error.
And exactly that is why using a datamodule is much more convenient.

You simply put all database access components (again, i note: actual data access components, not edit controls such as DBEdit and DBMemo) inside a datamodule. That way you can create as many forms as you like and use as many edit/display controls to edit/view the database in all size, shape and form, and for all i care from 300 different forms all at the same time.

The datamodule then also offers the possibility to add new methods that take care of the setup and closing process, or you can use the initialization/finalization sections of the datamodule for that (for small database project that should be no problem but, the suggestion is showing bad programming practice).


In case you do not wish to use datamodules (which is also perfectly valid when having given things some though), then it would be much simpler to just show both your units and forms, so that we are able to see if it can be solved without circular dependencies.

Rayvenhaus

  • Jr. Member
  • **
  • Posts: 80
Re: Help needed with first attempt at Database addition to program
« Reply #10 on: March 31, 2017, 03:04:36 pm »
@molly thank you so much. I understand now and will create the datamodule and move my database controls over there. I do not plan on having ANY data-aware components in this application.

I will report back once I have created the datamodule created and in place.

Thank you all for your help so far it is greatly appreciated!!!

taazz

  • Hero Member
  • *****
  • Posts: 3818
Re: Help needed with first attempt at Database addition to program
« Reply #11 on: March 31, 2017, 03:09:13 pm »
OK, maybe that's the crux of the issue. The Data controls do not exist in the form that I am using (wyckerload), they exist in the main form (wyckermain) and I'd assumed I could use them throughout my applications forms by referencing the main form name.
Correct on all accounts. It is not a very good way of working but it is acceptable for now.
The database exists and I have code, that works, that will create the database in the main form, on startup. Here's the structure of my database:
Code: Pascal  [Select]
  1.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "echos"('+
  2.              ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  3.              ' "echotag" Text NOT NULL,'+
  4.              ' "hubid" Integer NOT NULL );');
  5.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "echos_id_idx" ON "echos"( "id" );');
  6.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "hubs"('+
  7.              ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  8.              ' "hubaddr" Integer NOT NULL );');
  9.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "hubs_id_idx" ON "hubs"( "id" );');
  10.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "stats"('+
  11.              ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  12.              ' "hubid" Integer NOT NULL,'+
  13.              ' "datetime" DateTime NOT NULL,'+
  14.              ' "echotagid" Integer NOT NULL,'+
  15.              ' "msgcount" Iteger NOT NULL );');
  16.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "stats_id_idx" ON "stats"( "id" );');
  17.           SQLite3Connection1.ExecuteDirect('CREATE TABLE "systems"('+
  18.              ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
  19.              ' "sysname" Text NOT NULL );');
  20.           SQLite3Connection1.ExecuteDirect('CREATE INDEX "systems_id_idx" ON "systems"( "id" );');
  21.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(1,''Synchronet'');');
  22.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(2,''Mystic'');');
  23.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(3,''Argus'');');
  24.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(4,''Radius'');');
  25.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(5,''Taurus'');');
  26.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(6,''InternetRex'');');
  27.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(7,''BinkD'');');
  28.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(8,''Allfix'');');
  29.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(9,''TinyTic'');');
  30.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(10,''DBridge'');');
  31.           SQLite3Connection1.ExecuteDirect('INSERT INTO "systems" VALUES(11,''Serenity'');');
  32.           SQLTransaction1.Commit;    
  33.  
Do I need to have data controls in every form of an application that is going to access the database?
No you don't, everything is good enough just keep in mind that those are bad techniques you will need to change them in the future probably with much aggravation as well but we first learn to walk then to run.
How about the query1 parameters ? Can you show me the sql, insertsql etc commands? Do you have any static fields declared? do you use it to open more than one tables?
Also, Application.ProcessMessages is there to allow the labelw to be upadted for a "progress" report.
For now it is acceptable just don't make it a habit of using it, it creates all sorts of asynchronous conflicts especially in your code since it gives more than screen refreshes ee button and menus are pressable again.
all in all you are on the right track keep on going just provide as with the information needed to find the problem.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus