Hi HopefulGuy,
I'm sorry if my comments are not helpful to you. But your questions are mixing many subjects at the same time.
1. TSQLquery and DB-aware controls will do retrieving and storing image to database... LAZARUS will to those things for you.
2.
how do I tell Lazarus that what I want it to do is to insert a new record into the sub-table when I add a new image?
The answer is insert a record first, and then assign image file. ==> you said you know this.
3. Now your question is how to manage one-to-many relation tables. This is database design issue--- not image nor blob field issue.
Table 1, master table, woud look like
IDNo firstName LastName Address ....
-------- ------------- ------------- ----------
Table 2, picture table, is defined as (you wrote as: )
IDNo imgno picdata
------ -------- ----------
Now, let's go to algorithm. Assume you have chosen a person --- ie. IDno is fixed. Now you want to insert an image. Steps to follow are:
a. Insert new record
dataset.insert; // or dataset.delete to remove currently selected image record.
b. Fill in IDNo
dataSet.FieldbyName('IDNo').AsInteger := masterdataset.fieldByName('IDNo').AsInteger;
c. Fill in the imgno
this is rather complex. you are not recommended to use this kind of index.
But anyway you can count the current number of records for this person and give the number (not
plus one, because we already inserted).
d. Fill image
DBImage.Picture.Loadfromfile
e. If you want to store this record to DB, DataSet.ApplyUpdates; from you Lazarus application.
4. Your LAZARUS application DataSets
Now it's time to think about your application design. You need two separate queries, one for the master table and another for the image table (there is a way that you can combine them into one query. But let's forget about it for now).
And you have to think how to design it.
1) If the total number of persons and images are not large, you can always download whole tables to your application and operate on it.
SQLPerson.SQL.Text := 'select * from table1 order by IDNO';
SQLImage.SQL.Text := 'select * from table2 order by IDNo, imgno';
And you link the two table using keyfield, and so on. Then once a record of SQLPerson is selected, only those picture records will be active.
2) if you want to download all the persons, but only pictures of those persons, then you have to set it up as :
SQLPerson.SQL.Text := 'select * from table1 order by IDNO';
SQLImage.SQL.Text := 'select * from table2 where IDNO=:idno order by IDNo, imgno';
And SQLImage must be re-opened whenever SQLPerson's record is changed // or does keyfield do it?
3) there may be other options, but let's do the second option for now.
So you need two TSQLQueries and two TDataSource. Whether you put these on separate forms or on the same form is NOT important AT ALL.
I hope you add two DBGrids and TDBNavigators on the form, and link SQLConnection, SQLTransaction, SQLQueries, Datasources, DBGrids and DBNavigators, and then check whether you've set everything correctly. You may also put other Data-aware controls like TDBEditor, TDBMemo, TDBImage, etc. just by liking them to datasets via datasource + datafield.
Now inserting, deleting, or updating any record of any queries, are done as we've discussed previoiusly. But at the beginning state you'd better play with DBNavigator. Not much codes are necessary.
AGAIN, operation on these queries are just on local copy of the physical DB table --- even though you have opened whole table. You need to call ApplyUpdates to store them permanently.
SQLPerson.ApplyUpdates or SQLImage.ApplyUpdates will do for each dataset (dataset=TSQLQuery here. There are other TDataSet descendants, like SQLiteDataset, TIBTable, TTable of Delphi, etc.).
Sorry if this is worthless and redundant comment. But I think you need to understand the basic database application development architecture first.