Declare variable in SQLite and use it

Question

I want to declare a variable in SQLite and use it in insert operation   Like in MS SQL   declare  name as varchar 10  set name    name  select   from table where name    name   For example  I will need to get last insert row and use it in insert   I have found something about binding but I didn t really fully understood it

User · Answer

I appreciate that the other solutions do not depend on any other software tool  but why not just use another programming language that can interface to SQLite such as C   C    Go  Haskell  Java  Lua  Python  or Rust

User · Answer

Herman s solution worked for me  but the     had me mixed up for a bit  I m including the demo I worked up based on his answer  The additional features in my answer include foreign key support  auto incrementing keys  and use of the last insert rowid   function to get the last auto generated key in a transaction   My need for this information came up when I hit a transaction that required three foreign keys but I could only get the last one with last insert rowid     PRAGMA foreign keys   ON    -- sqlite foreign key support is off by default PRAGMA temp store   2       -- store temp table in memory  not on disk  CREATE TABLE Foo      Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL     CREATE TABLE Bar      Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL      FOREIGN KEY Thing2  REFERENCES Foo Thing1      BEGIN TRANSACTION   CREATE TEMP TABLE  Variables Key TEXT  Value INTEGER    INSERT INTO Foo Thing1  VALUES 2    INSERT INTO  Variables Key  Value  VALUES  FooThing   last insert rowid      INSERT INTO Bar Thing2  VALUES  SELECT Value FROM  Variables WHERE Key    FooThing      DROP TABLE  Variables   END TRANSACTION

User · Answer

SQLite doesn t support native variable syntax  but you can achieve virtually the same using an in-memory temp table  I ve used the below approach for large projects and works like a charm     Create in-memory temp table for variables    BEGIN   PRAGMA temp store   2  CREATE TEMP TABLE  Variables Name TEXT PRIMARY KEY  RealValue REAL  IntegerValue INTEGER  BlobValue BLOB  TextValue TEXT       Declaring a variable    INSERT INTO  Variables  Name  VALUES   VariableName        Assigning a variable  pick the right storage class     UPDATE  Variables SET IntegerValue       WHERE Name    VariableName       Getting variable value  use within expression          SELECT coalesce RealValue  IntegerValue  BlobValue  TextValue  FROM  Variables WHERE Name    VariableName  LIMIT 1       DROP TABLE  Variables  END

User · Answer

For a read-only variable  that is  a constant value set once and used anywhere in the query   use a Common Table Expression  CTE    WITH const AS  SELECT  name  AS name  10 AS more  SELECT table cost   table cost   const more  AS newCost FROM table  const  WHERE table name   const name   SQLite WITH clause

User · Answer

I found one solution for assign variables to COLUMN or TABLE  conn   sqlite3 connect  database db   cursor conn cursor   z  quot Cash payers quot      bring results from Table 1   Column  Customers and COLUMN    which are pays cash sorgu y  Customers  Column name query1  quot SELECT    FROM  Table 1 WHERE  quot   sorgu y   quot  LIKE    quot  print  query1  query  query1  cursor execute query  z     Don t forget input one space between the WHERE and double quotes and between the double quotes and LIKE

User · Answer

Try using Binding Values  You cannot use variables as you do in T-SQL but you can use  parameters   I hope the following link is usefull Binding Values

User · Answer

Herman s solution works  but it can be simplified because Sqlite allows to store any value type on any field   Here is a simpler version that uses one Value field declared as TEXT to store any value   CREATE TEMP TABLE IF NOT EXISTS Variables  Name TEXT PRIMARY KEY  Value TEXT    INSERT OR REPLACE INTO Variables VALUES   VarStr    Val1    INSERT OR REPLACE INTO Variables VALUES   VarInt   123   INSERT OR REPLACE INTO Variables VALUES   VarBlob   x 12345678     SELECT Value   FROM Variables  WHERE Name    VarStr  UNION ALL SELECT Value   FROM Variables  WHERE Name    VarInt  UNION ALL SELECT Value   FROM Variables  WHERE Name    VarBlob

[sql] Declare variable in SQLite and use it

Examples related to sql

Examples related to sqlite

Examples related to variables

Examples related to declaration