Recent

Author Topic: TSQLite3Connection table [NAME] has X columns but Y values where supplied  (Read 3271 times)

Hydexon

  • Full Member
  • ***
  • Posts: 170
After a good while, i decided to return to FreePascal/Lazarus IDE for developing some apps.

So i running an issue, i'm using TSQLQuery.SQL using SQLite3 for my INSERT and UPDATE SQL statements since an TSQLQuery object in my form is shared for many TFrames, but this time i want to create an record from the Main form, the table has no NOT-NULL constraints and you can omit them normally, for example my table looks like this:

Code: SQL  [Select][+][-]
  1. CREATE TABLE MedicalRecord(
  2.         record_id INTEGER UNIQUE NOT NULL PRIMARY KEY AUTOINCREMENT,
  3.         first_name TEXT,
  4.         last_name TEXT,
  5.         Age INTEGER,
  6.         Updated_Record DATETIME,
  7.         Created_Record DATETIME
  8. );
  9.  

As you can see the fields Updated_Record, Created_Record columns are optional, are only used for updating or inserting new records respectively, and record_id field must not can't be updated manually by the app, is something up for the database engine.

I use TSQLQuery.SQL for INSERT and UPDATE statements from the Wiki http://wiki.freepascal.org/Working_With_TSQLQuery#Insert_query_example with this code
Code: Pascal  [Select][+][-]
  1.        
  2. SQLQuery1.SQL.Text := 'INSERT INTO MedicalRecord VALUES(:FIRSTNAME , :LASTNAME , :AGE ,:CREATED_RECORD , :UPDATED_RECORD )';
  3.  
  4.     with SQLQuery1 do begin
  5.       ParamByName('FIRSTNAME').AsString := FirstNameEdit.Text;
  6.       ParamByName('LASTNAME').AsString:= LastNameEdit.Text;
  7.       ParamByName('AGE').AsInteger:= AgeSpinEdit.Value;
  8.       ParamByName('CREATED_RECORD').AsDateTime:= Now;
  9.       ParamByName('UPDATED_RECORD').AsDateTime:= Now;
  10.     end;
  11.  
  12.     SQLQuery1.ExecSQL;
  13.     SQLTransaction1.Commit;  
  14.  

But everytime i try to execute this code SQLite3Connection raises an exception: "SQLite3Connection table Records has 6 columns but 5 values where supplied".
It's complaining why the record_id field (the Primary Key) must be filled in the INSERT statement when shouldn't be.

There's an way to disable this unnecesary check done by SQLite3Connection?, disabling 'AutoCalcFields' and 'ParseSQL' properties from TSQLQuery doesn't help.

(Using the latest stable version of Lazarus, in Windows 10 x64)

Thanks.
« Last Edit: May 05, 2018, 02:27:58 am by Hydexon »
Lazarus 1.0.4 Stable - FPC 2.6.0 - gtk2 | Linux Mint 14 Nadia | GNOME Shell 3.6.2 - Awesome | Intel x86 | HP Mini 1020-LA Netbook (old)
Lazarus 2.0.2 Stable - FPC 3.0.2 - Windows | Lenovo IdeaPad Y

GAN

  • Sr. Member
  • ****
  • Posts: 370
Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text := 'INSERT INTO MedicalRecord (first_name, last_name, Age,
  2. Updated_Record, Created_Record)  
  3. VALUES (:FIRSTNAME , :LASTNAME , :AGE ,:CREATED_RECORD , :UPDATED_RECORD );';
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 1228
hello,
you can put a null value for the  autoincrement id field :
example :
Code: Pascal  [Select][+][-]
  1.  
  2. type
  3.  TMember = record
  4.                 FirstName, LastName : string;
  5.                 Age: integer;
  6.         end;
  7. procedure TForm1.Button1Click(Sender: TObject);
  8. var Members : array[1..5] of TMember =
  9.   (
  10.     (FirstName : 'Homer'; LastName : 'Simpson'; Age : 39 ),
  11.     (FirstName : 'Marge'; LastName : 'Simpson'; Age : 39 ),
  12.     (FirstName : 'Bart'; LastName : 'Simpson'; Age : 10 ),
  13.     (FirstName : 'Lisa'; LastName : 'Simpson'; Age : 8 ),
  14.     (FirstName : 'Maggie'; LastName : 'Simpson'; Age : 2 )
  15.   ) ;
  16.      x: integer;
  17. begin
  18.   SQlite3Conn.Open;
  19.   SQLite3Conn.ExecuteDirect('CREATE TABLE IF NOT EXISTS  MedicalRecord(' +
  20.         'record_id INTEGER UNIQUE NOT NULL PRIMARY KEY AUTOINCREMENT,' +
  21.         'first_name VARCHAR,' +
  22.         'last_name VARCHAR,'  +
  23.         'Age INTEGER,'     +
  24.         'Updated_Record DATETIME,' +
  25.         'Created_Record DATETIME);');
  26.   SQLTransaction1.Commit;
  27.   For x:= 1 to 5 do
  28.   begin
  29.   SQLQuery1.SQL.Text := 'INSERT INTO MedicalRecord VALUES(Null, :FIRSTNAME , :LASTNAME , :AGE ,:CREATED_RECORD , :UPDATED_RECORD )';
  30.       with SQLQuery1 do begin
  31.         ParamByName('FIRSTNAME').AsString := Members[x].FirstName;
  32.         ParamByName('LASTNAME').AsString:= Members[x].LastName;
  33.         ParamByName('AGE').AsInteger:= Members[x].Age;
  34.         ParamByName('CREATED_RECORD').AsDateTime:= Now;
  35.         ParamByName('UPDATED_RECORD').AsDateTime:= Now;
  36.       end;
  37.   SQLQuery1.ExecSQL;
  38.   end;
  39.   SQLTransaction1.Commit;
  40.   SQLQuery1.SQL.Text := 'select * from MedicalRecord';
  41.   SQLQuery1.Open;
  42. end;      
  43.  

Result in attachment

Friendly, J.P
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
It is very dangerous in this day and age to rely on old data - even if they are examples.
The correct ages since April 1987 are of course:
Code: Pascal  [Select][+][-]
  1. var Members : array[1..5] of TMember =
  2.   (
  3.     (FirstName : 'Homer'; LastName : 'Simpson'; Age : 70 ),
  4.     (FirstName : 'Marge'; LastName : 'Simpson'; Age : 70 ),
  5.     (FirstName : 'Bart'; LastName : 'Simpson'; Age : 41 ),
  6.     (FirstName : 'Lisa'; LastName : 'Simpson'; Age : 39 ),
  7.     (FirstName : 'Maggie'; LastName : 'Simpson'; Age : 33 )
  8.   ) ;

I guess there is something wrong with how the age field is maintained, it should auto-update... >:D >:D >:D O:-)
It demonstrates a flaw that is often made: age should be dynamically determined from current date and age at date of entry (e.g. calculated field or  a trigger). Basic programming failure. :D

Even if the record is meant to be entered today, it is still based on old data, which makes it rather useless for targeted advertising.

« Last Edit: May 01, 2018, 09:32:51 am by Thaddy »
Specialize a type, not a var.

 

TinyPortal © 2005-2018