Forum > Databases

[Solved] Debugging SQlite

(1/6) > >>

SymbolicFrank:
I create a long SQL statement, set all the parameters, execute it, and ... nothing happens. Or, when I insert a record that has a "Integer Primary Key Autoincrement", which should be an alias for RowID and should get a number automatically, when you don't insert it or insert "NULL", the value stays "NULL", in both cases.

So, I made a small testapp, to see if it all works as expected. It does. I double-check and triple-check my SQL statements and parameters. They are fine. I execute the statements, and ... noting happens. The tables are still mostly empty or contain many records that are mostly empty. Or the Primary Key is NULL for all records. No errors, though.

I change my insert and update queries and break them up into smaller ones that cannot go wrong. Or so I thought. There is little difference. I start first inserting records that only include the unique values other than the AutoInc primary key. Afterwards I update them and insert all the values. That results in tables filled with records without a primary key and only those other, unique values. No errors.

I log every action and see lots of rollbacks. I start committing each and every individual action immediately. More data enters the database, but most tables still have only NULL-values as AutoInc primary keys. Still no errors.

What more can I do?

I think I'll scrap the whole setup and only use the TSQLQuery datasets. See if that works. I'm not happy with the performance of databases in FPC, while I thought that was what it did best.

Zvoni:
You do know, that SQLite has a function which returns the SQL-Statement AFTER Parameters are set (To check if the Statement makes "sense")?
https://forum.lazarus.freepascal.org/index.php/topic,54225.msg402674.html#msg402674

Show your INSERT-Statement that fails

btw: You should avoid using AUTOINCREMENT --> https://www.sqlite.org/autoinc.html

SymbolicFrank:
Well, I reduced the insert statements to this:


--- Code: ---Commit:   Committing transaction
Prepare:  TQuerySQLStatement : insert or ignore into kenteken (ConversionKey)
values  (:ConversionKey);
Execute:  TQuerySQLStatement : insert or ignore into kenteken (ConversionKey)
values  (:ConversionKey);
Commit:   Committing transaction
--- End code ---

That works, except that all my AutoInc primary keys are NULL.

It is followed by an update statement of 5429 chars, which is just:


--- Code: ---update kenteken set fieldname = :fieldname, ... where ConversionKey = :ConversionKey;
--- End code ---

The log says:


--- Code: ---Commit:   Committing transaction
--- End code ---

And it is, except that my primary key is NULL for all records.

At first, I was using:


--- Code: ---insert into kenteken (<very many fields>)
values (<all the parameters>)
on conflict (ConversionKey) do
update set (Field1 = excluded.Field1, ...)
where ConversionKey = : ConversionKey;
--- End code ---

But that resulted in empty tables. I have tried a few other variants, but this is the one that works best (for a certain value of 'works').

That "ConversionKey" is the primary key from the external data source. It is far easier and faster to add that to the table, but they don't have to be Integers (in this case, they're GUIDs), and I need those integer keys for the rest of the database.

And yes, I do understand that I don't have to use primary keys and autoincrement, or any other constraints and that I could add those values myself, but I would want to have foreign keys and a functional, relational model. It's far easier to have the database enforce integrity and do the lookups (joins), than having to do that all by myself.

Zvoni:
1) I think you misunderstand the way AUTOINC works in SQLite. Read through my Link above
2) What are the properties of your PK-Field? I'm specifically asking for NOT NULL (if you get my drift.....) --> Could you post your CREATE TABLE-Statement?

EDIT: Since you mentioned GUID's: --> https://stackoverflow.com/questions/18954130/can-we-use-guid-as-a-primary-key-in-sqlite-database/23531060

SymbolicFrank:

--- Quote from: Zvoni on January 18, 2022, 03:26:51 pm ---1) I think you misunderstand the way AUTOINC works in SQLite. Read through my Link above
--- End quote ---
I don't understand it. Can you explain?


--- Quote ---2) What are the properties of your PK-Field? I'm specifically asking for NOT NULL (if you get my drift.....) --> Could you post your CREATE TABLE-Statement?
--- End quote ---


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---const  UniqueFields: string = 'kenteken=kenteken'; function Empty(const s: string): Boolean;begin  if Length(Trim(s)) = 0 then Result := True  else Result := False;end; function ExecuteCommand(ThisCommand: string; ThisConnection: TSQLConnection): Boolean;begin  Result := False;   if (not Assigned(ThisConnection)) or Empty(ThisCommand) then Exit;   ThisConnection.ExecuteDirect(ThisCommand);  ThisConnection.Transaction.Commit;  Result := True;end; function FieldToStr(ThisField: TField): string;begin  Result := '';   if not Assigned(ThisField) then Exit;   Result := ThisField.FieldName;   case ThisField.DataType of    ftFixedChar, ftFixedWideChar:      Result := 'char[' + IntToStr(ThisField.Size) + ']';    ftString, ftWideString:      begin        if ThisField.Size <= 50 then          Result := Result + ' char[' + IntToStr(ThisField.Size) + ']'        else          Result := Result + ' varchar[' + IntToStr(ThisField.Size) + ']';      end;    else      Result := Result + ' ' + FieldTypeNames[ThisField.DataType];  end;    if (ThisField.DataType = ftAutoInc) or (ThisField is TAutoIncField) then    Result := Result + ' Primary Key';end; function CreateSQLTable(TableName: string; SourceTable: TDataSet;  ThisConnection: TSQLConnection; AddConversionField: Boolean = False): Boolean;var  q: string;  i: Integer;  f: TField;  PK, UF: TStringList;  s: string;begin  Result := False;   if Empty(TableName) then Exit;  if not Assigned(SourceTable) then Exit;  if SourceTable.Fields.Count = 0 then Exit;   TableName := ChangeFileExt(TableName, '');   if SQLTableExists(TableName, ThisConnection) then    if not ExecuteCommand('drop table ' + TableName + ';', ThisConnection) then      Exit;   q := 'create table ' + TableName + ' (';   for i := 0 to SourceTable.Fields.Count - 1 do  begin    f := SourceTable.Fields[i];    q := q + FieldToStr(f);    if i < SourceTable.Fields.Count - 1 then q := q + ', ';  end;   // Field to keep the key from the exernal datasource  if AddConversionField then q := q + ', ' + ConversionFieldName + ' ' +    ConversionFieldType;   // No AutoInc, use lookup table  if Pos('primary key', LowerCase(q)) < 1 then  begin    PK := TStringList.Create;    PK.Text := PrimaryKeys;    PK.CaseSensitive := False;    s := PK.Values[TableName];    if Length(s) > 0 then q := q + ', Primary Key (' + s + ')';    PK.Free;  end;   // Another lookup table, just a single entry here  UF := TStringList.Create;  UF.Text := UniqueFields;  UF.CaseSensitive := False;  s := UF.Values[TableName];  if not Empty(s) then s := s + ', ';  s := s + ConversionFieldName;  q := q + ', Unique (' + s + ')';  UF.Free;   q := q + ');';   Result := ExecuteCommand(q, ThisConnection);end;
I import other (DBF) tables into the SQL database. The structure should be the same, except with an extra, unique field (that external Key, the GUID). Although the structure depends on the SQlite version, as the older versions only had 4 field types and the newer versions (since a month or so) also have all the other field types you would expect an SQL database to have. Although you can only see that in the release notes and with a tool like SQliteStudio, the documentation hasn't been updated yet.

The resulting primary keys are "Integer Primary Key Autoincrement" in the created table.

Navigation

[0] Message Index

[#] Next page

Go to full version