Print Page - [Solved] Help on Database Insert

Programming => Databases => Topic started by: daveinhull on October 08, 2017, 06:57:30 pm

Title: [Solved] Help on Database Insert
Post by: daveinhull on October 08, 2017, 06:57:30 pm

Hi,

I would appreciate a little help on inserting into a an access database, probably really easy, but I'm just missing something.
My SQL string includes an inner join and as I suspect it would, it says it is read only when I try to append a record.
I think I must create my own InsertSQL, but could someone give me a clue or point me in the right direction to learn about this.

Code: Pascal [Select][+]

SELECT Stakeholders.*, Stakeholder_Types.SH_Type
FROM Stakeholder_Types INNER JOIN Stakeholders ON Stakeholder_Types.ID = Stakeholders.T_Stakeholder_Type;    

Stakeholders has about 10 fields in it which are linked to controls on a form.
One of the fields points to an entry in the Stakeholder_Types which I want to show on the form instead of just the ID.

When I create an InsertSQL it no longer says it is read only, but I can't get the InsertSQL correct to update the just the Stakeholders table.

Any help or direction would be good, not just to solve this problem but to learn a little more about accessing databases.

Many thanks in advance
Dave

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 09, 2017, 05:08:53 am

Have you read?
http://wiki.freepascal.org/Lazarus_Database_Overview

And seen?
Lazarus SQL database video tutorial - YouTube
https://www.youtube.com/watch?v=pq2oCiJePHo

Lazarus SQL database tutorial 2 - YouTube
https://www.youtube.com/watch?v=ewNjqFXqDDc

Lazarus SQL database tutorial part 3 - YouTube
https://www.youtube.com/watch?v=urEdbyV_AbE

Lazarus Tutorial : Creating, editing, and searching a Firebird database ...
https://www.firebirdnews.org/lazarus-tutorial-creating-editing-and-searching-a-firebird-database-printing-a-report-stored-procedures-and-clientdatasets/

As a general SQL information, you can read (SELECT) many joined tables, but you must create (INSERT), update (UPDATE) or delete (DELETE) only one table at a time. That is a CRUD:
https://forum.lazarus.freepascal.org/index.php?topic=37893.0

Title: Re: Help on Database Insert
Post by: daveinhull on October 10, 2017, 10:41:09 pm

Hi valdir.marcos,

I'd already been through a couple, but have been through then all again now, but they don't really answer my query.

I'm still not sure how I setup the InsertSQL in the Object Inspector to just update the min table (Stakeholder), i.e. ignoring the inner join to the Stakeholder_Type.

Any further help would be really appreciated so I can understand how the SQL query works with the other three SLQ queries in the Object Inspector.

Thanks
Dave

Title: Re: Help on Database Insert
Post by: mangakissa on October 11, 2017, 08:55:26 am

Don't use * in your query, specially not with joins.

Code: SQL [Select][+]

    SELECT st.field1, st.field2, st.field3, stfield4 FROM Stakeholder_Types st 
      INNER JOIN Stakeholders s ON st.ID = s.T_Stakeholder_Type;    
 

insertquery:

Code: SQL [Select][+]

    INSERT INTO  Stakeholder_Types (field1,field2,field3,field4)
    VALUES (:field1,:field2,:field3,:field4);
 

Be carefull with primary keys. Mostly they are autoincrement. This fields has to be set to property required fields to false.
updatequery:

Code: SQL [Select][+]

    UPDATE Stakeholder_Types  SET field2 = :field2, field3 = :field3, field4 = :field4)
    WHERE field1 = :field1
 

In this case you use your primary key to save the updated record. This is a unique value, so only one record will be saved.

I don't know which database you're using, but on firebird I create my primary id before I save my record to the database.

Title: Re: Help on Database Insert
Post by: daveinhull on October 11, 2017, 10:27:35 am

Hi mangakissa,

Many thanks for the feedback and information, very useful for helping me understand, not just giving me the answer. I'll investigate your suggestions further and see what I can work out.

Thanks
Dave

Title: Re: Help on Database Insert
Post by: daveinhull on October 11, 2017, 02:59:17 pm

Hi,

Ok, so I've followed the advice and removed the .* and added specific fields to the SQL statement.
I've also changed the InsertSQL to the example shown and no longer received Dataset is read only (as I would probably expect now that I've specified an InsertSQL.

