Delphi: Database is locked (SQLite)

Question

I am facing two problems...

(1) When I try to write to a Database (SQLite) by using Delphi XE6, I always get the Database is locked error message. I am certain that I close the database everytime I access it by using the command FDConnection1.Close;

(2) How do I INSERT INTO a table from incoming parameters? I have the following incoming parameters

procedure TStock_Bookkeeping.Write_To_DB(const Stock_Code, Stock_Name,
Tran_Date, Buy_Sell, Price_Per_Share, Num_Shares, Trans_Fee: string);

and tried to write into the table with the following SQL command:

sSQL := 'INSERT INTO Each_Stock_Owned(Stock_Code, Stock_Name, Tran_Date, Buy_Sell,
         Price_Per_Share, Num_Shares, Trans_Fee) 
         VALUES (Stock_Code, Stock_Name, Tran_Date, Buy_Sell, Price_Per_Share,  
         Num_Shares, Trans_Fee)';

but it does not seem to work...

The following is the complete procedure I am having trouble with

procedure TStock_Bookkeeping.Write_To_DB(const Stock_Code, Stock_Name,
  Tran_Date, Buy_Sell, Price_Per_Share, Num_Shares, Trans_Fee: string);
var
  query : TFDQuery;
  sSQL: string;
begin
    query := TFDQuery.Create(nil);
  try
    ConnectToSQLite;
    query.Connection := FDConnection1;
  if Stock_Code.IsEmpty then
    ShowMessage('Stock Code Cannot Be Empty')
    else
      if Stock_Name.IsEmpty then
        ShowMessage('Stock Name Cannot Be Empty')
        else
          if Tran_Date.IsEmpty then
            ShowMessage('Transaction Date Cannot Be Empty')
            else
            begin
//              sSQL := 'INSERT INTO Each_Stock_Owned(Stock_Code, Stock_Name, Tran_Date, Buy_Sell, Price_Per_Share, Num_Shares, Trans_Fee) VALUES (Stock_Code, Stock_Name, Tran_Date, Buy_Sell, Price_Per_Share, Num_Shares, Trans_Fee)';
              sSQL := 'INSERT INTO Each_Stock_Owned(Stock_Code, Stock_Name, Tran_Date, Buy_Sell, Price_Per_Share, Num_Shares, Trans_Fee) VALUES (1,2,3,4,5,6,7)';
              query.sql.Text := sSQL;
              query.ExecSQL;
              query.Open();
        end;
  finally
    query.Close;
    query.DisposeOf;
    DisconnectFromSQLite;
  end;

end;

Any hints will be very appreciated. Thanks in advance.

Is this a desktop application? What platform? Have you checked if the database is somehow opened in the IDE? Do you get the lock if you start the application without the IDE? — Frazz, Jul 31 '14 at 06:59
It is a mobile application but I am testing on Win32 environment. I have just tried to run the application without the IDE, and I get a different error message: Cannot open/define command, which does not return result sets. Hint: use Execute/ExecSQ: method for non-SELECT commands. I will try to see how to debug it. Thanks for your reply. — Alvin Lin, Jul 31 '14 at 07:08
Wow I mistakenly put the query.Open() command. After removing it, the error no longer exists. Thanks a bunch @Frazz — Alvin Lin, Jul 31 '14 at 07:11
But I still have problem on the first question. How do I INSERT INTO database from incoming parameters? Thanks — Alvin Lin, Jul 31 '14 at 07:14
The error "I get is the ERROR: no such column: Stock_Code" But I am certain that the column is created in the table I try to write to — Alvin Lin, Jul 31 '14 at 07:18
I am sorry that I don't quite understand what you mean... Could you please elaborate it a little more? — Alvin Lin, Jul 31 '14 at 07:25
Please update your q title to reflect your actual problem, which evidently has nothing whatever to do with locking. And remove the Locked tag. — MartynA, Jul 31 '14 at 08:13
Two different questions need to be posted as, well, two separate questions. — Jerry Dodge, Jul 31 '14 at 13:38

Frazz · Accepted Answer · 2014-08-01T05:03:26.860

There are two techniques to executing a dynamic SQL statement. But I'll use a shorter SQL, to concentrate on the logic:

The pure way (using parameters)

q.SQL.Text:=
  'INSERT INTO Each_Stock_Owned (Stock_Code, Stock_Name) '+
  'VALUES (:Stock_Code, :Stock_Name)';
q.Prepare; //Optional
q.ParamsByName('Stock_Code').AsString := Stock_Code;
q.ParamsByName('Stock_Name').AsString := Stock_Name;
q.ExecSQL;

The dirty way (building SQL)

q.SQL.Text:=
  'INSERT INTO Each_Stock_Owned (Stock_Code, Stock_Name) VALUES ('+
  QuotedStr(Stock_Code) + ', '+
  QuotedStr(Stock_Name) + ')';
