Lazarus

Programming => Databases => Topic started by: Hydexon on May 01, 2018, 04:16:33 am

Title: TSQLite3Connection table [NAME] has X columns but Y values where supplied
Post by: Hydexon on May 01, 2018, 04:16:33 am

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 (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.

Title: Re: TSQLite3Connection table [NAME] has X columns but Y values where supplied
Post by: GAN on May 01, 2018, 05:49:53 am

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 );';

Title: Re: TSQLite3Connection table [NAME] has X columns but Y values where supplied
Post by: Jurassic Pork on May 01, 2018, 08:41:28 am

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

Title: Re: TSQLite3Connection table [NAME] has X columns but Y values where supplied
Post by: Thaddy on May 01, 2018, 09:14:34 am

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.