However when I run it, I get an error message saying that I'm trying to assign a Null value to a non variant - probably for a field that is connected to a form control which doesn't have anything in it. I could set all the field to a default value which would probably get rid of this error, but the question is why does it work when I leave it the ParseSQL to sort things out if I do not include an INNER JOIN and yet still leave controls empty?

I don't think I've fully grasp the concepts of these SQL fields. I guess I'm asking "How do you insert a record when the underlying SQL/Dataset includes an INNER JOIN to another table.

BTW I'm using an MS access database.

As always, thanks in advance for any help.

Dave

Title: Re: Help on Database Insert
Post by: mangakissa on October 11, 2017, 04:15:05 pm

That's a problem with your primary key.
I explained it earlier. Make your fields persistent in desingtime. Go to your primary field and set the property required to false.

http://wiki.freepascal.org/SQLdb_Tutorial1

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 11, 2017, 05:40:45 pm

Quote from: daveinhull on October 10, 2017, 10:41:09 pm

I'd already been through a couple, but have been through then all again now, but they don't really answer my query.

I'm still not sure how I setup the InsertSQL in the Object Inspector to just update the min table (Stakeholder), i.e. ignoring the inner join to the Stakeholder_Type.

Any further help would be really appreciated so I can understand how the SQL query works with the other three SLQ queries in the Object Inspector.

There are many ways. This is a very simple manual example for Firebird, it should be similar for Microsoft Access:

Code: Pascal [Select][+]

  with iqryTableB do
  begin
    Close;
    ParseSQL := False;
 
    SQL.Clear;
    SQL.Add('Select A.ID, A.Field1, A.Field2, A.Field3, B.ID, B.TableA_ID, B.Field1, B.Field2 B.Field3, B.Field4 From TableA A ');
    SQL.Add('Inner Join TableB B ');
    SQL.Add('  on B.TableA_ID = A.ID ');
    SQL.Add('Where ');
    SQL.Add('  B.TableA_ID = 1234567 ');
    SQL.Add('Order By ');
    SQL.Add('  B.ID;');
 
 
    InsertSQL.Clear;
    InsertSQL.Add('Insert Into TableB( ID,  TableA_ID,  Field1,  Field2,  Field3,  Field4) ');
    InsertSQL.Add('            Values(:ID, :TableA_ID, :Field1, :Field2, :Field3, :Field4); ');
 
    UpdateSQL.Clear;
    UpdateSQL.Add('Update TableB ');
    UpdateSQL.Add('Set ');
    UpdateSQL.Add('  Field1 = :Field1, ');
    UpdateSQL.Add('  Field2 = :Field2, ');
    UpdateSQL.Add('  Field3 = :Field3, ');
    UpdateSQL.Add('  Field4 = :Field4 ');
    UpdateSQL.Add('Where ');
    UpdateSQL.Add('  ID = :ID;');
 
    DeleteSQL.Clear;
    DeleteSQL.Add('Delete From TableB ');
    DeleteSQL.Add('Where ');
    DeleteSQL.Add('  ID = :ID;');
 
    Open;
 
    // ShowMessage('InsertSQL: ' + LineEnding + InsertSQL.Text);
    // ShowMessage('UpdateSQL: ' + LineEnding + UpdateSQL.Text);
    // ShowMessage('DeleteSQL: ' + LineEnding + DeleteSQL.Text);
  end;
 

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 11, 2017, 05:55:08 pm

Quote from: mangakissa on October 11, 2017, 04:15:05 pm

That's a problem with your primary key.
I explained it earlier. Make your fields persistent in desingtime. Go to your primary field and set the property required to false.

http://wiki.freepascal.org/SQLdb_Tutorial1

@daveinhull
Maybe, you need some extra settings:

Code: Pascal [Select][+]

procedure TForm1.FormCreate(Sender: TObject);
begin
  with iqryTableB do
  begin
    FieldByName('ID').Required := False;
    FieldByName('Field1').Required := False;
    FieldByName('Field2').Required := False;
    FieldByName('Field3').Required := False;
    FieldByName('Field4').Required := False;
  end;
end;
 
procedure TForm1.iqryTableBBeforePost(DataSet: TDataSet);
begin
  iqryTableB.FieldByName('ID').AsInteger := 1234567;
end;

Title: Re: Help on Database Insert
Post by: daveinhull on October 11, 2017, 10:11:36 pm

Thanks all,

