Recent

Author Topic: Update/Insert SQLITE  (Read 2003 times)

Weitentaaal

  • Sr. Member
  • ****
  • Posts: 303
Update/Insert SQLITE
« on: September 07, 2021, 04:04:02 pm »
Hello Guys :),

so i ran into this Problem : "No insert query specified and failed to generate one. (No fields found for insert- or update-statement found)"

Here is My Code:

Code: Pascal  [Select][+][-]
  1.  
  2. //Connection to Database
  3. { ----- WGR Connect ----- }
  4. DB_WGR := TSQLite3Connection.Create(Nil);  
  5. TransWGR := TSQLTransaction.Create(DB_WGR);    
  6. DB_WGR.Transaction := TransWGR;    
  7. DB_WGR.DatabaseName := WGRPath;
  8. //DB_WGR.HostName := SQLiteDBHost; Todo
  9. //DB_WGR.UserName := SQLiteDBUser; Todo
  10. DB_WGR.Password := WGRPass;
  11. DB_WGR.Connected := true;
  12. DB_WGR.KeepConnection := true;
  13.  
  14. //Init Query
  15. RsBtWert := TSQLQuery.Create(Nil);
  16. RsBtText := TSQLQuery.Create(Nil);
  17. RsBtWert .UpdateMode:= upWhereAll;
  18. RsBtText .UpdateMode:= upWhereAll;
  19.  
  20. //Function to Execute Query's
  21. Procedure wgrExecute(var Recordset: TSQLQuery; Query: String);
  22. Begin
  23.   try
  24.      Recordset.Clear;
  25.      //Recordset := TSQLQuery.Create(nil);
  26.      Recordset.UsePrimaryKeyAsKey := False;
  27.      Recordset.DataBase := DB_WGR;
  28.      Recordset.PacketRecords := -1;
  29.      Recordset.SQL.Text := Query;
  30.      Recordset.Open;
  31.   except
  32.      on E : Exception do
  33.         Application.MessageBox(PChar('An Exception was raised : ' + E.Message), PChar('Error'), MB_ICONERROR);
  34.   end;
  35. End;
  36.  
  37. //Save data to DB
  38. procedure TSWData.SaveData();
  39. begin
  40.    wgrExecute(RsCalc, 'SELECT * FROM Geraet WHERE Zeichnung = ' + Trim((ZeichNr).toString) + ' AND KOMP=0 AND ART = 1 AND ZArtNum = ' + Trim((BtlArtNum).toString));
  41.    RsCalc.Edit;
  42.    RsCalc.FieldByName('SomeField').AsInteger := SomeValue;
  43.    RsCalc.FieldByName('SomeField').AsInteger := SomeValue;
  44.    RsCalc.FieldByName('SomeField').AsInteger := SomeValue;
  45.    RsCalc.FieldByName('SomeField').AsFloat := SomeValue;
  46.    RsCalc.FieldByName('SomeField').AsString := SomeValue;
  47.    RsCalc.Post;
  48.    RsCalc.ApplyUpdates;
  49.    TransWGR.CommitRetaining;
  50.  
  51.    wgrExecute(RsBtWert, 'SELECT * FROM WERTE WHERE DATA = ' + Trim(DataNr.ToString));
  52.    RsBtWert.Edit;
  53.    wgrExecute(RsBtText, 'SELECT * FROM TEXTE WHERE DATA = ' + Trim(DataNr.ToString));
  54.    RsBtText.Edit;
  55.  
  56.    RsBtWert.FieldByName('Para1').AsInteger := SomeValue;
  57.    RsBtText.FieldByName('Para1').AsString :=SomeValue;
  58.    RsBtWert.FieldByName('Para2').AsInteger := SomeValue;
  59.    RsBtText.FieldByName('Para2').AsString := SomeValue;
  60.  
  61.    RsBtWert.Post;
  62.    RsBtWert.ApplyUpdates;
  63.    TransWGR.CommitRetaining; //--> just to test if inserting in "Werte" Table was successful (it was)
  64.    RsBtText.Post;
  65.    RsBtText.ApplyUpdates; //ERROR
  66.    TransWGR.CommitRetaining;
  67. end;
  68.  

