* * *

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

Hydexon

  • Full Member
  • ***
  • Posts: 159
    • My Blog (Spanish Only)...
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

GAN

  • Full Member
  • ***
  • Posts: 168
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 1.6 FPC 3.0.0 Linux Mint Mate 17.2 x86_64 GTK-2
Zeos 7.1.3 - Sqlite 3.8.2

Foro Lazarus en español http://forum.lazarus.freepascal.org/index.php/board,73.0.html

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 697
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

Thaddy

  • Hero Member
  • *****
  • Posts: 5987
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 »
I might not give the answer that you want me to.. Peter Green 1969

 

Recent

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