Ok so I've been through the tutorial again, but it doesn't really at anything about .required := false or around the other SQL statements in the Object Inspector.

I've also tried setting up .Required ;= false to all fields as suggested but I still get a MS Access error saying I'm trying to assign a null value.

What I don't understand fully is that if I just use a simple SQL statement such as
SELECT ID, Field2, Field3, Field4 From Table1

And do an insert from a set of assigned edit controls it works even if none of the field are filled in; it even sorts out the autoincrement field.

However as soon as I include an inner join to another table it fails with either the dataset is read only (without InterSQL setup) or assigning a Null value (if I set up an InsertSQL)

I'm really going around in circles here.

So I have

SQL:
SELECT Stakeholder.ID, Stakeholders.T_Stakeholder_Type, Stakeholders.SH_Name, Stakeholders.SH_Phone, Stakeholder_Types.SH_Type
FROM Stakeholders INNER JOIN Stakeholder_Types ON Stakeholders_Type.ID = Stakeholders.T_Stakeholder_Type;

InsertSQL:
INSERT INTO Stakeholder (ID, T_Stakeholder_Type, Stakeholder.SH_Name, Stakeholders.SH_Phone, Stakeholder_Types.SH_Type)
VALUES (:ID, :T_Stakeholder_Type, :Stakeholder.SH_Name, :Stakeholders.SH_Phone, :Stakeholder_Types.SH_Type);

I've set all the fields to .Required := False

All fields are attached to data aware controls.

And I get the MS access error trying to assign Null to non variant.....

Again any further help would be much appreciate.

Thanks

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 11, 2017, 11:22:19 pm

ParseSQL := False;

Quote from: daveinhull on October 11, 2017, 10:11:36 pm

...
What I don't understand fully is that if I just use a simple SQL statement such as
SELECT ID, Field2, Field3, Field4 From Table1

And do an insert from a set of assigned edit controls it works even if none of the field are filled in; it even sorts out the autoincrement field.

However as soon as I include an inner join to another table it fails with either the dataset is read only (without InterSQL setup) or assigning a Null value (if I set up an InsertSQL)
...

As you already did, make one only table to work, then change SQLQuery1.ParseSQL to False. This will prevent InsertSQL, UpdateSQL and DeleteSQL to be rebuilt.
Then change SQLQuery1.SQL to join another table.
Try to use it and tell us if it works.

SQLQuery1.SQL may contain none, one or many JOINs, but InsertSQL, UpdateSQL and DeleteSQL must contain only one table. And setting SQLQuery1.ParseSQL to False will make their content manually manageable.

Please, inform which fields of your SQLQuery1 are required or not:

Code: Pascal [Select][+]

procedure TForm1.FormCreate(Sender: TObject);
begin
  with SQLQuery1 do
  begin
    FieldByName('ID').Required := False;
    FieldByName('Field1').Required := False;
    FieldByName('Field2').Required := False;
    FieldByName('Field3').Required := False;
    FieldByName('Field4').Required := False;
  end;
end;

Title: Re: Help on Database Insert
Post by: daveinhull on October 12, 2017, 01:57:34 pm

Hi valdir.macros,

Many thanks for your help :D

I've already done what you suggest.

I added a simple query with one table and ParseSQL true and it works when I do an insert
I changed the query to include another table on an inner join and left everything else the same and (of course) it fails with a dataset is read only.
I change the ParseSQL to false and added a seperate query for InsertSQL which refers to just the first table and it fails with an Access reported error about assigning a Null value
I added code to set which field is required and it still errors with an access Null assignment error.