q.ExecSQL;

The differences are significant. The dirty way exposes you to SQL injection problems (as in most other languages, when you build SQL dinamically but without parameters). This could be or not be a problem for you. If you know that the procedure is only called privately by your own code, and that those procedure parameter values can only contain good values... or if you do some good parameter checking before building and executing your SQL... then you are safe.

But if you do it with parameters (the pure way) you are automatically protected from SQL injection, as the SQL statement is validated by the engine, without knowing the parameter values. So the SQL statement structure is known by the engine and cannot be altered by the actual values.

Another consideration is how frequently you will execute that INSERT statement. The pure way allows you to prepare the query ONCE, and execute it MANY TIMES with different parameter values (you must not destroy the query object, nor change the SQL property, and you must call the Prepare method once). If you run it frequently within a loop then it can be more efficient than building the SQL many times the dirty way. OTOH if you just need to insert one single row, it may pose a bit more overhead.

=================

As an aside... CL is right... those values should not be strings. Keep in mind that the Parameter object has many properties to handle different data types:

  q.ParamsByName('somedate').AsDateTime := Date;
  q.ParamsByName('somenumeric').AsFloat := 3/4;

... and so on.

If you don't use parameters, then things get difficult. The QuoteStr function is good for strings, but if you want to burn dates and currencies and other value types directly in your SQL you have to know what you are doing. You may encounter many different problems... locale specific or format settings that are not good for comunicating with your server, which may be on the opposite end of the world, or may just not be able to read values formatted that way. You may have to handle engine specific formatting and conversion problems.

If you do use parameters, then FireDAC should take care of all this for you ;)

Thank you very much for your reply. I am really bad at working with parameters so I changed everything to strings lol I will read some basic training materials on this topic. — Alvin Lin, Jul 31 '14 at 08:00
@AlvinLin, please do learn this and avoid the trap many have fallen in before you, please do use parameters, it is the only correct way! — whosrdaddy, Jul 31 '14 at 17:25
@whosrdaddy The problem is that the parameter way violates the language/API design guideline of "the easiest way to do something should be the correct way to do something". If you've got 10 parameters the code (and repetition) to execute the query becomes ridiculous, which is why people take shortcuts with building strings. I believe FireDAC solves this problem and lets you simply pass all the values as a parameter during query setup without all the "ParamsByName...AsWhatever". It keeps things to one line of code. XE6 should come with FireDAC SQL support. — alcalde, Jul 31 '14 at 19:36
@alcalde I absolutely disagree. First... both techniques are or can get very complex. Second... they can both be encapsulated to hide complexity. If FireDAC doesn't do that, then you can. You can hide most of this within a function or an object... completely separating the WHAT you want to do (which is the caller's responsibility) from the HOW (params or stringbuilding, which is the implementation's responsibility). I did this since the Delphi 5 BDE days... with my own TDatabaseHandler class... which had methods for both parameters and stringbuilding ;) — Frazz, Aug 01 '14 at 05:13
To guild the lilly a bit, the prepare statement consumes resources that may be already covered in the cache. Try doing: if not(q.prepared) then q.prepare(); — Michael, Aug 18 '21 at 15:34

CL. · Answer 2 · 2014-07-31T08:33:29.040

1

To get values into the query, use parameters (this is explained the documentation):

query.SQL.Text := 'INSERT INTO Each_Stock_Owned'+
                    '(Stock_Code, Stock_Name, Tran_Date, Buy_Sell, '+
                     'Price_Per_Share, Num_Shares, Trans_Fee) '+
                    'VALUES (:sc, :sn, :td, :bs, :pps, :ns, :tf)';
query.ParamByName('sc').AsString := Stock_Code;
query.ParamByName('sn').AsString := Stock_Name;
query.ParamByName('td').AsString := Tran_Date;
query.ParamByName('bs').AsString := Buy_Sell;
query.ParamByName('pps').AsString := Price_Per_Share;
query.ParamByName('ns').AsString := Num_Shares;
query.ParamByName('tf').AsString := Trans_Fee;
query.ExecSQL;

(And I doubt that all these values really should be strings …)

edited Jul 31 '14 at 08:33

answered Jul 31 '14 at 07:28

CL.

173,858
17
217
259

Thank you for teaching me. By the way, I think we should use () instead of [] in ParamByName right? It now compiles but hows another error saying that Parameter[STOCK_CODE] data type is unknown. I think it is because I changed everything to strings? I will try to debug. Thank you again – Alvin Lin Jul 31 '14 at 08:03
This answer will run into 'database locked'... Add LockingMode=Normal to your connection definition parameters and use transactions. Otherwise.... – user763539 Feb 07 '15 at 13:54

Delphi: Database is locked (SQLite)

2 Answers2

The pure way (using parameters)

The dirty way (building SQL)