i do get the Error in Line:
-->RsBtText.ApplyUpdates;

i did the same Process with my Second Table (which stores Floats and Ints). Only difference is that this Table is Storing Strings.
Here are the Objects if Needed (Only Listed the Values which are different, all other Values are the identical Except some Adresses but i guess they are always different):

Objects After ".Post" Operation:
Code: Pascal  [Select][+][-]
  1.  
  2. FBLOBFIELDCOUNT = 0, (WERTE)
  3. FBLOBFIELDCOUNT = 50, (TEXTE)
  4.  
  5.  

Objects After ".ApplyUpdate" Operation:
Code: Pascal  [Select][+][-]
  1.  
  2. FBLOBFIELDCOUNT = 0, (WERTE)
  3. FBLOBFIELDCOUNT = 50, (TEXTE)
  4.  
  5. FDISABLECONTROLSCOUNT = 0, (WERTE)
  6. FDISABLECONTROLSCOUNT = 1, (TEXTE)
  7.  
  8. FRECORDSIZE = 411,
  9. FRECORDSIZE = 811,
  10.  
  11. FUPDATEBLOBBUFFERS = $0,
  12. FUPDATEBLOBBUFFERS = $e1d03f8,
  13.  
  14. FINSERTQRY = $ea768b8,
  15. FINSERTQRY = $0,
  16.  
  17.  

Fiels in Database are Text(255) / Integer / Float

Any Help would be Appreciated :) I tried nearly everything and now i'm clueless  :(

Thanks in advice :)
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

GetMem

  • Hero Member
  • *****
  • Posts: 3493
Re: Update/Insert SQLITE
« Reply #1 on: September 07, 2021, 07:45:58 pm »
@Weitentaaal
Quote
Any Help would be Appreciated :) I tried nearly everything and now i'm clueless 

Hard to say...everything looks correct. However RsBtText does not know how to update table TEXTE and ask for an update query(property RsBtText.UpdateSQL), which makes me think that:
Code: Pascal  [Select][+][-]
  1. wgrExecute(RsBtText, 'SELECT * FROM TEXTE WHERE DATA = ' + Trim(DataNr.ToString));
returns more then one value. I also noticed that UpdateMode is upWhereAll, meaning you don't use primary keys. Besides the logical consistency issues, most RDBMS engines will benefit from including these primary keys(unique values, indexes, joins, etc...). Please consider to add primary keys to your table.

Weitentaaal

  • Sr. Member
  • ****
  • Posts: 303
Re: Update/Insert SQLITE
« Reply #2 on: September 08, 2021, 07:42:25 am »
Thats the Point, i can't :D

My my boss does not want to change the Database bc the db is used by many dll's. When i started working here i did not see any Structured Database. i can change the Code but not the Database :(.

However, I fixed it by manually inserting default values for each attribute in my database. There is no primary key or such in the table that affects the insertion.

Thats the Code i added (Worked after inserted this, but "Data" did not have any different Properties (even if it should have them)):
 
RsBtWert.FieldByName('Data').AsInteger := DataNr;
RsBtText.FieldByName('Data').AsInteger := DataNr;

Edit: forgot to mention that "FINSERTQRY = $0" did have a Valid Adress after the Correction.

Thanks @GetMem anyways for your Time, very appreciated :)

« Last Edit: September 08, 2021, 07:44:55 am by Weitentaaal »
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

Zvoni

  • Hero Member
  • *****
  • Posts: 742
Re: Update/Insert SQLITE
« Reply #3 on: September 08, 2021, 08:50:34 am »
Remove Line 63 in your first post?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

Weitentaaal

  • Sr. Member
  • ****
  • Posts: 303
Re: Update/Insert SQLITE
« Reply #4 on: September 08, 2021, 09:19:27 am »
I did, it was just for a Test Case.
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

 

TinyPortal © 2005-2018