Note that the fields are assigned to data aware controls and in both set-ups (e.g. single table or two table) the controls contain exactly the same information (i.e some controls have entries and others don't - but it is the same in both cases).

I'm just not sure what or where to do next.

Any further thoughts?

Many thanks
Dave

Title: Re: Help on Database Insert
Post by: rvk on October 12, 2017, 02:34:06 pm

This is really hard to diagnose without knowing the table definitions.

But setting required to false isn't going to help you if the field is defined as NOT NULL.
Your T_Stakeholder_Type for instance is a foreign key which points to another table. If you have declared that field NOT NULL you need to fill it correctly.

Otherwise try to make a small example project with a small database and see if it happens there too. If it does you can post that example.

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 12, 2017, 06:24:45 pm

Quote from: daveinhull on October 12, 2017, 01:57:34 pm

I've already done what you suggest.

I'm just not sure what or where to do next.

Any further thoughts?

Quote from: rvk on October 12, 2017, 02:34:06 pm

This is really hard to diagnose without knowing the table definitions.

Otherwise try to make a small example project with a small database and see if it happens there too. If it does you can post that example.

@daveinhull
Make a small sample project with a small sample Microsoft Access database and attach them to this thread so we can check what you might be missing.

Title: Re: Help on Database Insert
Post by: daveinhull on October 12, 2017, 10:47:02 pm

@valdir.marcos, @rvk,

Thanks for getting back to me and yes, I'll produce a small example programme, see if the same error occurs and then attach it.

Really appreciate your time.

in the meantime, I'll have a look at the T_Stakeholder_Type foreign key and look at making sure it is defined.

Thanks again.
Dave

Title: Re: Help on Database Insert
Post by: mangakissa on October 13, 2017, 08:23:11 am

@valdir.marcos

de property required should only uses if fields are not visible on form, but requires an input into the table.
That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true.
In most cases the property required is very usefull, because it tells the user not set the field on blank.

Title: Re: Help on Database Insert
Post by: rvk on October 13, 2017, 09:44:57 am

Quote from: daveinhull on October 11, 2017, 02:59:17 pm

However when I run it, I get an error message saying that I'm trying to assign a Null value to a non variant - probably for a field that is connected to a form control which doesn't have anything in it.

And this might also not even have anything to do with your insert statement.

What kind of controls are you connecting to that field. Maybe that control can only handle valid numbers and not NULL.

Anyway, when you create some example code things might get clear to you.
If the example code works try to recreate the same controls you have in your project to see if the control is the problem.

B.T.W. You also didn't mention the EXACT error message. That's usually very important to determine the problem. Trying to translate the error-message into "human-speak" is never a good idea.

Title: Re: Help on Database Insert
Post by: valdir.marcos on October 14, 2017, 03:10:02 am

Quote from: mangakissa on October 13, 2017, 08:23:11 am

the property required should only be used if fields are not visible on form, but requires an input into the table.
That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true.
In most cases the property required is very usefull, because it tells the user not set the field on blank.

+1

I was just providing means to @daveinhull understand what he was trying to achieve.

Title: Re: Help on Database Insert
Post by: daveinhull on October 14, 2017, 10:48:51 am

Hi,

Ok I was creating small test setup and in doing so I found the problem.

I had included the (autoincrement ID field, ie like INSERT INTO table (ID, field1) VALUE (:ID, :Field1);) in the InsertSQL query and it was this that was the problem. As soon as I removed reference to this (and basically let the Access connection deal with it) everything works.

I didn't then need the .Required on the fields.

Many thanks for helping me work out my own problem - best way to learn - although I may have more questions now - as they say you can't work out how well a plane will fly until you put the wings on, and then it might not :D

Dave

Title: Re: Help on Database Insert
Post by: mangakissa on October 15, 2017, 09:02:13 am

Then you have created a small problem. If there's not a unique field values, which is indexed, the update will fails. Example:

Quote

john father
john grandfather
jane sister

If you update a record with the value john, you're database raises an error.
That's why primary keys are important. It gives you an unique record to update.

It doesn't have to be like this, but I will mention it if it occurs.

Title: Re: Help on Database Insert
Post by: daveinhull on October 15, 2017, 09:48:44 am

Hi mangakissa,

The autoincrememt ID is the primary key in the database. As I said when I removed reference to it in the SQL Insert query everything works for the insert. Of course I still have it included for the Delete and Update queries.
I guess MS Access will deal with it as an autoincrememt field like it does when actually working in Access.

Does this sound right?

Thanks
Dave

Title: Re: Help on Database Insert
Post by: mangakissa on October 16, 2017, 09:18:34 am

Yes. But if the insert record is saved to the database, your application doesn't know the new primary key if the record is not refreshed. That's an issue to think about.

Title: Re: Help on Database Insert
Post by: daveinhull on October 17, 2017, 04:39:14 pm

Hi,

In thinking about this further the error is as expected. If I do an insert in MS Access, the Autoincrement field (or in FPC :ID) will be nothing until something is written to any of the other fields and then it gets it value assigned. So when I do an insert and include the :ID, it is of course Null at the point of the insert.

Anyway thanks for all the help - I think I get it!